Inconsistent identity values

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

Re: Inconsistent identity values

Check out "DBCC CHECKIDENT" in BOL.

Gert-Jan


"Bill E." wrote:
>
> 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

Re: Inconsistent identity values

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:
> Check out "DBCC CHECKIDENT" in BOL.
>
> Gert-Jan
>
>
> "Bill E." wrote:
> >
> > 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

Re: Inconsistent identity values

Bill E. (billmiami2@netscape.net) writes:
> 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.

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Inconsistent identity values

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@netscape.net) writes:
> > 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.
>
> 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/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Inconsistent identity values

Bill E. (billmiami2@netscape.net) writes:
> 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?

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx