COUNT(*) and COUNT(1): Performance Battle - SQL in Sixty Seconds 176

Count(0)

count ('chocula') 'cereal counting
count ('de Monet') 'It's good to be the king
count ('Dracula') 'Halloween
count ('Vertigo') 'DC comics
count ('Dooku') 'Come to the dark side

Count(1) is more efficient because you need one key press less ([1] vs [shift][8])

How the count(*) and count(1) perform when there is load on server

I always wondered but never knew for sure. Thanks for clearing that up

So, basically what is the difference between them and what is the point of using count(1) if it performs exactly as count(*)?

Count * will take more time when there are many number of rows with large data types. Reason is simple, Data page will be more compared to count 1
Thanks!

I tested it against a dataset that is missing a non clustered index. (Real world scenario for some of us)


select count(*) from logging.log where severity = 'Error'
select count(1) from logging.log where severity = 'Error'


Results 1,729,448

What's interesting is that i got different results from the 2 sample runs.

(1 row affected)
Table 'Log'. Scan count 17, logical reads 4137818, physical reads 11, read-ahead reads 4141000, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)
Table 'Log'. Scan count 17, logical reads 4145935, physical reads 4, read-ahead reads 4141382, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


(1 row affected)
Table 'Log'. Scan count 17, logical reads 4148384, physical reads 1, read-ahead reads 3998292, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)
Table 'Log'. Scan count 17, logical reads 4138450, physical reads 275, read-ahead reads 4084650, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Thanks Pinal, you are the best

You are champion sir! Keep up the good work

count(*) is faster irrespective of clustered or non-clustered index.

difference is only in few milli-seconds.

Note: In this case, count(*) and count(1) execution plan is almost the same.

I tested on two fact tables (20M+ rows) and one dim table (2M+ rows) ...

I'm a big fan of COUNT(DISTINCT 1) myself. ;)

If your table has Clustered index built on TWO columns - count(1) performance will be worse.

iirc you have to go back to SQL 6.5/7.0/2000 era to see the benefit with count(1) vs count(*)

Its always good to watch you, good learning all the time .. Thanks Pinal ..

SELECT 8, how many times have we all done that by mistake?
Thanks again Pinal!

Thanks Pinal, nice approach. I love all your articles there is one question, may be you have already answered

select columns from tablename where column in (@comaseperatorvalues) --> this is not working

select columns from tablename where column in ('1','2','3') --> this is working can i know the correct reason please

We Love You Pinal Dave

Hello Pinal sir. I have a question that if I have deployed some plan guide with MAXDOP 8 and later I changed to value to 1. Cureently I am not seeing any issue on my db but I want to know should I recreate those plan guide with configuration new configuration or not .?
Please advice

How to get records row wise iteration??
Example I have 100 records..
I want to get top 10 rows at each iteration like 1 to 10 ,11 to 20...

Please let me if this is not correct forum to ask..

Hello Sir

I want to learn about Statistics, load, memory. Sql Standards. Query I can write but i am lacking with this basic.

From where I can start

Nice :)

Hi Pinal. Thanks for this video. I have an interesting question. Does the database instance have a unique identifier that can be checked with a query?

It depends on sql server version.

A long time ago it did matter (like oracle 6 maybe 7), all RDBS I know of today (and year) have optimised count(*) to do the same thing. look at explain plans

Hi Pinal If there are n number of nested loop joins in the execution plan of the query what are the ways to optimize it and make it run quickly ?

count(id) -- counts rows where column 'id' is not null

When i tried count(*) and count(1) on Production.Product table in AdventureWorks2012 database count(1) performed better that count(*)
Count(*):Physical reads 1,CPU time = 16 ms, elapsed time = 8 ms.
Count(1):Physical reads 0, CPU time = 0 ms, elapsed time = 0 ms.

Thank you for making this video.I recently subscribed to your channel. All the videos are very helpful.

I would have thought that more columns in the table would mean more pages read. Instead, MS docs says

COUNT(*) does not require an expression parameter because by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.

Hi Sir, if we are not referring/using cluster index anywhere in any query can drop cluster index on that table