SQL Interview Question - Difference between Count(*), Count(1), Count(colname) | Which is fastest

This tutorial discusses the difference between Count(*), count(1) and count(colname) in SQL and explains which of them is the fastest

How to install SQL Server for practice?

Check out the complete list of SQL Query Interview Questions -

Get USD 100 off Coursera Plus Annual Subscription

Get 50% off Skillshare Annual Subscription with code AFF50.
Dates: 11/24 at midnight - 11/28 at midnight

Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization

Data Science Fundamentals with Python and SQL Specialization

IBM Data Science Professional Certificate

Python for Everybody Specialization

Google Data Analytics Professional Certificate

Coursera Plus - Data Science Career Skills

Please do not forget to like, subscribe and share.

For enrolling and enquiries, please contact us at

[LearnatKnowstar]

Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022.
The Coursera Plus membership gets you access to unlimited courses and unlimited certifications!

[jfletcher]

"There is no difference" - effectively demonstrates live that count(1) took 40x longer to execute than count(*)

[marcelositonio]

Great and clear explanation on Count(1), * plus ColumnName. Thanks!

[marcosbarcellos]

Thank you for the explanation. I'm sharing the video with a friend. He wanted to know the syntax difference of COUNT. 😁☺️

[tlozwarlock]

As a 10 year+ SQL DBA and Dev... a difference of a quarter second (0.250) versus a 7 thousandth of a second (0.007) process time in an execution plan means that count(*) for raw row count is indeed faster when you start scaling. Even running against a clustered index, count(1) is going to be marginally slower. count(*) is your winner. Imagine multiple millions of rows with a count(1) with just a PK index. I shudder at the thought.

[saleembasha]

It's like soo coool explanations, you are making everything so easy by perfect usage of all clauses, functions etc., of SQ language

[RajatKumar-rfxi]

Same count questions has been asked in 24th April 2021 in tiger analytics

[karunakaranr]

Nice explanation... Thank you for your time and effort.

[danieljust]

It all depends on the table size and it’s structure and number of CPUs.
On my PC, table with one column and 500k rows count(*) and count(1) take approximately the same time, count(column) takes longer.

Table with 3 columns and 500k rows - count(*) is slightly faster than count(1), count(column) takes double time.

The timing may be different for different version of database. I am sure we would see significant difference in the old versions such as SQL 2008. Count(1), count(*) and count(column) fetched rows differently and this caused the execution variation.

[Shinnra]

Yestarday I had some clases and a question similar to this one was asked to the professor, I understood your answer way better than his, thank you!

[rprasad]

Good Stuff, All are very useful. Plz provide more videos...

[designanddecor]

Thank you very much for the wonderful explanation.

[arpangupta]

count(*) was 40 times faster than count(1) in the example.
this is a big difference when we work in production.
just like hbase and hive

[miguelpetrarca]

I have seen/heard select count(*) from tableA returns count of all rows in table regardless of whether a row has all null or some null values, on the other hard select count(columnA) from tableA returns the number of records that do not have null in columnA. But what if we use a group by? I have seen no info on this. are they equivalent?

[kovalensue]

It's funny that in the Oracle database is count(1) and count(*) exactly the same... They have the same execution plan. 🙂

[joseignaciodiazgonzalez]

wow... the execution performance depends of many many things... on a local database so small as that one table youre working on you will never have a good sample... on remote databases with millions of rows those queries will differ significantly

[praveenshetty]

Hi, Can you make video on long running stored procedures. Daily its running fyn but one day its taking long time so how we can debug it

[madhuchowdarytalluri]

very good explanation, everyone can easily understand.

[gpcgpc]

Thanks for sharing, but I don't see you show Count(colname) cost how many sec.
Did I miss something?

[tableaututorialstips]

Thanks for sharing valueble videos mam :), Liked, shared, subscribed :)