Results 1 to 6 of 6

Thread: selection order

  1. #1
    Join Date
    Jun 2005
    Posts
    85

    Unanswered: selection order

    Hi,

    Can I assume that SQL Server 2000 will always give the result set of a simple query in the same order as the rows inserted into the table ?

    Eg :

    select *from test

    // there is no order by statement Or TOP clause used. Just a simple select query.

    Please advise,

    Thanks,
    MiraJ

  2. #2
    Join Date
    Feb 2004
    Posts
    88
    > Can I assume....

    No you can't. If you want a specific order, you HAVE to specify it.
    This doesn't mean you won't get lucky sometimes, though...

    HTH

    Bill

  3. #3
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    frm BOL

    "A clustered index determines the physical order of data in a table" that means the records will be re arranged with each INSERT statement.

    so the results returned by SELECT will not be in the same order as that of INSERT if the table has aclustered index
    Cheers....

    baburajv

  4. #4
    Join Date
    Jun 2005
    Posts
    85
    If there is no index on any of the columns, will it give in the same order ?

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by MiraJasmin
    If there is no index on any of the columns, will it give in the same order ?
    Not necessarily. No indexes = heap. It depends on the placement of the row within the data retrieval sequence, which depends on the placement on the disk (or cache). No index = no guarantee.

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Although the presence of a clustered index often seems to produce an ordered resultset, there is no guarantee that this will work in all cases. If you fire a SELECT statement without an explicit ORDER BY,sql server will try to return the information in the fastest possible way. This might be or might be not equal to the order of the clustered index keys.


    For example, there might already be data pages in the buffer pool present and sql server might opt to return those first before reading the remaining data from disk. Another example might be, when parallelism is involved.

    If you need to rely on the return of an sorted resultset, you need to have an explicit ORDER BY criteria.



    Though this is partially different to earlier versions of SQL Server where certain commands produced an ordered resultset. Even without ORDER BY, SQL Server 2000 is in conformance with the ANSI SQL standard.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

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