SQL Server identity column skipping ids on insert - no errors detected in logs

Question

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

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, May 07 '13 at 04:55
possible duplicate of [Why are there gaps in my IDENTITY column values?](http://stackoverflow.com/questions/14642013/why-are-there-gaps-in-my-identity-column-values) and [many others](http://stackoverflow.com/search?q=sql+server+identity+gaps) — Pondlife, May 07 '13 at 15:51

score 0 · Answer 1 · answered May 07 '13 at 05:01

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.

SQL Server identity column skipping ids on insert - no errors detected in logs

1 Answers1