SQL Clone
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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18959 Visits: 12426
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63538 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63538 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7591 Visits: 2250
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
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4350 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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18959 Visits: 12426
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.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1339 Visits: 795
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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18959 Visits: 12426
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.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1339 Visits: 795
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
SSC Eights!
SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)

Group: General Forum Members
Points: 811 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