Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IDENTITY(1,1) lost numbers Expand / Collapse
Author
Message
Posted Monday, January 19, 2009 9:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 10, 2013 10:34 AM
Points: 7, Visits: 21
Can you help me, please?

I created a table with this:

id_form int IDENTITY(1,1) NOT NULL,
PRIMARY KEY (id_form)

SELECT COUNT(*)
FROM table
UNION
SELECT MAX(id_form)
FROM table

The results are:
622
688

Why didn't generate some id's?
For example:
from id_form 83 jump to 85, what happened with 84.

Thanks for your attention.
Post #639413
Posted Monday, January 19, 2009 10:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
maybe the rows were deleted
Post #639415
Posted Monday, January 19, 2009 10:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 40,438, Visits: 36,895
That'll happen any time an insert doesn't succeed (due to a constraint violation, a datatype conversion error or a trigger rollback) or when rows are deleted. The id will be assigned as the insert starts, and the current identity seed incremented. If the insert doesn't succeed, the identity seed is not decremented again (there may already have been another insert)

It's normal with an identity column



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #639416
Posted Monday, January 19, 2009 2:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 10, 2013 10:34 AM
Points: 7, Visits: 21
Thanks.

GilaMonster (1/19/2009)
That'll happen any time an insert doesn't succeed (due to a constraint violation, a datatype conversion error or a trigger rollback) or when rows are deleted. The id will be assigned as the insert starts, and the current identity seed incremented. If the insert doesn't succeed, the identity seed is not decremented again (there may already have been another insert)

It's normal with an identity column
Post #639511
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse