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
Hi Pinal,
Can you clarify my doubt,
In my below result set, am missing the identity value 3. Is there any way to Continue the identity value, if rollback occurs.
————step 1 ———————-
create table #tbl(id int identity(1,1) , name varchar(10))
insert Into #tbl values (‘abhishek’) ,(‘raj’)
select * from #tbl
————step 2 ———————-
begin tran
insert INTO #tbl values (‘ram’)
rollback tran
select * from #tbl
————step 3 ———————-
insert INTO #tbl values (‘rose’)
select * from #tbl
————–
/*
id name
———– ———-
1 abhishek
2 raj
4 rose
(3 row(s) affected)
In my result , am missing the identity value 3. Is there any way to Continue the identity value if rollback occures.
*/
Regards,
abhIShek online4all
No. Identity columns do not guarantee sequential values.
Muchas gracia, Exelente tip.
Hi Pinal
This method doesn’t work in Sqlserver 2008. Once the table has been created table, it doest allow us to add identity property to its existing column. Is there any way to add identity property to an existing column in “SQL SERVER 2008”.
Thanks
Vandana
It is not possible to do it via query. You need to do it via Management studio
Hi Sir,
I am simanta. I am a software Engineer. I am stucking With a problem from last one week, Please help me sir…
I want to take a grid view and inside that grid view i want to display one image with values in 2 text box. Data will come from sql server.In a time interval that image will change and a different image will come, When Image will change in a time interval the values inside the textbox will also change. Please help me sir
With regards
Simanta Jyoti Medhi
[phone number removed]
i hv an identity column.i deleted a record .i want that all the record below this incremented by 1 .how it posible.plz help me.
for ex.
if there are 16 record i deleted 5th record then 6th record become 5th and so on…when i inserted the record is 16th no
plz help me
It is not possible by desing. Why do you worry about the gaps in number?
my update cant run
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim sqlConnectioin As New SqlConnection(“Data Source=lin-pc\lin;Initial Catalog=LotusSchedule;Integrated Security=True;”) ‘ connect sql server
Dim sql As String = “”
sql = “update Consultation set colstat = colstat – 1 where num = CsNum(‘TB_Consultation’)and (‘” + Me.TextBox1.Text + “‘,'” + Me.TextBox2.Text + “‘,'” + Me.TextBox1.Text + “‘,'” + Me.TextBox1.Text + “‘,'” + Me.TextBox1.Text + “‘)”
‘ write insert sql here~~~”
Dim SqlCommand As New Data.SqlClient.SqlCommand ‘
SqlCommand.CommandType = Data.CommandType.Text
SqlCommand.CommandText = sql
SqlCommand.Connection = sqlConnectioin
sqlConnectioin.Open()
SqlCommand.ExecuteNonQuery()
sqlConnectioin.Close()
this also cant work…any body can help me
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnectioin As New SqlConnection(“Data Source=lin-pc\lin;Initial Catalog=LotusSchedule;Integrated Security=True;”) ‘ connect sql server
Dim sql As String = “”
sql = “COMMIT Consultation” ‘ write insert sql here~~~”
Dim SqlCommand As New Data.SqlClient.SqlCommand ‘
SqlCommand.CommandType = Data.CommandType.Text
SqlCommand.CommandText = sql
SqlCommand.Connection = sqlConnectioin
sqlConnectioin.Open()
SqlCommand.ExecuteNonQuery()
sqlConnectioin.Close()
End Sub
End Class
A related really nasty event to the above is if there is an error with the insert to the temp table the script will delete your original table and rename the empty temp one to your original table name! Poof! All your data is gone and you’re left with an empty table with whatever mods you made to table structure. This is documented elsewhere but, so far, no real good examples of what to do about it. See…
thanks pinal for a wonderful article that’s not so wonderful and now people are filling you up with questions that you can’t answer because this article is as useless as dog poop
I have found faster way to do this Using ALTER TABLE…… SWITCH command.
ALTER TABLE…SWITCH statement changes the schema of a table without changing the data, meaning you can replace a table with an IDENTITY with an identical table schema, but without an IDENTITY column. The same trick works to add IDENTITY to an existing column.
Here’s a code sample of how to Drop Identity Property from a column
CREATE TABLE Test
(
id int identity(1,1),
somecolumn varchar(10)
);
INSERT INTO Test VALUES (‘Hello’);
INSERT INTO Test VALUES (‘World’);
— copy the table. use same schema, but no identity
CREATE TABLE Test2
(
id int NOT NULL,
somecolumn varchar(10)
);
ALTER TABLE Test SWITCH TO Test2;
— drop the original (now empty) table
DROP TABLE Test;
— rename new table to old table’s name
EXEC sp_rename ‘Test2′,’Test’;
— see same records
SELECT * FROM Test;
Here’s a code sample of how to SET Identity Property of an existing column
CREATE TABLE Test
(
id int NOT NULL,
somecolumn varchar(10)
);
INSERT INTO Test VALUES (1,’Hello’);
INSERT INTO Test VALUES (2,’World’);
— copy the table. use same schema, but no identity
CREATE TABLE Test2
(
id int identity(1,1),
somecolumn varchar(10)
);
ALTER TABLE Test SWITCH TO Test2;
— drop the original (now empty) table
DROP TABLE Test;
— rename new table to old table’s name
EXEC sp_rename ‘Test2′,’Test’;
— see same records
SELECT * FROM Test;
Reference : Justin Grant comment in this link……
i want to create a sequence in sql server 2008, but when write and run the syntax it gives error of
“Msg 343, Level 15, State 1, Line 1
Unknown object type ‘sequence’ used in a CREATE, DROP, or ALTER statement.”
i don’t understand why it happens b’coz in sql server we can create SEQUENCE right? as per MSDN….
pls help me in this point…..
Thanks in Advance
Thank you, this helped me alter a column to add the IDENTITY property.
Hi,
I am using Identity property in my data tables first time. Now i have one question let suppose i used a identity field as a foreign key in an other table. now i suddenly removed a one entry in a primary table and that entry is used in the other table. so its mean relationship between both table are broken. now i add a new entry with everything same. but its give me new identity. in this case i still did not manage to establish the relationship between table. i try to edit the table in design view its not allow me to edit the identity column. So how i can set the identity.
Another solution to the issue of having to convert an INT IDENTITY to a BIGINT IDENTITY due to MAXINT number of rows have been reached while maximising table availability and minimising transaction log space is:
•Create New table (XXXNew) with BIGINT instead of INT for the IDENTITY column (Setting initial value to a value some values higher then Current Value in the Original table)
•Rename Original table to XXXOrg
•Create VIEW to SELECT * FROM XXXOrg UNION ALL SELECT * FROM XXXNew (After this step any process/user can issue normal SELECT using the original table name (but it’s now a view) just as they could before the table rename)
•Create INSTEAD OF INSERT Trigger on VIEW to direct any insert into the XXXNew table (After this step processes/people can still INSERT as they did before without a problem)
•Create also INSTEAD OF UPDATE and INSTEAD OF DELETE if expected concurrent use of Original table requires this (If processes/people also needs to UPDATE or DELETE, these triggers needs to be added to continue giving the same functionality as before)
•SET IDENTITY INSERT ON
•Create a LOOP to move data from XXXOrg to XXXNew in chunks (1k – 100k rows), with sleep, to not interfere with Live table operations while moving data (This makes the new table contain all the data that the old table did)
•SET IDENTITY INSERT OFF
•DROP newly created INSTEAD OF TRIGGER(s) on the view
•Drop VIEW
•Rename the table XXXNew to Original table name
And here we have a table with the same name as the original table but with BIGINT instead of INT, is still using IDENTITY and contains all the data from the original table.
A very useful artile!
Thanks
Fabio
Thanks a lot for sharing very nice article and it’s implementation to Imran and Pinal.
Really you both guys are excellent!!!
Keep it up…:)
—Abhishek Udiya
I would like to congratulate both Pinal Dave and Imran Mohammed for this wonderful article. It was very helpful to me ! Thanks
Hi,
i had the same prob. with 2 SQL Instances you can export/import the db to another instance, click on arrange allocation, create destination table, arrange SQL, insert into the column you would like to have the autoincrement(pk)
[lfdnr] int NOT NULL identity(1,1) primary key,
and then after successfull export/import all works fine.
Merry christimas
CO
wow thank you….really helped..
Pinal dave’s blogs are always so useful!!
thanks !!
I am a new user of SQL server and have the requirement of updating the values of a column which has identity constraint and is present in many tables as child relationship. After reading this thread and many other threads on internet I can say SQL server is age old database management tool. for such a simple thing they have not built any solution. people have been posting this question since years in many forums and microsoft has not provided any clean solution. If it would have been oracle they would have fixed the issue by allowing users to drop the identity property. I really want to suggest my client to switch from SQL Server to Oracle. There are many other issues as well. I cant compare tables in 2 databases. i cant see the relation between 2 tables. what kind of database is this. i am sad i have to use it after working in oracle for 9 years.