Row_Number Example in SQL Server

Row_number -- Returns sequential number for the resultset returned by select statement

Syntax
Row_Number() (Over [Partition by], [Order by])

Partition By – It divides the resultset into partitions produced by from clause to which sequential number will be assigned.

Order by clause is used to assign sequence for the column which you mentioned. It is required when you use partition by clause.

CREATE TABLE rownumber_test (ServerName VARCHAR(20), Server_Type VARCHAR(20), Server_Location VARCHAR(20))

INSERT INTO rownumber_test VALUES ('Server1', 'VM', 'India')
INSERT INTO rownumber_test VALUES ('Server2', 'VM', 'India')
INSERT INTO rownumber_test VALUES ('Server3', 'VM', 'AUS')
INSERT INTO rownumber_test VALUES ('Server4', 'VM', 'Bangkok')
INSERT INTO rownumber_test VALUES ('Server5', 'Physical', 'South America')
INSERT INTO rownumber_test VALUES ('Server6', 'Physical', 'South America')
INSERT INTO rownumber_test VALUES ('Server7', 'Physical', 'Japan')
INSERT INTO rownumber_test VALUES ('Server8', 'Physical', 'Bangkok')

-- Assign Sequential Number for the result set.

SELECT Row_Number()over(ORDER BY ServerName) AS SlNo,
ServerName, server_type FROM rownumber_test

Note - Normally Partition by clause will be used to generate sequence numbers for subset in other words on a repetitive values, for example in the above example
we have two different types of server types, one is VM and other is Physical. We need to get sequential number by server type, below is the example

SELECT Row_Number()over(PARTITION BY server_type ORDER BY server_type) AS SlNo,
ServerName, server_type FROM rownumber_test

SELECT Row_Number()over(PARTITION BY server_location ORDER BY server_type) AS SlNo,
ServerName, server_type, Server_Location FROM rownumber_test

-- Example to specify multiple columns in partition by clause

SELECT Row_Number()over(PARTITION BY server_type, server_location ORDER BY server_type) AS SlNo,
ServerName, server_type, Server_Location FROM rownumber_test

-- Example to remove duplicates using row_number()

WITH duplicates(servername, row_cnt, servertype, serverlocation)as
(
SELECT servername, ROW_NUMBER()OVER(PARTITION BY server_type ORDER BY server_type), server_type, server_location
FROM rownumber_test
) delete FROM duplicates WHERE row_cnt>1

 --- Example for pagination using row_number function

DECLARE @records_per_page INT = 25;
DECLARE @currentPage INT = 10;
PRINT @records_per_page;
PRINT @currentPage;
WITH persondetails AS
(
SELECT TOP (@currentPage * @records_per_page) RowNumber= row_number() OVER (ORDER BY businessentityID), FirstName, LastName FROM person.Person
)SELECT RowNumber, firstname, LastName FROM persondetails
WHERE RowNumber BETWEEN ((@currentPage - 1) * @records_per_page + 1) and (@currentPage) * @records_per_page