sql server (alternate): Inconsistent identity values -- sql server (alternate)

Re: Inconsistent identity values

Gert-Jan Strik
8/18/2007 3:39:57 PM

Check out "DBCC CHECKIDENT" in BOL.

Gert-Jan

[quoted text, click to view]

Re: Inconsistent identity values

Erland Sommarskog
8/19/2007 12:00:00 AM

Bill E. (billmiami2@netscape.net) writes:
[quoted text, click to view]

Look at this:

CREATE TABLE ident (a int IDENTITY (1, 1) PRIMARY KEY,
b char(2) NOT NULL UNIQUE)
go
INSERT ident (b) VALUES ('AB')
INSERT ident (b) VALUES ('XZ')
INSERT ident (b) VALUES ('AB')
INSERT ident (b) VALUES ('AB')
INSERT ident (b) VALUES ('AB')
INSERT ident (b) VALUES ('AB')
INSERT ident (b) VALUES ('AB')
INSERT ident (b) VALUES ('AB')
INSERT ident (b) VALUES ('AB')
INSERT ident (b) VALUES ('CD')
go
DBCC CHECKIDENT(ident, RESEED, 5)
go
INSERT ident(b) VALUES ('EF')
SELECT ident_current('ident'), a, b FROM ident ORDER BY a
go
DROP TABLE ident

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Re: Inconsistent identity values

8/19/2007 5:16:26 AM

I'm aware of this and it certainly is easy to fix, but it doesn't give
me insight into how this could have happened in the first place.

Bill

[quoted text, click to view]

Re: Inconsistent identity values

8/19/2007 6:35:02 AM

That's fine Erland, but you had to reseed the identity in order to get
the next value to fall below an existing one.
I'll look into it but I seriously doubt that anyone did this. Is
there another way that this could happen?
For example, a sudden shutdown of the server or some kind of error in
a DTS process?

Bill

[quoted text, click to view]

Re: Inconsistent identity values

Erland Sommarskog
8/19/2007 4:02:26 PM

Bill E. (billmiami2@netscape.net) writes:
[quoted text, click to view]

How much below is it? In SQL 6.5 it was indeed a common problem that
the identity values fell behind when there was a unexpected shutdown,
but I have not seen this with SQL 2000 and SQL 2005. In any case, it
the value is just a little behind the max, one could suspect something
like that. But it if the difference is big, I rather place my bets on
DBCC CHECKIDENT or some other manoeuvre, although I can't think of one
right now.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server (alternate) group.