SQL 2005 利用Row_Number()函数刪除大量重複數據

如果需要删除大量重复数据时,可以使用如下脚本: 
WITH [EMPLOYEE ORDERED BY ROWID] AS 
(SELECT ROW_NUMBER() OVER (PARTITION BY EMPID,FNAME,LNAME ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE) 
SELECT * FROM [EMPLOYEE ORDERED BY ROWID] WHERE ROWID>=2 

如果数据量超大时,建议不要使用通用表达式,通用表达式的效率远比临时表要低很多.