Alexander Kuznetsov (10/19/2010)
Did you verify how method 4 works under high concurrency? Can we get somewhat incorrect totals?
Hi Alex,
Examples of incorrect rsults for this method have already been posted in the disussion. Though "incorrect" depends on the goal here - do you need to know the number of order detail lines, or the number of rows that the table occupies at moment X. In the latter case, I'd argue that uncommitted rows HAVE to be counted.
In the former case, the only truly reliable way is method #1 with either snapshot isolation, or a table lock.
mtillman-921105 (10/19/2010)
It's simple tasks like this that I think MS needs to work on. Judging by this question, most of us (yours truly included) can't even get an accurate count of rows in a table. That's just nuts! MS can't simply write us a function so we don't have to sweat the small stuff like this?I think I know why they can get away with it. It's because Oracle's even harder to use. :hehe:
Note that you only run into problems under high concurrency. Oracle has less of those problems, because it uses snapshot isolation by default. You can set the same default in SQL Server (read_committed_snapshot). This does come at the expense of extra overhead, which is why this option is disabled by default in SQL Server.
Richard Sisk (10/19/2010)
Well, if I'm typing the query into a SSMS window, I'll go with number 1 and finish way before anyone else 😀
Not if it's a 20TB table 😛