From the above records inserted we want to delete the duplicate records, other than identity column everything needs to be unique, so we are going to partition the table in row_number over the columns other an identityor primary key

SELECT'ACTUAL RECORDS'

SELECT*FROM EMPLOYEE

;WITH EMP AS

(

SELECT*,ROW_NUMBER()

OVER(PARTITIONBYFIRSTNAME,

DESIGNATION,

PHONE,

LASTNAME,

DEPARTMENTID

ORDERBY DEPARTMENTID)AS ROWNUMBER

FROM EMPLOYEE

)

DELETEFROM EMP WHERE ROWNUMBER > 1

SELECT'AFTER DELETE DUPLICATE RECORDS'

SELECT*FROM EMPLOYEE

From this post you can learn how to delete the duplicate records from the Table