Hugo Kornelis (10/19/2010)
With this additional information, I'd argue that options 2 and 4 are both correct.
And what do you think about counting uncommitted records, is it such a reliable thing? :-)
If you want to count order detail lines, none of the queries is good enough. (See below)
If you want to count rows, I'd argue that you WANT uncommitted rows as well. Counting rows is soemthing a DBA does, to estimate space needed. Uncommitted rows take space as well. Counting order detail lines is more a business function; uncommitted rows should not be counted there.
If you want total accuracy, you either have to use one of the snapshot isolation level, or you need to take an explicit table lock. Under RAD_COMMITTED, you can still get incorrect results. For example:
In query window #1, run:
CREATE TABLE dbo.QOTD_TEST (ID INT PRIMARY KEY);
INSERT dbo.QOTD_TEST VALUES(1);
INSERT dbo.QOTD_TEST VALUES(2);
INSERT dbo.QOTD_TEST VALUES(3);
INSERT dbo.QOTD_TEST VALUES(4);
INSERT dbo.QOTD_TEST VALUES(5);
In query window #2, run:
SELECT COUNT(*) FROM dbo.QOTD_TEST;
Execution will start, but not finish as it waits for locks from window #1 to be released.
Now go back to window #1 and run:
DELETE dbo.QOTD_TEST WHERE ID IN (2, 3, 4);
The query in window #2 will now finish and show an incorrect result: 4. When the SELECT COUNT query started, there were 3 committed and 2 uncommitted rows; when it ended, there were 2 rows. This answer is arguably even less correct than the numbers returned by the other queries!
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis