SQL Server | Remove Duplicate Records From Table

watch_later 12/26/2018

In this article, I am going to explain how to remove duplicate records from the table and get only unique/distinct Result Sets in SQL server and also show you how you can merge result sets in SQL server using UNION and EXCEPT Operator. In SQL server there are several methods are available for removing duplicate/dummy records/rows such as with the help of the ROW_NUMBER() function and COMMON TABLE EXPRESSION and etc.

What is UNION in SQLServer?

In SQL Server UNION operator is used to merge/combine two or more result sets into a single result set. UNION operator removes the duplicate rows between the various result sets.

Note: Each and Every result sets within the UNION operator must have the same number of columns with similar data types in the result sets.

What is EXCEPT in SQL Server?

In SQL Server EXCEPT operator is used to merge/combine result sets. EXCEPT operator returns rows from the first result sets that are not returned by the second result sets, in short EXCEPT returns only rows, which are not available in the second result sets.

In my previous articles, I showed how to create a stored procedure and how to Split Comma Separated String in SQL Server as well as create and execute parameterized stored procedure and also showed you how to create a dynamic stored procedure in SQL server that you might like to read.

So, Let's Start with an example for demonstration purposes.

First of all, we will create/declare a temporary table and also we will insert some dummy records into the table.

/* Declate temporary table */
DECLARE @Student AS TABLE
(
Id INT,
Name NVARCHAR(50),
Branch NVARCHAR(50),
University NVARCHAR(50)
)
 
/* Insert records in created temporary table */
 
INSERT INTO @Student
         ( Id, Name, Branch, University )
VALUES  ( 1, N'Nikunj Satasiya', N'B.Tech', N'RK University, Rajkot'),
         ( 2, N'Hiren Dobariya', N'B.Tech', N'RK University, Rajkot'),
         ( 5, N'Krupa Patel', N'PHD', N'MS University, Baroda'),
         ( 2, N'Hiren Dobariya', N'B.Tech', N'RK University, Rajkot'),
         ( 3, N'Vivek Ghadiya', N'B.Tech', N'RK University, Rajkot'),
         ( 1, N'Nikunj Satasiya', N'B.Tech', N'RK University, Rajkot'),
         ( 4, N'Shreya Patel', N'M.Tech', N'Nirma University, Ahmedabad'),
         ( 1, N'Nikunj Satasiya', N'B.Tech', N'RK University, Rajkot'),
         ( 3, N'Vivek Ghadiya', N'B.Tech', N'RK University, Rajkot'),
         ( 6, N'Nikunj Ladani', N'B.E', N'Maliba Engineering College,  
Bardoli')

Now, if you execute the created table using a select statement then this table will return 10 records/rows with duplicate records/rows.

SQL Server Remove Duplicate Records
SQL Server Remove Duplicate Records

Now, to remove/delete duplicate rows/records from the result set, use the same table and UNION it with an empty result set returned by the same table, and for that write the following query shown below.

SELECT * FROM  @Student
UNION
SELECT * FROM  @Student WHERE 1=0

If you analyzed then the second part of the above query this statement uses the WHERE condition 1=0 to return the empty result set and this result set is merged with the first part of the UNION operator and in the SQL server UNION operator simply returns the distinct/unique result set and will remove the duplicate records from result sets.

In SQL Server you also can remove/delete duplicate rows/records from the result set, by using EXCEPT operator same as the UNION operator.

SELECT * FROM  @Student
EXCEPT
SELECT * FROM  @Student WHERE 1=0

If you analyzed the above SQL statement the second part of the above query uses where condition something like 1=0 to return the empty result set and this result set will merge/combine with the first part of EXCEPT operator and simply, returns the result set and removing the records/rows of the second result set that is empty and will return distinct records/rows.

Output

SQL Server Remove Duplicate Records From Table Using UNION And EXCEPT Operator
SQL Server Remove Duplicate Records From Table

Summary

This article explains how to remove duplicate records from the table and get only unique/distinct Result Sets in SQL server using UNION and EXCEPT Operator.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

sentiment_satisfied Emoticon