Zusätzliche SQL Server-Funktionen und -Themen, die nicht von bestimmten Kategorien abgedeckt werden
Ledger verification error 37392 on a table with a filtered unique index, fine after dropping the index
We use database ledger on Azure SQL Database (Sweden Central, S1, 12.0.2000.8). One updatable ledger table, system-versioned, has a filtered unique index on (EmployeeId, Day) WHERE FullDay = 1 AND Cancelled = 0. Rows are never deleted. Cancelling means UPDATE Cancelled = 1, so the row leaves the index, and a new row for the same employee and day can enter it later.
After a few rounds of insert, cancel, insert for another employee, cancel, then insert for the first employee again, sys.sp_verify_database_ledger returns 37392, for the whole database and for this table only. The other ledger tables verify fine. DBCC CHECKTABLE is clean. On restored copies of the database the second round fails every time with the index, and ten rounds pass without it. A fresh database with the same schema but no data, and small stand-alone test tables, do not fail, so I cannot reduce it to a script without our data.
We dropped the index and enforce the rule in a trigger and with an application lock instead. Since then verification is green again on the original database, without any change to rows or history, which makes me think the index check is what failed and not the digest chain.
Two questions for anyone who knows the internals: is verification of nonclustered indexes on ledger tables known to have trouble with filtered indexes whose rows leave the filter and come back, and can we still rely on the existing digests and ledger data as evidence after this? I can provide verification output and a point-in-time copy to Microsoft if that helps.