This article contribution from one of my favorite SQL Expert Imran Mohammed. He is one man who has lots of ideas and helps people from all over the world with passion using this community as platform. His constant zeal to learn more about SQL Server keeps him engaging him to do new SQL Server related activity every time.
1. Adding Identity Property to an existing column in a table.
How difficult is it to add an Identity property to an existing column in a table? Is there any T-SQL that can perform this action?
For most, the answer to the above two questions is an absolute NO! There is no straightforward T-SQL like ALTER TABLE… or MODIFY COLUMN to add an Identity Property to an existing column in a table.
However, there is an easy way to accomplish this action. It can be done through SSMS.
Are you finding my answer difficult to believe? Let me explain.
Let’s first see what SSMS does in backend when you add Identity property on an existing column in any table.
Now, let’s create an example table for better understanding.
This table is vacant, with no records, as you can see in the following screenshot.
Take a look at the design of this table in SSMS.
Now let us make eid, an Identity column.
This is very easy. Â All you have to do is just select Yes from the drop down list and you are done!
But before moving further let’s see what T-SQL SQL Server is using to make this change.
You will notice that T-SQL is used by SQL Server to make this change.
After you make the change for Identity property from No to Yes, on top in tools box, you will see Generate Change Script. This is the T-SQL Script that SQL Server will use to make this change.
Unfortunately, I cannot expand this dialogue box further to show you the complete script, so I have copied this script below.
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGINÂ TRANSACTION
GO
CREATEÂ TABLE dbo.Tmp_example1
(
eid INT NOTÂ NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SETÂ IDENTITY_INSERT dbo.Tmp_example1 ON
GO
IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERTÂ INTOÂ dbo.Tmp_example1Â (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')
GO
SETÂ IDENTITY_INSERT dbo.Tmp_example1 OFF
GO
DROPÂ TABLE dbo.example1
GO
EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO
COMMIT
Let us understand above code in simple words:
Begin Transaction
Step 1: Create similar table structure as example1, say Tmp_Example1 with Identity Column.
Step 2: Set IDENTITY_INSERT ON on new table just created.Step 3: Insert all values from example1 to Tmp_Example1
Step 4: Set IDENTITY_INSERT OFF on Tmp_Example..
Step 5: Drop example1 (What is going on… dropping our original table???)
Step 6: Rename Tmp_Example1 to Example1.
Commit Transaction
What if you have millions and millions of records in your table?
Imagine how much time it would take to copy an entire table?
Just to make a column Identity do you really need to go through all these and take so much pain?
Certainly not!
Same is the case when you want to remove Identity column property from a column.
2. Removing Identity Property from an existing column in a table.
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGINÂ TRANSACTION
GO
CREATEÂ TABLE dbo.Tmp_example1
(
eid INT NOTÂ NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERTÂ INTOÂ dbo.Tmp_example1Â (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')
GO
DROPÂ TABLE dbo.example1
GO
EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO
COMMIT
Once again, I would like to congratulate Imran Mohammed for this wonderful article.
Reference : Pinal Dave (https://blog.sqlauthority.com)
107 Comments. Leave new
Thanx for the simple blog. Sometimes we need to go through the “simple” things to remind us of what is available.
Thanks for your suggestion. I saved my a lot time…
Thank you very much
First of all i would like to thank you Pinal Dave for creating this community which helps to know new things in this challenging world , and also the article written by Mr. Mohammed Imran is also very helpfull and clear to understand, which is very new and unique…
Thanks and regards
Md shakeel.
Hi pinal,
How we can add identity in existing column of a Table.
Like I have ID column in a table, i want to Add identity in ID column. plz reply soon.
Thanks
Praveen Agrawal
Hi pinal sir,
SQL Server 2012 identity problem
In my case I created a table with identity, inserted 7 records (identity 1-7) The next record I inserted it was 1002 – inserted 2 more and it was 2002 .
I Don’t know the exact problem , It may be because of
1) sever restart or
2) (rollback, recovery, concurrency)
eg;
1
2
3
4
5
6
7
1002
1003
1004
2002
plz reply soon.
Thanks
Tanveer Sayyed
How to drop an identity property for an existing column in the table using SQL query
What if you have millions and millions of records in your table?
Imagine how much time it would take to copy an entire table?
Just to make a column Identity do you really need to go through all these and take so much pain?
Certainly not!
Why so why not ?
Mr Imran clearly explained that
SQL server copies the entire table and then
changes the properties in the copied version,
delete original and then
renames copied table
Which means that we are going thru a lot of pain right ?
so..
“What if you have millions and millions of records in your table?
Imagine how much time it would take to copy an entire table?
Just to make a column Identity do you really need to go through all these and take so much pain?
Certainly not!”
Ok… so, how can we do it?
thanks for example excellent
What is the solution guys, if not through the above document process as it is painful .. You know that
Do you have it or not? Dont leave it dangling asking us.
Hello All,
Okay sorry for not giving complete solution. Example above shows the first half, solution to this would be to do following.
Step1: Add a new column to existing table with Identity Identity Property ON.
Step2: Rename/delete existing column to which you wanted to enable Identity property.
Step3: Rename new column you just created with Identity property ON to old column name.
That’s it, 3 simple steps and you can avoid lot of overhead.
Let me know if any one else knows any better way of doing this…
~ IM.
So What’s if my database is in a hosting, and the space for log file is limited, because I have a millions of records , Then when the table copy to tableTemp the log will full, please help me…
Hi Imran,
I have the doubt with the above solution.
How can i delete my old column. I will loose the the data.
Please go through the script below and give me any alternative.
Create table Test (Id int, name nvarchar(50))
insert into Test values (1, ‘ram’), (2, ‘venkat’),(3, ‘ramkoti’), (4, ‘koti’), (5, ‘lava’)
delete from test where id = 3
select * from test
alter table test add ID_PK int Identity(1,1)
select id as Old_ID, name, ID_PK as New_ID
from test
Now the old values and new values are different. So we can not delete the old column right and we can not rename. ???
Hi Imran,
I have the doubt with the above solution.
How can i delete my old column. I will loose the the data.
Please go through the script below and give me any alternative.
Create table Test (Id int, name nvarchar(50))
insert into Test values (1, ‘ram’), (2, ‘venkat’),(3, ‘ramkoti’), (4, ‘koti’), (5, ‘lava’)
delete from test where id = 3
select * from test
alter table test add ID_PK int Identity(1,1)
select id as Old_ID, name, ID_PK as New_ID
from test
Now the old values and new values are different. So we can not delete the old column right and we can not rename???
can you Provide above steps by screenshot
wise example on table
which steps?
Where is the data population step in the new solution ? is it implicit
In the new solution by Imran what I observed,
When anybody is creating a new column, certainly they want the data in the old column because it may not be ordered and have identity gaps. So they dont want to generate new values, instead old values should be dumped. (I want to make new column exactly a replica of old one)
How do I get them, even I cannot update new column as it is an Identity.
Any solutions …
Hello Naveen,
The statement “SET IDENTITY_INSERT dbo.Tmp_example1 OFF” changes the table to allow insert old values in identity column.
Please let us know if this doesn’t answer your question.
Regards,
Pinal Dave
Hi Pinal,
I have a table ‘AllUsers’ with fields UserID bigint unique and UserName.
I have appoximate 10000 records.
now i want add IDENTITY in column ‘UserId’ of table ‘AllUsers’ by Sql query.
Alter Table AllUsers alter column UserId ADD IDENTITY(10001,1)
Then showing error:
Incorrect syntax near the keyword ‘IDENTITY’.
Please give me a solution. I am waiting for your reply.
Thanks & Regards,
Deepak MIttal
Im having the same problem……………..
And im using SSMS……..
But still the same issue…
the problem is i cant delete the column because i have set it as a foreign key in another tabel…..
Hello Deepak,
You can add identity property to an existing column only by SSMS, not by t-sql query.
Regards,
Pinal Dave
Hello Imran / Pinal
You mentioned above three steps to convert a column to Identity column. Thanks for that but that is not a solution, that is simply creating an Identity column with the name you wanted. What happened to all the data in the column?
Scenario: An Integer identity column is reaching the BIGINT range and now needs to be converted to BIGINT. The table has a millions of rows in it and some rows have been deleted over time.
The solution mentioned above will create a new Identity column thus failing on referential integrity, since just one deleted id will cause all rows after it to point to the wrong data using the identity column.
PS.
SET IDENTITY_INSERT tablename ON does not allow updates on the column, only inserts.
If all you are looking for is a way to increase the size of an existing IDENTITY column from INT to BIGINT, you can do the following:
1. Drop all constraints/indexes that depend on the column.
2. Change the column type to bigint using the ALTER TABLE ALTER COLUMN command. Do NOT use the IDENTITY keyword as part of the command; the column already has the IDENTITY property set, and will retain it automatically.
3. Re-create all the constraints/indexes you had dropped in step 1.
if i have many tables to remove the identity property, i must change on design editor one by one ???
thnx
Pinal,
In this solution I dont think you can maintain the foreign key and other relations. Can you pls verify?
Pinal & Imran,
Thanks for this wonderful article. I was struggling to automate a few stuff in SSIS, specially with Enabling/Disabling the Identity column using T-Sql. Just one thing needs to be mentioned in Sql Server Management studio. If you don’t see the “Generate Change Script” in the toolbar. Just right click and select “Table Designer”.
Thank you guys
Ganesh
Pinal,Imran,
Thanks for the solution. But do you know what will be the solution when ID column is referenced as foreign key in other multiple tables?
because in this case it wouldnt let me delete that old ID column.
Very helpful article and the method used in this is very tricky. This helped us to improve knowledge in SQL Server.
Thanks,
Saurabh
Hello all,
My problem related with identity filed on SQL SERVER 2000. I have a table named EmployeeMaster .It contain two fields EmpId int identity & newEmpId int . And an update trigger written for this EmployeeMaster. I want an updation on EmployeeMaster with EmpId replaced with newEmpId and the update trigger firing. So keep a log details of old Empid and new empId to another table. But EmpId is an identity column. When updation, an error message occured ‘Updation not possible on identity filed’.
I can turn on/off identity through enterprise manager and updation possible.
But I want this through script.
How can I solve this issue ?