Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL


TSQL

Author
Message
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
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

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3059
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

Ben Leighton
Ben Leighton
Mr or Mrs. 500
Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)

Group: General Forum Members
Points: 590 Visits: 359
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?
j nuñez
j nuñez
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1109 Visits: 279
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)
j nuñez
j nuñez
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1109 Visits: 279
And, this is my version:

Edition: Enterprise Edition
Engine: 3
Version: 8.00.2039
upgrade: SP4
DPhillips-731960
DPhillips-731960
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1158 Visits: 801
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?
michael.kaufmann
michael.kaufmann
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1293 Visits: 1082
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).
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
j nuñez
j nuñez
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1109 Visits: 279
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?
Attachments
SQL SERVER 2005.doc (6 views, 63.00 KB)
TheRedneckDBA
TheRedneckDBA
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1872 Visits: 2592
serinor.e090266 (3/27/2009)
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?



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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search