Eliminating Duplicate Records From a Table

CREATE A TABLE EMPLOYEE WITH SOME DUPLICATE RECORDS AS SHOWN BELOW

ID 2,3 and 4 are repeated Multiple times. Now let's see the different ways to delete the duplicate Records.

First Method:

Using 'Distinct' Keyword copy the distinct records to temporary place, after that delete the total records from the table. Now copy the distinct data from temporary place to the table. Here am storing the data temporarily in 'Temporary Table' as Shown below.

Second Way:

First we need to add an Identity Column to the Table. Use Rank Function with partition By EMPID and NAME.  And now if the Rank > 1 that means it's a duplicate record, So delete those records. Once the Operation is completed remove identity column from the table.

Third Way:

Using Co-related Sub-Query. In this case also we need Identity Column.

Fourth Way:

If you want to delete all the Rows if the Record is repeated more than 1 time.