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

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.

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:

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?

Click to Download Scripts

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

About these ads

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

  1. – 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

  2. 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).

  3. – 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;

  4. 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)

  5. 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()

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

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s