Count(*) vs Count(1) - SQL Server

Question

Just wondering if any of you guys use Count(1) over Count(*) and if there is a noticeable difference in performance or if this is just a legacy habit that has been brought forward from days gone past?

(The specific database is SQL Server 2005.)

Answer 1

There’s no major difference between COUNT(1) and COUNT(*) so they are almost treated as equivalent. SQL Server converts COUNT(1) into COUNT(*). Here ‘1’ is nothing but a literal.

Want to learn SQL from basics! Here's the right video for you on SQL provided by Intellipaat:

Although there’s a simple difference between them let me list out for your better understanding:

COUNT(1): this returns the count of all records including null.  

COUNT(*) : this counts the no of rows which are produced by the query.

Note: If you include a literal such as a string in a query or a number, then this literal will be attached or added to every row which is produced by the FROM syntax.

• In general, it’s recommended to use COUNT(*). Because, the database can count rows by accessing the index, which is much faster as compare to accessing the table.
• But if you are using the COUNT(column_name), the database will look for  individual values in the column, since it will not count NULLs. Aggregate functions like SUM and COUNT always ignore NULLs.

Let’s take an example:

In the below example, you can observe that I have created a table and inserted 10000 rows into it.

CREATE TABLEA test1Counts(id int) GO

INSERT INTO test1Counts VALUES ( 100 )

GO 10000

Now, I’ll write SQL statements to fetch a total number of rows from the table. Some of them are written below:

SELECT COUNT(1) FROM dbo.test1Counts

SELECT COUNT(-1) FROM dbo.test1Counts

 SELECT COUNT(*) FROM dbo.test1Counts

 SELECT COUNT(1001) FROM dbo.test1Counts

 SELECT COUNT(10000) FROM dbo.test1Counts

 SELECT COUNT('ABC') FROM dbo.test1Counts

All the above code will give you the same result i.e. 10000 rows. All of them will perform in the same manner.

Output: