Post reply

where not exists sub-query and how it effects sort order of a table

  • August 7, 2014 at 2:39 pm

    #291867

    Hello folks.

    I have a question. I have a query that is taking the top XX rows of a table and putting it in a temp table that I've ordered by a unique id.

    I then pull a portion of those rows out to do some manipulation (irrelevant for this topic as it happens after the problem I'm describing) but

    I've found that the order is not always correct and sometimes rows are skipped in my final return statement (actually it's a delete statement).

    the portion I pull is a top XX

    with a not exists in a reference table

    the #originaltmptable is just a table that I originally populate in order to reduce the number of rows I am deleting to relieve pressure because this is an

    OLTP database in high use.

    so the query looks a little like this:

    INSERT TOP (1000)

    INTO #mytmpdelete

    SELECT

    Idfield

    FROM #originaltmptable AS T WITH (NOLOCK)

    WHERE NOT EXISTS (

    SELECT *

    FROM referencetable AS r WITH (NOLOCK)

    WHERE rId = @passedparameter

    AND r.Idfield = T.IdField

    AND r.reffield BETWEEN T.firstreffield and T.lastreffield)

    so the #originaltmptable has already been sorted, but the order comes back slightly different sometimes in the query above even though the results are usually identical, can anyone tell me why?

    I have solved it by wrapping the select statement with another select where I do an order by, so I know how to fix it, but I'd like to know the root cause of this. Can the not exists sub-query actually change the sort order of the parent query?

  • August 7, 2014 at 2:59 pm

    #1736211

    First things first - remove the NOLOCK hints. It's not a magic "accelerate my query" command. Do you get correct answers if you do that?

  • August 7, 2014 at 3:25 pm

    #1736219

    No order by, no guarantee of order. End of story.

    Tables don't have a defined order. They are unordered sets of rows.

    Edit: and put the TOP on the select. Along with an appropriate order by, that should get you the rows you want.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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