Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am using SQL Server 2008 to maintain a database where I have an userAcccounts table where the user details are stored while registering on the website. The userID column is an integer with identity on.

What I have noticed is that somehow, the column is skipping some ids (random number of ids, sometimes it skips 5,11,10 etc), i.e., the ID's are not consecutive. I have a read a similar question here, with a similar issue which suggested

"An identity sequence is incremented any time an insert is attempted -- this includes a failed or rolled back INSERT"

I have checked my logs for errors, but there were no error during the period when the ids were skipped. Also my insert is not within any transaction for a rollback to happen, because there is no requirement of deleting an account. Also no other tables are involved during the registration

Although this is not a major issue as the ids will be unique nevertheless, what I would like to find out is why this behavior? Perhaps I am overlooking something.

Are there any other scenarios where this kind of behavior can occur? i.e. the identity column skipping a few ids? Could this be an issue with concurrent access?

here is the table's create script. 

CREATE TABLE [dbo].[UserAccounts](
    [AccountID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](30) NULL,
    [LastName] [varchar](30) NULL,
    [Gender] [varchar](10) NULL,
    [Email] [varchar](150) NULL,
    [EmailVerified] [bit] NULL,
    [UserName] [varchar](30) NULL,
    [Password] [varchar](50) NULL,
    [ProfilePicture] [varchar](150) NULL,
    [BackgroundPicture] [varchar](150) NULL,
    [DateOfBirth] [smalldatetime] NULL,
    [CreateDate] [smalldatetime] NULL,
    [LastUpdatedOn] [smalldatetime] NULL,
    [Points] [int] NULL,
    [CurrentBadge] [varchar](30) NULL,
    [FBID] [varchar](50) NULL,
    [TwitterID] [varchar](50) NULL,
    [Abused] [int] NULL,
    [isActive] [bit] NULL,
    [AccountTypeID] [int] NULL,
    [DateStamp] [timestamp] NOT NULL,
    [Location] [varchar](50) NULL,
    [About] [varchar](250) NULL,
    [UsingBackgroundPicture] [bit] NULL,
 CONSTRAINT [PK__UserAcco__349DA5867F60ED59] PRIMARY KEY CLUSTERED 
(
    [AccountID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Any help in this regard is appreciated? Also if you require any other detail, please let me know.

Thanks

share|improve this question
3  
An identity might be skipped if (a) the transaction to insert a row has been rolled back, or (b) when a row is deleted etc. - there are lots of reasons. Just don't worry about this - it's normal, it's defined and documented behavior - just accept it and move on. – marc_s 21 hours ago
possible duplicate of Why are there gaps in my IDENTITY column values? and many others – Pondlife 10 hours ago

1 Answer

up vote 0 down vote

It's impossible to guarantee exact sequentiality of Sequences or IDs, without globally locking the entire database on every transaction -- to prevent any concurrent transactions, which could possibly also ask for an ID.

This would obviously reduce it to effectively a single-user database, and totally destroy performance.

For such reasons, there is explicitly no guarantee of continuous sequentiality.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.