Click here to Skip to main content
15,867,594 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
4.95/5 (12 votes)
20 Feb 2011CPOL 106.5K   22   13
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:
SQL
CREATE TABLE TestTable (
   Column1 varchar(1),
   Column2 int
);
Add some rows with few duplicates:
SQL
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:
SQL
SELECT *
FROM   TestTable a
ORDER BY a.Column1, a.Column2;
Now let's delete the two duplicates using the %%physloc%%:
SQL
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.
SQL
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)


Written By
Architect
Europe Europe
Biography provided

Comments and Discussions

 
Questionusing datagrip(postgres) and %%physloc%% is undefined Pin
Member 1455982115-Aug-19 4:09
Member 1455982115-Aug-19 4:09 
GeneralMy vote of 5 Pin
Maciej Los12-Feb-15 9:24
mveMaciej Los12-Feb-15 9:24 
GeneralRe: My vote of 5 Pin
Wendelius12-Feb-15 9:25
mentorWendelius12-Feb-15 9:25 
QuestionGreat Pin
An2dSingh8-Feb-13 1:06
An2dSingh8-Feb-13 1:06 
QuestionNo need Pin
MartinSmith10000009-Jun-12 0:53
MartinSmith10000009-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
mentorWendelius17-Apr-12 3:32 
GeneralReason for my vote of 5 I didn't know that trick too Pin
beginner20115-Sep-11 15:01
beginner20115-Sep-11 15:01 
GeneralRe: Thanks Pin
Wendelius5-Sep-11 17:31
mentorWendelius5-Sep-11 17:31 
GeneralReason for my vote of 5 This is something everybody should k... Pin
jeyre22-Feb-11 4:07
jeyre22-Feb-11 4:07 
GeneralRe: Thank you Pin
Wendelius3-Aug-11 8:43
mentorWendelius3-Aug-11 8:43 
GeneralReason for my vote of 5 I didn't know that trick :) Pin
Jcmorin22-Feb-11 0:20
Jcmorin22-Feb-11 0:20 
GeneralRe: Thanks Pin
Wendelius3-Aug-11 8:43
mentorWendelius3-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.