Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Temporary Table and Order By

    I created a temp table with an order by clause. If later on I query it, would my result set still follow the order by sort or is it going to be indeterminate like querying an actual table? This is hard to test because 999 of 1000 times I might get an ordered result set.

    Thanks in advance.

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    There is no such thing as an ordered table, temporary or otherwise. If your query doesn't have an ORDER BY clause then the order is undefined and could possibly be different each time the query is used.

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    are you sure? someone answered differently from another (codeproject) forum.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well they were dead wrong.
    Code:
    CREATE TABLE dbo.order_by (
       id int identity(1,1) PRIMARY KEY CLUSTERED
     , some_field char(1)
    )
    
    INSERT INTO dbo.order_by (some_field) VALUES ('B')
    INSERT INTO dbo.order_by (some_field) VALUES ('A')
    
    SELECT *
    FROM   dbo.order_by
    
    CREATE NONCLUSTERED INDEX ni_order_by_some_field ON dbo.order_by (some_field ASC)
    
    SELECT *
    FROM   dbo.order_by
    
    GO
    DROP TABLE dbo.order_by
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by g11DB
    are you sure? someone answered differently from another (codeproject) forum.
    What order do you expect if you don't specify ORDER BY? Since there is no way to define an "ordering" for a table how can you expect to know in what order rows are stored or retrieved? Even if you insert rows one at a time in sequence there is no guarantee they are actually stored in any predictable order (because page splits may occur for example) and even if they were there is no way to instruct SQL Server to use that ordering again because the optimiser decides whether to scan indexes using either index or allocation order, or possibly using a so-called "merry-go-round" scan. (Google it)

    Also, Books Online specifically says: "When used together with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order."

    ORDER BY Clause (Transact-SQL)

Posting Permissions

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