Difference between SQLServer 2005 and SQLServer 7.0 Queries

Question

Post reply

Difference between SQLServer 2005 and SQLServer 7.0 Queries

chileu17

Hall of Fame

Points: 3093
More actions
October 3, 2008 at 4:53 pm

#198934

Hi everyone, I am performing a SQL Server migration from SQL Server 7.0 to 2k5 and I have found myself in a very weird and unusual situation :ermm:
I run a select over a table on 2k5 and store the result set into a temporary table, but on this temporary table I have a field that is an identity something like this:
select
foo.*,IDENTITY(int,1,1) as POS
into #tmpFoo
from foo
When I query this table, I always get the result set ordered by the POS field.
The weird thing is that I did this exact same procedure on SQLServer 7.0 and a couple of times I got a difference result, I mean the result set ordered in a different way :ermm:
Any suggestions? Advices? Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 1

You can't rely on an "implicit" sort order... you'll need to use ORDER BY or GROUP BY WITH ROLLUP/CUBE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chileu17 Hall of Fame Points: 3093 More actions · Answer 2

eventhough the table has a primary key? and a non-clustered index?

non-clustered indexes are supposed to be inserted in a specific order right? :ermm: please correct me if I am mistaken XD But well would that have an impact on this order problem?

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 3

chileu17 (10/6/2008)
eventhough the table has a primary key? and a non-clustered index?
non-clustered indexes are supposed to be inserted in a specific order right? :ermm: please correct me if I am mistaken XD But well would that have an impact on this order problem?

Correct... no index will guarantee the order of a Select. Sometimes it happens, but the very next time you use that method, it could just as easily go BOOM!

The real key here is that if the index is constructed properly, the ORDER BY will absolutely fly through the data. If it happens to hit a clustered index just right, you won't even see a sort pop up in the execution plan... but the ORDER BY is still necessary to guarantee the correct sort order for Selects and to prevent merry-go-round indexes which frequently happen.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)