In SQL Server, the comparison of Count (*), Count (Col), Count (1)

Source: Internet
Author: User

Let's take a look at the description of Count (*) and Count (col) in Bol:

COUNT(*Returns the number of items in the group. IncludingNULLvalues and duplicates. COUNT( Allexpression) evaluates expression for each row in the group and returns the number of non-null values . Expression in addition totext、ImageOrntextAn expression of any type other than. The use of aggregate functions and subqueries is not allowed. *Specifies that all rows should be computed to return the total number of rows in the table. COUNT(*) does not require any parameters and cannot beDISTINCTused together. COUNT(*The expression parameter is not required because, by definition, the function does not use information about any particular column. COUNT(*Returns the number of rows in the specified table without deleting the copy. It counts the rows individually. Include rows that contain null values.

Count (*), COUNT (1), Count (col) tests compare the execution plans of various queries as summarized below

Count The efficiency of (*) and COUNT (1) execution is exactly the same. the execution efficiency of count(*) is higher than count (COL), so do not use count (col) when you can use COUNT (*). the execution efficiency of count(COL) is higher than the count (distinct  col), but this conclusion is not very significant, and both methods are also seen to be used. If you are doing count on a particular column, the nonclustered index that establishes the column can be of great help to count. If you frequently count (*), you can find a minimum col to create a nonclustered index to avoid full table scanning and affect overall performance.

The focus of this article is to remind yourself that the count (*) return result may be inconsistent with the count (col) results, especially the Col index, where null may not be indexed and should be noted later.

In SQL Server, the comparison of Count (*), Count (Col), Count (1)

Related Article

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.