A discussion on the notation of count in SQL

When we query the number of data in a table, someone likes to use COUNT (*), someone uses count (1), and some friends use count (primary key), but these kinds of usage will be better? Below is the Shenyang 463 Plastic Surgery Hospital Program in CSDN above to see an article, excerpt as follows:

The notation for count is roughly the following:
COUNT (*)
COUNT (1)
COUNT (primary key)
COUNT (column name)

I'll write a small example:

Use TEMPDB
GO
IF object_id (' TB ') is not NULL DROP TABLE TB
GO
CREATE TABLE TB (COL1 int,col2 int,col3 int,col4 INT)
GO
CREATE CLUSTERED INDEX inx_tb_col1_col2 on TB (col1,col2)
GO
CREATE INDEX inx_tb_col3 on TB (COL3)
GO
INSERT into TB
SELECT T1.number,t2.number,case when t1.number%3=0 and then NULL ELSE t1.number end,t1. Number+t2.number
From MASTER. Spt_values T1
INNER JOIN MASTER: Spt_values T2 on T1.number=t2.number-1
GO
SELECT COUNT (*)
From TB

SELECT COUNT (1)
From TB

SELECT COUNT (COL1)
From TB

SELECT COUNT (COL2)
From TB

SELECT COUNT (COL3)
From TB

SELECT COUNT (COL4)
From TB

SELECT COUNT (DISTINCT COL3)
From TB

SELECT COUNT (1),
COUNT (COL3),
COUNT (DISTINCT COL3),
COUNT (COL4),
COUNT (DISTINCT COL4)
From TB

Run the Select one by one to see the execution plan, and you'll see that the first five execution plans look the same, all inx_tb_col3, because the system determines that the index size of the COL3 is less than the Col1+col2 clustered index, and COL3 contains all the columns that are required for the first five statements.

But when you see the first step in the Index_scan, you will find that count (*) and COUNT (1) are not output objects, that is, false output, only the output returns the number of rows, do not output a specific column. With Count (COL1), Count (COL2), COUNT (COL3) has a list, respectively, of the three columns. This is because the system calculates how many non-null values are in the three columns, and COUNT (*) and COUNT (1) all have a row, whether null or not.

Count (COL4) goes inx_tb_col1_col2, that is, the clustered index is scanned, because COL3 contains only the key column COL1, COL2, and the Value column COL3, so count (COL4) cannot walk COL3, only the clustered index scan, which is the full table scan.

The following count (DISTINCT COL3) is for you to compare the plan, the so-called DISTINCT, is the output of the COL3 and then group by again calculate the number of rows.

The last sentence is a demonstration of count usage, with no other meaning.


http://www.hengnaya.com/concludes:
count (1) and COUNT (*) are the same , you can write any constant in it, such as count (' CSDNDSB '), does not output a column constant, does not go to the system table to find all the column names.

count (1) and Count (column name) have different meanings and cannot be compared together . The only comparison is that the column name is the primary key column of the single primary key table, which is the clustered non-empty single-key index, in which case the count (1) is better than the count (column name) unless you want to force a clustered index scan, because the former allows the plan to select the most evaluated index, and there is no column output.

A discussion on the notation of count in SQL