Sql-server – Where does SQL Server physically store the IDENTITY VALUE for a table

database-internalsidentitysql serversql server 2014

I'm hoping someone can point me in the right direction on this one. Here's my workings out so far.

SELECT * FROM sys.identity_columns is a system view which gives "last_value" but the definition for that view uses an internal function IdentityProperty(colName, 'LastValue') – so that's a dead end (not pulling it from a system table there).

Everywhere (I've looked) on the internet suggests using DBCC IDENT_... commands to uncover the value but that still leaves me in the dark as to where it's actually stored.

So, I arrived at searching the individual pages with DBCC PAGE(TestDB,1,1325,3) against my test harness db and using the RESEED command to reseed between values 10 and 12.

In doing this, I noticed the hex values on the IAM: Header, IAM: Single Page Allocations and IAM: Extent Alloc Status Slot 1 all changed. (And realised they change periodically anyway along with the bUse1 value which changes incrementally by itself too).

So another dead-end and I'm all out of ideas. Where else can I search?

I'm running SQL Server 2014. I have an insatiable thirst for internals knowledge and have yet to come across anything as elusive as this. It's caught my attention because in theory, it (an absolute value) is stored somewhere and should (arguably) be locatable. In my quest to unearth locations of internally stored data / meta data, this particular value strikes me as particularly elusive. I'm guessing/hoping that someone will come along and tell me, you can get it with DBCC PAGE but I was looking in the wrong place.

Best Answer

If you can access the DAC (Dedicated Administrator Console), you can inspect the value of the identity column, for INT columns, by looking at the idtval column in sys.syscolpars.

Thanks to Martin Smith for directing me to that table via this very useful answer by Roi Gavish on a related question here.

Take, for instance, the following temporary table:

USE tempdb;

CREATE TABLE #d
(
    ID INT NOT NULL IDENTITY(1,1)
);

TRUNCATE TABLE #d;

DBCC CHECKIDENT ('#d',RESEED, 2147483635);

INSERT INTO #d DEFAULT VALUES;

Let's see what the table contains:

SELECT *
FROM #d;

+------------+
| ID         |
+------------+
| 2147483635 |
+------------+

The identity value can be inspected by this code:

DECLARE @idtval VARBINARY(64);

SELECT @idtval = scp.idtval
FROM sys.syscolpars scp
    INNER JOIN sys.objects o ON scp.id = o.object_id
WHERE o.name LIKE '#d____%'

DECLARE @LittleEndian NVARCHAR(10);
SET @LittleEndian = LEFT(sys.fn_varbintohexstr(@idtval), 10);
SELECT @LittleEndian;
DECLARE @BigEndian NVARCHAR(10) = '0x';
DECLARE @Loop INT = 0;
WHILE @Loop < 4
BEGIN
  SET @BigEndian = @BigEndian + SUBSTRING(@LittleEndian, ((4 - @Loop) * 2) + 1, 2);
  SET @Loop += 1;
END
SELECT CurrentIdentityValue = CONVERT(INT, 
    CONVERT(VARBINARY(32), @BigEndian, 1), 2);

+----------------------+
| CurrentIdentityValue |
+----------------------+
|                      |
| 2147483635           |
+----------------------+

For BIGINT identity columns, we need to expand the size of some variables used in the code, such as:

CREATE TABLE #dBig
(
    ID BIGINT NOT NULL IDENTITY(1,1)
);

TRUNCATE TABLE #dBig;

DBCC CHECKIDENT ('#dBig',RESEED, 9223372036854775704);

INSERT INTO #dBig DEFAULT VALUES;

SELECT *
FROM #dBig;


DECLARE @idtval VARBINARY(64);

SELECT @idtval = scp.idtval
FROM sys.syscolpars scp
    INNER JOIN sys.objects o ON scp.id = o.object_id
WHERE o.name LIKE '#dBig____%'

DECLARE @LittleEndian NVARCHAR(18);
SET @LittleEndian = LEFT(sys.fn_varbintohexstr(@idtval), 18);
DECLARE @BigEndian NVARCHAR(18) = '0x';
DECLARE @Loop INT = 0;
WHILE @Loop < 8
BEGIN
  SET @BigEndian = @BigEndian + SUBSTRING(@LittleEndian, ((8 - @Loop) * 2) + 1, 2);
  SET @Loop += 1;
END
SELECT CurrentIdentityValue = CONVERT(BIGINT, 
    CONVERT(VARBINARY(32), @BigEndian, 1), 2);

Results for the BIGINT:

+----------------------+
| CurrentIdentityValue |
+----------------------+
|                      |
| 9223372036854775704  |
+----------------------+
Related Question