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

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)