How to Find duplicate records in a SQL Server Table

Duplicate records cost money, time, and space to maintain. Learn how to use the GROUP BY and HAVING clauses in SQL to identify and remove duplicate values.

Duplicates in table data can be displayed using the GROUP BY and HAVING clauses. With the help of some functions, the GROUP BY command in SQL is used to group similar data elements together. The rows in question will be grouped together if a certain column contains the same values in several rows.

We'll show you how to identify duplicate values in a single column in this example. In this instance,

USE SampleDB

CREATE TABLE dbo.CheckTB

( ID INT, 

FirstName VARCHAR(100),

LastName VARCHAR(100),

Age INT)

GO

INSERT INTO dbo.CheckTB

 VALUES(1,'Anishur','Rehman',34)

,(1,'Anishur','Rehman',34)

,(2,'Raza','M',32)

,(3,'Vikas','Raja',27)

,(4,'Vikas','Raja',28)

SELECT * FROM  dbo.CheckTB

--Find duplicate records by using Group by:

SELECT FirstName,

            LastName,

            COUNT(*) AS RecordCnt

    FROM   dbo.CheckTB

    GROUP  BY FirstName,

            LastName

    HAVING COUNT(*) > 1

 

Results

 

FirstName	LastName	RecordCnt
Anishur	Rehman	2
Vikas	Raja	2