2015-02-09

MSSQL 2012 server failure results in Identity gaps

In SQL Server 2012, the database engine changes its mechanism for generating Identity values. Prior to SQL Server 2012, identity have no any cache in memory. SQL Server 2012 introduces cache in identity, leading to gaps will be resulted after server failover (ref.: Failover or Restart Results in Reseed of Identity). Below is the cache values for different data types.
typeidentity
TINYINT10
SMALLINT100
INT1000
BIGINT10000
NUMERIC10000
You can do a simple test to demonstrate how a server failure will result a gap in identity values:
IF OBJECT_ID(N'dbo.T1' , N'U') IS NOT NULL DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 (keycol INT IDENTITY);
GO
INSERT INTO dbo.T1 DEFAULT VALUES;
GO 2
SELECT IDENT_CURRENT(N'dbo.T1');
The result is 2.
To force an unclean termination of the SQL Server process, open Task Manager (Ctrl+Shift+Esc), right-click the SQL Server process, and choose End task.
Next, start the SQL Server using SQL Server Configuration Manager.
Then query the current identity value again:
SELECT IDENT_CURRENT(N'dbo.T1');
The result is 1001.

No comments:

Post a Comment