September 24, 2015 at 10:44 am
Hi is it safe to rely on the order of predicates.
select 'none' fld into #t union all select 'end' union all select '123' union all
select '567' union all select '0' union all select '0000'
go
SELECT fld
FROM #t
WHERE ISNUMERIC(fld) =1
AND cast(fld as int) > 0
-- ok returns 123 + 567
SELECT fld
FROM #t
WHERE cast(fld as int) > 0
AND ISNUMERIC(fld) =1
-- fails Conversion failed when converting the varchar value 'none' to data type int
Is it safe to rely on this behaviour? if I put isnumeric in first the cast will only be working on numeric values
WITH CTE AS
(
SELECT fld
FROM #t
WHERE ISNUMERIC(fld) =1
)
SELECT fld from CTE WHERE cast(fld as int) > 0
-- fails Conversion failed when converting the varchar value 'none' to data type int
The CTE also failed I thought the CTE would exclude non numeric values when I used it.
Thanks
September 24, 2015 at 10:57 am
T-SQL is a declarative language, which means that you're not telling the engine how to process the query, you just tell it what you want it to do. That means that the conditions won't be executed in order.
One option is to use the CASE statement which actually allows that behavior.
There's another problem with your code. I included a demonstration, but for further information read the following article: http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/
select 'none' fld into #t union all select 'end' union all select '123' union all
select '567' union all select '0' union all select '0000'
go
SELECT fld
FROM #t
WHERE CASE WHEN ISNUMERIC(fld) = 1 THEN cast(fld as int) END > 0
GO
INSERT INTO #t VALUES('$');
SELECT fld
FROM #t
WHERE CASE WHEN ISNUMERIC(fld) = 1 THEN cast(fld as int) END > 0
GO
SELECT fld
FROM #t
WHERE CASE WHEN fld NOT LIKE '%[^0-9]%' THEN cast(fld as int) END > 0
GO
DROP TABLE #t
September 24, 2015 at 11:01 am
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
-- Itzik Ben-Gan 2001
September 24, 2015 at 11:08 am
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 fld
FROM #t
WHERE ISNUMERIC(fld) =1
AND cast(fld as int) > 0
Their execution plans are identical.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2015 at 11:53 am
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?
September 24, 2015 at 5:52 pm
Thank you Luis
I didn't know of the isnumeric issue, I made the same wrong assumption as your link says most people make.
I treat it as declarative but was surprised to see the order made a difference.
I didn't know a CASE could be used in a WHERE clause. Am I correct in reading that when isnumeric = 0 the result is NULL and NULL is not > 0 (it's null) so that row is not selected.
September 29, 2015 at 2:15 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply