In this programming tutorial we will learn how to delete duplicate rows without any primary key in ms sql server. This is the most common question asked in interview. In this programming tutorial we will create a temporary table and then insert some duplicate rows in it and finally we will delete these duplicate rows. So let's have a look.

Delete duplicate rows without primary key in ms sql server

Create Temporary Table

create table #my_temp_table
(
user_name varchar(30),
user_type varchar(30)
)

Insert Rows

insert into #my_temp_table values('abdur rehman','admin')
insert into #my_temp_table values('adeel fakhar','admin')
insert into #my_temp_table values('adeel fakhar','admin')
insert into #my_temp_table values('irfan ghani','user')
insert into #my_temp_table values('waqar shafique','moderator')
insert into #my_temp_table values('waqas beg','contributor')

Now we have temporary table in our database, when we perform select * then we will get following records

Retrieving Records - Rows

select * from #my_temp_table

Output:-

In above picture i have highlighted the duplicate row, the row that we want to delete. We will use the built-in function ROW_NUMBER() that comes with ms sql server 2005. ROW_NUMBER() Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.  

Syntax of ROW_NUMBER()

ROW_NUMBER ( )     OVER ( [  ]  )

<partition_by_clause> </partition_by_clause>

Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied.

<order_by_clause>

Determines the order in which the ROW_NUMBER() value is assigned to the rows in a partition. For more information, see ORDER BY Clause (Transact-SQL). An integer cannot represent a column when the <order_by_clause> is used in a ranking function.

The return type of ROW_NUMBER() is bigint. Remarks The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. Ok, we have learn enough about ROW_NUMBER() function, now let's come back to our tutorial.

For generating row number we have to write this query

select * 
from 
(
	SELECT ROW_NUMBER () OVER (ORDER BY ( USER_NAME )) AS sr_no ,* from #my_temp_table 
) as A 

Output:-

As you have seen, we generated unique row number for every row, now its easy for us to delete the duplicate row. For instance i want to delete the third row, so i will query like this.

delete a 
from
(
	SELECT ROW_NUMBER () OVER (ORDER BY ( USER_NAME )) AS sr_no ,* from #my_temp_table 
) as A where sr_no = 3

The above query will delete the third row.

Output:-

Note:- The scope of this tutorial is now limited to the temporary table, you can apply this technique to user tables as well. Moreover, very soon i will write a tutorial that will demonstrate how to delete all duplicate rows without any primary key dynamically in ms sql server, without hard coding any thing.

So that's it. I love your feedback.