Remove Duplicate Rows from a Table in SQL Server

--Create Table Script
CREATE TABLE [dbo].[ATTENDANCE](
    [AUTOID] INT IDENTITY(1,1),
    [EMPLOYEE_ID] [varchar](50) NOT NULL,
    [ATTENDANCE_DATE] [date] NOT NULL
) ON [PRIMARY] 

--Insert the into the newly created table
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
            ('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
            ('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
            ('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
            ('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
            ('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
            ('A003',CONVERT(DATETIME,'01-01-11',5))

--Check the data from the table            
SELECT * FROM dbo.ATTENDANCE

--Check the duplicate data
SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)
    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

--Delete the duplicate data
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)
    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

--Alternatively you can use the below query as well
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MAX(AUTOID)
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

--After deleting the duplicate data check data from the table
SELECT * FROM dbo.ATTENDANCE


So in general use the below Query-
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3. 


Happy learning..... :)