When working with SQL Server, you may have come across the difference between using SELECT * and SELECT COUNT(*). In this article, we will explore this topic and understand why SELECT * throws an error while SELECT COUNT(*) does not.

Let’s start by understanding what each of these statements does:

• SELECT *: This statement retrieves all columns from a table. It expects a table to be specified in the FROM clause. If no table is provided, it will throw an error.
• SELECT COUNT(*): This statement is an aggregate function that counts the number of rows in a table. It does not require a table to be specified and will always return a result, even if the table is empty.

Now, let’s dive into the reasons behind the different behaviors of these statements:

1. SELECT *:
When you use SELECT *, SQL Server expects a table to be present in the FROM clause. It needs a source from which it can extract the result set. If no table is specified, SQL Server throws an error because it cannot retrieve any columns without a table.

2. SELECT COUNT(*):
The COUNT(*) function is designed to count the number of rows in a table. It does not look at the columns or their values. It simply counts the rows. Even if the FROM clause is missing, SQL Server assumes there is one row to count and returns the result as 1.

It’s important to note that COUNT(*) is an aggregate function and expects a constant or a column name as an expression. The * in COUNT(*) is treated as a constant value, not as a column name or a set of column names.

Here’s an example to illustrate this:

DECLARE @cnt INT
SET @cnt = COUNT(*)
SELECT @cnt

In this example, we are assigning the result of COUNT(*) to a variable. Even without specifying a table, SQL Server will return the count as 1.

In conclusion, the difference between SELECT * and SELECT COUNT(*) lies in their functionality and expectations. SELECT * requires a table to retrieve columns, while SELECT COUNT(*) counts the number of rows in a table, regardless of the presence of columns.

Understanding these concepts will help you write more efficient and error-free SQL queries. If you have any further questions or insights, feel free to share them in the comments below.

Thank you for reading!