Re: Recordset's Order and Database's Physical Order?
This is a discussion on Re: Recordset's Order and Database's Physical Order? within the SQL Server forums, part of the Microsoft SQL Server category; --> Jacco Schalkwijk Wrote: > Hi Rhett, > > Rowsets in SQL are unordered by definition. If you want them ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 06:39 PM
| ||||
| ||||
 Re: Recordset's Order and Database's Physical Order? Jacco Schalkwijk Wrote: > Hi Rhett, > > Rowsets in SQL are unordered by definition. If you want them ordered you > need to use an ORDER BY clause. > > SQL Server's Query Optimizer (specially in SQL Server 2000, earlier versions > were not as good at it) uses the fact that the returned rowset is unordered > to return the data in the fastest way possible. Sometimes you see quite > unexpected orders when you don't specify an order by clause. > > In your case the situation is as follows: > You have a primary key on your table which is created with a clustered index > by default. When you do your first select the Query Optimizer uses the > clustered index. Next you create a unique constraint on the categoryname > column, which is implemented with a non-clustered index. A non-clustered > index uses a pointer to locate the row it refers to in the table, and if the > table has a clustered index, the clustered index is the pointer. So the > non-clustered index in this case exists of both the categoryname and the > categoryid columns. Because this are all the columns that are used in your > query, the Query optimizer considers to use this index as well as the > clustered index, and it actually decides that using the non-clustered index > is cheaper than using the clustered index, so it uses the non-clustered > index and your results are returned in the order of the non-clustered index. > > Note that even though the Query Optimizer uses a certain index to retreive > the data, this doesn't guarantee that the data is actually returned in that > order. Data can be stored scattered all over the disk, and, as I said > earlier, unless you specify an ORDER BY clause, the data will be returned in > the way it is read, because that is the quickest way to return it. > > If you want more information about this, read Kalen Dealney's excellent book > 'Inside SQL Server'. > Thanks for you all pretty guys! All confusion gone with the wind. My fault is at that assuming the order of the returned recordset is just the order physically stored,so fool. Thanks!      |
Linear Mode
