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?
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
30 Comments. Leave new
CREATE VIEW vw_TestTable
AS
SELECT ROW_NUMBER() OVER (ORDER BY Col1) AS Id, Col1, Col2 FROM TestTable
SELECT * FROM vw_TestTable
Very good and straightforward solution.
Thanks Pinal
SELECT ROW_NUMBER()over (order by Col1 asc)ID,Col1,Col2
FROM TestTable
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
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
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).
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.
— 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;
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)
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()
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.
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!
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
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.
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?
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
Interesting indeed. Thanks.
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
Thanks for taking time. Great solutions.
the solution is not good since the ID column can be change into identity column
bravo very good i use that for my real project
Great. Thanks @Farshid
This is a great solution, thank you!
Thanks Scott. Glad to hear that.
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?
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