Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    1

    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.

    TIA. Rick

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    If there is clustered index - sorting by clustered index, else sorting by inserting records.

    BOL says:

    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.

  3. #3
    Join Date
    Jun 2002
    Location
    USA
    Posts
    21
    Hi ,

    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

    FYI:
    ----
    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.

    For more information , see BOL - Collation

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "If there is no ORDER BY clause in the Query , then the data will be in the same order as inserted"

    if you're lucky

    records can get physically stored not in insertion sequence (e.g. a row happens to be too big for the current physical database page, but the next one inserted after that isn't)

    i believe records are returned in physical sequence, all things being equal

    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

    i too have heard that the sort order is unpredictable, but i too have been unable to find this on microsoft's site

    a "definitive" answer will be found, of course, only on microsoft's site

    rudy
    http://rudy.ca/

  5. #5
    Join Date
    Jun 2002
    Location
    USA
    Posts
    21
    Hi,

    I dont agree with "R937". Sorry to say that.

    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,

    Use pubs
    go
    /* This will return rows where job_id > 5 */
    Select * from jobs where job_id > 5
    go

    Select * from jobs
    go
    AS per your statement, this has to return the jobid>5 from cache first and then job_id<4 from disk.

    Thats not true.

    Concept:
    ----------
    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.

    Have Fun
    Varad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •