Fastest Way to Retrieve Rowcount for a Table - SQL in Sixty Seconds 096

In this video, we will see the fastest way to retrieve a row count for a table.
Here is the script used in the blog post:

Stay tuned for bloopers at the end of the video.

0:00 Introduction
1:15 Script for RowCount
2:16 Bloopers

#SQLServer #PerformanceTuning #SQLAuthority

[melissam]

Here because I got asked this question in a job interview. I didn’t get the job 😂 thanks to the simple explanation I’ll know this if it happens again.

[pushpayerraguntla]

you are awesome, the very joyful way of teaching, thank you my friend.

[ced]

Thanks for that tip Pinal! Most people, myself included, probably use the select count (*) because it is easy to remember. I would convert the query into a stored procedure with a short name and parameter for table name.

[mandarhadke]

Pinal sir, you are really doing a fabulous job by sharing your valuable knowledge in just 60 seconds.
Thank you for all your efforts. We are learning a lot from you.

Keep Posting! 😊

[jorgeconcheyro]

Great video Pinal, informative, and fun!!! Cheers from Buenos Aires...

[MrAtulvats]

As i am GUI friendly so i use right click on table and properties and storage.... There you will find the row count .... 😊

[TonVerkleijT]

I think there is a much quicker way:

#to limit the amount of output or create a top n rows put the next line above the query:
set rowcount n

SELECT name, count(*) as aantal FROM sysobjects
WHERE type = 'U' AND name not like '%sys%' # just to be sure
-- optional WHERE name = '<whatever>'
GROUP BY name
ORDER BY aantal
DESC

No inner joins needed, no expensive SUM calculations.

[pragashj]

Please make a video for execution plan ?

[shreyaskupsad]

Great series, feels good to get enlightened in such little time.
Will these concepts/tricks hold good for Oracle SQL too? If not for all, please mention in the videos whether or not they apply for Oracle..it helps! Thanks

[saikrishnareddy]

Hi,

To find the row count of a view, is there any query apart from
select count(*) from viewname

[richeek]

Great series. Could you tell if these videos are applicable to MySQL with innodb as server?

[Kirl]

Hi, Pinal. Thanks a bunch for your videos! Do you consider it a good practice to put reserved words in square brackets? (in this script you have the [name] word bracketed while [type_desc] not? I sometimes see that in other scripts on the web.

[krishnaveer]

Can I use in sp also. What about execution plan

[robbybankston]

Curious if there is every any delay with updating the partition stats where they are not up to date.

[naveenchnr]

I think query won't give accurate count of records as we are reading the data from stats system table unless we do collect statistics.
If I need accurate count, I have to go with count (*) only right?

[vijayasrisuryadevara]

Good tip, thank you. But will the metadata table automatically gets updated every time it undergo changes? And, should we need to configure to capture such metadata?

[LuigiZambetti]

Pinal, what about a playlist on PL/SQL for whom has to work with Oracle databases?

[syamsada]

I think we can also use sp_spaceused ‘tablename’ to get row count for a single table very fast.

[gregloeffler]

Question on this and how it relates to large rowcount changes to a table. I have a table showing over 255M rows using the query from this post, but in reality a select count(1) and using the rows column from sys.dm_db_stats_properties (assuming a fullscan update was done) show the table has about 46k. This DB is a clone of another DB where only certain rows were inserted into it, leaving a very large difference between the query here and the actual rowcount. I can get this query to update only when I rebuild the clustered index on the table. Is an index rebuild the only thing that can change values returned by this query? Curious if we can affect how often SQL updates these system tables so we can avoid using select count(1), but still get an accurate count.

[sreedharasameerkumarilapav]

Sir.. how about using sum(1) to get row count. Does this work as same as count(*) & count(1).
Please explain if you get time