SQL 2000 - 2005 difference - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2000 - 2005 difference

Author

Topic

stephe40
Posting Yak Master

218 Posts

Posted - 2007-02-16 : 10:48:48

A coworker of mine said that after we migrated his database to 2005 he noticed an issue with order bys in his stored procedures. This is what he told me:


Here is what I was doing:

..
  t.BomOrder,
  t.ItemNum,
  t.Required,
  t.OrdDescr,
  t.BOMDwgNum,
  t.PicNum,
  t.GradeChangePart,
  t.SafetyStock
FROM tbStorlib s,
     #tbWudsResults t
WHERE s.PKId = t.PKId
ORDER BY t.WudsMode, t.ItemNum

Here is how to fix it:

..
  t.BomOrder,
  'ItemNum'  = t.ItemNum,
  t.Required,
  t.OrdDescr,
  t.BOMDwgNum,
  t.PicNum,
  t.GradeChangePart,
  t.SafetyStock
FROM tbStorlib s,
     #tbWudsResults t
WHERE s.PKId = t.PKId
ORDER BY 'WudsMode', 'ItemNum'

Now am I missing something with 2005? I can't imagine in anyway why a simple select statement like this would have to be modified? This is one of the first larger databases we migrated, so I definatly want to investigate this to make sure others will not have the same problems. I have yet to hear back from him with more details. I initially thought that since he was not using the sql92 style joins it may cause problems, but that is hard for me to believe. I just wanted to post this and see if anyone else came across the same issue when migrating databases to 2005. Does anyone have something to add that would help me understand this?

- Eric

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 10:58:27

Bad move!
Now all records are sorted randomly.

Try to switch to ANSI style JOIN first!

		..
		t.BomOrder,
		t.ItemNum,
		t.Required,
		t.OrdDescr,
		t.BOMDwgNum,
		t.PicNum,
		t.GradeChangePart,
		t.SafetyStock
FROM		tbStorlib AS s
INNER JOIN	#tbWudsResults AS t ON t.PKId = s.PKId
ORDER BY	t.WudsMode,
		t.ItemNum

And now you check what kind of data (if any) is present in t.WudsMode column and t.ItemNum column.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 10:59:45

I think your co-worker did a mistake with this. Maybe he was too tired?

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-16 : 11:06:03

You didn't explain what problem he claims to be having, so it is hard to comment on a "fix" for an unknown problem.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-02-16 : 12:01:29

"he noticed an issue with order bys in his stored procedures"

Is the combination of t.WudsMode & t.ItemNum in this report Unique?

If not I can believe that the order that items with the same t.WudsMode & t.ItemNum values appear may be different - but that would only be exposing a bug, really, in that to be repeatable the ORDER BY ought to resolve to a unique combination.

But I may be answering the wrong question!

Kristen

stephe40
Posting Yak Master

218 Posts

Posted - 2007-02-16 : 12:04:12

Yeah, he really didn't explain to may what the problem was either. I am waiting on a reply from him with more info. I was just curious if anyone could spot anything wrong with what he did tell me so far.

Yes I know, I already suggested he use the ansi style join syntax.

- Eric

stephe40
Posting Yak Master

218 Posts

Posted - 2007-02-16 : 13:27:19

Yeah, after seeing the whole stored procedure.... He was using a case statement to change the value of the WudsMode column, and he really wanted it ordered by that result, not the acutal value in the table. That was too easy.

- Eric

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 13:46:24

Then use integer values for order by, denoting the ordinal position in the records set,

select ...
from ...
order by 1, 3 desc, 5

Peter Larsson
Helsingborg, Sweden

stephe40
Posting Yak Master

218 Posts

Posted - 2007-02-16 : 15:50:44

Bad idea IMO to use ordinal position in the order by. I have been burned in the past by someone going in and adding a column to the select list but not updating the order by statement. I had him actually use the case statement in the order by clause.

- Eric

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources