SQL SERVER – Add Identity Column to Table Based on Order of Another Column

After reading my earlier article on Identity Column, I received a very interesting question. The reason, I like to call it interesting is though, I have provided answers to him, I believe there should be another better alternative to this problem. Let us see the question first in his own words.

“Hi Pinal,

I already have existing table and the table already have fewer columns. The table does not have identity column and I would like to add an identity column to this table. The problem is that every time when I try to add an identity column to the table, it adds the value based on the default order of the table. I would like to add the identity value based on the order sequence of another column from the table. Do you have any alternative to it. To illustrate my problem here is the simple script based on my table schema. My table also do not have any index as of now.

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
GO
-- Insert Data
INSERT INTO TestTable (Col1, Col2)
VALUES (33, 'Pinal');
INSERT INTO TestTable (Col1, Col2)
VALUES (22, 'Nupur');
INSERT INTO TestTable (Col1, Col2)
VALUES (11, 'Shaivi');
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Add Identity Column
ALTER TABLE TestTable
ADD ID INT IDENTITY(1, 1)
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Clean up
DROP TABLE TestTable
GO

Solarwinds

Here is the result set of the query above. Currently the result is ordered by the column Col1 DESC but ideally I would like to get the result ordered by Col1 but in ASC order.

SQL SERVER - Add Identity Column to Table Based on Order of Another Column identity-order

Is there any workaround to do the same?”

As I said I find this question very interesting and I was able to come up with the solution as well fairly quickly as the user had not created an index on the table. I quickly created clustered index in ASC order on Col1 and it ordered the table as expected and later added the identity column there. Let us see the script to get the desired result.

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
GO
-- Insert Data
INSERT INTO TestTable (Col1, Col2)
VALUES (33, 'Pinal');
INSERT INTO TestTable (Col1, Col2)
VALUES (22, 'Nupur');
INSERT INTO TestTable (Col1, Col2)
VALUES (11, 'Shaivi');
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Create Clustered Index on Column ID
CREATE CLUSTERED INDEX IX_TestTable ON dbo.TestTable
(Col1 ASC)
GO
-- Add Identity Column
ALTER TABLE TestTable
ADD ID INT IDENTITY(1, 1)
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Clean up
DROP TABLE TestTable
GO

The above script will produce the following answer which user is expecting:

SQL SERVER - Add Identity Column to Table Based on Order of Another Column identity-order1

Now here is my question back to, this was fairly simple for me to do as there was no index created on the table. I was able to create clustered index on the column and get the desired result. However, what will be the alternative solution to this question if the clustered index is already created on the table and there was no option to modify the same. Another alternative solution would be to drop the table and do processing but that is never a good solution as well, it is not possible if there are foreign keys exists on the table.

What would you do in the situation mentioned above?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Puzzle SET ANSI_NULLS and Resultset – SQL in Sixty Seconds #052
Next Post
SQLAuthority News – New Theme of SQLAuthority and Video Courses

Related Posts

