Just a day ago, I received interesting question on this blog. Read original question here. This is very good question and after reading this question I quickly wrote small script as answer. Let us see the question and answer together.
Q. How can we generate incremented linear number in sql server as in oracle we generate in via sequence?
1
2
3
4
5
6
7
8
9
A. Run following script which will generate output in sequence, which will generate output as you requested.
DECLARE @idt INT
SET @idt = 0
WHILE (@idt < 100)
BEGIN
SELECT @idt = @idt + 1
PRINT @idt
END
Reference : Pinal Dave (http://blog.SQLAuthority.com)
I doubt this is the answer your question asker was looking for.
Most likely the asker wanted to know how to do this in a table. in a table, you would use an identity column, not a while loop.
If this question is about creating a “sequence” in MS SQL – I’ve wanted this functionality as well and came up with the following solution.
Here’s what I did…
Create a table with one column, I chose “id” as an “int” type.
Create a stored procedure, mine was “reserve_ordernumber” and was short and sweet.
DECLARE @id int
UPDATE order_sequence SET id = id + 1, @id = id + 1
SELECT @id AS ‘id’
Voila! – You can then call the procedure and have it return a unique integer from a linear number sequence.
If stored procedures aren’t your thing, you could really do this a number of ways by mutating things here and there. (e.g. trigger (instead-of), scalar value function, etc…)
Hope that helps.
Hi
Is it possible to store in a single variable like as below
1,2,3,4 and so on. Output can be shown in this format.
I have tested out: UPDATE order_sequence SET id = id + 1, @id = id + 1 and it is working in generating a sequence.
I also have this problem to generate serial number for rows returned by an sql query. I have done this and it solved my problem.
SELECT (ROW_NUMBER() OVER ( ORDER BY fieldname)) AS ID from tablename
Dear Steena,
I tried this statement and it worked. Thanks a lot. Keep Posting.
Hi Pinal,
Can you share your input and thoughts on this Database Document generator. Here is the sample document
http://www.sqldocumentor.com/sqlserverhtml/sql_server_index.html
Thanks
Nilesh
In a multiuser environment, eg a Departmental store where there are around 20 computers running simultaneously, i need to generate a Primary key for the Invoice Numbers.
The Invoice Number must be unique, in a serial and skipping / missing / leaving out a Number is not acceptable.
How can this Primary Key be acquired in SQL. Is it Possible?
SQL Server 2005: The Update Statement did not work for me as is, this worked:
DECLARE @id int
set @id = 0
UPDATE mytable SET id = @id, @id = @id + 1
I need to generate a sequence number for groups of information on a table.
For example, I have a student table with multiple rows for a student, as below –
Student Subject
A English
A Mathematics
A Science
B Economics
B Acturian science
C Mathematics
I need a seq-no to be appended to each of the above records such that the sequence number resets once the student changes –
Student Subject Generated seq-no
A English 0
A Mathematics 1
A Science 2
B Economics 0
B Acturian science 1
C Mathematics 0
In short, I’m looking for a sequence genrator that works with a group by sort of a thing.
Is there a way to do this using SQL
Refer method 2
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
select top 25 partition_key, ROW_NUMBER() Over(Order By getdate())-1 as slno from
— I think this will help you
create table tblSequence
(
Id int identity(1,1) primary key,
Sname varchar(50),
subject_Name varchar(50)
)
insert into tblSequence (Sname,Subject_Name)
SELECT ‘C’,'English’ union all
SELECT ‘A’,'Mathematics’ union all
SELECT ‘A’,'Science’ union all
SELECT ‘B’,'Econamics’ union all
SELECT ‘B’,'Acturian science’ union all
SELECT ‘B’,'Mathematics’ union all
SELECT ‘C’,'Mathematics’
select SName,Subject_Name ,
Row_Number() over(partition by sname order by id) as RowNumber
from tblSequence
We were looking for a dynamic solution resulting in a table with just the sequential counter. Here is the resulting user defined function that returns a table that you can use in-line with a SELECT statement.
CREATE FUNCTION SequenceTable (@startNum INT, @endNum INT)
RETURNS @tmptbl TABLE ( counter INT )
AS
BEGIN
WHILE (@startNum <= @endNum)
BEGIN
INSERT INTO @tmptbl SELECT @startNum
SELECT @startNum = @startNum + 1
END
RETURN
END
Now you can use it to dynamically return a sequence of numbers:
SELECT * FROM SequenceTable (0, 30)
This was usefully for our purposes to return the next 30 days for a report (to be LEFT JOIN'ed to the data table which might not contain values for each day)
SELECT DATEADD(d, counter, GetDate()) AS SeqDate FROM SequenceTable (0, 30)
how to increase the class,
i.e 6th class-01-02-2011; now
7th class 01-02-2012; how to write a sequence query for in sql server 2008
SELECT ISNULL(MAX(ColumnName),0) + 1 as AliasColumnName FROM TableName
or More Complicated one with String + Numeric generating IDs:
select ‘SNO’ + REPLICATE(’0′,5-len(cast(isnull(max(cast(right(ColumnName,5) as int)),0) + 1 as varchar(20)))) + cast(isnull(max(cast(right(ColumnName,5) as int)),0) + 1 as varchar(20)) As AliasColumnName from TableName
Hi Pinal and other SQL Server Kings,
I am trying to generate sequence number with following condition.Can you please help me out.
1.Single Select Query
2.Not to use row number function
Thanks for help in advance….
Kamesh
Why do you need this? You can use a co-related subquery if the table has a unique column but that would be expensive in time
Hi,
We can use cte for this:
below code will generate 1to 50 numbers.
WITH cte
AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM cte WHERE n < 50
)
SELECT * FROM cte