SQL SERVER – Generate Incremented Linear Number Sequence

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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Sharpen Your Skills: Joins, Groupings, and Data Types
Next Post
SQL SERVER – Shrinking Truncate Log File – Log Full – Part 2

Related Posts

38 Comments. Leave new

  • 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.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • I have tested out: UPDATE order_sequence SET id = id + 1, @id = id + 1 and it is working in generating a sequence.

    Reply
  • 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

    Reply
  • Hi Pinal,
    Can you share your input and thoughts on this Database Document generator. Here is the sample document

    Thanks
    Nilesh

    Reply
  • kulwant singh
    August 16, 2010 1:42 pm

    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?

    Reply
  • luis martinez
    January 17, 2011 8:42 am

    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

    Reply
    • It’s great and simple for updating a column with incremental values. Thanks.

      Reply
    • Vivekananda
      May 10, 2013 10:59 am

      Hi, i am having more than 6,00,000 records in my table with Id as default to -1. Now when i am using the above statement in Sql Server 2005, the table is getting updated properly with sequence numbers. But in 2012, i am getting each id getting duplicated 2 to 32 times which is very strange to me. Please advise if i need to use any SET parameters or any other solution. Note that the statement is wiorking in the table in 2012 which is having 2,00,000 records. Please copy to my mail id (r.vivek1982@gmail.com)

      Reply
  • kzkataria@gmail.com
    January 24, 2011 11:08 pm

    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

    Reply
    • Refer method 2

      Reply
    • — 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

      Reply
  • Paul Kramer
    June 18, 2011 1:24 am

    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)

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
    • 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

      Reply
  • 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

    Reply
  • Hi All,
    Need help to generate sequence number in SQL Server 2008, for below condition ASAP please.
    1. I am importing data from Excel to SQL Server 2008, which always has 10 rows.
    2. Whenever I import this data, the row should get a sequence number tagged to it. Let’s say a column with Tag_id.

    Reply
  • Hi
    SELECT ROW_NUMBER() OVER (order by name ),* FROM SYS.OBJECTS

    Design query without Partition by it will give sequence number if no repeated values in a column..

    [personal information removed]

    Reply
  • Andrea Tani
    May 25, 2012 7:36 pm

    Tested only in SQL Server

    SELECT number
    FROM master..spt_values
    WHERE Type = ‘P’
    AND number BETWEEN 1 AND 100
    ORDER BY number

    Unfortunately it’s limited from 0 to 2047

    Reply
  • Hi All,

    Can anyone please help me on generating the Sequence number as the below format

    I have a table that contains 1 to 40 sequence numbers
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12……
    20
    21……30
    31……40

    I need to generate the output based on the above values as
    if
    1 then 01
    2 then 02
    ……
    9 then 09

    10 then A1
    11 then A2
    12 then A3…..
    19 then A9

    20 then B1
    21 then B2….
    29 then B3….

    30 then C1
    31 then C2…..
    39 then C3

    40 then D1

    For every set of 9 records the output should be alphabet + the corresponding Seq no.

    Please can anyone answer this..

    thanks in advance

    Reply
  • How can i connect this to my application[asp.net c#] and with sql table.

    Thanks in advance

    Reply
  • Hi Pinal,

    Reply
    • Vivekananda
      May 10, 2013 11:01 am

      Hi Pinal,

      DECLARE @id NUMERIC(19,0)
      SET @id = 0
      UPDATE stage.deal_detail_1
      SET @id = id = @id + 1

      I am having more than 6,00,000 records in the above table with Id as default to -1. Now when i am using the above statement in Sql Server 2005, the table is getting updated properly with sequence numbers. But in 2012, i am getting each id getting duplicated 2 to 32 times which is very strange to me. Please advise if i need to use any SET parameters or any other solution. Note that the statement is wiorking in the table in 2012 which is having 2,00,000 records. Please copy to my mail id (r.vivek1982@gmail.com)

      Reply
      • Hi, Pinal,
        It’s always very helpful to read your Notes and discussion around them.

        Unfortunately, we got very similar situation as Vivekananda with ID duplicated in SQL Server 2012 SP1 when we used clause SET @id = id = @id + 1, .though that operation works perfectly fine in SQL Server 2008 R2. Do you know what happened to the feature in SQL Server 2012.

        Thanks in advance

Leave a Reply Cancel reply

Exit mobile version