Count duplicate records or rows in SQL Server

To count all the duplicate records in a column of the table use this code:

SELECT Column_name, COUNT(*) Count_Duplicate
FROM Table_name
GROUP BY Column_name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

To count all the duplicate records in two columns of the table:

SELECT Column1, Column2, COUNT(*) Count_Duplicate
FROM Table_name
GROUP BY Column1, Column2
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

To count all the duplicate records in all columns of the table:

SELECT , COUNT(*) Count_Duplicate
FROM Table_name
GROUP BY HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

To see how to delete all the duplicate records in one query using CTE read article:
Delete Duplicate Rows in SQL Server 2005 and 2008 using CTE