Differences Between SQL Select count (*) and COUNT (1) and how to perform and SQL performance optimizations

Source: Internet
Author: User

In SQL Server, COUNT (*) or COUNT (1) or count ([column]) may be the most commonly used aggregate function. A lot of people actually have a clear distinction between the three. This article will describe the roles, relationships, and principles behind the three.

As always, I often see some so-called optimizations that don't use COUNT (*) instead of count (1), which can improve performance, given the reason that count (*) brings a full table scan. And actually how to write count is no different.

Count (1) and COUNT (*) actually mean that the evaluation of the expression in count () is NULL, if NULL is not counted, and not null. For example, we look at code 1 and specify NULL in count (the optimizer does not allow explicit null, so it needs to be assigned to a variable to specify).

SET @xx =null
SELECT COUNT (@xx) from [AdventureWorks2012]. [Sales]. [SalesOrderHeader]

Code Listing 1. Null specified in Count

Since all rows are null, the result is not counted as 0, as shown in result 1.

Figure 1. Obviously, the result is 0.

So when you specify count (*) or COUNT (1) or count (' anything '), the result will be the same, because these values are not shown in null,2.

Figure 2. As long as a non-null expression is specified in count, the result is no different

What about the Count column?

For Count (column), the same applies to the above rule, evaluates whether the value of each row in the column is NULL, if NULL is not counted, not NULL is counted. Therefore count (column) calculates the count of columns or combinations of this column that are not empty.

How does the count (*) be executed specifically?

When you mentioned that count () has a non-null value, in SQL Server you only need to find the number of rows that are not NULL in the specific table-that is, all the rows (if the row value is all null, the line does not exist). The simplest way to do this is to find a column that is not NULL, and if the column has an index, the index is used, and of course, for performance, SQL Server chooses the narrowest index to reduce IO.

We are in the ADVENTUREWORKS2012 sample database [person]. Delete all nonclustered indexes on the [Address] table, and on the modifydate this data type is a datetime column, we look at the execution plan, as shown in 3:

Figure 3: An index using the CreateDate

We continue to build an index on the StateProvinceID column, which is an int column that accounts for 4 bytes and is shorter than the previous 8-byte datetime type, so SQL Server chooses the StateProvinceID index. As shown in 4.

Figure 4: A shorter StateProvinceID index is selected

Therefore, if the count (*) on a table is used for a long period of comparison, it can greatly improve performance by considering establishing a single-column index in the shortest column.

Differences Between SQL Select count (*) and COUNT (1) and how to perform and SQL performance optimizations

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.