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

  • Ghulam Mustafa
    May 28, 2013 9:18 pm

    Hi All,
    Can anyone please help me on generating a range from the following data.
    I have a table that contains following values
    1
    2
    3
    5
    7
    8
    10

    I want them as:
    From To
    1 3
    5 5
    7 8
    10 10

    Thanks in advance.

    Reply
  • Hi
    In MS Sql i need a SP to generate a sequence number starting from 1 till the End of the day 23:59:59 and when the clock starts 24:00:00(next day) it should again start with 1 and repeat.
    Pls help!!

    Thanks

    Reply
  • Hi All,

    You can also check this article that discusses how to generate unique numbers at:

    Regards,

    Doron
    The Farber Consulting Group, Inc.
    [Link Removed as it is domain of earlier link]

    Reply
  • one of these work in a recursive CTE Query. I desperately need to sequentially number a recursive query. Any reasonably fast method will do.

    Reply
  • i want to change a column value in sql table after date has been changed.
    ex:
    ID Date DueDays
    — ———– —————–
    1 15/06/2014 1
    2 16/06/2014 2
    3 17/06/2014 3

    any suggestions ?

    Reply
  • Hi Dave,

    I was doing exactly this today and I thought of another great way to do this using the simplest possible recursive CTE.

    ;WITH Num_Cons AS
    (
    SELECT 1 AS Num
    UNION ALL
    SELECT Num + 1 AS Num
    FROM Num_Cons
    WHERE Num < 100
    )
    SELECT * FROM Num_Cons
    option (MAXRECURSION 0)

    One advantage of this is that you will not need to use a loop which can become costly when your dataset becomes large, I processed 1 million consecutive numbers this way in 8 seconds versus 18 seconds the loop way.

    Another advantage is that you have all the results in a tabular format which saves having to create a table and insert them.

    Reply
  • I want to create a procedure which would create a serial number using a stored procedure.

    I have three tables:

    Table 1:

    create table ItemTypes
    (
    ItemTypeID int not null primary key,
    ItemType varchar(30)
    )

    Table 2:

    create table ItemBatchNumber
    (
    ItemBatchNumberID int not null primary key,
    ItemBatchNumber varchar(20),
    ItemType varchar(30),
    )

    Table 3:

    create table ItemMfg
    (
    ManufactureID int not null primary key,
    ItemBatchNumberID int foreign key references ItemBatchNumber(ItemBatchNumberID),
    MfgDate datetime
    )

    For each Item Type there are several Item batch number.

    Now, first 3 digit of serial no is xyz. 4 digit of serial no should be Item Type(e.g if Item type is ‘b’ then serial no should be xyzb).

    5 digit of serial no should be like this:

    In a day, for first Item batch number of a Item type, 5th digit should be 1 and it will remain 1 for that day.For the next different Item batch number it should be 2 and it will remain 2 for that day.
    Same rule applied for next day and other item type.

    e.g suppose ‘b’ Item Type has 3 Item batch number WB101,WB110,WB231. If today someone select WB110(Item batch number) of ‘b’ Item Type first then Serial No should be xyzb1 and it will remain xyzb1 for today for WB110. Now if someone select WR101 next then Serial No should be xyzb2 for today. Tomorrow which Item batch number of ‘b’ Item type will be selected first, for that batch number and that type serial no should be xyzb1. Same rule applied for other item type.
    I have tried till now:

    Create Procedure SerialNumber
    (
    @ItemType varchar(30),
    @ItemBatchNumber varchar(20)
    )
    As
    Begin
    Select
    ‘serial No is: xyz’+” + @ItemType + ” +
    (Select cast(Row_Number() over (order by ItemBatchNumber) as varchar(5))
    from ItemBatchNumber
    where ItemBatchNumber = @ItemBatchNumber
    and ItemType = @ItemType
    from
    ItemTypes
    where
    ItemTypes.ItemType = @ItemType
    end

    I am new to SQL. 4rth digit of SN is generated correctly from my code.Please give me some idea on generating 5th digit and let me know if you have any doubt. Thanks in advance.

    Reply

Leave a Reply

Menu