Posts Tagged ‘Delete Duplicate’

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.


I had a myth about SQL from the day I learned CTE and I corrected it a week back.

Did you have the same ? Let us check out.

I am not going to discus about CTE but about one of the main feature about it that can be achieved without CTE.

The Main use CTE – If you Google this the answers would be like this

1. Recursive use of tables ( referring same table multiple times like loop).

2. Delete duplicate rows – one of the most common interview question I faced delete duplicate rows using CTE.

Yes recursion can be achieved by CTE but for duplicate row delete do we really need CTE ???

Just try the below code.


Yes , Let us correct our answers. CTE is not required to remove duplicate rows from a table. Yes it can be achieved in a single query by using derived table.