How to find out duplicate records (duplicate data) in a SQL Server table

Records duplication or data redundancy is the common issue we face with SQL Server table. In this article we will find out all the duplicate records (duplicate data) in a SQL Server table. We have to use the group by with having command to get the duplicate records (duplicate data).

Syntex:
SELECT column_Name FROM Table_name
GROUP BY column_Name
HAVING count(column_Name) > 1
 
Example:
SELECT studentName FROM tblStudentDtl
GROUP BY studentName
HAVING count(studentName) > 1

This technique is good while fatching single column, if you want more column to fetch you need to modify it like…

Syntex:
SELECT
<list of all columns>
FROM
tablename
GROUP BY
<list of all columns>
HAVING
count(*) > 1
 
Example:
SELECT studentName, studentAddress FROM tblStudentDtl
GROUP BY studentName, studentAddress
HAVING count(*) > 1

You can get same result in alternate way like…

SELECT sName, sAddress,ClassName FROM tblStudentDtl
WHERE sName IN (SELECT sName FROM tblStudentDtl GROUP BY sName Having Count(sName)>1)
 
SELECT A.CompanyName, A.WebSite, B.CompanyId FROM tblCompanyMaster AS A JOIN tblCompanyID AS B ON A.GlobalCompanyId=B.CompanyId WHERE A.GlobalCompanyId in (SELECT CompanyId FROM tblCompanyID GROUP BY CompanyId Having Count(CompanyId)>1)