CODEWITHSUNDEEP
sql-serverindexingsitecore
AmoebaMan17
AmoebaMan17

Reputation: 742

Is there any purpose to creating a clustered index on a column that only contains null?

We are running a Sitecore CMS system and store the data in a MS SQL database. On some of the tables, I see a column named DAC_Index. The value in this field is all null. And then there is a Clustered Index created on this field.

  1. What purpose is there to cluster index a field of all Nulls?
  2. Could this be to prevent anyone from creating a clustered index on another field?
  3. Is there any negative performance implications that having this field causes?
  4. Is it possible some 3rd party component or standard .NET component leverages a DAC_Index column for something, so maybe it's just required to be there? But then, still I wonder why there needs to be a clustered index.

It is possible that the data in that field changes occasionally (maybe during something transactional happening to a set of rows). I just know that when I've queried the data, it always come back as all Null.

Any insight on the purpose of this and/or what performance implications are of having this?

Upvotes: 0

Views: 156

Answers (1)

Martin Smith
Martin Smith

Reputation: 453608

All of the rows with equal key values will be given a hidden uniqueifier so it will act somewhat similarly to an integer identity column. But less efficiently stored than an integer identity column would be.

From a quick Google of DAC_Index it looks as though it may have been written to migrate heap tables to Azure.

Azure requires all tables to have a clustered index. It looks like that script just takes the approach of adding a new nullable column and using that for the CI.

I don't know anything about siteCore. Likely this is a bit of a hack and some analysis of the tables involved would identify better clustered index candidates.

Upvotes: 2

Related Questions