SQL Server Performance

With (NoLock) gives different resultset

Discussion in 'General Developer Questions' started by saransh, Jan 12, 2006.

  1. saransh New Member

    Hi,

    I have a table with more than 1.5 Million records.

    Let us say table name is A
    then following queries give me different resultset

    Query 1: select top 1 * from A

    Query 2: select top 1 * from A with (nolock)

    what i do not understand is that Query 1 returns same resultset everytime, but Query 2 is flip-flopping between only two resultsets.

    Why?


  2. FrankKalis Moderator

  3. saransh New Member

    I agree but just out of curiosity, I want to check as I am getting same rows with Query 1 everytime, but with Query 2, I am getting flip-flop between 2 rows. Why not other rows, why only those 2 rows coming alternatively with every execution.
  4. Madhivanan Moderator

    Usually Top is used only if there is Order by Clause [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. Adriaan New Member

    If you need predictable results with TOP, you must use ORDER BY.

    There probably is an explanation for the flip/flop of two results in the WITH (NOLOCK) version, but you're entering guru territory there.

Share This Page