How to Delete Duplicate Records from Table

Below query is useful to delete the duplicate Ids data from table. The table must have identity column, which will be used to identify the duplicate records.

CREATE TABLE StudentDetail(ID INT IDENTITY(1,1),StudentID INT,Name VARCHAR(10),Address VARCHAR(100))

INSERT INTO StudentDetail
SELECT 10001,’Test1′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10002,’Test2′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10003,’Test3′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10004,’Test4′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10005,’Test5′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10001,’Test11′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10005,’Test51′,’Mumbai’

;WITH CTE
AS
(
SELECT ID,ROW_NUMBER()OVER(PARTITION BY StudentID ORDER BY ID ASC)DuplID
FROM StudentDetail
)
DELETE FROM CTE WHERE DuplID > 1

SELECT * FROM StudentDetail;