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

What is the difference between COUNT(DISTINCT) vs COUNT(ALL)?

I found this question from the student very interesting. He seems to have read the documentation (Book Online) and was then asking me this question.

I always carry laptop which has SQL Server installed. I quickly opened it and ran the following script. After looking at the result, I think it was clear to everybody.

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


The above script will give me the following results.

You can clearly notice from the result set that COUNT (ALL ColumnName) is the same as COUNT(ColumnName). The reality is that the "ALL" is actually  the default option and it needs not to be specified. The ALL keyword includes all the non-NULL values.

I know this is very simple and may be it does not change how we work; however looking at the whole angle, I really enjoyed the question.