How to Find Duplicate Rows in a Table in SQL Server?

Duplicate Rows in a table can be very irritating and cause for many headaches. So how can you identify duplicate rows in a table?  SQL Server provides no visual tool or a built in function to show you the duplicate rows in a table, so you need to write your own query to get the duplicate rows.

Here is a Query which should give you a list of duplicate rows in a table. Please remember to replace the columnName and TableName with appropriate values in the SQL Queries below.

 

--How to Find Duplicate Rows

SELECT DISTINCT columnName FROM TableName

GROUP BY columnName

HAVING count(columnName) > 1

ORDER BY columnName

 

And here is another Query to do the same thing.

 

SELECT columnName

FROM TableName

WHERE columnName in

(SELECT columnName FROM TableName

GROUP BY columnName

HAVING count(columnName)>1)

ORDER BY columnName