TSQL - How to Find duplicate records in a SQL Server Table

A very common question asked to TSQL Developers is "How will you find duplicate records in a table?" and also as developer we encounter this situation all the time. Here are couple of ways to find the duplicate records in a SQL Server table.

In below example, we want to find all the record which are duplicate by FirstName and LastName.

USE TestDB

CREATE TABLE dbo.Customer( ID INT, FirstName VARCHAR(100),LastName VARCHAR(100),Age INT)

GO

INSERT INTO dbo.Customer

 VALUES(1,'Aamir','Shahzad',34)
,(1,'Aamir','Shahzad',34)
,(2,'Raza','M',32)
,(3,'Sukhjeet','Singh',27)
,(4,'Sukhjeet','Singh',28)

SELECT * FROM  dbo.Customer

Find duplicate records by using Group by:

SELECT FirstName,
            LastName,
            COUNT(*) AS RecordCnt
    FROM   dbo.Customer
    GROUP  BY FirstName,
            LastName
    HAVING COUNT(*) > 1

Find duplicate records by using Common Table Expressions(CTE)

;WITH CTE
            AS (
    SELECT FirstName,
                LastName,
                Row_number()
                OVER (
                    PARTITION BY FirstName, LastName
            ORDER BY (
            SELECT 1) ) AS Rn
        FROM   dbo.Customer)
       SELECT *
    FROM   CTE
    WHERE  Rn > 1