Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What's the best way to count?


What's the best way to count?

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8273 Visits: 11536
vk-kirov (10/19/2010)
Hugo Kornelis (10/19/2010)
The same page in Books Online also contains this warning: "Partitioned tables and indexes are not fully supported in this view; use the sys.indexes catalog view instead." ... Since method 2 does not work for partitioned tables, it's not reliable.

The 2nd query works just fine for partitioned tables :-)
That BOL page states what columns are not supported in case of partitioned tables: first, root, groupid, dpages, reserved, used, rowcnt, rowmodctr, and FirstAIM. The column 'rows' is not in this list, and it's supported.

I missed that - I stopped reading after seeing that quote. Thanks, vk-kirov!
With this additional information, I'd argue that options 2 and 4 are both correct. The unreliability of option 2 stopped after SQL 2000 (and the question specifically mentions SQL 2005 and later), and being deprecated was nog mentioned as a deciding factor in the question.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16332 Visits: 13197
Option 1 isn't that reliable. For really really large tables (e.g. number of records > 2^31-1) it will produce an error. To be 100% sure, COUNT_BIG should have been used.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16332 Visits: 13197
Oh yeah I forgot: great question! :-D



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2873 Visits: 2235
One more challenging question, thank you Dave.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3492 Visits: 4408
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? :-)
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8273 Visits: 11536
vk-kirov (10/19/2010)
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? :-)

Depends.
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);

BEGIN TRAN;
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);
COMMIT TRAN;



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
dave.farmer
dave.farmer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1151 Visits: 768
Thanks for this one - lots of stuff I didn't know there.

Does anyone know if there's a similar fast count method for views? I have a health check procedure which counts the number of records in a variety of tables and views (to check data load success and ensure data is there for the next stage in the process). The table counts are fine (biggest table has around a million records), but the views are terribly complex and counting them with SELECT COUNT(*)... takes ages.

Cheers,
Dave.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8273 Visits: 11536
dave.farmer (10/19/2010)
Does anyone know if there's a similar fast count method for views?

Only if they are indexed. Or if the relationships between the underlying tables are such that you can derive the number of rows in the view from the number of rows in the tables.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
dave.farmer
dave.farmer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1151 Visits: 768
Hugo Kornelis (10/19/2010)

Only if they are indexed. Or if the relationships between the underlying tables are such that you can derive the number of rows in the view from the number of rows in the tables.


Sadly no, on both counts Crying

Oh well... thanks anyway.
f_ernestog
f_ernestog
Right there with Babe
Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)

Group: General Forum Members
Points: 753 Visits: 186
I tried the four queries on a table that has 36k rows, using as parameter the amount of rows returned by the count(*) query, the fourth option is not accurate at all, actually it displays about 400k rows (this table have no modifications at all), and compared to the count(*) one, it less efficient as well.

The only one that gave the most reliable result and was fast, was the count(*) option, followed by the third query. That's what I've find for this table, it might change for bigger tables :-)

Frank.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search