How to Delete Duplicate Records from an SQL Server Table
March 09, 2010
Deleting duplicate records from an SQL Server table can sometime become a very tedious activity especially in scenarios when you don’t have a Primary Key defined for the source table. In this article we will take advantage of Common Table Expression (CTE) feature of SQL Server 2005 to delete duplicate records from the table.
What is Common Table Expression (CTE)?
A common table expression (CTE) can be considered as a temporary result set which is defined within the execution scope of a single INSERT, UPDATE, SELECT OR DELETE statements. It is much similar to a derived table. However, a CTE can be self referenced multiple times within the scope of the same query.
Deleting Duplicate Records Using Common Table Expression (CTE) Feature of SQL Server 2005
Let us go through the below mentioned example to remove duplicates from a table which doesn’t have a Primary Key defined.
/* Drop EmployeeDetails Table if already exists */
IF OBJECT_ID (N'dbo.EmployeeDetails', N'U') IS NOT NULL
DROP TABLE dbo.EmployeeDetails;
/* Create EmployeeDetails Details Table*/
CREATE TABLE EmployeeDetails
EmpID INT IDENTITY (1,1),
/* Insert Script is using Row Value Constructor Feature of SQL
INSERT INTO EmployeeDetails
/* Using Common Table Expression Feature to Delete Duplicate
SELECT ROW_NUMBER () OVER ( PARTITION BY NAME, AGE ORDER BY EmpID) AS RNUM
SampleCTE WHERE RNUM
/* Employee Details without Duplicates */
SELECT * FROM EmployeeDetails
The below mentioned snippet shows that the duplicate records are removed successfully.
In this article you have seen how easily you can remove duplicate records from a table using the Common Table Expression (CTE) feature of SQL Server 2005.
Continue Free Learning...
Geeks who read this article also read…
- Installing SQL Server 2008 R2 on Windows Server 2008 R2
- How to Get Exclusive Access to SQL Server Database
- Understanding SQL Server Recovery Models
- Changing Default SQL Server Backup Folder in SQL Server 2008
- Changing the default location of SQL Server Data and Log files
- Enforce Password Policies and Password Expiration for SQL Server Logins
- Auditing SQL Server Logins
- New Date and Time Data Types in SQL Server 2008
- Date and Time Functions in SQL Server 2008
- Using Transparent Data Encryption Feature of SQL Server 2008
- New Features in Microsoft SQL Server 2008 R2
- Configuring Database Instant File Initialization Feature of SQL Server
- Microsoft SQL Server Build Versions
- Database Backup Compression Feature In SQL Server 2008
- Read More SQL Server Articles…