SQL2008 没有主键的情况下删除重复的行

2008使用%%physloc%%这个没有文档化的功能可以去掉数据库中重复的行。

An easy way to remove duplicate rows from a table in SQL Server 2008 is to use undocumented feature called %%physloc%%. This pseudo column shows the physical location of a row.

注意这个功能没有在文档中记录，所以你使用的时候要自己承担风险

Note that this feature is undocumented and unsupported so use at your own risk!

开始输入测试数据
A simple test-case. Create a test table:

CREATE TABLE TestTable (
   Column1 varchar(1),
   Column2 int
);

 

Add some rows with few duplicates:

INSERT INTO TestTable VALUES ('A', 1);
INSERT INTO TestTable VALUES ('A', 1); -- duplicate
INSERT INTO TestTable VALUES ('A', 2);
INSERT INTO TestTable VALUES ('B', 1);
INSERT INTO TestTable VALUES ('B', 2);
INSERT INTO TestTable VALUES ('B', 2); -- duplicate
INSERT INTO TestTable VALUES ('C', 2);

　　

You can select the data to see that all seven rows are present:

SELECT *
FROM   TestTable a
ORDER BY a.Column1, a.Column2; 

 

现在可以试试用%%physloc%%来删除重复行记录

Now let's delete the two duplicates using the %%physloc%%:

DELETE
FROM  TestTable
WHERE TestTable.%%physloc%%
      NOT IN (SELECT MIN(b.%%physloc%%)
              FROM   TestTable b
              GROUP BY b.column1, b.Column2); 

And if you run the query again you'll see that only five rows remain and duplicates have been deleted.

SELECT *
FROM   TestTable a
ORDER BY a.Column1, a.Column2; 

For more information about %%physloc%%, see Physical location of a row in SQL Server[^]. 参考http://www.codeproject.com/KB/database/PhysLocOfARowInSqlServer.aspx