don (3/26/2009)
In SQL 2008, the error is produced by the Between clause, not the initial Insert and Unions.The Help states the expressions used in the Between statement must all be the same type.
"Select COL From test Where COL Between 1 and 6" produces an error.
"Select COL From test Where COL Between 'A' and 'D'" is successful.
This was a poor question because the SQL Version wasn't stated, and even the explanation was incorrect!
Don,
I don't have 2008, so maybe you can tell me if it really is different from SQL 2005. After running the script and getting the conversion error, I find the table is still empty. How could the error be referring to the BETWEEN operation in the WHERE clause if there is no row with an "A" trying to cast to an int?
The error seems to come from the UNION of incompatible types. This code, stark as it is, fails: (Note again, I'm running SQL2005
Select 7
UNION
Select 'A'
In further testing, I find that after creating the table, I can insert a bunch of numbers OR a bunch of letters, but only in separate runs.
This works:
Insert into Test
Select 1
union Select 2
and this works:
Insert into Test
Select 'A'
union Select 'B'
But this fails with the conversion error:
Insert into Test
Select 'A'
union Select 2
and this fails with the conversion error:
Insert into Test
Select 1
union Select 'B'
The weird stuff starts with various SELECT queries.
This works, finding all the numbers:
Select Col
From Test
Where Isnumeric(Col)=1
And this works, finding numbers in a range:
Select Col
From Test
Where Isnumeric(Col)=1
And Col Between 1 and 3
The subselect run by itself finds the numbers:
Select Col
From Test
Where Isnumeric(Col)=1
But the original problem code fails.
Select Col
From (Select Col
From Test
Where Isnumeric(Col)=1) X
Where Col Between 1 and 6
Why? That's the puzzle. It looks as though the optimizer is not really building an intermediate resultset of numeric values, but rather is applying the various conditions (col>=1, col<=6, IsNumeric=1) all against the original table Test.
Sure enough, looking at the estimated plan (highlight the query and press Ctrl+L), we see that the query will be handled by a table scan with a predicate of
CONVERT_IMPLICIT(int,[MyDatabase].[dbo].[Test].[col],0)>={1)
AND CONVERT_IMPLICIT(int,[MyDatabase].[dbo].[Test].[col],0)<=(6)
AND isnumeric([MyDatabase].[dbo].[Test].[col])=1