TSQL Count function Three ways to describe

Count common there are three kinds of notation, count (*), count (expression), count (column_name)

COUNT (expression), count (column_name) is calculated by calculating expression, or whether the value of column_name in the table is NULL, if NULL is not counted, and if not null.

Count (*), which returns the number of rows in the table. Specifies that all rows should is counted to return the total number of rows in a table, even if there is null or duplicate value, is also counted.

If the value of expression is not null,count (expression) and count (*), the result returned is the same.

Sample code

1. Create Sample Data

Create Table  int)insert into dbo.testvalues(1), (  2), (null)

2, test count (expression)

DECLARE @var int = NULL Select Count (@var from Dbo.test

The result returned is 0, because expression is the Null,count function that does not count null values.

3, test count (0), COUNT (*)

Select Count (*from  dbo.testSelectcount(0from Dbo.test

Result Analysis: COUNT (*) calculates the number of rows in the table, not excluding null values or duplicate values. Because 0 is a non-null value, COUNT (0) and COUNT (*) execute the same result.

4, test count (column_name)

Select Count   from Dbo.test

Result analysis: The ID value is taken out of the table, if NULL, the count is not counted, if not null.

Reference Document

Http://www.cnblogs.com/CareySon/p/DifferenceBetweenCountStarAndCount1.html

Https://technet.microsoft.com/en-us/library/ms175997.aspx

TSQL Count function Three ways to describe