SQL SERVER – Add or Remove Identity Property on Column

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.

SQL SERVER - Add or Remove Identity Property on Column im1

This table is vacant, with no records, as you can see in the following screenshot.

SQL SERVER - Add or Remove Identity Property on Column im2

Take a look at the design of this table in SSMS.

SQL SERVER - Add or Remove Identity Property on Column im3

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!

SQL SERVER - Add or Remove Identity Property on Column im4

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.

SQL SERVER - Add or Remove Identity Property on Column im5

SQL SERVER - Add or Remove Identity Property on Column im6

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)

,
Previous Post
SQL SERVER – Example of DDL, DML, DCL and TCL Commands
Next Post
SQL SERVER – Roadmap of Microsoft Certifications – SQL Server Certifications

Related Posts

106 Comments. Leave new

  • abhIShek online4all
    October 5, 2011 5:01 pm

    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

    Reply
  • Muchas gracia, Exelente tip.

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

    Reply
    • It works the same way in SQL Server 2008 as well. I think you might be facing different error. Please post your screenshot.

      Reply
      • Hi Pinal

        Thanks a lot for ur response, but I am very much sure that this method doesn’t work in sqlserver 2008 coz, we r facing same issue from last 1 year(since we have upgraded the sql server 2008 from 2005 ).

        I am not able to post the screen shots here, so I am posting one example.

        1. create a table say “tblOS” with two column nOSID, nvcOSName.
        2. make nOSID as Primary Key
        3. Save the table.
        4. Now try to set the “Identity Property” as “Yes” for nOSID.
        5. Save the changes.
        5. It wont allow us to save the changes.
        6. Error:Saving changes is not permitted.The changes you have made requires the following tables to be dropped and re-created.You have either made changes to a table that can’t be re-created or enable option Prevent saving changes that requires the table to be recreated.

        and its not very easy to drop and recreate the table, specially its a part of live database and and contains a large amount of data. It also requires to drop all the relations to other table.

        If there is any solution please post it, It can make our life simple

        Thanks a lot
        Vandana Maurya

      • You can change using SSMS in SQL 2008. You couldn’t change it because of your SQL Server setting. Go to Tools –> Options –> Designers –> Table and Database Designers —> Uncheck the box “Prevent saving changes that require table re-creation” ,,,,,,,,,,, and you will be able to save the changes you made on the Identity Column

    • It is not possible to do it via query. You need to do it via Management studio

      Reply
  • 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]

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

    Reply
  • 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()

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

    Reply
  • 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…

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

    Reply
  • 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……

    Reply
  • Chirag Mehta
    April 3, 2012 3:34 pm

    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

    Reply
  • Thank you, this helped me alter a column to add the IDENTITY property.

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

    Reply
  • Hans Lindgren
    July 13, 2012 1:28 pm

    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.

    Reply
  • A very useful artile!
    Thanks
    Fabio

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

    Reply
  • Massimo Chiaratti
    November 16, 2012 7:01 pm

    I would like to congratulate both Pinal Dave and Imran Mohammed for this wonderful article. It was very helpful to me ! Thanks

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

    Reply
  • wow thank you….really helped..
    Pinal dave’s blogs are always so useful!!
    thanks !!

    Reply
  • Deepak Aggarwal
    January 16, 2013 8:53 am

    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.

    Reply

Leave a Reply

Menu