Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    41

    Unanswered: MS SQL query, what's the default order the rows returned are sorted by?

    i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?

    this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in
    Last edited by Chauzer; 01-12-05 at 15:11.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you want an order, specify the order with the ORDER BY clause. If you are willing to take whatever order the optimizer decides on, omit the ORDER BY clause.

    -PatP

  3. #3
    Join Date
    Dec 2003
    Posts
    41
    hmm this is weird.. in the access database if i select it, they're returned in the order the rows were inserted.. but after importing that database into ms sql, and selecting that table, the order isn't the same row i got when i ran the query in the access db

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Jet, the default database engine used by MS-Access is rather "simple-minded" when it comes to query optimization. MS-SQL has a much more powerful optimizer, which is a two-edged sword... The MS-SQL optimizer is able to easily process queries that Jet would never complete, but it does that processing in a very different way. As an interesting side effect, it also means that unless you specify an order in your query, there is no guarantee that running the exact same query on the same box will ever return the rows in the same order, even though it often will return them in a consistant order.

    -PatP

  5. #5
    Join Date
    Dec 2003
    Posts
    41
    Ahhh ok i see what you're saying.

    In my query, i had a left join statement in there.. i took that out and used a subquery instead of the join and it returns the rows fine now in the order they were inserted. Looks like the join caused the problem.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, the JOIN did not cause the problem. The absence of an ORDER BY statement caused the problem.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem is actually a lot simpler than "the join caused the problem". If you want an order, specify it with the ORDER BY clause. If you don't care about an order and are willing to accept the order determined by the optimizer at the moment, omit the ORDER BY clause.

    -PatP

Posting Permissions

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