Post reply

Sort Order when SELECT FROM Table Variables

  • June 7, 2011 at 8:22 am

    #241823

    Hi,

    Would I be correct in thinking that if you omit an ORDER BY clause when querying a Table variable in TSQL that you cannot guarantee the order in which the resulting records will be returned? That is, the Query Optimizer treats them the same as a normal user table.

    For example, the output from the following TSQL would not guarantee a result set ordered by Col1:

    --Insert into a table variable in order of Column 1, asceding.

    INSERT @aTable

    SELECT

    Col1

    , Col2

    , Col3

    FROM

    anotherTable

    ORDER BY

    Col1

    --Select from the table variable with no ORDER BY clause.

    SELECT

    *

    FROM

    @aTable

    Thanks!

  • June 7, 2011 at 8:23 am

    #1335046

    There are 0 exceptions to that rule. If you want order by you MUST use order by.

  • June 7, 2011 at 8:33 am

    #1335051

    Great, thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply