RSS 2.0 Feed
RSS 2.0


Atom 1.0 Feed
Atom 1.0

  Tips for SQL Server Identity Columns 


Allowing inserts to identity columns:

If you are inserting data from some other source to a table with an identity column and you need to ensure you retain the indentity values, you can temporarily allow inserts to the indentity column. Without doing so explicitly you will receive an error if you attempt to insert a value into the indentity column. For example, if I have a table named MYTABLE and I want to allow inserts into it's identity column, I can execute the following:

set identity_insert mytable on

Once you execute the command you will be able to insert values into the table's identity column. This will stay in effect in until you turn it off by executing the following:

set identity_insert mytable off

Be aware that at any time, only a single table in a session can have the identity_insert set to on.  If you attempt to enable this for a table and another table already has this enabled, you will receive an error and will not be able to do so until you first turn this off for the other table. Also, if the value used for the indentity is larger than the current identity value then the new value will be used for the identity seed for the column.  


Reseeding the identity value:

You can reseed the indentity value, that is, to have the identity values reset or start at a new predefined value by using DBCC CHECKIDENT.  For example, if I have a table named MYTABLE and I want to reseed the indentity column to 30 I would execute the following:

dbcc checkident (mytable, reseed, 30)

If you wanted to reseed the table to start with an identity of 1 with the next insert then you would reseed the table's identity to 0.  The identity seed is what the value is currently at, meaning that the next value will increment the seed and use that.  However, one thing to keep in mind is that if you set the identity seed below values that you currently have in the table, that you will violate the indentity column's uniqueness constraint as soon as the values start to overlap.  The identity value will not just “skip” values that already exist in the table.




                   



Leave a comment below.

