Happy New Year to everyone!
Just found great web site about SQL Server maintained by Remus Rusanu (MS Employee)
It contains great info not only about published releases (SQL Server 2005/2008/2008R2) but also new features,command about SQL Server 2012
http://rusanu.com/2011/08/05/
Consider simple table with one column defined as REAL datatype
CREATE TABLE #t (c REAL)
INSERT INTO #t VALUES (0)
SELECT COUNT(*) FROM #t WHERE c=”
In above statement you expect getting 0 rows to be returns as we filter out for all nonempty rows..But it returns 1 and the answer you find looking at execution plan.
SQL Server will implicitly convert ” to REAL datatype with 0 and a result is 1 row to be return.
Just help out may colleague to write a query where we needed to filter out all data that contains characters and because a column is defined as VARCHAR we CAST it to INTEGER in order to implement range searching. Please see simplified demo script.
CREATE TABLE #t (c varchar(50))
INSERT INTO #t VALUES (’122′)
INSERT INTO #t VALUES (’4545′)
INSERT INTO #t VALUES (’4545/454′)
INSERT INTO #t VALUES (’4899′)
—-Failed
SELECT * FROM
(
SELECT c FROM #t WHERE c NOT LIKE ‘%[/]%’
) AS d WHERE CAST(c AS INT)>10
—-Succeed
SELECT * FROM #t
WHERE CASE WHEN c LIKE ‘%[^0-9]%’ THEN 0
WHEN CAST(c AS int) BETWEEN 1 AND 1000 THEN 1
ELSE 0 END = 1
The answer why the first attemp is failed we found looking at execution plan.
Predicate
CONVERT(int,[tempdb].[dbo].[#t].[c],0)>(10) AND NOT [tempdb].[dbo].[#t].[c] like ‘%[/]%’
Thinking that we filter out all “bad” rows and can CAST the rest is wrong because as we see above predicate is applied for the whole table.
As opposite the second query we used CASE expression to filter out “bad” rows CASE…=1 we see that SQL Server really filters out “bad” rows and now CAST is working.
Predicate
CASE WHEN [tempdb].[dbo].[#t].[c] like ‘%[^0-9]%’ THEN (0) ELSE CASE WHEN CONVERT(int,[tempdb].[dbo].[#t].[c],0)>=(1) AND CONVERT(int,[tempdb].[dbo].[#t].[c],0)<=(1000) THEN (1) ELSE (0) END END=(1)
