SQL Myth : Delete Duplicate Rows

Posted: 22/11/2014 in T-SQL

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.

  1. Rajnish says:

    Duplicate record deletion is one of the CTE function but the prime functionality is, We Can use the CTE for other uses like use of multiple result sets in a single statement avoiding Temp Tables.


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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