Identity column increaments randomly
-
Hi ,
I have sql 2000 db which has identity column in a table. It has been fine for a while but in few months the identity column value is increments abnormally with out any clue, some it increaments with 2, 4 , 7 .....
Please help me or give a clue what would have happend.
Thanks
venkat
venkatesh -
Define the identity property is no guarantee for a gapless sequence.
CREATE TABLE MyIdentity(
id INT IDENTITY(1,1) NOT NULL,
some_other VARCHAR(10)
)
GO
BEGIN TRAN
INSERT INTO MyIdentity VALUES('Erster')
COMMIT TRAN
BEGIN TRAN
INSERT INTO MyIdentity VALUES('Zweiter')
ROLLBACK TRAN
BEGIN TRAN
INSERT INTO MyIdentity VALUES('Dritter')
COMMIT TRAN
SELECT * FROM MyIdentity
DROP TABLE MyIdentity
GO
ergibt einen Output von
id some_other
----------- ----------
1 Erster
3 Dritter
(2 row(s) affected)
Can this also be your point?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
And to add to Frank's post, deleted rows would cause the appearance of gaps.
------------
Ray Higdon MCSE, MCDBA, CCNA -
In addition to Ray's addition
:Having more Deadlocks lately ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Also, if any any inserts are done to your table, and the transaction is rolled back, the identities don't get re-used and will therefore cause gaps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply