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

In SQL Server, when we have a larger number of tables with many rows, we can use the COUNT() function to count the rows in the table. But there will be a question when to use COUNT(*) and COUNT(1), as both the functions give the same result, but there is a difference in the internal execution. In this article, let’s understand the difference between them and when to use the COUNT() function. 

Table of Contents:

• Why COUNT(*) and COUNT(1) Are Used in SQL Server
• COUNT(*) function in SQL Server
• COUNT(1) function in SQL Server
• Performance and Best Practices
• Key Differences between COUNT(*) vs COUNT(1)
• Real-World Examples
  • For COUNT(*)
  • Using COUNT(1)
• Conclusion

Why COUNT(*) and COUNT(1) Are Used in SQL Server

In SQL Server, COUNT(*) and COUNT(1) are used to count the number of rows in a table. It is very useful when you are analyzing the data, reporting any logs, or performing optimization. This is very helpful in real-world scenarios like inventory management, library, transactions, monitoring system logs, customer feedback, and so on. Both COUNT(*) and COUNT(1) are very efficient with NULL handling and also COUNT(1) uses a constant expression 1 to count the number of rows. These things help the application to work more efficiently. 

Let’s create a dataset to perform COUNT(*) and COUNT(1) functions.

--Creating the VehicleRegistrations Table

CREATE TABLE VehicleRegistrations (

    RegistrationID INT PRIMARY KEY,

    Model VARCHAR(50),

    OwnerName VARCHAR(100),

    RegistrationYear INT

);

----Inserting Sample Data

INSERT INTO VehicleRegistrations (RegistrationID, Model, OwnerName, RegistrationYear) VALUES

(1, 'Toyota Corolla', 'Ravi', 2022),

(2, 'Honda Civic', 'Guru', 2021),

(3, 'Ford Focus', 'Jithin', 2023),

(4, 'Tesla Model 3', 'Kumar', 2022),

(5, 'Nissan Altima', NULL, 2020);

SELECT * FROM VehicleRegistrations;

Output:

This is how the table looks after creating and inserting values. 

COUNT(*) function in SQL Server

The COUNT(*) function in SQL Server is used to count all rows in a table. This will also count the NULL values in a table, as it will not check the column values. It will only count the ones present in the table. 

Syntax:

SELECT COUNT(*) FROM TableName;

Example:

SELECT COUNT(*) AS TotalVehicles FROM VehicleRegistrations;

Output:

Explanation: The COUNT(*) AS TotalVehicles query counts all the rows in a table. There is a null value in the table, but it does not affect the row count.

COUNT(1) function in SQL Server

The COUNT(1) function is similar to the COUNT(*) function, but the COUNT(1) function counts the row using the constant value (1). The COUNT(1) function was used in old times, but now both functions work the same in online compilers. 

Syntax:

SELECT COUNT(1) FROM TableName;

Example:

SELECT COUNT(1) AS TotalVehicles FROM VehicleRegistrations;

Output:

Explanation: The COUNT(1) function considers all the rows. The constant expression 1 is a non-null expression, the null value will not affect the count of rows. 

Performance and Best Practices

1. SQL Server will treat COUNT(*) and COUNT(1) similarly as their functionality is the same. 
2. If there is any primary key in the table, COUNT(1) will be optimized and count rows based on the indexed value. 
3. The COUNT(*) function is preferred in most cases as it counts the rows efficiently and clearly. 
4. The optimization technique between them is different, as their strategy is different when it comes to indexing. By indexing, it can enhance the overall query performance.

Key Differences between COUNT(*) vs COUNT(1)

Real-World Examples

For COUNT(*)

Case 1: Counting the stocks in the inventory. 

Example:

---Create table 

CREATE TABLE Inventory (

    ProductID INT PRIMARY KEY,

    ProductName VARCHAR(100),

    Quantity INT

);

----Insert the values

INSERT INTO Inventory (ProductID, ProductName, Quantity) VALUES

(1, 'Laptop', 50),

(2, 'Phone', 200),

(3, 'Tablet', 100);

SELECT COUNT(*) AS TotalProducts FROM Inventory;

Output

Explanation: The COUNT(*) fetched the count of the stocks that are available in the inventory. 

Case 2: Counting numbers of pending orders in an e-commerce platform.

Example:

---Create table

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    CustomerName VARCHAR(100),

    OrderStatus VARCHAR(20)

);

---Insert the values

INSERT INTO Orders (OrderID, CustomerName, OrderStatus) VALUES

(1, 'Kishore', 'Pending'),

(2, 'Akila', 'Completed'),

(3, 'Baskar', 'Pending');

SELECT COUNT(*) AS PendingOrders FROM Orders WHERE OrderStatus = 'Pending';

Output:

Explanation: The COUNT(*) function fetched the count of pending orders by using the WHERE OrderStatus = ‘Pending’; query to get the pending order details. 

Using COUNT(1) 

Case 3: To get the count of customer feedback received in a company. 

Example:

CREATE TABLE Feedback (

    FeedbackID INT PRIMARY KEY,

    CustomerName VARCHAR(100),

    Comments TEXT

);

INSERT INTO Feedback (FeedbackID, CustomerName, Comments) VALUES

(1, 'Kiran', 'Great service!'),

(2, 'Wendy', 'Average experience.'),

(3, 'Karan', 'Will shop again!');

SELECT COUNT(1) AS TotalFeedback FROM Feedback;

Output:

Explanation: The COUNT(1) function counted all the rows from the feedback table. 

Case 4: To count the IOT sensor that is present in the smart home building. 

Example:

---Create table

CREATE TABLE IoTDevices (

    DeviceID INT PRIMARY KEY,

    DeviceType VARCHAR(50),

    Status VARCHAR(20)

);

---Insert the values

INSERT INTO IoTDevices (DeviceID, DeviceType, Status) VALUES

(1, 'Temperature Sensor', 'Active'),

(2, 'Motion Detector', 'Inactive'),

(3, 'Humidity Sensor', 'Active');

SELECT COUNT(1) AS ActiveSensors FROM IoTDevices WHERE Status = 'Active';

Output:

Explanation: The COUNT(1) function counts all the active sensors in a smart home building. 

Conclusion

In SQL Server, the COUNT() and COUNT(1) have the same functionality of counting rows. But they have differences in execution. As COUNT(*) counts all the rows in a table and COUNT(1) counts rows with constant expression 1. They both neglect the NULLs, as they only count the rows in a table, not the column value in it. Their optimization and efficiency are the same, and there is no significant performance difference between them. However, COUNT(*) is generally preferred due to its better readability and clarity). Understanding these differences will help a developer learn more. 

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

Count(*) vs Count(1) – SQL Server – FAQs