Comments

  1. Joe Chin 12/20/2004 1:41 PM
    Gravatar
    While we're on the subject of identity columns, here's a link to some important information about the difference between SCOPE_IDENTITY() and @@IDENTITY:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-
    us/tsqlref/ts_sa-ses_6n8p.asp

    In most circumstances, you should use Scope_Identity() rather than @@Identity.

    Just thought I'd pass it along...
  2. Ryan Farley 12/20/2004 2:00 PM
    Gravatar
    True, however in cases where triggers etc are not involved, there are no issues with using @@identity. I think that the misunderstood use of "session" and how that applies to a SQL connection is where things turn to the desire to use scope_identity() instead of @@identity.

    For most cases, @@identity is just fine to use. It *does* return the last Identity update for open connection. if you execute insert and also select @@identity in the same stored procedure. Using DataAdapters/DataSets will cause sessions to be "mixed" causing scope to change and scope_identity() should be used in OnRowUpdated or similar in those cases (as well as with triggers since there are possibilities to have other identities created for the same session). If you have no triggers in your database, @@identity, as a rule, is safe to use, as it is scoped within a connection.

    The problem is, which you alude to Joe, is that there is a possibility of scope change then you would definately want to use scope_identity(). You might have a DBA that adds a trigger without your knowledge and then all things will go crazy since you'd be getting back the wrong identity (assuming that you're using @@identity). It is a safe route to go with scope_identity(), however the big problem is where developers are not sure of when to use one over the other and why.

    Thanks for the comments Joe.
    -Ryan
  3. ESquared (Erik Eckhardt) 1/14/2005 12:53 PM
    Gravatar
    It is misleading to say "for most cases, @@identity is just fine to use." The audience who needs this advice is likely to interpret this as "@@identity can be used as a default" when in fact that's not a good idea.

    Given that people *are* unsure of when to use which, they should always go with scope_identity() as the default. The programmer who actually wishes to know the last identity value inserted, trigger or no, should be competent enough to decide to use @@identity.

  4. Ryan Farley 1/14/2005 1:25 PM
    Gravatar
    Hi Erik,

    Ok, I'll buy that. Using scope_identiy as the "default" surely doesn't hurt anything. Good point.

    Thanks,
    -Ryan
  5. mikep@aicorporation.com 10/4/2005 7:49 AM
    Gravatar
    In T-SQL how do you find which tabke, (if any), currently has identity_insert ON ?
    Mike P.
  6. MOWS 1/19/2006 1:21 PM
    Gravatar
    Question: Is there something like a "set identity_update on" ??

    I need to do an update on my identity colum
  7. Ryan Farley 1/19/2006 1:24 PM
    Gravatar
    MOWS,

    I don't know of anything like that - but I would consider it bad design to update an identity. You'd likely break all kinds of stuff doing something like that.

    -Ryan
  8. Pradeep 2/23/2006 9:45 PM
    Gravatar
    It was informative... Thanks,,
  9. bob 8/9/2006 12:36 PM
    Gravatar
    thanks for the tips
  10. Bear 8/30/2006 5:45 AM
    Gravatar
    Excellent tip on "dbcc checkident" - couldn't find that anywhere else!
  11. Tom 11/7/2006 2:23 PM
    Gravatar
    "You might have a DBA that adds a trigger without your knowledge ..." - if things can go wrong - they always do - just go the safest route. DBAs other programmers etc. don't leave hidden mines that people can step into. scope_identiy is safer
  12. JT 11/27/2006 7:31 AM
    Gravatar
    Mike P. -

    I don't know of an "identity_update" command in t-sql, but you can do the same thing as follows below...note that this has to be used CAREFULLY! You have to check your data model/data first and be certain that there will not be any data integrity issues caused by updating the related identities. Here you go:

    delete from tablex
    dbcc checkident (tablex, reseed, 0)
    -- insert inito...reinsert the rows into tablex

  13. free web hosting 2/16/2007 3:21 AM
    Gravatar
    Nice article thanks!
  14. Gurpreet 3/15/2007 12:39 AM
    Gravatar
    Hey guys....

    I am having sime problem with Identity Key....... I made an application with which users can only add records to the table (no updation, no deletion) now my problem is that the identity key is being keep on skiping like it add an record in 1456 then the next recoeds identity comes to be 1462...... and its keep on skipping continously by certain numbers.... some times to 2, some times by 8.....

    Its making me crazy..... since its auto generated so there is nothing wrong with my coding...... and 1 thing more.... its running on my web server...... Do you have any idea about it...!!
  15. sethu 3/20/2007 3:09 AM
    Gravatar
    thankkkssss for the tips
  16. SD 3/26/2007 3:52 AM
    Gravatar
    excellant just what i was looking for !!

    Thanks
  17. Sanjay Kattimani 4/17/2007 7:24 AM
    Gravatar
    Quick tip
  18. IVILLA 6/22/2007 7:54 AM
    Gravatar
    Gurpreet, when you have an failed insertion or a transaction rollback the internal number also is increased, although the registry was not registered
  19. kyle 7/10/2007 12:11 AM
    Gravatar
    very good tip
  20. Jim 9/11/2007 1:51 PM
    Gravatar

    Is there any reason why i should expect a table to use 0 for an identity(1,1) after a RESTORE? We have just one table (out of 100's!) that seems scripted identically but the first insert delivers a 0 for SCOPE_IDENTITY( ) on first insert.

    a seed of 10 with increment of 2 (just for giggles) causes 0,10,12,14.. I'm totally perplexed. This is on 2005.

    Thanks for clues!

  21. Jim 9/11/2007 2:11 PM
    Gravatar

    apologies for the continued pestering. in my post above, this only occurs when restoring from a file, not another db. backup dbA to file, restore to dbB, and the symptom appears.

    CREATE TABLE [dbo].[SearchHit](
    [SearchHitId] [bigint] IDENTITY(1,1) NOT NULL,
    [DocumentId] [bigint] NULL,
    [SearchId] [bigint] NOT NULL,
    [Path] [nvarchar](2000) NULL,
    [PathChecksum] AS (binary_checksum([path])) PERSISTED,
    CONSTRAINT [PK_SearchHit] PRIMARY KEY NONCLUSTERED
    (
    [SearchHitId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    CREATE CLUSTERED INDEX [IX_SearchHit] ON [dbo].[SearchHit]
    (
    [DocumentId] ASC,
    [SearchId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    Jim
  22. proxy site 10/15/2007 8:41 PM
    Gravatar
    ...In T-SQL how do you find which tabke, (if any), currently has identity_insert ON ?
    Mike P.
    ..
  23. jim 11/8/2007 9:28 AM
    Gravatar
    Is there any way to SET the scope_identity? I have an "instead of trigger" that catches duplicates and I want scope_identity to return the ID of the existing row when it already exists.

    Thanks
  24. anonymous proxy 12/6/2007 10:31 PM
    Gravatar
    Is there any reason why i should expect a table to use 0 for an identity(1,1) after a RESTORE? We have just one table (out of 100's!) that seems scripted identically but the first insert delivers a 0 for SCOPE_IDENTITY( ) on first insert.

    a seed of 10 with increment of 2 (just for giggles) causes 0,10,12,14.. I'm totally perplexed. This is on 2005.
  25. Grawerowanie laserowe 12/19/2007 10:14 AM
    Gravatar
    Interesting article!
  26. automotive repair manual 1/10/2008 2:52 AM
    Gravatar
    Is there any accomplishment why i should expect a table to use 0 for an metaphor(1,1) suitable for a RESTORE? We have apt one table (out of 100's!) that seems scripted identically but the anterior inculcate delivers a 0 for SCOPE_IDENTITY( ) on blue ribbon enter.

    a scatter apparentation of 10 with mounting of 2 (exclusively for giggles) causes 0,10,12,14.. I'm totally perplexed. This is on 2005.

    Thanks for clues!
  27. Atlanta New Homes 2/10/2008 6:52 AM
    Gravatar
    We were having similar problems with our server. This really helps us get to the bottom of what is going on. thanks for your help.
  28. Gary 2/11/2008 1:48 PM
    Gravatar
    I am maintaining an existing app that used Create Table statements to generate temp tables. The columns defs are taken from the actual tables in use at the time. As those tables change, the sprocs start erroring.

    My idea was to use a dummy Select INTO .. FROM statement to create the temp tables instead. Since the original table had an identity column (used everywhere in this app), I then tried to set IDENTITY_INSERT ON before calling routines that poulate the temp table. I'm getting the error "explicit value must be ID'd for Identity column...".

    Anybody know how to work around this?

    My code looks like:


    DROP TABLE #temp
    SELECT TOP 1 *
    INTO #temp
    FROM MyRealTable
    WHERE 1=2

    SELECT * FROM #temp
    set identity_insert #temp on

    INSERT INTO #DSLTemp
    EXEC sproc_that_returns_table_row_with_idenity_column

    Thanks for any tips
  29. liz 2/12/2008 8:17 AM
    Gravatar
    distant memory but I think when you need to explicitly name the columns so
    INSERT INTO #DSLTemp
    EXEC sproc_that_returns_table_row_with_idenity_column
    would become

    INSERT INTO #DSLTemp
    (
    colname1,
    colname2.. you get the idea
    )
    EXEC sproc_that_returns_table_row_with_idenity_column
    columns must be in the same order that they come out of the proc in.
  30. Forrest 3/4/2008 11:34 AM
    Gravatar
    Thanks for the tip ... I couldn't remember how to reseed an existing identity column. After forcing a zero in, the next value is two. I know creating the table with a seed value of zero would make more sense, except for seemingly arbitrary business rules that prevent that.
  31. Chandru 4/4/2008 8:07 AM
    Gravatar
    Hi ..

    I have an issue here. I create a DB and some tables through a script in SQL Server. I have lot of tables in DB and quite a lot have identity columns with seed set to 1 and increment set to 1. The scripts executed fine and all the tables created. Now when I do the first insert records into the tables the identity column associated starts with 0 even though the seed is set at 1. Its the case with all the tables where the identity column are set. The first records into all these tables starts with 0 for all the identity columns.

    I could'nt figure out what is causing this issue ..

    Any fix for the issue ..

    thanx
  32. Mahima 4/23/2008 7:22 PM
    Gravatar
    I want to update an identity column. how can it be done ?
  33. Mahesh 5/10/2008 4:47 AM
    Gravatar
    Anybody know how to insert a value for indentity column without using
    set identity_insert Temp on?

    My code looks like:

    CREATE TABLE Temp(
    ID INT IDENTITY(1,1) )

    in this case how can i go for insert statement?

    Thanks for any tips

  34. Vinu 5/29/2008 8:37 AM
    Gravatar
    can i create two identity columns in a single table?
  35. Imran Akram 7/27/2008 11:50 PM
    Gravatar
    hi Ryan, Thanks for the article, it helped me sort out the problem of reseeding the identity values back to start from 1
  36. Dariusz Czechowicz 7/31/2008 7:16 AM
    Gravatar
    Mahesh,

    This should work for you.

    CREATE TABLE Temp(
    ID INT IDENTITY(1,1) )

    INSERT INTO Temp DEFAULT VALUES
  37. Abdulrahman magdy 8/28/2008 5:14 AM
    Gravatar
    i have database with number of tables id of all tables is identity suddenly when i try to insert in a some tables i found an error that say item has already been added. key in dictionary '144184' key being added: '144184' i found the proplem in the identity where the table has 60 record and the identity stop at 40 and doesn't see the last 20 records when i stop the identity from sql server and i turn it on again the identity saw the 60 records and set the identity with the right value i ask for the reasons that cause these please help me
    thanks
  38. Befreiphone Reloaded 9/27/2008 3:06 PM
    Gravatar
    excellent i was just looking for this!! Thanks a lot.
  39. Ramesh Babu Y 11/4/2008 8:46 AM
    Gravatar
    How can i adding Identity Property to an existing Column?
    I am adding like this

    ALTER TABLE tbltemp
    ALTER COLUMN ID INT IDENTITY(1,1) NOT NULL

    But i got an error as "Incorrect syntax near the keyword 'IDENTITY'"

    Any one can help on this ASAP.

    Thanks in advance.
  40. Jamie 11/11/2008 1:13 PM
    Gravatar
    INSERT INTO #DSLTemp
    EXEC sproc_that_returns_table_row_with_idenity_column

    Isn´t there an error?
  41. Evan 11/19/2008 12:34 PM
    Gravatar
    I have created a table from an import of a flat file. The column that would be the identity column just came across as int without identity because of duplicate numbers.

    I have changed the column to Ident with increment of 1 and seed of 1000.

    How can I wipe out the data in that column and repopulate it starting at 1000 and working up?
  42. alex 11/21/2008 5:57 AM
    Gravatar
    thanks for the tips
  43. Matratze 11/21/2008 8:27 AM
    Gravatar
    Does anyone know a way of how to set the scope_identity? I didn't find a way... Thanks for yoru help... ;-)
  44. Vibrationsplatte 12/1/2008 2:04 PM
    Gravatar
    @ Jamie: no why an error. It works wonderfull for me!
  45. Rezepte für Diabetiker 12/2/2008 4:08 AM
    Gravatar
    I also don't get an error. It works perfect...

    @Jamie: What did your exactly do, before you got the error?
  46. Suchmaschinenoptimierung 12/3/2008 6:43 AM
    Gravatar
    I also need help with the scope_identity. I'm sure, I searched everywhere but can't find a way to set the value...

    Thanks for helping me... ;-)
  47. Seitensprung 12/5/2008 5:14 PM
    Gravatar
    Hi Ryan,

    your tips for SQL Server Identity Columns solved my problem. It works and your description is really easy to understand - even for me as a newbie in this sector.

    Thanks a lot for posting this tips. Now I can continue solving the rest of the problems on my site...


  48. Adidas Schuhe 12/10/2008 5:00 AM
    Gravatar
    I've been looking for help in SQL Server Identity Columns and your post really helped me a lot. Thanks!
  49. Mobile SEO 12/11/2008 6:06 PM
    Gravatar
    Your article contains exactly the information I needed. Very good work. Now I don't need to search anymore for these information and saved a lot of time.
  50. Asha Verma 12/12/2008 4:01 AM
    Gravatar
    I have table with around 100 record. Now I want to set identity to primary column so that next value will start from next value of last max number. How can I do this?
  51. Holzhandel 12/13/2008 3:53 AM
    Gravatar
    Thanks Ryan. I don't know that SQL supports "Allowing inserts to identity columns".
    Greets Holzhandel
  52. Partnersuche 12/13/2008 9:08 PM
    Gravatar
    This is the information about server identity colums on sql servers, I've been looking for. Your article helps me a lot. Thanks for sharing this tips.
  53. Enda 1/7/2009 10:14 AM
    Gravatar
    Hi Ryan,

    I have a table with a timestamp column and a locked (bit) column.
    When I am updating the locked column I dont want SQL Server to increment the timestamp column. Any ideas??
  54. Partnerbörse 1/11/2009 4:56 AM
    Gravatar
    While I was migrating some data to a new database, I stumpled upon the need to make inserts into an identity column - the crazy thing is, that I´ve searched the msdn for about 10 minutes, before I tried google and found this very helpful post. Thanks a lot!
  55. Mickey 1/15/2009 4:18 AM
    Gravatar
    I am use something like this when I migrating data from one to another database and tables with identity columns:

    SET IDENTITY_INSERT dbo.Temp ON
    IF EXISTS(SELECT * FROM [DiffDatabase].dbo.Temp)
    EXEC ('INSERT INTO Temp (column1, column2)
    SELECT column1, column2 FROM [DiffDatabase].dbo.Temp')
    SET IDENTITY_INSERT dbo.Temp OFF
  56. ujwala 2/11/2009 11:08 PM
    Gravatar
    Hi Ryan,

    Your post is very helpfull.

    Can I have two IDENTITY columns in a single table?

    Currently I am working on a project related to Sequences and identity columns.

  57. PC USB Mikroskop 3/3/2009 8:32 AM
    Gravatar
    this article rocks. first result on google and the right post :) thanks.
  58. Joe Quaglia 3/4/2009 11:38 AM
    Gravatar
    I would like to use the SET IDENTITY_INSERT ON command in the context of doing a bcp for multiple tables. Currently, I have say 30 tables that I would like to reload and I want to keep the Ids that the bcp flat files have. I have tried using SET_IDENTITY_INSERT ON, but it does not seem to work. I guess my question is does the SET_IDENTITY_INSERT ON work for a bcp operation. I know it works for an SQL INSERT statement. Thanks in advance for the help.
  59. Joe Quaglia 3/4/2009 2:22 PM
    Gravatar
    The -E switch on the bcp was the answer to my question. It works very well and lets you automate things.
  60. Hosting 3/9/2009 1:13 AM
    Gravatar
    I've been looking for help in SQL Server Identity Columns and your post really helped me a lot. Thanks..
    very nice thankss
  61. 3/16/2009 8:41 AM
    Gravatar
    Acerca de los campos IDENTITY en MSSQL | Jorge Iv??n Meza Mart??nez
  62. Miami Houses, Atlanta houses 3/27/2009 12:41 AM
    Gravatar
    This is the information about server identity colums on sql servers, I've been looking for. Your article helps me a lot. Thanks for sharing this tips. thanks again for the great article.
  63. betclic 4/2/2009 8:04 AM
    Gravatar
    Thanks for sharing ! Very usefull for a noob like me !
  64. Matratze 4/16/2009 5:23 AM
    Gravatar
    I have difficulties with the processing of MySQL very much. This article has further helped me. Thanks for good work.
  65. rechtsberatung 4/16/2009 2:24 PM
    Gravatar
    Thank you for sharing with us! It helped me a lot as I had trouble with MySQL processes.
  66. Amoyachts Ibiza yachtcharter 4/21/2009 5:53 AM
    Gravatar
    I am looking for infos about SQL Server Identity.
    Your article is great and was really helpful! TY
  67. sem 5/8/2009 11:52 AM
    Gravatar
    Thanks, helped me a lot in my new project..
  68. Bayerischer Wald 5/27/2009 4:37 AM
    Gravatar
    Thanks for the basics Ryan. I haven't understand before. Your site is a fantastic ressource for newcomer.
    Greets Bayerischer Wald
  69. yonja 7/15/2009 4:16 PM
    Gravatar
    I've been looking for help in SQL Server Identity Columns and your post really helped me a lot. Thanks!
  70. alan adi 7/19/2009 12:52 AM
    Gravatar
    i am new to sql and thankful for every information. thx for your article.
  71. sohbet 7/19/2009 4:09 AM
    Gravatar
    I’m really very useful to follow a long-time see this as a blog here Thank you for your valuable information.
  72. Kinderbücher 7/27/2009 3:12 AM
    Gravatar
    Nice article thanks, this site is really a good source for solving all kind of problems. Greetings from Kinderbücher
  73. Liliana 7/29/2009 10:30 AM
    Gravatar
    Thank you for your tips, I solve the problem with these instructions..
  74. Hosting 9/1/2009 10:47 PM
    Gravatar
    his is the information about server identity colums on sql servers, I've been looking for. Your article helps me a lot. Thanks.
  75. Jakob 10/1/2009 5:58 AM
    Gravatar
    Cool article Ryan, Thanks
  76. Kind Weihnachtsgeschenk 11/9/2009 3:33 AM
    Gravatar
    Thanks for the information about server identity colums on sql servers. Your article helps me a lot. Thanks from Kind Weihnachtsgeschenk.
  77. Sanyusha 12/31/2009 2:30 AM
    Gravatar
    Thanks for very useful tips!
  78. Dan Douglas 1/29/2010 6:23 AM
    Gravatar

    (Just wanted to comment on the 'Identity Update' post that someone else posted earlier and why it might be useful in 'some' cases)

    An identity update (not insert) would be useful in my situation. I'm trying to change some identity primary key IDs in one table so that they will cascade update to the related tables (just turned cascade updates on for this purpose). The only reason I am doing this is that it's a 'one off' situation where I need to massage the data (change IDs) to merge two systems into one.

    The other option for this would be to turn off the identity completely, make my changes, and then turn it back on. Unfortunately, this operation will require more down time than if I could do an 'Identity Update'.
  79. Linkaufbau 1/31/2010 4:43 PM
    Gravatar
    I always received an error, when trying to insert data from same source to a table with an identity column.

    Your Tips really helped me to understand, how to solve the problem.

    Great Thanks, Ryan... ;-)
  80. jason 5/25/2010 10:57 PM
    Gravatar
    how to seed the identity column, in sql server 2005, its disabled .
  81. Phil Hellmuth 5/31/2010 12:54 PM
    Gravatar
    Easily, the post is really the greatest on this laudable topic. I concur with your conclusions and will thirstily look forward to your future updates. Saying thanks will not just be sufficient, for the fantastic c lucidity in your writing. I will instantly grab your rss feed to stay privy of any updates. Solid work and much success in your business enterprise!
  82. Amol 7/5/2010 2:04 AM
    Gravatar
    HI,

    Can i update identity column?

    Thanks,

    AMOL
  83. sohbet 8/4/2010 3:27 AM
    Gravatar
    thanx for sharing.
  84. 8/28/2012 2:24 PM
    Gravatar
    Default Dimension Members and Identity Columns | Microsoft Enterprise Technologies
  85. 12/11/2014 8:18 PM
    Gravatar
    SQL server identity column values start at 0 instead of 1 | Ysquierdo Answers
  86. 3/7/2016 1:44 PM
    Gravatar
    Default Dimension Members And Identity Columns | Microsoft Enterprise Technologies
Comments have been closed on this topic.



 

News


Also see my CRM Developer blog

Connect:              


Sponsor

Sections