SQL SERVER – Difference between COUNT(DISTINCT) vs COUNT(ALL)

http://blog.sqlauthority.com/2011/03/08/sql-server-difference-between-countdistinct-vs-countall/

COUNT(DISTINCT)和COUNT(ALL)的区别：前者像select distinct 字段1,字段2 from table一样会去掉重复的，后者则是统计字段的所有数据条数，与Count(字段1)相同。
COUNT(ALL)中的ALL实际上是缺省项，不需要指定。
(注：本人还有点怀疑COUNT(ALL)与COUNT(字段1)是否有区别，待验证)。

Here is the script:

SELECT COUNT([Title]) Value
FROM [AdventureWorks].[Person].[Contact]
GO
SELECT COUNT(ALL [Title]) ALLValue
FROM [AdventureWorks].[Person].[Contact]
GO
SELECT COUNT(DISTINCT [Title]) DistinctValue
FROM [AdventureWorks].[Person].[Contact]
GO

注：select count(字段1) from table，当字段1若有为null的记录，则此记录不在count结果内，要注意。

SELECT COUNT(*) FROM table
WHERE Column1 IS NULL.

select sum(any_null_data_count) from
(
select case when [any_null_data] is null then 1 else 0 end as any_null_data_count FROM [AdventureWorks].[Person].[Contact] where [any_null_data] is null
) h1