Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005

    Question Unanswered: Ordering of data rows

    It is one of the covenants of DBMS that data rows are retrieved in no particular order, and I have learned not to rely on that. However, I'd like to know under what circumstances could rows be returned, e.g., as a result of a simple query on SQL Server, not in the order they were stored. I have never seen this happen so far. I can't even test my sorting algorithms (on the data after it gets retrieved from the server) since it always comes already sorted. Naturally, I can always simulate this scenario (and I have) but this is kind of a "conflict of interest", I'd like to see "the real thing". Any ideas?

  2. #2
    Join Date
    Jan 2004
    In a large office with bad lighting
    Based on what I have seen, the easiest way to prove this would to create a heap (table with no PK) inseert some rows, then do an update on a few rows. Then do a select * from <tablename>. the rows will be returned not in input order, but unmodified rows first, followed by modified rows.

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

  3. #3
    Join Date
    Jan 2003
    Provided Answers: 17
    create table test1
    (col1 int)
    create index ind_test on test1 (col1 desc) 
    insert into test1 values (1)
    insert into test1 values (2)
    insert into test1 values (3)
    insert into test1 values (4)
    insert into test1 values (5)
    insert into test1 values (6)
    insert into test1 values (7)
    insert into test1 values (8)
    select *
    from test1

  4. #4
    Join Date
    Jun 2003
    Provided Answers: 1
    Any accolyte should know that we do not publicly reveal the secret covenants of DBMS. Report yourself to your DB Mentor at once for pennance.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  5. #5
    Join Date
    Dec 2005
    Thanks for the suggestions (especially by the Illuminati above ), however my database tables all have PK and data there is only INSERT-ed at the end, never UPDATE-d. I guess I shouldn't be concerned until I'm given a reason to be.

Posting Permissions

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