Luis Cazares (9/24/2015)
GilaMonster (9/24/2015)
Alan.B (9/24/2015)
Just as a side note, this won't fail:
WITH X AS(
SELECT fld
FROM #t
WHERE cast(fld as int) > 0
)
SELECT fld
FROM X
WHERE ISNUMERIC(fld) =1
Since the optimiser pushes predicates down as far as it can, that's equivalent to
SELECT fldFROM #t
WHERE ISNUMERIC(fld) =1
AND cast(fld as int) > 0
Their execution plans are identical.
Is there any way to anticipate this behavior? Is it documented anywhere? Or was I correct on the safe option?
I know this thread has gone a little stale but...
I was trying to figure something similar out and posted my question in this thread[/b]).
Eirikur's replies were very informative. The undocumented traceflags give an interesting view of what's happening under the hood and I ended up watching Benjamin Nevarez' video that discusses them in detail.
-- Itzik Ben-Gan 2001