Removing Duplicates using CTE in Sql Server

Introduction

Here's a code snippet which removes the duplicate records,using CTE.Most of the time we are face problems  with duplicate data in database. based on the database design you have to write code to delete duplicate records from table.

To delete duplicate records , there are so many ways.

1.direct query

2.using stored proc

3.CTE.

In this code snippet give a common way of delete duplicates records using CTE.

CodeSnippet

WITH CTE ([col1], cnt)AS(SELECT [col1],ROW_NUMBER() OVER(PARTITION BY [col1] ORDER BY [col1]) AS cnt
FROM table1
)DELETEFROM CTE
WHERE cnt > 1

Copy above script and paste in your database and change col1 as it your table column name.that's all i hope this is help to you all.