With (NoLock) gives different resultset | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

With (NoLock) gives different resultset

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?

Unless you don’t specify an ORDER BY using TOP is pretty meaningless as there is no guarantee that always the same row will be returned. —
Frank Kalis
Microsoft SQL Server MVP
Heute schon gebloggt?http://www.insidesql.de/blogs

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.
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
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.