Ben Leighton (3/19/2009)
this question is flawed...if the SET READ_COMMITTED_SNAPSHOT is set to on for the database you are using then the query will return 1 and 2...
I'm being picky here I admit...
I decided it should be 1 & 2 and gave that answer, then tested it in my scratch database and, of course, got 1 & 2 returned in the second window.
QotD didn't state that database should have READ_COMMITTED_SNAPSHOT set to OFF and my scratch database still had it set to ON from tests done when responding to a QotD a few weeks back! 😀
Of course, since the scratch database rarely had any extended transactions in it, the difference isn't often noticed.
Derek