Saturday, January 18, 2014

Various way of finding duplicate rows in SQL Server

Recently One of the friend ask how we can find duplicate rows in SQL server so I said there are various way of finding duplicates and then I show him few ways of findings duplicate based on criteria. I thought it is a very basic question and still lots of people does not know how to find duplicate rows in SQL Server. So it is good idea to write blog post about it. In this blog post I’m going to explain the same various way of finding duplicates.

First all, We need a table to find duplicate rows. So following is a table for that.I have created Employee table with four fields.

CREATE TABLE [dbo].[Employee](
    [Id] [int] NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Designation] [nvarchar](50) NULL

Now once you create this “Employee” table now it’s time to write insert data. I have inserted following data.


Finding duplicate rows in SQL Server:

Now let’s first write query to find duplicate rows in “Employee” table. Following is a query for that.

     Count(*) as 'Number of Rows'
FROM Employee 
GROUP BY Id,FirstName,LastName,Designation HAVING COUNT(*) > 1

Once you run this query following result will be there.


If you can see the data of “Employee” table clearly then you will see that there are two people with first name“Tushar” and “Jalpesh”. Let’s find that via following queries.

     Count(*) as 'Number of Rows'
FROM Employee 
GROUP BY FirstName HAVING COUNT(FirstName) > 1

Now when you run this query it will load following result.


That’s it. You can see its very easy to find duplicate rows in SQL Server. Hope you like it. Stay tuned for more.


Post a Comment

Your feedback is very important to me. Please provide your feedback via putting comments.

Support this blog-Buy me a coffee

Buy me a coffeeBuy me a coffee
Search This Blog
Subscribe to my blog


My Mvp Profile
Follow us on facebook
Blog Archive
Total Pageviews