February 12, 2008 at 10:47 am
All,
I was working on some optimization of a stored procedure in SQL2000 recently which created a table variable and filled it with some 11k rows and then had 2 selects below to output data. I decided I'd remove the table variable.
All looked good. The stored procedure was 4x as fast, the number of rows output was the same for each query, but oh dear....the sort order of the 2nd select was incorrect and I could not correct it with the order by clause.
So I ran a test.
--Test a physical table
CREATE TABLE T1 (ENTITYID INT PRIMARY KEY)
INSERT INTO T1
SELECT ENTITYID
FROM ENTITY
ORDER BY ENTITYNAME DESC
SELECT * FROM T1
DROP TABLE T1
--Test a table variable
DECLARE @T1 TABLE (ENTITYID INT PRIMARY KEY)
INSERT INTO @T1
SELECT ENTITYID
FROM ENTITY
ORDER BY ENTITYNAME DESC
SELECT * FROM @T1
The output is different. The problem is when I use the DESC in the order by when inserting to the table variable. If I remove this, the output is correct. When using the DESC, it's not like the data is sorted from top to bottom, it's mixed up.
I am wondering if this is the behaviour in SQL2000 or if it is a bug? I have tested this in SQL2005 and the output is identical.
Anyone care to shed any light on this? Maybe its normal and I have just missed some documentation.
Thanks
February 12, 2008 at 11:05 am
I forgot to add, it's SP4. No additional hot fixes after SP4 have been applied - I am trolling through those now to see if there was a fix.
February 12, 2008 at 11:18 am
Move the order by clause from the insert to the select.
DECLARE @T1 TABLE (ENTITYID INT PRIMARY KEY, entityname varchar(100))
INSERT INTO @T1
SELECT ENTITYID, entityname
FROM ENTITY
SELECT entityid FROM @T1
order by entityname desc
(Changes in lower-case.)
See if that does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2008 at 11:27 am
Sorry, didn't quite explain myself there. I'm not trying to make the resultsets the same.
The two pieces of code are the same, with the exception that one inserts to a static table and one inserts to a table variable.
What I am trying to work out is if this is the correct behaviour for SQL2000 or if it's possibly a bug which they fixed with a hotfix post SP4 or whether it was only fixed/changed in SQL2005.
February 12, 2008 at 12:26 pm
Ordering is only guaranteed within a SELECT with an ORDER BY clause.
Ordering during an INSERT may or may not happen the way you hope in any version of SQL Server.
February 12, 2008 at 1:18 pm
Clive Strong (2/12/2008)
Sorry, didn't quite explain myself there. I'm not trying to make the resultsets the same.The two pieces of code are the same, with the exception that one inserts to a static table and one inserts to a table variable.
What I am trying to work out is if this is the correct behaviour for SQL2000 or if it's possibly a bug which they fixed with a hotfix post SP4 or whether it was only fixed/changed in SQL2005.
If you don't use Order By in your select, it's just luck (basically) that you get the order you want.
Usually, with a permanent or temporary table, or even a table variable, the odds are good that you'll get data back in the sequence of the clustered index. But that's just "usually", it can't be counted on.
That's not a bug, it's very nearly part of the definition of relational databases. To the database, per definition, row-order doesn't matter. Neither does column order.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2008 at 1:22 pm
I should ammend what I just said. A table variable doesn't have a clustered index. Doesn't have any indexes. But it's common (though not guaranteed) that unordered selects from it will come back in the sequence of the primary key.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2008 at 6:27 pm
Michael Valentine Jones (2/12/2008)
Ordering is only guaranteed within a SELECT with an ORDER BY clause.Ordering during an INSERT may or may not happen the way you hope in any version of SQL Server.
As I recall - the only time ORDER BY has any impact on an INSERT statement is when there is an identity field. And even in that case - it doesn't guarantee physical order although I understand it ensures that the rows get the correctly sequenced ID. To quote Conor Cunningham:
INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted
the rest of the blog being quoted is here:
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply