SQL Server 2005

We have a table where the current identity value is lower than the
maximum value. This is the first time I've seen such a thing. How
can this occur? I see postings about this problem but they seem to
concern older versions of SQL Server.

I did some tests on another table to see if I could reproduce the
problem by setting Identity_Insert on and then forcing the identity to
a value. I then set Identity_Insert off and added a row normally, but
the next identity was always the next largest number, not some number
in between.

Bill E.
Hollywood, FL

Check out "DBCC CHECKIDENT" in BOL.

Gert-Jan


"Bill E." wrote:

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

Gert-Jan Strik wrote:

Bill E. (billmiami2 (AT) netscape (DOT) net) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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


Erland Sommarskog wrote:

Bill E. (billmiami2 (AT) netscape (DOT) net) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx