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:

The actual execution plans for all three queries are the same:

Add a nonclustered rowstore index on any column and try the queries again:

And again, all three query plans are the same because SQL Server optimizes away the contents of the COUNT(), using the index in all three cases:

The index gets used every time. So for years, ever since Jeremiah Peschka or Kendra Little (I can’t remember which one) showed me that 1/0 trick, I’ve used COUNT(1/0) because it’s funny.

Well, the laughing stops when you add a nonclustered columnstore index:

Because only two of those queries work at all! The first two work fine, scanning the nonclustered columnstore index, but the COUNT(1/0) fails with the obvious error:

The estimated execution plans for all three queries are now slightly different:

The COUNT(1/0) plan has a Compute Scalar operator that the other two plans don’t have, and here’s the disappointing definition:

Diabolical.

So what’s the lesson here? Well, don’t be amusing with your T-SQL, I guess, because sooner or later, the query optimizer behavior you depended on can change.

Update 2026/07/23: turns out this is breaking with rowstore indexes these days too, and it has to do with batch mode execution. Batch mode seems to push the 1/0 further down the query plan, causing it to be executed before the grouping & summing.