THE SQL UNIQUIFIER
On a recent project, we hit upon an interesting problem - the SQL indexing uniquifier.
It seems to be one of those deep, dark SQL secrets that's been tucked away down the back of the sofa and forgotten about over several years; an obscure journey into SQL's data storage and indexing mechanisms!
What is the SQL Uniquifier?
In a nutshell, if you have a non-unique, clustered index on a SQL table, SQL server will automatically "uniquify" this for you!
It does this by adding a hidden integer column that acts like an auto-incrementing identity column. This is fine until you start hitting the maximum limit of an integer: 2,147,483,647.
How does it affect me?
You may well think "my table doesn't have 2 billion rows" and you may be correct, but if it has millions of rows that get deleted and re-inserted several times a day, you will soon effectively start approaching that integer maximum limit.
This appeared to be the case with Sitecore's Descendant table.
Every time Sitecore's RebuildDatabase() method kicks in, this is exactly what appears to happen. The following code is lifted directly from Sitecore.Data.DataProviders.Sql.SqlDataProvider.RebuildDescendants(), which shows this process in action:
Factory.GetRetryer().ExecuteNoResult(delegate
{
using (DataProviderTransaction dataProviderTransaction = this.api.CreateTransaction())
{
this.api.Execute("DELETE FROM {0}Descendants{1}", new object[0]);
this.api.Execute("INSERT INTO {0}Descendants{1} ({0}ID{1}, {0}Ancestor{1},.... );
...
...
dataProviderTransaction.Complete();
}
});
If you have hundreds of thousands of items in your database, having this being called several times a day for several months will quickly lead you to this issue.
The problem manifests itself interestingly enough as a 666 message coming from the SQL backend. The first hints of a problem probably look like this:
Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 72057623316398080. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.
Resolution
As we've seen, deleting from the table doesn't reset the hidden counter. Nor, as you might expect does truncating it. It acts like an identity column, but it isn't one. If possible, your only route to take is the advice from the error message - drop and recreate the index.
Of course, it's easier to avoid the problem by avoiding the use of a clustered, non-unique index.
