The difference and execution method of select count() and select count(1)

Count(*) or Count(1) or Count([column]) are perhaps the most commonly used aggregate functions in SQL Server. In fact, many people cannot distinguish between these three. This article will explain the functions, relationships and principles behind these three.

I often see some so-called optimization suggestions in the past that use Count(1) instead of Count(*) to improve performance. The reason given is that Count(*) will result in a full table scan. It actually makes no difference how you write Count.

Count(1) and Count(*) actually mean that the expression in Count() is evaluated to see if it is NULL. If it is NULL, it is not counted, but if it is not NULL, it is counted. For example, as shown in Code 1, NULL is specified in Count (the optimizer does not allow NULL to be explicitly specified, so it needs to be assigned to a variable to specify it).

DECLARE @xx INT
SET @xx=NULL
SELECT COUNT(@xx) FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]

Code Listing 1. NULL is specified in Count. Since all rows are NULL, the result is not counted at all. Obviously, the result is 0.

So when you specify Count(*) or Count(1) or Count('anything') the result will be the same because none of these values ​​are NULL, as shown in the following figure.

As long as you specify a non-NULL expression in Count, the result will not change.

If we only look at the results, Select Count(*) and Select Count(1) return the same results.

If the table does not have a primary key, then count(1) is faster than count(*). If the table has a primary key, then count(primary key) is fastest when the primary key is used as the count condition.

If your table has only one field, then count(*) is the fastest.

The results of count(*) and count(1) are the same, both include NULL statistics, while count(column) does not include NULL statistics.

1. The difference between select 1 and select *

select constant from ... For all rows, there is always only one value returned, the constant. So normally it is only used to determine whether it exists or not (such as the exists clause). Select * from ... returns all columns of all rows.

The difference in performance depends on your from and where clauses. For example, if your where condition can be indexed, then select 1 from ... will obviously perform better than select * from ...

2. Use of select sum(1)

select count(*) returns the number of records that meet the conditions, which is the same as select sum(1).

However, sum() can be passed any number, including negative numbers and floating-point numbers, and the returned value is the passed value n * the number of records that meet the conditions m.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links