Click here to Skip to main content
14,303,876 members

How to remove duplicate rows in SQL Server 2008 when no key is present

Rate this:
4.95 (12 votes)
Please Sign up or sign in to vote.
4.95 (12 votes)
20 Feb 2011CPOL
This tip describes how to delete duplicate rows from a table that doesn't have a key.
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;
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[^].

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Wendelius
Architect
Europe Europe
This member does have enough reputation to be able to display their biography and homepage.







Comments and Discussions

 
Questionusing datagrip(postgres) and %%physloc%% is undefined Pin
Member 1455982115-Aug-19 4:09
memberMember 1455982115-Aug-19 4:09 
GeneralMy vote of 5 Pin
Maciej Los12-Feb-15 9:24
protectorMaciej Los12-Feb-15 9:24 
GeneralRe: My vote of 5 Pin
Wendelius12-Feb-15 9:25
mveWendelius12-Feb-15 9:25 
QuestionGreat Pin
An2dSingh8-Feb-13 1:06
memberAn2dSingh8-Feb-13 1:06 
QuestionNo need Pin
MartinSmith10000009-Jun-12 0:53
memberMartinSmith10000009-Jun-12 0:53 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey17-Apr-12 0:07
professionalManoj Kumar Choubey17-Apr-12 0:07 
GeneralRe: My vote of 5 Pin
Wendelius17-Apr-12 3:32
mveWendelius17-Apr-12 3:32 
GeneralReason for my vote of 5 I didn't know that trick too Pin
beginner20115-Sep-11 15:01
memberbeginner20115-Sep-11 15:01 
GeneralRe: Thanks Pin
Wendelius5-Sep-11 17:31
mveWendelius5-Sep-11 17:31 
GeneralReason for my vote of 5 This is something everybody should k... Pin
jeyre22-Feb-11 4:07
memberjeyre22-Feb-11 4:07 
GeneralRe: Thank you Pin
Wendelius3-Aug-11 8:43
mveWendelius3-Aug-11 8:43 
GeneralReason for my vote of 5 I didn't know that trick :) Pin
Jcmorin22-Feb-11 0:20
memberJcmorin22-Feb-11 0:20 
GeneralRe: Thanks Pin
Wendelius3-Aug-11 8:43
mveWendelius3-Aug-11 8:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 20 Feb 2011

Stats

94.4K views
22 bookmarked