A SQL Server table can have only one identity column, and a second one fails with Msg 2744. This second identity column workaround compares a computed column with a sequence, shows what each does with gaps, rollbacks and large values, and explains when to pick which.
Nonclustered Primary Key Test: Last Page Insert Waits
A nonclustered primary key test shows whether a heap fixes last page insert waits. On SQL Server 2025 it doesn’t, because the key index still rises. A lane layout that spreads the inserts removes the latch wait, and the post shows the cost of that layout.
OPTIMIZE_FOR_SEQUENTIAL_KEY: Test the Last Page Insert Fix
OPTIMIZE_FOR_SEQUENTIAL_KEY is an index option that targets last page insert contention. This post tests it with 32 connections that insert into a clustered identity key, with the option off and on. The option changes the kind of waiting, and the post shows how to decide whether it helps your table.
SQL SERVER – Last Page Insert PAGELATCH_EX Contention Due to Identity Column
Today’s goal is to understand what is the Last Page Insert PAGELATCH_EX Contention Due to Primary Key. Let us start understanding it together.
SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE
Has your identity column ever jumped by 1000 after a restart? SQL Server 2017 fixes it with IDENTITY_CACHE. Here is a demo with the setting ON and OFF.
When to Use IDENT_CURRENT? – Interview Question of the Week #150
When should you use IDENT_CURRENT instead of SCOPE_IDENTITY()? A question I heard at the Group By conference, answered with the key difference.
ALTER Column From INT to BIGINT: Errors and Safe Solutions
To change a column from INT to BIGINT, you first drop every key and index that depends on it, because ALTER COLUMN refuses with Msg 5074 and Msg 4922. This post builds a small ticket system, shows the errors, and changes the key and its foreign key inside one transaction. It also shows how a negative identity reseed buys time, and when to copy the table instead.