V7 vs V8 - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

V7 vs V8

Author

Topic

gugul
Starting Member

2 Posts

Posted - 2010-11-12 : 09:36:27

Why in V7 you could do
a Select with more then 1 row and fetch the 1st row, using for exemple an order by statment by age.
"Controling" the -811 error you would get the lowest age.

With the V8 it returns the 2nd row.
Why change that?

Now i've to use fetch first row only.

The problem is that now i've to change all my programes!

Why change something like that?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2010-11-12 : 11:04:28

DB2?
http://www.ibmmainframes.com/viewtopic.php?t=2920&highlight=811

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 11:20:22

if it is DB2, they have removed the functionality that GROUP BY, DISTINCT, UNION no longer sort the result sets

You must use an ORDER BY

Pretty amazing, huh

...I thought there 5 operators that caused a sort....What am I missing

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

gugul
Starting Member

2 Posts

Posted - 2010-11-12 : 11:47:56

I know how to solve the problem.

What i would like to know is why change something that work before.

Maybe what i did, using an db2 error to do something, is not a good thing.
But it worked, and now it doesn't.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 11:55:09

you gotta post a code sample

But the is a SQL Server site

So you can put it here

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=7

Or here

http://www.dbforums.com/db2/

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-12 : 12:49:59

quote:
Originally posted by X002548

...I thought there 5 operators that caused a sort....What am I missing

No idea about DB2, but in SQL there are two ways to do group by, distinct and union. One involves a sort, the other a hash.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 13:20:51

quote:
Originally posted by GilaMonster

No idea about DB2, but in SQL there are two ways to do group by, distinct and union. One involves a sort, the other a hash.

--
Gail Shaw
SQL Server MVP

Huh?

Order by?

And I didn't think anything BUT ORDER BY caused an order in sql server

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 13:30:28

So now, why did I think that for all these years?




CREATE TABLE myTable99(Col1 int, col2 char(1))
GO

INSERT INTO myTable99(Col1, col2)
SELECT 2, 'a' UNION ALL
SELECT 3, 'b' UNION ALL
SELECT 1, 'c'
GO

SELECT Col1 FROM myTable99

SELECT Col1 FROM myTable99 ORDR BY Col1

SELECT DISTINCT Col1 FROM myTable99

SELECT Col1 FROM myTable99 GROUP BY Col1

SELECT Col1 FROM myTable99 UNION SELECT Col1 FROM myTable99 
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-12 : 14:48:38

quote:
Originally posted by X002548

And I didn't think anything BUT ORDER BY caused an order in sql server

Mostly correct. Nothing but order by guarantees the order of a returned resultset, but there are a number of operators that require the data in a particular order and may either sort the data or require a sort earlier in the plan

Stream aggregate
Union (can be implemented as a Sort/Distinct sort)
Distinct (can be implemented as a Sort/Distinct sort)
Merge join

Whether or not that affects the final order of results is another matter

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 15:09:37

ok...so it must do something internal..BUT DOES NOT guarantee that order in the result set..correct?

So....ORDER BY is the only guarantee

DB2 USED to guarantee the order...but has now stopped...which is freakin unbelievable...which is why the Oppy (and probably myself) is pining away

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-12 : 15:28:19

quote:
Originally posted by X002548

ok...so it must do something internal..BUT DOES NOT guarantee that order in the result set..correct?

Correct

quote:
So....ORDER BY is the only guarantee

Correct

quote:
DB2 USED to guarantee the order...but has now stopped...which is freakin unbelievable

Documented guaranteed order, or just always behaved that way?

If the latter, SQL's had the same recently in several cases. Like order by in a view definition. The order by was incorrectly honoured in SQL 2000, lots of people assumed it was by design. 2005 'fixed' it.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 15:45:48

no, a "knew" it wasn't the case...

I was in a DB2 V9 seminar when the made mention of the order going away in 8

I am still stunned

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-11-13 : 05:40:56

"QL's had the same recently in several cases. Like order by in a view definition. The order by was incorrectly honoured in SQL 2000, lots of people assumed it was by design. 2005 'fixed' it."

Although its much wider than that, isn't it Gail?

I figure that lots of people forget (or think they don't need) to use ORDER BY because their query is simple and "always" comes back in Clustered Index order, so it gets through DEV and QA tests without breaking ... but in production starts giving random sorts, maybe only very occasionally, when some data happens to be in RAM and SQL short-circuits the normal process to use that data before loading the rest from disk (and in the absence of an explicit ORDER BY returns it in whatever order that turns out to be).

On this matter I think there should either be:

DEV mode that randomises unsorted-SELECT statements to flush out bugs
and/or
LINT tool, or "verbose warnings" DEV mode, that moans about "loose" code.

JOIN MyOtherTable on MyVarcharCol = MyIntCol

for example. Don't want it to moan? Put a CAST in

and then SQL 2000 SP4 won't be a disaster for you (old news ... but no doubt there is a similar Gotcha! waiting in the wings for some future S/Pack or Upgrade)

I wish I knew how to use a compiler-compiler, or a lexer or somesuch, as I imagine it would not be hard to build. But MS using MS SQL's own Lexer must be a better route as it could easily be coaxed into spitting out a warning when it had to do implicit casts, and in other scenarios like:


SELECT FirstColumnIWant
       SecondColumnButIForgotTheComma

where AS was omitted.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-13 : 10:52:40

WHERE AS Committed

Test

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-13 : 14:35:53

quote:
Originally posted by Kristen... but in production starts giving random sorts, maybe only very occasionally, when some data happens to be in RAM and SQL short-circuits the normal process to use that data before loading the rest from disk (and in the absence of an explicit ORDER BY returns it in whatever order that turns out to be).

More commonly because parallelism's kicked in or a join or group by has switched to hash.

Merry-go-round scans (what you're describing there) only happen under very specific circumstances.

quote:
LINT tool, or "verbose warnings" DEV mode, that moans about "loose" code.

But how would you tell a query that has no order by but should have one from one that doesn't need to return in any specific order (perhaps because the front end is sorting it)?

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-11-14 : 05:10:18

"But how would you tell a query that has no order by but should have one from one that doesn't need to return in any specific order (perhaps because the front end is sorting it)?"

Th LINT tools that I have used (for C/C++ and Javascript) allow a comment (in a special format) to be used to indicate that a particular warning should be ignored. Bit like using a CAST really, you are indicating that you know that such-and-such is happening and are "authorising" it. You can turn a warning off for all/part of a script, and then turn it back on again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources