In SQL Server, both COUNT(*) and COUNT(1) can be used to count the number of rows in a table or a result set. However, there are some differences between the two, and choosing one over the other can have an impact on query performance.

COUNT(*) counts the total number of rows in a table or result set, regardless of whether the individual rows contain any data or not. This means that even if a row has null values in all its columns, it will still be counted by COUNT(*). This is because the asterisk symbol (*) in COUNT(*) is a wildcard that includes all columns in the table.

On the other hand, COUNT(1) is a shorthand way of counting the rows in a table or result set. It works by counting the number of non-null values in the first column of each row. Since every row in a table or result set will have a value in the first column (even if it is null), COUNT(1) will always return the same result as COUNT(*).

So, what is the difference between the two and which one should you use?

The main difference between COUNT(*) and COUNT(1) is in their query execution plans. When you use COUNT(*), SQL Server has to retrieve all columns of the table or result set to determine the number of rows. This can be slow and resource-intensive, especially if you are working with large tables.

In contrast, COUNT(1) only needs to look at the first column of each row to determine if it is null or not. This can be faster than COUNT(*), as it requires less data to be retrieved from disk.

However, the performance difference between the two may not be significant, especially for small tables or result sets. In such cases, either COUNT(*) or COUNT(1) can be used without any major impact on query performance.

In general, it is recommended to use COUNT(1) over COUNT(*) as it is more efficient and can potentially improve query performance. However, if you need to count the total number of rows in a table or result set, regardless of whether they contain any data or not, then COUNT(*) is the appropriate choice.

In summary, both COUNT(*) and COUNT(1) can be used to count the number of rows in a table or result set in SQL Server. COUNT(*) counts all rows, while COUNT(1) counts the number of non-null values in the first column of each row. COUNT(1) is generally more efficient and should be used in most cases, but COUNT(*) is necessary when you need to count all rows, regardless of their contents.