May 29, 2015 at 2:29 pm
I have a query that joins a dimension to a fact table. The fact table has a columnstore index on it. In my select clause I do a cast on a field from the dimension from a varchar to a date. One row in dimension with a key of -1 has values that would cause the cast to fail bc the string doesn't represent a valid date. I have in the where clause, where key <> -1 to filter out that 1 row. So I would assume the where clause would not return that row to the select statement and the cast would never happen against the invalid string. When I do a select with just the dimension, that is in fact how it behaves (no error). However, if I join that dimension to a fact table with a columnstore index, it fails on the conversion. If I drop that columnstore index and run the same query, it's fine. It's as if it's querying all of the dimension and doing the cast in the select before it's joining to the fact and applying the Where clause.
I realize there are some design flaws here. We should probably use a try_convert, or simply store the date as a date field in the dimension. But I'd like to understand why it is behaving that way. We are starting to use columnstore indexes on all our fact tables with great benefits, but want to fully udnerstand these "gotchas". Do columnstore indexes change the order of operations? Where we just getting lucky with the execution plan that was chosen before the columnstore index applied? Thanks for the help.
May 30, 2015 at 3:46 am
It's hard to understand precisely what's going on. Any chance of posting the query and the execution plan that does work? Can you get an estimated execution plan from the query that is erroring out? If the error is a conversion error, maybe it has something to do with the data involved?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply