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.

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 (http://blog.SQLAuthority.com)

About these ads

90 thoughts on “SQL SERVER – Add or Remove Identity Property on Column

  1. Thanx for the simple blog. Sometimes we need to go through the “simple” things to remind us of what is available.

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

  5. “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?

    Like

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

    Like

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

    Like

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

      Like

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

      Like

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

      Like

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

    Like

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

    Like

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

    Like

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

      Like

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

    Like

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

      Like

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

    Like

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

    Like

  14. Very helpful article and the method used in this is very tricky. This helped us to improve knowledge in SQL Server.

    Thanks,
    Saurabh

    Like

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

      Like

  15. ALTER TABLE dbo.[MyTable] ADD NewID INT IDENTITY(1,1)

    SET IDENTITY_INSERT dbo.[MyTable] ON

    UPDATE dbo.[MyTable]
    SET NewID
    = ID

    sp_RENAME ‘[MyTable].[NewID]’ , ‘[ID]’, ‘COLUMN’

    SET IDENTITY_INSERT dbo.[MyTable] OFF

    When it tries the update I get:
    Msg 8102, Level 16, State 1, Line 1
    Cannot update identity column ‘NewID’.

    What am I missing? I too, want the “certainly not” option. ;)

    Like

      • I found the solution for this update problem

        …………………………………

        sp_configure ‘allow update’, 1
        go
        reconfigure with override
        go
        update syscolumns set colstat = colstat – 1
        where id = object_id(‘TB_EmployeeMaster’)
        and name = ‘EmpId’
        go
        exec sp_configure ‘allow update’, 0
        go
        reconfigure with override
        go

        update TB_EmployeeMaster set EmpId=newEmpId

        go
        sp_configure ‘allow update’, 1
        go
        reconfigure with override
        go
        update syscolumns set colstat = colstat + 1
        where id = object_id(‘TB_EmployeeMaster’)
        and name = ‘EmpId’
        go
        exec sp_configure ‘allow update’, 0
        go
        reconfigure with override
        go

        ……………………………

        EmpId is the identity field
        newEmpId is the new field

        sp_configure ‘allow update’, 1 means system table is allowed for editing

        After updation use sp_configure ‘allow update’, 0

        it means system table editing locked

        Also identity field colstat is 1. We can change it to 0 for updation.

        After updation set colstat as 1 for original state

        before any changes in system table, we can set sp_configure ‘allow update’, 1

        and after change we can set
        sp_configure ‘allow update’, 0

        ‘reconfigure with override’ use change to take immediate effect without sql server restarted

        note that sp_configure change server level settings
        improper use of ‘sp_configure’ may lead to DB crash.

        Like

        • Hi ,

          Am getting below error when am trying to change the colstat in syscolumn table
          Ad hoc updates to system catalogs are not allowed.

          Pl suggest what can be done to update identity column value.

          Thanks in anticipation.

          Like

  16. i m set identity_insert on but still show error

    (:mssql2005)
    MESSSAGE

    create table temp_students(id int primary key identity,f_name varchar(30))

    SET IDENTITY_INSERT dbo.temp_students ON

    insert into temp_students(id) SELECT id FROM students
    WITH (HOLDLOCK TABLOCKX)

    Msg 544, Level 16, State 1, Line 1
    Cannot insert explicit value for identity column in table ‘temp_students’ when IDENTITY_INSERT is set to OFF.

    SET IDENTITY_INSERT dbo.temp_students OFF

    DROP TABLE students

    sp_rename temp_students,students
    Caution: Changing any part of an object name could break scripts and stored procedures.

    Like

  17. Create similar table structure as example, say Tmp_Example with Identity Column.

    while fire

    SELECT *
    INTO temp_Example
    FROM Example
    WHERE 1=2

    but constarint not copy onlu table structure copy

    Like

  18. Hi im using Visual Studio 2008 & MS SQL SERVER Management Studio Express

    i don’t know where is my error

    Please help me out?

    THIS IS MY REGISTER.

    Imports System.Data.Sql
    Imports System.Data.SqlClient
    Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    ‘Dim connection As String = ConfigurationManager.ConnectionStrings(“Data Source=JED-PC;Initial Catalog=comicon;Trusted_Connection=True”).ConnectionString

    Using cn As New SqlConnection
    cn.ConnectionString = “Data Source=JED-PC;Initial Catalog=comicon;Trusted_Connection=True”
    Dim c As String
    c = “INSERT INTO Logins(First_Name,Last_Name,Password,Address,Contact_No,Email,Account) VALUES (‘” & TextBox1.Text.Trim & “‘,’” & TextBox2.Text.Trim & “‘,’” & TextBox3.Text.Trim & “‘,’” & TextBox5.Text.Trim & “‘” & “, ‘” & TextBox6.Text.Trim & “‘,’” & TextBox7.Text.Trim & “‘,’1′ )”
    Dim sqlcmd1 As New Data.SqlClient.SqlCommand
    sqlcmd1.Connection = cn
    Try
    cn.Open()
    sqlcmd1.CommandText = c
    sqlcmd1.ExecuteNonQuery()
    cn.Close()

    Catch ex As Exception
    MsgBox(ex.ToString)

    End Try

    If TextBox6.Text.Length < 7 Then
    MsgBox("contact # is too short")

    Else
    Button1.Visible = True
    MsgBox("Your Successfully Registered", 6, "Confirmation Message")
    Response.Redirect("Login.aspx")
    End If
    End Using

    End Sub
    End Class

    Like

  19. (When I Submit it won’t redirect to other..)

    Imports System.Data.Sql
    Imports System.Data.SqlClient
    Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim connection As String = ConfigurationManager.ConnectionStrings(“Data Source=JED-PC;Initial Catalog=comicon;Trusted_Connection=True”).ConnectionString

    Using cn As New SqlConnection
    Dim Account As String
    Dim login As String
    login = “Select count(*) from Logins where Email = ‘” & TextBox1.Text & “‘ and Password = ‘” & TextBox2.Text & “‘”
    Dim sqlcmd1 As New SqlCommand(login, cn)
    Try

    cn.Open()
    Account = sqlcmd1.ExecuteScalar

    If Account = 1 Then
    sqlcmd1.CommandText = login
    Account = sqlcmd1.ExecuteScalar()
    cn.Close()

    Select Case Account
    Case 0
    MsgBox(“welcome admin”)
    Session(“Logins”) = TextBox1.Text
    Response.Redirect(“/planetcomic/user/admin/Home.aspx”)
    Case 1
    MsgBox(“You are Succesfully Login”)
    Session(“Logins”) = TextBox2.Text()
    Response.Redirect(“/planetcomic/user/customer/Home.aspx”)

    Case Else
    Label6.Text = “Invalid Login or Password”
    TextBox1.Text = “”
    TextBox2.Text = “”
    Label6.Visible = True
    cn.Close()

    ‘If sqlcmd1.ExecuteScalar = “1″ Then
    ‘Session(“Login”) = TextBox1.Text
    ‘Response.Redirect(“/planetcomic/user/customer/Home.aspx”)

    End Select
    End If
    Catch ex As Exception
    MsgBox(“Welcome to Planetx”)

    End Try

    End Using
    End Sub

    End Class

    Like

  20. Pinal / Imran,

    Thanks for this wonderful article. Just one thing needs to be mentioned.

    I ran into following error :

    ” Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”

    and then when i googled, following link helped me with solution.

    http://blog.programmingsolution.net/sql-server-management-studio-ssms/sql-server-table-design-saving-changes-is-not-permitted/

    Like

  21. HI Pinal,
    I want to add a column in select statement which is rank. And Rank should contain function of identity. Is this possible . If yes plz let me know.

    Thanks,

    Regards,
    Avinash

    Like

  22. you have created a new table with non-identity coln and renaming it as the table is small and have one column but do you think this is the best practice for a table with lot of indexes and constraints?

    Like

  23. Hi,
    how to concatenate string in identity column.
    for eg:-
    emp1
    emp2
    emp3
    i want to increase the identity column like this…………….

    Like

  24. I want those row from a table who has value only 1 in In_Store. If value is 0 in In_store for a Item_no. Then those item_no should not displayed.

    Result i want :— Item_no In_store
    2 1

    Ex:- Item_no In_store
    1 0
    1 1
    2 1

    please give me ur suggestion.

    Regards,
    Avinash Kumar

    Like

  25. Item_no In_Store
    2 1

    I want this result from the Displayed Table below,

    Item_no In_store
    1 0
    1 1
    2 1

    Means to say if value is 0 for any item then those item should not display.

    Please give me your valuable suggestion.

    Regards,
    Avinash Kumar

    Like

  26. Hello all,

    Please help me…

    My table is :-
    Product Qty
    P1 3
    P2 2

    and I want the result like:-
    P1 1
    P1 1
    P1 1
    P2 1
    P2 1

    Please suggest me the query.

    Regards,
    Avinash kumar

    Like

  27. Please give the answer if any one know about this question.

    order_day prod_id Quantity price
    18-10-11 P1 2 25
    18-10-11 P2 3 10
    18-10-11 P3 5 5
    18-10-11 P4 3 25
    18-10-11 P5 4 20
    18-10-11 P6 4 30
    18-10-11 P1 4 25
    19-10-11 P1 2 25
    19-10-11 P3 3 10
    19-10-11 P4 4 15
    19-10-11 P2 1 15

    Question:- Select the product which is sold on 18th & 19th and how many time on each day.

    Output Should be like this:-
    Product Count
    P1 2
    P1 1

    Like

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

    Like

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

    Like

      • 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

        Like

        • 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

          Like

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

    Like

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

    Like

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

    Like

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

    Like

  34. 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… http://www.swegler.com/becky/blog/2009/10/27/beware-the-change-scripts-created-by-sql-server-management-studio/

    Like

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

    Like

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

    http://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  43. is there something called sequence in sql server which can be dropped and reset at will? i have 30+ tables and the foreign key relationships between them. i cant really do the methods suggested above as it is going to make my work 100 times more if i start dropping columns or tables and i will have to change the foreign keys also i guess. can i chaneg the order of columns in a table as i dont want to disturb the column sequence of my primary key column after using the above suggested methods.

    Like

  44. Hi
    thank you for this article.
    I’m facing a problem trying to import data from a csv file to a table with identity column :

    SET IDENTITY_INSERT mytable ON

    BULK INSERT mytable
    FROM ‘C:\file.csv’
    WITH ( FIELDTERMINATOR = ‘;’, ROWTERMINATOR = ‘\n’ )
    GO

    SET IDENTITY_INSERT mytable OFF

    I got this error

    Explicit value must be specified for identity column in table ‘TABLE_NAME’ either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column

    The csv file has already the values for ID column.

    Any idea about this error ?

    Thank you.

    Like

  45. Can i update the identity column value? I know that is not the concept of identity property this will break the concept of identity. but i confirm this.

    Like

  46. I am using Sqlserver 2008. I want to display it .I am very new in this system(Freshers).I need it urgently.So please help me.Thanks in Advance.

    Table Structure :

    CREATE TABLE dbo.DwlEntry
    (
    Id INT IDENTITY(1,1),
    UserName VARCHAR(100),
    ConfigItem VARCHAR(100),
    Component VARCHAR(100),
    TimeLogged DECIMAL(13,2)
    )

    INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bubai’,’AFOTA’,’Documentation’,’3′)
    INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bubai’,’GENERAL’,’Troubleshooting’,’3′)
    INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bhanu’,’AFOTA’,’CodeChange’,’3′)
    INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bubai’,’GENERAL’,’ProjectTraining’,’1′)
    INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bhanu’,’GENERAL’,’Troubleshooting’,’5′)
    INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bhanu’,’AFOTA’,’CodeChange’,’1′)
    INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bubai’,’AFOTA’,’CodeChange’,’5′)
    INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bubai’,’GENERAL’,’ProjectTraining’,’4′)
    INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bhanu’,’AFOTA’,’Documentation’,’2′)
    INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bhanu’,’GENERAL’,’ProjectTraining’,’4′)

    I want the Result in below way . Config item would be first row. and grouping come Horizontally like below Format

    ConfigItem AFOTA AFOTA GENERAL GENERAL
    UserName Documentation CodeChange ProjectTraining Troubleshooting Total
    Bhanu 2 4 4 5 15
    Bubai 3 5 5 3 16
    Total 5 9 9 8 31

    Like

  47. hi i have a table which contains 500 records
    i do the fallowing process

    ALTER TABLE [dbo].[table1] ADD [abc] INT IDENTITY(2,1)

    SET IDENTITY_INSERT [dbo].[table1] ON

    INSERT INTO [dbo].[table1]([abc])
    SELECT sno FROM [dbo].[table1] with (HOLDLOCK TABLOCKX)

    ALTER TABLE [dbo].[table1] DROP column [abc]

    EXEC sp_rename
    @objname = ‘[dbo].[table1].[abc]’,
    @newname = ‘sno’,
    @objtype = ‘COLUMN’

    SET IDENTITY_INSERT [dbo].[table1] off

    select * from [table1]

    but here my problem was

    Now the old values and new values are different

    Like

  48. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

  49. Pingback: SQL SERVER – How to an Add Identity Column to Table in SQL Server | Journey to SQL Authority with Pinal Dave

  50. Hi pinal
    i have a database with perfect structure with out data(empty tables) where among many tables have identity increment(1,1) ,when ever iam trying to insert records into that empty tables iam getting error as-
    (SET IDENTITY_INSERT orders off; you cannot insert value into orders when identity is set off)
    what i required is , with out using my insert query in between SET IDENTITY_INSERT orders ON and SET IDENTITY_INSERT orders off- the data has to be inserted into my table ,
    Please help me out its very very Ugent.

    reply me at
    my emaild: [email removed for privacy]

    ThankU

    Like

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