Deleting Duplicates without a Primary Key - SQL Server

Overview¶

This article will explain how to delete duplicate rows from a table that has no primary key

Setup¶

if object_id('dbo.Person') is not null drop table dbo.Person
go
create table dbo.Person (
     FirstName varchar(50) not null
    ,LastName varchar(50) not null
)

insert into dbo.Person (FirstName, LastName) values ('Patrick','Henry'),('Patrick','Henry'),('George','Washington'),
('Thomas','Jefferson'),('John','Hancock') ,('Thomas','Jefferson')

select * from dbo.Person

Deleting Duplicates¶

;with cte as (
    select 
         FirstName
        ,LastName
        ,RowNum = row_number() over (partition by FirstName, LastName order by FirstName)
    from 
        dbo.Person
    )
delete cte 
where  RowNum > 1

select * from dbo.Person