March 26, 2009 at 3:37 pm
dphillips (3/26/2009)
RBarryYoung (3/26/2009)
Have you checked the compatibility level?Score. 2 points. Thanks for asking... yes, the defaulted DB was in fact creating the table in database that was set to 2000 compatibility mode.
Yeah, like I said there is one heck of a wicked question waiting to be born there. Something like"
Jane has three databases: A, B and C on three servers. One is a Level 80 DB on a version 2000 server, one is a Level 80 DB on a version 2005 server and one is a Level 90 DB on a version 2005 server. Unfortunately, Jane no longer remembers which is which.
Jane executes the following query on all three databases: {insert query here}. On database A it returns 1, 2, 3, 4, 5, and 6. In database B it returns the following error: {insert error message here}. On database C it returns both.
Which database is which?
Now that's a wicked question! :w00t:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 26, 2009 at 3:43 pm
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
March 26, 2009 at 4:21 pm
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
Which suggests that the execution plan might be different depending on the result experienced... The question is How/why would that happen?
March 27, 2009 at 1:52 am
I ran the code in a SQL Server 2005 and the results were:
1
2
3
4
5
6
So the correct answer is: c.
I expect you correct this situation, and give me my points (lol, ja, ja, ja)
March 27, 2009 at 2:02 am
And, this is my version:
Edition: Enterprise Edition
Engine:3
Version:8.00.2039
upgrade:SP4
March 27, 2009 at 2:06 am
serinor.e090266 (3/27/2009)
And, this is my version:Edition: Enterprise Edition
Engine:3
Version:8.00.2039
upgrade:SP4
And the compatibility mode for the database you ran the code in?
March 27, 2009 at 4:13 am
FYI:
In SQL 2000, the code runs without any errors--data is input into the table and the query returns 1,2,3,4,5 and 6.
In SQL 2005, database in compatibility mode 80, the data is input into the table and the query returns both the above result set and the error message.
In SQL 2005, database in compatibility mode 90, no data is input into the table and the query returns only the error message.
Unfortunately I don't have SQL 2008. Still a fascinating outcome of one sample code run against various versions of SQL Server and database compatibility mode.
(BTW--got it wrong as I first cross ckecked against SQL 2000).
March 27, 2009 at 5:24 am
serinor.e090266 (3/27/2009)
And, this is my version:Edition: Enterprise Edition
Engine:3
Version:8.00.2039
upgrade:SP4
That is SQL Server 2000, NOT SQL Server 2005.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2009 at 6:06 am
I dont know if there is a problem, but i have tested the sentence in a SQL Server 2005
More information:
1)
Output for SELECT @@VERSION
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2) See the attached document.
This server is 2005 or not?
March 27, 2009 at 6:25 am
serinor.e090266 (3/27/2009)
I dont know if there is a problem, but i have tested the sentence in a SQL Server 2005More information:
1)
Output for SELECT @@VERSION
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2) See the attached document.
This server is 2005 or not?
It's a SQL 2000 server you are connecting to, you're just using the 2005 client tool to do so. This is perfectly legal and shouldn't affect (or is it effect) your results...it will still behave like a SQL 2000 server.
Generally speaking, Enterprise manager or SSMS can connect to anything earlier than it from what I've seen.
The Redneck DBA
March 27, 2009 at 6:25 am
serinor.e090266 (3/27/2009)
I dont know if there is a problem, but i have tested the sentence in a SQL Server 2005More information:
1)
Output for SELECT @@VERSION
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2) See the attached document.
This server is 2005 or not?
Nope that's definitely SQL 2000, SP4!
Kev
March 27, 2009 at 1:32 pm
Even with newer version of SQL being stricter on mixing "union" statements such as
select 7 union select 'A', the original explation is not adequite to say the least.
If that is all the problem, then there is no need to have the follow-up select statements.
The fact is, if you work around the new "union" feature/bug, by breaking the insertion to 2 parts - select 1 union 2... and select "A" union select "B", you have all items inserted into the table. Then, you still likely get an error running the follow-up select statements - I did.
Regardless the select SQL executed with error or without error, the explanation is based on how SQL interprates those select SQL statements - that clearly is not universally the same as we can be seen by different posts.
In my case, SQL is doing a "implicit conversion" on Col to compare with 1 and 6, therefor it fails for values such as "A".
If I change the filter condition from Between 1 and 6 -> Between '1' and '6', it runs with out errors.
April 1, 2009 at 9:18 am
My Asnwer is perfectly valid i.e will display 1..6.. if we execute this query in 2000.
We should have this question specific to the SQL Server version.
Thanks -- Vijaya Kadiyala
April 1, 2009 at 9:29 am
Vijaya Kadiyala (4/1/2009)
We should have this question specific to the SQL Server version.
Thanks -- Vijaya Kadiyala
http://www.dotnetvj.vom[/quote%5D
It was specific to a particular SQL Server version. You didn't read the question carefully.
April 1, 2009 at 9:31 am
The question was edited to note 2005.
Viewing 15 posts - 61 through 75 (of 87 total)
You must be logged in to reply to this topic. Login to reply