Identity column starts from zero instead of one

Question

Post reply

Identity column starts from zero instead of one

vkelman

SSC-Addicted

Points: 406
More actions
July 29, 2009 at 10:38 am

#208800

We experience a strange problem. Identity field of a table is set to [1,1] by generating script. Every time, when we drop the whole database, and then re-create it by running script in SQL Server 2008 Management Studio, and then insert a record into that table programmatically (C# code), SQL Server set identity field value of that first record to 0 instead of 1!
Is it a SQL Server 2005 / 2008 bug?
http://pro-thoughts.blogspot.com/2008/03/identity-starts-from-0-instead-of-1-sql.html
http://blogs.x2line.com/al/archive/2003/12/13/158.aspx
http://www.scriptstar.co.uk/?p=17
Vladimir Kelman
http://pro-thoughts.blogspot.com/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

I've never run into that one before. It sounds like a bug or possibly a coding error.

I'm recreating databases pretty regularly in 2008 and I haven't seen this. Just a quick test:

CREATE DATABASE MyTest;

GO

USE MyTest;

CREATE TABLE dbo.X

(Id INT IDENTITY(1,1)

,Val NVARCHAR(50));

INSERT INTO dbo.X

(Val)

VALUES ('a'),

('b'),

('c');

SELECT * FROM dbo.X;

USE master;

GO

DROP DATABASE MyTest;

This worked every time. The values coming out are numbered 1-3. Does this work on your system?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

vkelman SSC-Addicted Points: 406 More actions · Answer 2

I'll double-check and post result here, thank you.

Vladimir Kelman
http://pro-thoughts.blogspot.com/

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Can you check that your code doesn’t run dbcc checkident that reseeds the table? Also can you check if the code doesn’t insert the zero by enabling identity insert on the table and explicitly inserting the value

zero?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

vkelman SSC-Addicted Points: 406 More actions · Answer 4

I'm sorry I'll be on a vacation for 2 weeks. I'll check it and post results when I back.

Vladimir Kelman
http://pro-thoughts.blogspot.com/

Narendra-243090 SSC Rookie Points: 46 More actions · Answer 5

Hopefully, you had a nice holiday and recharged your batteries.

http://www.scriptstar.co.uk/?p=17

You were asking me to explain how my code works.

First of all, I am not sure why SQL server is starting some tables with identity column 0 and some tables with 1 after using reseed.

Actually I wrote a procedure and it will start by checking whether the table in the database is having any identity column set or not.

If the table has an identity column then it will try to truncate the table data and set the reseed to 1. If you don't use truncate then table identity column is starting from 0.

But you can't use truncate for all the tables, some tables may throw an exception (you can use delete statement for these tables) so I wrapped the above statement with TRY block.

If it throws some error then I can catch that and use delete statement and reseed to 0.

Hope this explanation is helpful.

May not be logical but works fine for me. Have you tried and tested my solution?

Cheers,

Narendra

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

vkelman (7/29/2009)
SQL Server set identity field value of that first record to 0 instead of 1!

Uh huh... and where is that value coming from? I mean, what is the code that told you the "first record" had a value of zero? The only time I've seen this happen is when someone does asks for the "current" identity before the first row is actually commited. Look at the table with SSMS and verify that a record 0 actually exists.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 7

Yeah, I'm with Jeff on this. You can rely on TRUNCATE resetting to the original definition. I have not seen "0" popping up, ever, after rebuilding a table, truncating, or correctly reseeding. You can get a "0" by reseeding it to start at "0" or by doing an IDENTITY_INSERT to make a "0." Or you could get really weird and make the seed value a positive number but make the increments negative and at some point you'll get a "0" but that should be expected.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning