I have taken three versions of the same phone call. An auditor pulls a year of invoice numbers and finds holes in the sequence. A developer's INSERT starts failing with a primary key violation on a table whose identity column has worked for six years. And the one that ruins a weekend: Msg 8115, Arithmetic overflow error converting IDENTITY to data type int, on the busiest table in the system, every insert stopped.
All three come from one feature, and the feature is eight characters of DDL that nobody reviews.
Everything below applies to SQL Server 2012 through 2025 unless a version is named. One edition exception: table partitioning, which the partition-switching remediation rests on, was Enterprise-only until SQL Server 2016 SP1. The survey query reads sys.dm_db_partition_stats, so the caller needs VIEW DATABASE STATE and VIEW DEFINITION, or VIEW DATABASE PERFORMANCE STATE and VIEW SECURITY DEFINITION on 2022 and later, which replaced them for database scoped DMVs.
Identity promises less than you think
Microsoft's list of what the identity property does not guarantee is worth reading before you defend a number sequence to an auditor. No uniqueness (that is the PRIMARY KEY or UNIQUE constraint's job), no consecutive values inside a transaction, no consecutive values after a restart. On reuse the documentation is blunt: "If a particular insert statement fails, or if the insert statement is rolled back then the consumed identity values are lost and aren't generated again."
The property goes on tinyint, smallint, int, bigint, decimal(p, 0), or numeric(p, 0). One per table, default (1,1). A gap takes twenty seconds to produce:
CREATE TABLE dbo.Invoice
(
InvoiceID int IDENTITY(1,1) CONSTRAINT PK_Invoice PRIMARY KEY,
Amount decimal(19,4) NOT NULL CONSTRAINT CK_Invoice_Amount CHECK (Amount > 0)
);
GO
INSERT dbo.Invoice (Amount) VALUES (100.00); -- gets 1
BEGIN TRANSACTION;
INSERT dbo.Invoice (Amount) VALUES (200.00); -- consumes 2
ROLLBACK TRANSACTION; -- 2 is gone
BEGIN TRY
INSERT dbo.Invoice (Amount) VALUES (-5.00); -- consumes 3, then fails
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH;
INSERT dbo.Invoice (Amount) VALUES (300.00); -- gets 4
SELECT InvoiceID, Amount FROM dbo.Invoice ORDER BY InvoiceID;
You get 1 and 4. The counter runs outside the transaction that asked for the value, which is the whole point: two sessions inserting at once must not queue behind a shared counter. The rolled-back value never comes back, and neither does the one burned by the CHECK constraint, because the engine hands out the identity value before it validates the row.
If a regulator requires an unbroken sequence of document numbers, an identity column is the wrong tool and always was. Generate that number in a separate table under an explicit transaction and pay the serialization cost, or use a SEQUENCE with NO CACHE, which narrows gaps to transactions that never commit.
The identity cache, and the jump after a crash
SQL Server 2012 changed how identity values get allocated. Instead of logging every value, the engine preallocates a block, logs the top of it, and hands values out of memory. If the instance goes down without a clean shutdown, or an availability group fails over, the unused remainder of that block is lost and the counter resumes at the next block.
Microsoft documents the cache and both switches that turn it off, and names no block size anywhere: not on the IDENTITY property page, not in the trace flag 272 entry, not in the IDENTITY_CACHE entry. The figures in circulation come from people who measured them, and land on roughly a thousand values for int and ten thousand for bigint and numeric. Observed, not contractual. An int identity sitting at 4,500 comes back from a crash near 5,001. Published repros do not all agree: one 2012-era test on a two-row table reported a jump of nine, so the boundary is not a constant to build a process around.
Reproduce it on a test instance. Nowhere else.
-- test instance only
CREATE TABLE dbo.CacheDemo (ID int IDENTITY(1,1) PRIMARY KEY, Note sysname NOT NULL);
GO
INSERT dbo.CacheDemo (Note) VALUES (N'a'), (N'b'), (N'c');
SELECT MAX(ID) FROM dbo.CacheDemo; -- 3
SHUTDOWN WITH NOWAIT; -- skips checkpoint, simulates a crash
Restart the service, then:
INSERT dbo.CacheDemo (Note) VALUES (N'd');
SELECT MAX(ID) FROM dbo.CacheDemo; -- jumps to the next block boundary
-- 1001 in a measured run on a default instance
Two controls exist. Trace flag 272 is the older one: "Disables identity preallocation to avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server." Its scope is global only, so set it as a startup parameter (-T272) in Configuration Manager rather than with DBCC TRACEON.
SQL Server 2017 added a database scoped version:
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF;
The default is ON. Microsoft calls it "similar to the existing trace flag 272, but is set at the database level," and notes you can set it only on the primary replica. That matters in an availability group: the setting rides along with the database, so a failover does not drop you back to cached behavior.
The cache exists to cut log writes, so turning it off should cost one write per value where you paid one per block. Microsoft says only that "identity caching improves INSERT performance" and leaves the mechanism there, so measure rather than model. On a table taking thousands of inserts per second, test before and after. On a table taking dozens, the cost vanishes into the noise and the auditor stops calling.
DBCC CHECKIDENT, and the version of it that breaks your table
DBCC CHECKIDENT reads and writes the current identity value. Three forms, one trap.
-- report only, changes nothing
DBCC CHECKIDENT ('dbo.Invoice', NORESEED);
-- Checking identity information: current identity value '4', current column value '4'.
NORESEED returns the current identity value and the current maximum value in the column. Use this form for every investigation.
-- no option at all: this WRITES
DBCC CHECKIDENT ('dbo.Invoice');
Erin Stellato calls this the nuance that drives her crazy, and she is right. In her words: "if you do not include the NORESEED option, if the identity and column values do not match, the identity will reseed." A command that reads like an inspection is a write. If the current identity value sits below the maximum value in the column, it snaps up to that maximum.
The third form sets an explicit value. This is where tables get broken:
CREATE TABLE dbo.Widget
(
WidgetID int IDENTITY(1,1) CONSTRAINT PK_Widget PRIMARY KEY,
Name sysname NOT NULL
);
INSERT dbo.Widget (Name) VALUES (N'a'), (N'b'), (N'c'), (N'd'), (N'e'); -- 1 through 5
DBCC CHECKIDENT ('dbo.Widget', RESEED, 0);
INSERT dbo.Widget (Name) VALUES (N'f');
-- Msg 2627, Level 14, State 1
-- Violation of PRIMARY KEY constraint 'PK_Widget'. Cannot insert duplicate key
-- in object 'dbo.Widget'. The duplicate key value is (1).
Microsoft documents both outcomes. With a PRIMARY KEY or UNIQUE constraint you get 2627 on later inserts. Without one, "later insert operations result in duplicate identity values," and nothing tells you.
What makes this worse in production: the counter still advances on each failed attempt. The next insert tries 2 and fails, then 3, then 4. A table reseeded 40,000 values too low rejects the next 40,000 inserts one at a time, each rejection looking to the application like a transient error worth retrying. Then it starts working again and nobody investigates.
One more trap. When you reseed with a value, the next row does not always use it. If rows are present, or if all rows were removed with DELETE, the next row gets new_reseed_value plus the increment. If the table has never held rows, or was emptied with TRUNCATE TABLE, the next row gets new_reseed_value itself. Solomon Rutzky documented the DELETE case after finding the docs silent on it. Reseed to 0 on a DELETE-emptied table and you start at 1. Reseed to 0 after a TRUNCATE and you start at 0. That difference is one of the few that survives when you are choosing between the two statements on other grounds: what TRUNCATE does to the seed that DELETE does not.
SET IDENTITY_INSERT
To put a specific value back, turn the property off for the statement:
SET IDENTITY_INSERT dbo.Invoice ON;
INSERT dbo.Invoice (InvoiceID, Amount) VALUES (2, 200.00); -- column list is mandatory
SET IDENTITY_INSERT dbo.Invoice OFF;
The column list is required: leave it out with IDENTITY_INSERT on and you get error 545, "Explicit value must be specified for identity column." Only one table per session can have it on at a time, so forgetting the OFF fails the next table in the migration script.
Without it on, the failure takes two shapes. Name the identity column in a column list and you get 544, "Cannot insert explicit value for identity column in table 'X' when IDENTITY_INSERT is set to OFF." Supply a value with no column list and you get 8101: "An explicit value for the identity column in table 'X' can only be specified when a column list is used and IDENTITY_INSERT is ON." Microsoft prints that second message without a number; both numbers come from practitioner reports.
The useful side effect: when the value you insert exceeds the current identity value and the increment is positive, the engine "automatically uses the new inserted value as the current identity value." Backfill rows 900,000 to 1,000,000 into a table sitting at 5,000 and the counter follows you up. No separate reseed needed.
Permissions: ALTER on the table, or ownership. DBCC CHECKIDENT wants schema ownership, db_owner, db_ddladmin, or sysadmin.
@@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT()
Three functions, three answers, one of which writes bad foreign keys without raising an error.
SCOPE_IDENTITY(): last identity value generated in the current session and the current scope.@@IDENTITY: last identity value generated in the current session, any scope.IDENT_CURRENT('table'): last identity value generated for that table, any session, any scope.
Add a trigger and the difference stops being academic:
CREATE TABLE dbo.Orders
(
OrderID int IDENTITY(1,1) CONSTRAINT PK_Orders PRIMARY KEY,
CustomerID int NOT NULL
);
CREATE TABLE dbo.OrderAudit
(
AuditID int IDENTITY(5000,1) CONSTRAINT PK_OrderAudit PRIMARY KEY,
OrderID int NOT NULL,
LoggedAt datetime2(3) NOT NULL CONSTRAINT DF_OrderAudit_LoggedAt DEFAULT SYSDATETIME()
);
GO
CREATE TRIGGER dbo.trg_Orders_Audit ON dbo.Orders AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.OrderAudit (OrderID) SELECT i.OrderID FROM inserted AS i;
END
GO
INSERT dbo.Orders (CustomerID) VALUES (42);
SELECT
AtIdentity = @@IDENTITY,
ScopeIdentity = SCOPE_IDENTITY(),
IdentCurrent = IDENT_CURRENT('dbo.Orders');
-- AtIdentity 5000, ScopeIdentity 1, IdentCurrent 1
@@IDENTITY returns 5000, the audit row's key. That number comes from IDENTITY(5000,1) on dbo.OrderAudit, seeded high so the wrong answer is unmistakable; the engine did not invent it. You get it because the trigger ran in a different scope in the same session and generated an identity value after the one you wanted. An application that writes 5000 into a foreign key column now points at an order that does not exist. Hand-written triggers are not the only source: merge replication adds triggers to published tables, so @@IDENTITY on a replicated table can return a key from a replication system table.
IDENT_CURRENT fails the other way. It ignores sessions, so it returns whatever value another connection generated a microsecond ago. Microsoft's warning: "Use caution when using IDENT_CURRENT to predict the next generated identity value. The actual generated value can be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions." It is a monitoring function. It also returns the seed value, not NULL, when the table has never held rows or was truncated.
SCOPE_IDENTITY() carried a real bug too, worth knowing because the workaround is better practice anyway. Microsoft's KB reads: "When you use either SCOPE_IDENTITY() or @@IDENTITY functions to retrieve the values inserted into an identity column, you may notice that these functions sometimes return incorrect values. The problem occurs only when your queries use parallel execution plans." The fix shipped in Cumulative Update 5 for SQL Server 2008 R2 Service Pack 1, so no supported version carries it. Microsoft's first recommended workaround was to drop the functions and use OUTPUT:
DECLARE @NewIDs TABLE (OrderID int NOT NULL);
INSERT dbo.Orders (CustomerID)
OUTPUT inserted.OrderID INTO @NewIDs (OrderID)
SELECT c.CustomerID FROM dbo.NewCustomers AS c;
SELECT OrderID FROM @NewIDs;
OUTPUT handles multi-row inserts, which none of the three functions do, and it has no opinion about triggers or scopes. An upsert makes the case sharper still, because the generated keys arrive from two branches at once: returning generated keys with OUTPUT instead.
Finding the tables that are about to overflow
Run this before you need it. Every identity column in the database, values remaining, percentage of the range consumed.
WITH IdentityColumns AS
(
SELECT
TableName = QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(t.name),
ColumnName = c.name,
DataType = ty.name,
TableRows = ps.RowCnt,
LastValue = CONVERT(decimal(38,0), c.last_value),
SeedValue = CONVERT(decimal(38,0), c.seed_value),
IncrementValue = CONVERT(decimal(38,0), c.increment_value),
MaxValue = CONVERT(decimal(38,0),
CASE ty.name
WHEN 'tinyint' THEN 255.
WHEN 'smallint' THEN 32767.
WHEN 'int' THEN 2147483647.
WHEN 'bigint' THEN 9223372036854775807.
ELSE CONVERT(decimal(38,0),
CONVERT(varchar(40), REPLICATE('9', c.precision)))
END),
MinValue = CONVERT(decimal(38,0),
CASE ty.name
WHEN 'tinyint' THEN 0.
WHEN 'smallint' THEN -32768.
WHEN 'int' THEN -2147483648.
WHEN 'bigint' THEN -9223372036854775808.
ELSE -CONVERT(decimal(38,0),
CONVERT(varchar(40), REPLICATE('9', c.precision)))
END)
FROM sys.identity_columns AS c
JOIN sys.tables AS t
ON t.object_id = c.object_id
JOIN sys.types AS ty
ON ty.user_type_id = c.system_type_id -- base type, not an alias type
CROSS APPLY
(
SELECT RowCnt = SUM(p.row_count)
FROM sys.dm_db_partition_stats AS p
WHERE p.object_id = t.object_id
AND p.index_id IN (0, 1)
) AS ps
WHERE t.is_ms_shipped = 0
),
Bounded AS
(
SELECT *,
CurrentVal = ISNULL(LastValue, SeedValue),
CeilingVal = CASE WHEN IncrementValue > 0 THEN MaxValue ELSE MinValue END
FROM IdentityColumns
)
SELECT
TableName,
ColumnName,
DataType,
TableRows,
CurrentValue = CurrentVal,
CeilingValue = CeilingVal,
ValuesRemaining = ABS(CONVERT(float, CeilingVal) - CONVERT(float, CurrentVal))
/ ABS(CONVERT(float, IncrementValue)),
PctConsumed = CONVERT(decimal(7,3),
100.0 * ABS(CONVERT(float, CurrentVal) - CONVERT(float, SeedValue))
/ NULLIF(ABS(CONVERT(float, CeilingVal) - CONVERT(float, SeedValue)), 0)),
ValuesBurnedPerRow = CASE WHEN TableRows > 0
THEN CONVERT(decimal(38,2),
ABS(CONVERT(float, CurrentVal) - CONVERT(float, SeedValue))
/ CONVERT(float, TableRows))
END
FROM Bounded
ORDER BY PctConsumed DESC;
last_value, seed_value and increment_value are all sql_variant on sys.identity_columns, which is why each one gets a CONVERT before it touches arithmetic. last_value is NULL for a table that has never held a row, so ISNULL falls back to the seed.
Every division runs through float on purpose. decimal(38,0) operands push intermediate precision past 38 digits, and the engine answers with an arithmetic overflow instead of a number, so one wide decimal identity column anywhere in the database kills the report. The price is precision past about 15 significant digits, which for a distance-to-the-ceiling reading is noise.
The join to sys.types goes through system_type_id, so an identity column declared on an alias type resolves to int rather than falling into the decimal branch and reporting a ceiling almost five times too high.
ValuesBurnedPerRow turns this from a report into a diagnosis. A table with 4 million rows and a current value of 1.9 billion has burned 475 identity values per surviving row: a nightly job inserting and rolling back, or a delete-and-reload nobody costed out. Fix the pattern and the ceiling stops moving toward you.
Where I draw the line, and it is judgement rather than a standard: past 70% on an int I put the widening on the project plan, and past 90% I put it on this quarter's change calendar.
What overflow looks like
CREATE TABLE dbo.NearTheCeiling
(
ID int IDENTITY(2147483646, 1) CONSTRAINT PK_NearTheCeiling PRIMARY KEY,
Filler char(1) NOT NULL CONSTRAINT DF_NearTheCeiling DEFAULT 'x'
);
INSERT dbo.NearTheCeiling DEFAULT VALUES; -- 2147483646
INSERT dbo.NearTheCeiling DEFAULT VALUES; -- 2147483647
INSERT dbo.NearTheCeiling DEFAULT VALUES;
-- Msg 8115, Level 16, State 1
-- Arithmetic overflow error converting IDENTITY to data type int.
-- Arithmetic overflow occurred.
Every insert fails from that moment. Reads keep working, updates keep working, and the application looks half alive, which is why the first ticket says something vague about the order form. No partial degradation, no grace period.
Fixing an int that is running out
If you arrived from a sizing argument rather than an outage, read the two sections above first: the survey query says how long this column has left, and 8115 is what the deadline looks like. The cheapest fix is the one you can no longer take: declaring the column bigint the day you created the table, four extra bytes per row that remove everything below from your life. Int against bigint, four bytes and what they buy.
For a column already climbing, four options. The first buys time. The other three fix it.
Reseed into the negative half. An int runs from -2,147,483,648 to 2,147,483,647, so seeding at 1 throws away half the type. If the column holds 1 through 2.1 billion, the bottom half sits empty:
DBCC CHECKIDENT ('dbo.BigTable', RESEED, -2147483648);
-- next inserted row gets -2147483647
Milliseconds to run, no outage, another 2.1 billion values. The caveats live in the application. Anything treating the key as a positive number breaks: a CHECK (ID > 0) constraint, an unsigned integer in client code, a URL or barcode format that never expected a minus sign, a report that sorts by ID as a stand-in for insert order. New rows now sort before every existing row. Test the code path that renders the ID first, then spend the time it buys on a real fix. Duct tape with a known expiry.
ALTER TABLE ... ALTER COLUMN to bigint. Start with what it cannot do. Microsoft's restriction list is explicit: the modified column cannot be "used in a PRIMARY KEY or FOREIGN KEY REFERENCES constraint," and "you can't change the data type of columns included in an index unless the column is a varchar, nvarchar, or varbinary data type." An identity column on a clustered primary key is both. So the real script drops every inbound foreign key, drops the primary key (rewriting the table), alters the column, recreates the primary key (rewriting it again), then recreates the foreign keys with a full check. Each rewrite is fully logged, with rollback space reserved on top. Microsoft on the lock: "Any ALTER TABLE DDL operation (including ALTER COLUMN) acquires a schema modification (SCH M) lock for the duration of execution." Sch-M blocks everything, including readers under READ COMMITTED SNAPSHOT. On a 400 GB table that is hours, and you size the log and tempdb against a restored copy first: sizing the log before you rewrite a 400 GB table. A rewrite that fills the log rolls back, and the rollback is slower than the work it undoes.
Partition switching. ALTER TABLE ... SWITCH will not bridge a type change. Source and target need identical columns including data type, so an int column cannot switch into a bigint one. What partitioning buys is somewhere else to do the rewrite: switch a partition out to a staging table (metadata only), rewrite that staging copy into bigint form while nothing queries it, switch the result into the new table. The live table gives up one partition at a time instead of going offline whole. Three things to watch. The identity property does not have to match for a switch, which Microsoft spells out as a hazard: "Partition switching can introduce duplicate values in IDENTITY columns of the target table, and gaps in the values of IDENTITY columns in the source table." The table has to be partitioned already, and partitioning it first is the same rewrite you were avoiding. And I have not seen this published end to end, so prototype it on a restored copy before planning an outage around it: the switch back in still needs a schema-identical target and a boundary-matching CHECK constraint.
The shadow table. Build dbo.BigTable_New with a bigint identity, copy the history in batches over days with the source online, keep the two in step with a trigger or change tracking, then take a short outage to drain the last rows and rename. It scales, it lets you stop halfway, and it costs the most engineering. Aaron Bertrand worked through a variant that swaps the identity for a SEQUENCE default, and flagged the catch: "If you have code that relies on SCOPE_IDENTITY(), @@IDENTITY, or IDENT_CURRENT(), it would also have to change."
Pick on table size and outage budget. My own rule of thumb, not a sourced threshold: under about 50 GB with a two-hour window I alter it, and over about 500 GB with no window I build the shadow table. Time it on a restored copy and trust that over my numbers. The negative reseed is what you do at 2am so you can choose between the other two in daylight.
When SEQUENCE is the better object
SEQUENCE arrived in SQL Server 2012 as a schema-level object rather than a column property, and that difference is the whole feature. Microsoft's reasons to prefer one:
- The application needs the number before the row exists.
NEXT VALUE FORhands it over without an insert. - One series has to be shared across several tables or columns.
- The series has to restart at a boundary, using
CYCLE. - Values need assigning in an order set by another column, using
NEXT VALUE FOR ... OVER (ORDER BY ...). - The application needs a block of numbers at once.
sp_sequence_get_rangereserves a contiguous range that other sessions cannot interleave into. - The specification has to change later. You can
ALTER SEQUENCE; you cannot change the increment on an existing identity column.
CREATE SEQUENCE dbo.DocumentNumber
AS bigint
START WITH 1
INCREMENT BY 1
NO CACHE;
CREATE TABLE dbo.Document
(
DocumentID bigint NOT NULL
CONSTRAINT DF_Document_ID DEFAULT (NEXT VALUE FOR dbo.DocumentNumber)
CONSTRAINT PK_Document PRIMARY KEY,
Body nvarchar(max) NULL
);
Sequences cache too, and the default is CACHE, so they lose values on an unclean shutdown the same way identity does. Here the docs at least admit it: with no cache size given, "the Database Engine selects a size" but "users shouldn't rely upon the selection being consistent." NO CACHE costs a log write per value and limits gaps to uncommitted transactions. For a document number a human reads, that is the right trade.
Sequences do not populate SCOPE_IDENTITY(). Use OUTPUT, or pull the value into a variable with NEXT VALUE FOR before the insert.
The clustered key hot spot
An identity column on a clustered primary key sends every insert to the same page at the right edge of the index. Under enough concurrency that page becomes a latch queue. Microsoft's rough profile: a sequential leading key, heavy concurrent inserts, and "typically 16 CPUs or more." The symptom is PAGELATCH_EX waits on one page of one index. Check that it is PAGELATCH and not PAGEIOLATCH before you blame storage.
SQL Server 2019 added an index option, default OFF:
ALTER INDEX PK_Orders ON dbo.Orders
SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON); -- metadata only, no rebuild
Read Pam Lahoud's description before turning it on everywhere. It does not remove the latch. It adds "an upstream flow control mechanism for all the threads that request to enter the critical section," limiting one thread per scheduler and favouring threads likely to finish fast. The target is the convoy, where "if something slows down one of the threads that is holding the latch, this can trigger a convoy and throughput suddenly falls off a cliff." Her caution matters as much: "if you're not experiencing the convoy phenomenon in your workload, you may not see a huge benefit from this option, and you may even see a slight degradation in performance." You get a new wait type, BTREE_INSERT_FLOW_CONTROL, and PAGELATCH waits may hold steady or rise while throughput improves. Judge it on inserts per second, not the wait chart.
The older fixes still work and sometimes work better: make the primary key nonclustered, or lead the clustered index with a column that spreads inserts. Both rewrite every nonclustered index in the table, so they are design decisions, not switches. Fill factor will not help: it reserves space on pages that already exist and does nothing for the page being appended to. The right-edge hot spot and what fill factor does not fix.
The order to do this in
- Run the survey on every production database and save the output. You now have a baseline and a date.
- Past 70% on an
int, readValuesBurnedPerRow. If it is high, find the rollback or reload pattern burning values and fix that first. It may be the whole problem. - Before touching any identity value, run
DBCC CHECKIDENT (table, NORESEED)and write down both numbers. Never run the no-argument form on production. - To fill gaps, use
SET IDENTITY_INSERTwith the column list, and turn it off in the same batch. - Audit application code for
@@IDENTITY. Replace it withOUTPUT, orSCOPE_IDENTITY()where a single-row insert makes that simpler. - If gaps after failover are a compliance problem, set
IDENTITY_CACHE = OFFon 2017 and later, or start the instance with-T272on 2016 and earlier, and measure throughput either side. - For a table that overflows inside a year, schedule the widening now and rehearse it against a restored backup. Time the
ALTER, measure the log growth, then choose between altering it and building the shadow table.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.