SQL- Checking Duplicate Records in a Table

Q. How to check duplicate records in a table using SQL Server ?

        Suppose we have a table without a primary key. We want to find whether duplicate records are present in the table or not. For this scenario, we have to select all columns name and count from a table and group it by all columns name in a table and then filter based on count greater than 1. Lets see the below query.

 SELECT OrderNo,Customer,Amount,Location,Item,COUNT(*)AS TotCount 
 FROM OrderDetails 
 GROUP BY OrderNo,Customer,Amount,Location,Item
HAVING COUNT(*)>1

We see that records which are marked with red is the duplicate record. Lets find out with the help of query.

Suppose we have a scenario where we need to find all the records based on the duplicate value present in a column. Lets assume we have to find all the records from the table where column  'Location' has duplicate value.

SELECT OD.* FROM OrderDetails OD 
INNER JOIN 
(SELECT Location
FROM OrderDetails
GROUP BY Location
HAVING COUNT(*)>1)A
 ON A.Location=OD.Location

Thus we see that all the records related to duplicates present in the column 'Location' has been filtered out.