Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»»

TSQL Expand / Collapse
Author
Message
Posted Thursday, March 26, 2009 3:37 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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!


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #684642
Posted Thursday, March 26, 2009 3:43 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,521, Visits: 3,039
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

Post #684650
Posted Thursday, March 26, 2009 4:21 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 5, 2011 8:18 AM
Points: 582, 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?
Post #684674
Posted Friday, March 27, 2009 1:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:46 AM
Points: 1,109, 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)
Post #684814
Posted Friday, March 27, 2009 2:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:46 AM
Points: 1,109, Visits: 279
And, this is my version:

Edition: Enterprise Edition
Engine: 3
Version: 8.00.2039
upgrade: SP4
Post #684817
Posted Friday, March 27, 2009 2:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 10, 2012 6:08 PM
Points: 1,156, 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?
Post #684818
Posted Friday, March 27, 2009 4:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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).
Post #684857
Posted Friday, March 27, 2009 5:24 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #684904
Posted Friday, March 27, 2009 6:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:46 AM
Points: 1,109, 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?


  Post Attachments 
SQL SERVER 2005.doc (5 views, 63.50 KB)
Post #684942
Posted Friday, March 27, 2009 6:25 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 6:15 AM
Points: 1,510, Visits: 2,164
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.


Jason Shadonix
MCTS, SQL 2005
Post #684976
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse