Delete Duplicate : Using internal storage key/Row Id

Posted: 03/09/2015 in Tips and Tricks

When we think of delete duplicate, we are familiar with deleting data by generating row number for each row( either using derived table or CTE). Now here I would like to show you a method to identify each row from duplicated records without using row number or temp table logic.

The method would break down a concept of duplicate rows in SQL and this key word works after SQL2005. As I said there is no duplicate rows in SQL, even the rows are duplicated each rows are stored identically and they can be identified using an internal storage id called Row Id. Let us see how to utilize this id to resolve our issue.


Above I have created a table and inserted duplicated rows, in the select query I have used “%%Physiloc%%” keyword and displays row id of each rows (this is an hexadecimal value of the location where rows are being stored), we can use this row ID to delete duplicate rows from the table using very simple logic.


Here I have deleted the duplicate rows only by executing the first query in the above screenshot.

Take away : There is no duplicate rows in a table, each rows can be identified by Row ID of each row. Please do leave your comments, we will discuss more about physiloc in up coming post.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s