Skip Navigation LinksHome > Categories > Code from a Category

Delete duplicate rows without primary key in ms sql server



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 2566
Add Date: 12/06/2012
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

Insert Rows

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

Retrieving Records - Rows


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()

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

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 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

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.

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.

Happy Programming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.