In SQL Server, when dealing with a significant quantity of tables containing numerous rows, we can implement the COUNT() function to tally the rows in the table. However, a common inquiry arises regarding when to employ COUNT(*) and COUNT(1), as both functions yield identical outcomes, yet there is a distinction in their internal execution. In this article, let’s explore the divergence between them and the appropriate instances to use the COUNT() function.

Table of Contents:

• Reasons for Using COUNT(*) and COUNT(1) in SQL Server
• COUNT(*) Function in SQL Server
• COUNT(1) Function in SQL Server
• Performance and Recommended Practices
• Key Distinctions Between COUNT(*) and COUNT(1)
• Practical Examples
  • For COUNT(*)
  • Using COUNT(1)
• Conclusion

Reasons for Using COUNT(*) and COUNT(1) in SQL Server

In SQL Server, both COUNT(*) and COUNT(1) serve the purpose of tallying the number of rows within a table. This proves to be extremely beneficial when analyzing data, generating reports, or undertaking optimizations. Such functions are particularly advantageous in real-world applications such as inventory management, libraries, transaction tracking, system log monitoring, customer feedback, and much more. Both COUNT(*) and COUNT(1) manage NULL values efficiently, with COUNT(1) specifically utilizing a constant expression of 1 to count the rows. These factors contribute to enhanced application performance.

Let’s create a dataset to illustrate the use of 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 the appearance of the table after its creation and the insertion of values.

COUNT(*) Function in SQL Server

The COUNT(*) function in SQL Server is designed to count all rows within a table. This function counts NULL values as well since it does not examine the column values, instead only tallying those present in the table.

Syntax:

SELECT COUNT(*) FROM TableName;

Example:

SELECT COUNT(*) AS TotalVehicles FROM VehicleRegistrations;

Output:

Explanation: The COUNT(*) AS TotalVehicles query counts every row in the table. Despite the presence of a null value, it does not impact the overall row count.

COUNT(1) Function in SQL Server

The COUNT(1) function shares similarities with the COUNT(*) function; however, it counts rows using the constant value (1). While COUNT(1) was more common historically, both functions currently perform identically in online compilers.

Syntax:

SELECT COUNT(1) FROM TableName;

Example:

SELECT COUNT(1) AS TotalVehicles FROM VehicleRegistrations;

Output:

Explanation: The COUNT(1) function takes all rows into account. Being a constant expression, 1 is a non-null value, and thus the presence of null does not influence row counting.

Performance and Recommended Practices

1. SQL Server treats COUNT(*) and COUNT(1) as functionally equivalent.
2. If a primary key is established in the table, COUNT(1) will undergo optimization, counting rows based on the indexed values.
3. Generally, the COUNT(*) function is favored because it counts rows precisely and effectively.
4. The optimization mechanisms for these functions vary, as they employ different strategies in relation to indexing, which can substantially improve overall query performance.

Key Distinctions Between COUNT(*) and COUNT(1)

Differences	COUNT(*)	COUNT(1)
Row Counting	The COUNT(*) function tallies all rows, including NULLs.	The COUNT(1) function counts rows based on the constant value (1).
Column Dependency	It does not depend on any specific column.	It explicitly uses the constant expression (1) for row counting.
Execution Plan	Highly optimized for use in SQL Server.	Highly effectively optimized for SQL Server.
Index Utilization	In earlier versions, COUNT(*) did not directly utilize the index. However, in modern versions COUNT(*) operates efficiently with indexes.	The COUNT(1) function leverages the indexed column for execution planning.
Readability	It demonstrates superior readability as it effectively counts rows.	Some developers find it less efficient compared to COUNT(1).

Practical Examples

For COUNT(*)

Case 1: Counting Inventory Stocks.

Example:

---Create table 

CREATE TABLE Inventory (

    ProductID```html
 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(*) command retrieved the total number of items that are in stock within the inventory. 

Case 2: Counting the quantity of outstanding orders on 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 tallied the number of pending orders by applying the WHERE OrderStatus = ‘Pending’; condition to identify the pending order information. 

Utilizing COUNT(1)

Case 3: To obtain the count of customer feedback received by 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 calculated all the entries within the feedback table. 

Case 4: To count the IOT sensors present in a smart home structure. 

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 structure. 

Conclusion

In SQL Server, the COUNT() and COUNT(1) share the same capability of tallying rows. However, they hold distinctions in execution. COUNT(*) counts all rows within a table, while COUNT(1) counts rows associated with the constant expression 1. Both overlook the NULL values, as they strictly count the rows in a table rather than the column values within it. Their optimization and performance remain similar, with no considerable disparity between them. Count(*) tends to be preferred for its enhanced readability and straightforwardness. Grasping these distinctions can enrich a developer’s understanding. 

To gain deeper insights into SQL functions, explore this SQL course and also review SQL Interview Questions curated by industry professionals.

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

The post Count(*) vs Count(1) in SQL Server emerged first on Intellipaat Blog.

“`