Delete Duplicate Records from the table

Suppose there are multiple duplicate records in the [Emp_Salary] table whose structure is as follows

Create Table [Emp_Salary](EMP_ID Varchar(10), EMP_Salary decimal(5,2))

Step 1
Adding a identity column in [Emp_Salary]
Alter Table [Emp_Salary]
Add [Id] Int Identity(1,1)

Step 2
Deleting duplicate records from [Emp_Salary]
You should include all columns of the table [Emp_Salary] in the 'Group By' Clause accept [Id] like


Delete From [Emp_Salary]
Where [Id] Not In (Select Min([Id])
From [Emp_Salary]
Group By [EMP_ID],[EMP_Salary])

Step 3
Droping the newly added column.


Alter Table [Emp_Salary]
Drop Column [Id]

Now the table [Emp_Salary] contains the unique records