Delete Duplicates in SQL 2000, SQL 2005, SQL 2008 or Higher

-- SQL 2005, SQL 2008 and Higher
WITH XT(i, notes, RowNumber) AS
(
SELECT i, notes, ROW_NUMBER() OVER (PARTITION BY i, notes ORDER BY i, notes) AS RowNumber
FROM dbo.temp
)
DELETE FROM XT WHERE RowNumber > 1;
GO

-- SQL 2000 Script
DECLARE @DupCount INT
SET @DupCount = 0
SELECT @DupCount = COUNT(*)
FROM dbo.temp
GROUP BY i, notes
HAVING COUNT(*) > 1

WHILE @DupCount > 0
BEGIN
SET ROWCOUNT 1

DELETE dbo.temp
FROM dbo.temp t1
JOIN (SELECT i, notes
FROM dbo.temp
GROUP BY i, notes
HAVING COUNT(*) > 1
) t2
ON t1.i = t2.i
AND t1.notes = t2.notes

SET ROWCOUNT 0

SELECT @DupCount = COUNT(*)
FROM (SELECT *
FROM dbo.temp
GROUP BY i, notes
HAVING COUNT(*) > 1
) tp
END
GO


SELECT * FROM dbo.temp

/*
-- Sample Create Table/Insert Scripts for above queries
--DROP TABLE dbo.temp
CREATE TABLE dbo.temp(i int, notes varchar(100))
GO

INSERT INTO dbo.temp VALUES (1, 'Notes1')
INSERT INTO dbo.temp VALUES (1, 'Notes1')
INSERT INTO dbo.temp VALUES (1, 'Notes1')
INSERT INTO dbo.temp VALUES (2, 'Notes1')
INSERT INTO dbo.temp VALUES (3, 'Notes1')
INSERT INTO dbo.temp VALUES (1, 'Notes2')
INSERT INTO dbo.temp VALUES (2, 'Notes2')
INSERT INTO dbo.temp VALUES (2, 'Notes2')
INSERT INTO dbo.temp VALUES (2, 'Notes3')
INSERT INTO dbo.temp VALUES (4, 'Notes4')
INSERT INTO dbo.temp VALUES (4, 'Notes4')
GO
*/