SQL Server interview questions: -How to delete duplicate records which does not have primary key?

Let me first explain what this question is all about. Let’s say you have table which has names as shown in the below figure. Now in the names table you have duplicate records (ex. Shiv) and this table does not have a primary key.

So the question is how can we delete duplicate records and keep one of the records from the duplicates. For example from the below table how can we delete one “Shiv” and keep the other one.

name's

n 1

Now there are lots of ways of doing and the best I found personally was by using “Row_Number” and “CTE” (common table expression). In case you are not aware of CTE and row_number please refer(What is CTE ) SQL Server interview question and (What is row_number ?) SQL Server interview question.


It’s a 2 step process:-

• Create a temp result set using CTE which has a new column which uses “row_number” using partition.
Now the “row_number” with partition will create unique numbers for unique records with same. In case the records are same it will increment the number. For instance you can see for duplicate Shiv record, he has numbered them 1 and 2.But for “Raju” and “Shyam” he has created fresh number sequence.

ct2

 • Once the CTE is created delete the records whose row sequence number is greater than one.

n 2

RN

Below is the complete code snippet for the same.

where

Here are some SQL Server interview question around database design taken from the book SQL Server interview question by Shivprasadkoirala.

Do visit us for more SQL Server interview questions

Advertisements

About c# and .NET Interview questions

This blog is for developers who want to crack .NET and C# interviews. It has all tips and tricks needed to crack .NET interviews , C# interview , SQL Server interview , Java interview , WCF Interview , Silverlight interview , WPF interview , LINQ interview , Entity framework Interview. Do not forget to watch our Learn step by step video series. Learn MVC in 16 hours:- https://www.youtube.com/watch?v=Lp7nSImO5vk Learn AngularJS Step by Step:- https://www.youtube.com/watch?v=0kmdjqgO9IY Learn Design Pattern in 8 hours:- https://www.youtube.com/watch?v=YDobmucohqk Learn C# and .NET in 60 days:- https://www.youtube.com/watch?v=yh2SrzCkNQA Learn MSBI in 32 hours:- https://www.youtube.com/watch?v=mGPJx3ocFgg Learn SharePoint Step by Step in 8 hours:- https://youtu.be/C2fW76SwJNU
This entry was posted in Uncategorized and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s