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?
Webdesign 7/27/2008 2:52 AM

excellent i was just looking for this!! Thanks a lot.
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.