When a Columnstore Index Makes Your Query Fail


If you wanna count the number of rows in a table, I’ve usually said, “It doesn’t matter what you put inside the COUNT() – it can be COUNT(*), COUNT(Id), COUNT(1), or COUNT(‘Chocula’), or even COUNT(1/0) – it all works the same.”

And that was true right up until I added a columnstore index.

Start with the Stack Overflow database – any size will do – and do a plain ol’ COUNT when no nonclustered indexes are present:

DropIndexes; GO SELECT COUNT(*) FROM dbo.Users; SELECT COUNT(‘Chocula’) FROM dbo.Users; SELECT COUNT(1/0) FROM dbo.Users; GO

The actual execution plans for all three queries are