Unanswered: Default sort order when using SELECT with no ORDER BY clause
For SQL Server 2000 and 7.0:
What is the default sort order of a dataset when you perform a SELECT * FROM [tablename] without using the ORDER BY clause?
I've been told it is ordered by the clustered index (my testing does not bear this out) and I've been told that it is completely unpredictable (closer to what I'm seeing). I believe the latter to be true but cannot find this outlined anywhere in Microsoft's BOL, TechNet, KnowledgeBase, MSDN, etc.
Any pointers on where to find the definitive answer is appreciated.
If there is clustered index - sorting by clustered index, else sorting by inserting records.
SQL Server 7.0 tables use one of two methods to organize their data pages:
Clustered tables are tables that have a clustered index.
The data rows are stored in order based on the clustered index key. The data pages are linked in a doubly-linked list. The index is implemented as a B-tree index structure that supports fast retrieval of the rows based on their clustered index key values.
Heaps are tables that have no clustered index.
The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list.
SQL Server also supports up to 249 nonclustered indexes on each table. The nonclustered indexes have a B-tree index structure similar to the one in clustered indexes. The difference is that nonclustered indexes have no effect on the order of the data rows. Clustered tables keep their data rows in order based on the clustered index key. The collection of data pages for a heap is not affected if nonclustered indexes are defined for the table. The data pages remain in a heap unless a clustered index is defined.
If there is no ORDER BY clause in the Query , then the data will be in the same order as inserted.
If you mention ORDER BY Column name , then the dafault sort order will be ASCending if SQL Server is installed by default collation.
Ex. Select * from authors order by authorname
Use the locale identified by Setup, and then choose the desired binary, case, or other options.
For the release of SQL Server 2000, when Setup detects that the computer is running the U.S. English locale, Setup automatically selects the SQL collation: Dictionary order, case-insensitive, for use with 1252 character set.
To select the equivalent Windows collation, select Collation designator, choose the Latin1_General collation designator, do not select case-sensitive, and select accent-sensitive.
To his Question" but what if some of the rows are already in cache, and the remaining rows need to be fetched from disk? it's a safe bet that the ones in cache are pumped out first"
This is absolutely wrong. Thats not the concept of caching or Query Optimization.
For Example, if I excute the query as below,
/* This will return rows where job_id > 5 */
Select * from jobs where job_id > 5
Select * from jobs
AS per your statement, this has to return the jobid>5 from cache first and then job_id<4 from disk.
Thats not true.
As per SQL Server, both the query are entirely different.
First it will just look in cache whether the same SQL Query is available in cache not the data. If the SQL Query is not avaiable , then it will be excuted for optimization and return the rows from disk.
Hope you agree with me.
Any constructive criticism will be appreciated.
FYI: Concepts on Query Optimization will explain well.