30 Comments. Leave new

  • Shekhar Teke, Sr DBA, Talkingtech Limited, NZ
    May 30, 2013 7:53 am

    CREATE VIEW vw_TestTable
    AS
    SELECT ROW_NUMBER() OVER (ORDER BY Col1) AS Id, Col1, Col2 FROM TestTable

    SELECT * FROM vw_TestTable

    Reply
  • Very good and straightforward solution.
    Thanks Pinal

    Reply
  • rudrashiskaran
    May 31, 2013 1:02 am

    SELECT ROW_NUMBER()over (order by Col1 asc)ID,Col1,Col2
    FROM TestTable
    GO

    Reply
  • — Create Table
    CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
    GO
    — Insert Data
    INSERT INTO TestTable (Col1, Col2)
    VALUES (33, ‘Pinal’);
    INSERT INTO TestTable (Col1, Col2)
    VALUES (22, ‘Nupur’);
    INSERT INTO TestTable (Col1, Col2)
    VALUES (11, ‘Shaivi’);
    GO
    — Select Data
    SELECT *
    FROM TestTable
    GO

    select * into #TempTable
    from TestTable

    Alter Table #TempTable
    ADD ID INT NULL
    GO

    update x
    set ID = x.OrderByRow
    from (select ROW_NUMBER()over (order by Col1 asc) as OrderByRow, * from #TempTable) as x

    truncate table TestTable

    — Add Identity Column
    ALTER TABLE TestTable
    ADD ID INT IDENTITY(1, 1)
    GO

    set identity_insert TestTable on
    insert into TestTable (Col1, Col2, ID)
    select Col1, Col2, ID
    from #TempTable

    set identity_insert TestTable off

    — Select Data
    SELECT *
    FROM TestTable
    GO
    — Clean up
    DROP TABLE TestTable
    GO

    drop table #TempTable

    Reply
  • Is there any documentation that the IDENTITY value is calculated from the clustered index key? i.e. what if SQL Server can initialise an IDENTITY value in parallel (say if the clustered index key is partitioned into files and SQL Server initialises each partition concurrently).

    Reply
    • All I can find is that, on MSDN Blogs, you’re guaranteed to have control over the sequence generated if you use INSERT INTO … ORDER BY

      So it would seem like the most future-proof solution is to recreate the table.

      Reply
  • — This assumes the Identity column starts at 1 and is incremented by 1 —

    USE [tempdb]
    GO

    IF EXISTS ( SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]’)
    AND type IN ( N’U’ ) )
    DROP TABLE [dbo].[TestTable]
    GO

    USE [tempdb]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[TestTable]
    ( [Col1] [int] NULL
    , [Col2] [varchar](100) NULL
    , [Identity] [bigint] NULL
    CONSTRAINT Ident DEFAULT 0 )
    ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    — Insert Data
    INSERT INTO TestTable
    ( Col1 ,Col2 )
    VALUES ( 88 ,’Marc’ );
    INSERT INTO TestTable
    ( Col1 ,Col2 )
    VALUES ( 77 ,’Mike’ );
    INSERT INTO TestTable
    ( Col1 ,Col2 )
    VALUES ( 55 ,’Dave’ );
    INSERT INTO TestTable
    ( Col1 ,Col2 )
    VALUES ( 66 ,’Mark’ );
    INSERT INTO TestTable
    ( Col1 ,Col2 )
    VALUES ( 44 ,’Frank’ );
    INSERT INTO TestTable
    ( Col1 ,Col2 )
    VALUES ( 33 ,’John’ );
    INSERT INTO TestTable
    ( Col1 ,Col2 )
    VALUES ( 22 ,’Bob’ );
    INSERT INTO TestTable
    ( Col1 ,Col2 )
    VALUES ( 11 ,’Bill’ );
    INSERT INTO TestTable
    ( Col1 ,Col2 )
    VALUES ( 99 ,’Tom’ );
    INSERT INTO TestTable
    ( Col1 ,Col2 )
    VALUES ( 110 ,’Hank’ );
    GO
    — Select Data
    SELECT *
    FROM TestTable
    GO

    DECLARE @Rowcount AS BIGINT
    DECLARE @CurrentRow AS BIGINT = 1
    SELECT @Rowcount = COUNT_BIG(Col1)
    FROM [tempdb].[dbo].TestTable;

    WHILE @CurrentRow <= @Rowcount
    BEGIN
    UPDATE dbo.TestTable
    SET [identity] = @CurrentRow
    WHERE Col1 IN ( SELECT TOP 1 Col1
    FROM dbo.TestTable
    WHERE [identity] < 1
    ORDER BY Col1 ASC )
    SET @CurrentRow = @CurrentRow + 1;
    END

    SELECT *
    FROM [tempdb].[dbo].[TestTable]
    ORDER BY [IDENTITY] ASC;

    Reply
  • atulbansalsqll
    June 3, 2013 10:28 am

    declare a primary key on the base table
    create a temp table taking the primary key of above table and column on which you want to order. add a new column to this table i.e. ID
    update the ID column with identity value based on whatever column and order (ASC, DESC) you want
    update the base table with this temp table (based on primary key)

    Reply
  • Does this solution – adding a clustered index and then an identity field – work in SQL 2012? I had heard it does not and we are changing our code to use ROWNUMBER()

    Reply
  • Alternatively, don’t. This is not what an identity column is for. If you are going to use an identity column, you should never expect it to run in perfect sequence with another column in your table.

    Too much work to set up;t oo much work to maintain — especially when the next user comes along and inserts (’00’, ‘Fred’) and gets identity #4 instead of identity #0.

    Use the ROW_NUMBER function with a decent ORDER BY (as suggested by others), if you need a correct, dense sequence over a ordered set of data — that’s what it is for.

    Reply
  • Add NONCLUSTERED Index on Col1,
    USE tempdb
    GO
    — Create Table
    CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
    GO
    — Insert Data
    INSERT INTO TestTable (Col1, Col2)
    VALUES (33, ‘1 Pinal’);
    INSERT INTO TestTable (Col1, Col2)
    VALUES (22, ‘2 Nupur’);
    INSERT INTO TestTable (Col1, Col2)
    VALUES (11, ‘3 Shaivi’);
    GO
    — Select Data
    SELECT *
    FROM TestTable
    GO
    — Create Clustered Index on Col2
    CREATE CLUSTERED INDEX IX_TestTable ON dbo.TestTable
    (Col2 ASC)
    GO

    SELECT *
    FROM TestTable

    — Create NONCLUSTERED Index on Col1
    CREATE NONCLUSTERED INDEX NX_TestTable ON dbo.TestTable
    (Col1 ASC)
    GO

    — Add Identity Column
    ALTER TABLE TestTable
    ADD ID INT IDENTITY(1, 1)
    GO
    — Select Data
    SELECT *
    FROM TestTable
    GO

    — Clean up
    DROP TABLE TestTable
    GO

    Reply
    • One Possible option would be (would be b’coz not tested)-
      1. Add the Identity Column to the columns & let is generate the values in any sequence.
      2. SET IDENTITY INSERT ON
      3. Fire an UPDATE query on the Table and update the newly added column using ROWNUM() OVER(ORDER BY {Required Sequence})
      4. SET IDENTITY INSERT OFF

      Hope, this helps!

      Reply
      • developer learn999
        November 21, 2019 2:01 am

        i tried it and it’s not possible on SQL2012 :
        Cannot update identity column ‘ID’.

  • can we recover data from database if we don’t have any backup and recover strategy in sql server 2012

    Reply
  • Hello Sir,
    I m creating a table with 1 identity column and 1 unique column.But when we insert a duplicate values in table then it show error as we are expected,But identity column incremented by 1.which is not showing.Our Question is Why Identity column increase?
    Example :-
    create table test (
    id int identity( 1,1) ,
    name varchar(20) not null unique,
    description varchar(100)
    )

    First insert command :-
    insert into test
    values(‘Hello’,’India’)

    again run same command
    insert into test
    values(‘Hello’,’India’)

    error which is expected
    Msg 2627, Level 14, State 1, Line 1
    Violation of UNIQUE KEY constraint ‘UQ__test__72E12F1B1571C0B7’. Cannot insert duplicate key in object ‘dbo.test’. The duplicate key value is (Hello).
    The statement has been terminated.

    insert into test
    values(‘Hello Kalyan’,’India’)
    then ouput is

    id name description
    1 Hello India
    3 Hello Kalyan India

    why 2 is missing in table?

    Please Help.

    Reply
  • I need help please to write SQL Script to do the following: I have two tables

    Address table Id Street City State Zip

    Brand Table Id Name Website AddressId

    I have the Data in flat Excel sheet now and I’m going to use the ETL process to fill these tables because they are 1000 rows

    How can I sill the AddressId field in the Brand table please?

    Reply
  • drop table testtable
    drop table #TempTable
    — Create Table
    CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
    GO
    — Insert Data
    INSERT INTO TestTable (Col1, Col2)
    VALUES (33, ‘Pinal’);
    INSERT INTO TestTable (Col1, Col2)
    VALUES (22, ‘Nupur’);
    INSERT INTO TestTable (Col1, Col2)
    VALUES (11, ‘Shaivi’);
    GO

    SELECT *
    FROM TestTable
    GO

    select *,id = identity(int,1,1) into #TempTable
    from TestTable
    order by col1

    Reply
  • drop table testtable
    drop table #temptable
    — Create Table
    CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
    GO
    — Insert Data
    INSERT INTO TestTable (Col1, Col2)
    VALUES (33, ‘Pinal’);
    INSERT INTO TestTable (Col1, Col2)
    VALUES (22, ‘Nupur’);
    INSERT INTO TestTable (Col1, Col2)
    VALUES (11, ‘Shaivi’);
    GO
    — Select Data
    SELECT *
    FROM TestTable
    GO

    select * into #TempTable
    from TestTable

    Alter Table TestTable
    ADD ID INT NULL
    GO

    update x
    set ID = x.OrderByRow
    from (select ROW_NUMBER()over (order by Col1 asc) as OrderByRow, * from TestTable) as x

    Reply
  • bravo very good i use that for my real project

    Reply
  • This is a great solution, thank you!

    Reply
  • Is it possible to filling in the blanks and skip if number already exists?

    Example:
    [NewNumber]
    904969
    904984
    NULL
    904999
    904998
    NULL
    905142
    NULL
    905141
    NULL
    905249
    NULL
    NULL
    905250
    905395
    NULL

    Id like [NewNumber] to be identity field (900001,1) no null. If it has to be done using additional column id be okay with that. How can i do that?

    Reply
  • IF OBJECT_ID(‘TEMPDB..#TESTTABLE’) IS NOT NULL
    DROP TABLE #TESTTABLE
    — CREATE TABLE
    CREATE TABLE #TESTTABLE (COL1 INT, COL2 VARCHAR(100))
    GO

    — INSERT DATA
    INSERT INTO #TESTTABLE (COL1, COL2)
    VALUES (33, ‘PINAL’);
    INSERT INTO #TESTTABLE (COL1, COL2)
    VALUES (22, ‘NUPUR’);
    INSERT INTO #TESTTABLE (COL1, COL2)
    VALUES (11, ‘SHAIVI’);
    GO

    — SELECT DATA
    SELECT *
    FROM #TESTTABLE
    GO

    — ADD IDENTITY COLUMN
    ALTER TABLE #TESTTABLE
    ADD ID INT IDENTITY(1, 1)
    GO

    — SELECT DATA
    SELECT *
    FROM #TESTTABLE
    GO

    IF OBJECT_ID(‘TEMPDB..#TESTTABLE1’) IS NOT NULL
    DROP TABLE #TESTTABLE1

    –CREATE ANOTHER TABLE WITH SAME STRUCTURE
    CREATE TABLE #TESTTABLE1 (COL1 INT, COL2 VARCHAR(100),ID INT NOT NULL IDENTITY(1,1) )
    GO

    –COPY THE DATA TO ANOTHER TABLE
    ALTER TABLE #TESTTABLE SWITCH TO #TESTTABLE1

    –SELECT THE COPIED DATA
    SELECT * FROM #TESTTABLE1

    — RESET THE IDENTITY – SOURCE
    TRUNCATE TABLE #TESTTABLE

    –INSERT ORDERED DATA TO SOURCE TABLE
    ;WITH CTE
    AS
    (
    SELECT ROW_NUMBER() OVER (ORDER BY COL1,ID ASC) RNO,* FROM #TESTTABLE1
    )
    INSERT INTO #TESTTABLE ( COL1,COL2 )
    SELECT COL1,COL2 FROM CTE ORDER BY RNO

    –ANSWER
    SELECT * FROM #TESTTABLE

    Reply

Leave a Reply

Menu