September 11, 2003 at 11:51 am
I recently came across an interesting problem. An application that I work on broke because data was not returning in the correct order. An ORDER BY was added to the stored procedure, which fixed the problem, but I don't understand how it could have broken.
There are two tables, a and b, both of which have a single clustered index on the id field, which is an integer. The clustered index is the only index on each table. The following query returned data that was NOT ordered by the id after the tables were defragmented (and possibly reindexed) one night by a maintenance job. Every subsequent defrag/reindex caused the data to again be returned in order of the clustered index.
select a.id,
a.x,
a.y,
a.z
from a
inner join b on b.id = a.id
where b.active = 0
Does anyone know how this could have happened? I thought that the clustered indexes would always return the data ordered. Could this have happened because of defragmenting or reindexing?
September 11, 2003 at 7:05 pm
Hi there
Unless you have an explicit order by, no matter the indexing in place, sql server will not guarantee the results are returned sorted. Remember that rows must be bought into the buffer cache to be process by the sql engine, as such, the order may be skewed from the read from this cache, along with a possible mix of physical and logical reads. It is understood that the clustered index is pre-sorted in the order of its keys, but that has nothing to do with the return rows from the sql engine to the end user. Whack in the order by to guarantee you dont have these unpredictable results.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply