Weird behaviour of SQL SERVER 2005.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Weird behaviour of SQL SERVER 2005.

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-06-10 : 04:48:36

Hi All,

I hope all of you are doing good. !!

Well, this is one of the issue which i came accross while migration from SQL SERVER 2000 to SQL SERVER 2005.

The query perform differently in 2000 and 2005.

PS: This is just an Test Data and not the Real

SQL SERVER 2000


Declare @a Table 
(
	i varchar(10),
	k int
)

Insert @a
Select 'a', 1 Union All 
Select 'h', 3 Union All 
Select 'j', 4 Union All 
Select 'l', 2 Union All 
Select 'g', 5 

-- FIRST QUERY 
Select * From @a order by k 

-- SECOND QUERY 
Select * 
From 
(
	Select Top 100 Percent * From @a order by k 
) as f 

--OUTPUT 

i          k
---------- -----------
a          1
l          2
h          3
j          4
g          5



i          k
---------- -----------
a          1
l          2
h          3
j          4
g          5

SQL SERVER 2005


Declare @a Table 
(
	i varchar(10),
	k int
)

Insert @a
Select 'a', 1 Union All 
Select 'h', 3 Union All 
Select 'j', 4 Union All 
Select 'l', 2 Union All 
Select 'g', 5 

-- FIRST QUERY 

Select * From @a order by k 

-- SECOND QUERY 

Select * 
From 
(
	Select Top 100 Percent * From @a order by k 
) as f 

--OUTPUT 

i          k
---------- -----------
a          1
l          2
h          3
j          4
g          5


i          k
---------- -----------
a          1
h          3
j          4
l          2
g          5

I solved in the thing in the query.. but was just wondering is this a new thing or know???

Chirag

http://chirikworld.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2007-06-10 : 06:38:17

"just wondering is this a new thing or know"

"Select Top 100 Percent" in sub-select is known to behave differently in SQL 2005

http://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspx

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-06-10 : 09:15:31

aha.. Thanks Kristen for the pointer..!!

Chirag

http://chirikworld.blogspot.com/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-06-11 : 00:33:41

Waht a shame TOP 100 PERCENT is still legal syntax when used like this. I'm not sure which is worse after an upgrade - breaking loads of 'working' code & views or having them still execute but return different results to before.

Out of interest, what do you want an order by in a sub-select anyway? Why not just do it on the outside? My guess is some internal framework perhaps?

Kristen
Test

22859 Posts

Posted - 2007-06-11 : 03:42:58

"I'm not sure which is worse after an upgrade - breaking loads of 'working' code & views or having them still execute but return different results to before"

Well I take a different view! I can't imagine putting "TOP 100 PERCENT" in my code as it so obviously reads like a kludge ...

"Out of interest, what do you want an order by in a sub-select anyway"

There you go then - you agree too!!

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-06-11 : 04:14:26

quote:
Out of interest, what do you want an order by in a sub-select anyway? Why not just do it on the outside? My guess is some internal framework perhaps?

Well it was a view which was coded 5 years back.. and was working wonderfully without this upgrade...

..

And its functionality was just to bind to one of grid and then user uses to extract this to excel ....

Chirag

http://chirikworld.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2007-06-11 : 06:04:31

Shouldn't code Order Dependency in a VIEW - any more than you can't do that in a Table either !!

But-I-know-that-you-know-that !!

Pain to sort out, none the less, but think of it like moving house and clearing out all the crud (CRUD?

) in the attic!

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-06-11 : 06:45:38

Pain to sort out, none the less, but think of it like moving house and clearing out all the crud (CRUD? ) in the attic!

Aha.. thatz the good way to pump in the Enthusiams.. for rework

Chirag

http://chirikworld.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2007-06-11 : 07:45:38

Nah, its Refactoring ... you can charge more for that!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 09:27:15

can Anyone give any example how order by in a subquery matters?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-06-11 : 10:25:00

"can Anyone give any example how order by in a subquery matters?"


CREATE VIEW V
AS
(
    SELECT TOP 100 PERCENT * 
    FROM MyTable
    ORDER BY MyColumn DESC
)

but it is Crap Code to start with of course ...

... and it isn't strictly a Sub Query ...

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-06-12 : 07:43:06

Nah, its Refactoring ... you can charge more for that!

Company provides as an complementary.. so cant charge

Chirag

http://chirikworld.blogspot.com/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-12 : 07:57:03

order in subquery is completly irrelevant so top 100 percent is also irrelevant.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 09:11:33

I still dont understand why order by in subquery is allowed

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-06-12 : 09:17:35

order in subquery is completly irrelevant so top 100 percent is also irrelevant.

Agreed !!!

I still dont understand why order by in subquery is allowed

May be just for backward compatiblity, with 2000..

Chirag

http://chirikworld.blogspot.com/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-12 : 10:04:29

it should be allowed exactly because of top clause.

and also top 100 percent is rightfully disregarded in parsing.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-06-12 : 12:24:45

hehehe .. so it HAS provided the TOP 100 Percent of the sub-query .... just there is no Side Effect of ordering the data. Seems OK to me ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources