How to delete nth row of the table in SQL Server?

If database table contains any primary key value then performing update and delete operations are not big task. But generally we face problem when we don’t have any primary key in table, on that case we can’t target to any particular column for deleting or updating records because that column can contain duplicate values.

In this article, I am explaining –

How to delete record based on row number? 
Or
How to delete nth row of the table?           
Or
How to delete record without primary key?           
 
                             
To perform this operation we have to use ROW_NUMBER() function of SQL Server. This function will help us to get nth row which we want to delete from table.

ROW_NUMBER()- 

ROW_NUMBER() is function in SQL Server which return sequential number for each record in result set. This returned number can be used as an identity for each record which is present in result set. When we don’t have any primary key in table then it is very useful to identify the record in result set. Using this sequential number we can perform operation like delete and update even we don’t have primary key for table.

Here is syntax to delete nth row from the table-

Syntax-

DELETE  Record
FROM    (
        SELECT  rowno = ROW_NUMBER() OVER (ORDER BY (Column_Name))
        FROM    Table_ame
        Where Condition
        ) AS Record
WHERE   Record.rowno = RowIndex

Here RowIndex is an index of the row which you want to delete from table. Here inner query will return result set including sequential number for each record and this will be ordered based on column_name which will be passed in ORDER BY clause. Finally outer query will perform delete operation based on given condition . 

Example-

Suppose if we want to delete 5th row of the Employee table then query will be like this-

DELETE  Record
FROM    (
        SELECT  rowno = ROW_NUMBER() OVER (ORDER BY (EmpName))
        FROM    Employee
        ) AS Record
WHERE   Record.rowno = 5

In above query, First inner query will be executed, which will generate result set over Employee table including row number over EmpName. Finally outer query will perform delete operation over result of inner query with given condition-

WHERE   Record.rowno = 5

This condition will detect 5th row from inner result and delete record from the table. In this example I didn't put any condition for inner query but if we have requirement then we have put where condition also to get result set like I have written in given syntax.


Thanks