Computed Columns And Columnstore

Kendra Little exposes a gotcha with non-clustered columnstore indexes and computed columns:

Looking at the execution plan, SQL Server decided to scan the non-clustered columnstore index, even though it doesn’t contain the computed column BirthYear! This surprised me, because I have a plain old non-clustered index on BirthYear which covers the query as well. I guess the optimizer is really excited about that nonclustered columnstore.

Kendra links to a Connect item from Niko Neugebauer to add persisted computed columns to columnstore indexes.

Related Posts

Logical Equivalence and Query Tuning

Erik Darling didn’t warn us that there would be math: Often when query tuning, I’ll try a change that I think makes sense, only to have it backfire. It’s not that the query got slower, it’s that the results that came back were wrong different. Now, this can totally happen because of a bug in previously used […]

Read More

SQL Server Execution Plan Operators

Bert Wagner takes us through some of the more common execution plan operators: Spools come in a variety of types, but most of them can be summarized as operators that store an intermediary result table in tempdb. SQL Server often uses spools to process complex queries, transforming the data into a tempdb worktable to allow […]

Read More

Categories

December 2016
MTWTFSS
« Nov Jan »
 1234
567891011
12131415161718
19202122232425
262728293031