Delete duplicate entries from a data store while leaving a single copy

Introduction

The code explained here will show how to delete duplicate entries from a data store, while leaving a single copy. The code will first create a temp table with duplicated records for the field 'FullName' and then get the IDs of the record which must be deleted and then delete those records.

Using the code

Here is the complete SQL code:

           --Create Temp Table
IF OBJECT_ID('TempDup') IS NOT NULL
DROP TABLE 'TempDup'
GO
CREATE TABLE [dbo].[TempDup]
(
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_TempDup_ID] DEFAULT (newid()),
    [FullName] [nchar](10) NOT NULL,
    CONSTRAINT [PK_TempDup] PRIMARY KEY CLUSTERED ( [ID] ASC )ON [PRIMARY]
) ON [PRIMARY]
GO
 INSERT INTO TempDup VALUES   ( NEWID(), 'N1')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N2')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N2')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N2')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N3')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N3')
-- This code will select the Duplicate row only and keep single copy from row
SELECT [ID], [FullName], [RowIndex]
FROM
(
    SELECT 
        [ID], [FullName], RANK() OVER (PARTITION BY [FullName] 
        ORDER BY [ID] ASC) AS [RowIndex]
    FROM [dbo].[TempDup]
)[T1]
WHERE [T1].[RowIndex] > 1
GO
-- If replace SELECT SQL command with DELETE SQL command
DELETE FROM [dbo].[TempDup] WHERE [ID] IN
(
    SELECT [ID] FROM
    (
        SELECT 
        [ID], [FullName],
         RANK() OVER (PARTITION BY [FullName] ORDER BY [ID] ASC) AS [RowIndex]
        FROM [dbo].[TempDup]
    )[T1] WHERE [T1].[RowIndex] > 1
)
GO