Predicate Execution Order on Mixed Data Type Columns

Sometimes the order that SQL Server decides to filter your data causes problems. This episode explores an example where implicit conversions can cause SQL Server to error out when retrieving your query's data.

Related blog post with example code:

Follow me on Twitter:

[Zanoni]

This design falls under "sad but true"... In DW, I've seen this often, where a survey system has a question, and the score is rated A (Always), B(Often)… E(Never)… then someone asks:"What is the average"?... "Uhmmm C.34"? The only real way I have found to get around this is a "spin-off" view that maps each char value to a "real" number. Then do the calcs there, before mapping the result back... e.g. if the averrage is 4, 1, you can say the average is D. Of course, it does mean more work that you need to set up in the back-end, but makes your life a LOT simpler when doing calcs.But thanks again for another thought-provoking video!!!

[TorgeirFredriksen]

I have had success in the past by using a CTE to "pre filter" the data. I am far from sure this is guaranteed to work in all scenarioes, but as I recall it made SQL apply the filter inside the CTE before the main query.

[stefanotorelli]

I do not know why they did it... but they did it... such kind of database design that is considered to be not in 3NF. It is like shooting themself in one foot by randomly tossing a coin... but today... they continue to do that... bof... and CTEs mean to tossing two coins instead of one...

[JohnDogplace]

Why not use sql_variant? Is sql_variant not a good datatype??

[ChaosTheory]

First off, thank you for all the videos/articles/etc. I've gotten tons of helpful tidbits and concepts you've presented along with suggesting your materials to many others.

With this particular example, I'm glad that my "never trust anything" tendencies line up with what you explained. I've been aware of not being able to trust execution order of predicates, but I didn't have a great understanding of why and this explanation helped a lot. While watching I kept thinking about the idea of just needing to force the issue sometimes and make it execute in the order needed even though that wasn't the exact premise of the video. With that said, wouldn't this sort of approach ensure the predicates get executed in the expected order at the trade-off of a derived table? I'm legitimately asking in case I'm actually missing something.

select PageName, DataValue
from (
select *
from dbo.Pages
where PageName like 'NumbersOnlyPage%'
) as p
where DataValue = 1.2