Tips for SQL Server Identity Columns

RSS 2.0 Feed
RSS 2.0

Atom 1.0 Feed
Atom 1.0

Sunday, December 19, 2004 8:39 PM Filed under:

General

SQL/Data

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.

Comments

Joe Chin 12/20/2004 1:41 PM

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...
Ryan Farley 12/20/2004 2:00 PM

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
ESquared (Erik Eckhardt) 1/14/2005 12:53 PM

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.
Ryan Farley 1/14/2005 1:25 PM

Hi Erik,

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

Thanks,
-Ryan
mikep@aicorporation.com 10/4/2005 7:49 AM

In T-SQL how do you find which tabke, (if any), currently has identity_insert ON ?
Mike P.
MOWS 1/19/2006 1:21 PM

Question: Is there something like a "set identity_update on" ??

I need to do an update on my identity colum
Ryan Farley 1/19/2006 1:24 PM

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
Pradeep 2/23/2006 9:45 PM

It was informative... Thanks,,
bob 8/9/2006 12:36 PM

thanks for the tips
Bear 8/30/2006 5:45 AM

Excellent tip on "dbcc checkident" - couldn't find that anywhere else!
Tom 11/7/2006 2:23 PM

"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
JT 11/27/2006 7:31 AM

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
free web hosting 2/16/2007 3:21 AM

Nice article thanks!
Gurpreet 3/15/2007 12:39 AM

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...!!
sethu 3/20/2007 3:09 AM

thankkkssss for the tips
SD 3/26/2007 3:52 AM

excellant just what i was looking for !!

Thanks
Sanjay Kattimani 4/17/2007 7:24 AM

Quick tip
IVILLA 6/22/2007 7:54 AM

Gurpreet, when you have an failed insertion or a transaction rollback the internal number also is increased, although the registry was not registered
kyle 7/10/2007 12:11 AM

very good tip
Jim 9/11/2007 1:51 PM

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!
Jim 9/11/2007 2:11 PM

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
proxy site 10/15/2007 8:41 PM

...In T-SQL how do you find which tabke, (if any), currently has identity_insert ON ?
Mike P.
..
jim 11/8/2007 9:28 AM

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
anonymous proxy 12/6/2007 10:31 PM

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.
Grawerowanie laserowe 12/19/2007 10:14 AM

Interesting article!
automotive repair manual 1/10/2008 2:52 AM

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!
Atlanta New Homes 2/10/2008 6:52 AM

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.
Gary 2/11/2008 1:48 PM

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
liz 2/12/2008 8:17 AM

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.
Forrest 3/4/2008 11:34 AM

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.
Chandru 4/4/2008 8:07 AM

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
Mahima 4/23/2008 7:22 PM

I want to update an identity column. how can it be done ?
Mahesh 5/10/2008 4:47 AM

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
Vinu 5/29/2008 8:37 AM

can i create two identity columns in a single table?
Imran Akram 7/27/2008 11:50 PM

hi Ryan, Thanks for the article, it helped me sort out the problem of reseeding the identity values back to start from 1
Dariusz Czechowicz 7/31/2008 7:16 AM

Mahesh,

This should work for you.

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

INSERT INTO Temp DEFAULT VALUES
Abdulrahman magdy 8/28/2008 5:14 AM

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
Befreiphone Reloaded 9/27/2008 3:06 PM

excellent i was just looking for this!! Thanks a lot.
Ramesh Babu Y 11/4/2008 8:46 AM

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.
Jamie 11/11/2008 1:13 PM

INSERT INTO #DSLTemp
EXEC sproc_that_returns_table_row_with_idenity_column

Isn´t there an error?
Evan 11/19/2008 12:34 PM

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?
alex 11/21/2008 5:57 AM

thanks for the tips
Matratze 11/21/2008 8:27 AM

Does anyone know a way of how to set the scope_identity? I didn't find a way... Thanks for yoru help... ;-)
Vibrationsplatte 12/1/2008 2:04 PM

@ Jamie: no why an error. It works wonderfull for me!
Rezepte für Diabetiker 12/2/2008 4:08 AM

I also don't get an error. It works perfect...

@Jamie: What did your exactly do, before you got the error?
Suchmaschinenoptimierung 12/3/2008 6:43 AM

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... ;-)
Seitensprung 12/5/2008 5:14 PM

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...
Adidas Schuhe 12/10/2008 5:00 AM

I've been looking for help in SQL Server Identity Columns and your post really helped me a lot. Thanks!
Mobile SEO 12/11/2008 6:06 PM

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.
Asha Verma 12/12/2008 4:01 AM

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?
Holzhandel 12/13/2008 3:53 AM

Thanks Ryan. I don't know that SQL supports "Allowing inserts to identity columns".
Greets Holzhandel
Partnersuche 12/13/2008 9:08 PM

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.
Enda 1/7/2009 10:14 AM

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??
Partnerbörse 1/11/2009 4:56 AM

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!
Mickey 1/15/2009 4:18 AM

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
ujwala 2/11/2009 11:08 PM

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.
PC USB Mikroskop 3/3/2009 8:32 AM

this article rocks. first result on google and the right post :) thanks.
Joe Quaglia 3/4/2009 11:38 AM

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.
Joe Quaglia 3/4/2009 2:22 PM

The -E switch on the bcp was the answer to my question. It works very well and lets you automate things.
Hosting 3/9/2009 1:13 AM

I've been looking for help in SQL Server Identity Columns and your post really helped me a lot. Thanks..
very nice thankss
3/16/2009 8:41 AM

Acerca de los campos IDENTITY en MSSQL | Jorge Iv??n Meza Mart??nez
Miami Houses, Atlanta houses 3/27/2009 12:41 AM

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.
betclic 4/2/2009 8:04 AM

Thanks for sharing ! Very usefull for a noob like me !
Matratze 4/16/2009 5:23 AM

I have difficulties with the processing of MySQL very much. This article has further helped me. Thanks for good work.
rechtsberatung 4/16/2009 2:24 PM

Thank you for sharing with us! It helped me a lot as I had trouble with MySQL processes.
Amoyachts Ibiza yachtcharter 4/21/2009 5:53 AM

I am looking for infos about SQL Server Identity.
Your article is great and was really helpful! TY
sem 5/8/2009 11:52 AM

Thanks, helped me a lot in my new project..
Bayerischer Wald 5/27/2009 4:37 AM

Thanks for the basics Ryan. I haven't understand before. Your site is a fantastic ressource for newcomer.
Greets Bayerischer Wald
yonja 7/15/2009 4:16 PM

I've been looking for help in SQL Server Identity Columns and your post really helped me a lot. Thanks!
alan adi 7/19/2009 12:52 AM

i am new to sql and thankful for every information. thx for your article.
sohbet 7/19/2009 4:09 AM

I’m really very useful to follow a long-time see this as a blog here Thank you for your valuable information.
Kinderbücher 7/27/2009 3:12 AM

Nice article thanks, this site is really a good source for solving all kind of problems. Greetings from Kinderbücher
Liliana 7/29/2009 10:30 AM

Thank you for your tips, I solve the problem with these instructions..
Hosting 9/1/2009 10:47 PM

his is the information about server identity colums on sql servers, I've been looking for. Your article helps me a lot. Thanks.
Jakob 10/1/2009 5:58 AM

Cool article Ryan, Thanks
Kind Weihnachtsgeschenk 11/9/2009 3:33 AM

Thanks for the information about server identity colums on sql servers. Your article helps me a lot. Thanks from Kind Weihnachtsgeschenk.
Sanyusha 12/31/2009 2:30 AM

Thanks for very useful tips!
Dan Douglas 1/29/2010 6:23 AM

(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'.
Linkaufbau 1/31/2010 4:43 PM

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... ;-)
jason 5/25/2010 10:57 PM

how to seed the identity column, in sql server 2005, its disabled .
Phil Hellmuth 5/31/2010 12:54 PM

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!
Amol 7/5/2010 2:04 AM

HI,

Can i update identity column?

Thanks,

AMOL
sohbet 8/4/2010 3:27 AM

thanx for sharing.
8/28/2012 2:24 PM

Default Dimension Members and Identity Columns | Microsoft Enterprise Technologies
12/11/2014 8:18 PM

SQL server identity column values start at 0 instead of 1 | Ysquierdo Answers
3/7/2016 1:44 PM

Default Dimension Members And Identity Columns | Microsoft Enterprise Technologies