Please refer the article Remove duplicate records/data from Sql Server database table http://www.webcodeexpert.com/2013/11/how-to-remove-duplicate-recordsdata.html

CREATE TABLE dbo.duplicateTest

(

[ID] [int] ,

[FirstName] [varchar](25),

[LastName] [varchar](25)

) ON [PRIMARY]

go
select * from duplicatetest
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')

INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White')

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')

WITH A(rowid,ID,FirstName,LastName) AS

(

SELECT ROW_NUMBER() OVER (ORDER BY id ASC) AS ROWID, * FROM duplicatetest

)

Delete A from A inner join A as B ON( A.rowid <> B.rowid and A.ID=A.ID

and A.FirstName=B.FirstName

and A.LastName=B.LastName

AND A.ROWID < B.ROWID

)

select * from duplicatetest