Count(All) and Count(Distinct) are two aggregate functions in SQL Server.
Count function is a part of the SQL Server's aggregate functions. In aggregates, we consider various types of functions like count, max, avg, min, and sum. In this article, you consider the Count function which is used to count the number of rows in a database table.
Let's have a look at a practical example of how to get the difference between the Count(All) and Count(Distinct) in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Count() Function in SQL Server

The COUNT() function is used to count the number of rows when you use a where condition with the Count function in a select statement. It will return the number of rows from the table satisfying the where condition. NULL values will not be counted by the Count function.
Example
  1. SELECT [EmpID]
  2. ,[EmpName]
  3. ,[EmpSalary]
  4. FROM [master].[dbo].[Employee]
  5. Go
  6. select Count(empname) as CountResult from [Employee]
Output
Count-function-in SQL-Server.jpg

Count(All ColumnName) Function

The COUNT(All ColumnName) function is similar to Count(ColumnName). It also produces the same result as Count(ColumnName).
Example
  1. SELECT [EmpID]
  2. ,[EmpName]
  3. ,[EmpSalary]
  4. FROM [master].[dbo].[Employee]
  5. Go
  6. select Count(All empname) as CountResult from [Employee]
Output
Count-all-function-in SQL-Server.jpg

Count(Distinct ColumnName) Function

When the DISTINCT keyword is used, all duplicate values are eliminated before the function count is applied, or if you want to count the number of unique rows in the table. This form of Count does not count rows with null values for the column.
Example
  1. SELECT [EmpID]
  2. ,[EmpName]
  3. ,[EmpSalary]
  4. FROM [master].[dbo].[Employee]
  5. Go
  6. select Count(Distinct empname) as CountResult from [Employee]
Output
Count-distinct-function-in SQL-Server.jpg