Find Duplicate Record in Sql Server

Introduction

In this article i will explain how to find or count duplicate record in SQL Server.

In this example i have a datatable and this table doesn't contain any primary key because of that duplicate records inserted in table.

First i will create table without contain any primary key

Query

CREATE TABLE [dbo].[Emp_Info]

(

      [Emp_Id] [int] NULL,

      [Name] [varchar](50) NULL,

      [Salary] [int] NULL,

      [City] [varchar](50) NULL

)

Now insert some duplicate record in this table.

Now count how many duplicate record exits in datatable with the help of below query

Query

select Emp_Id, Name, Count(*) as DuplicateRecord

From Emp_Info

Group By Emp_Id, Name, City

Having Count(*) >1 Order By Emp_Id