SQL Server Window Function - ROW_NUMBER() usage and examples

Overview

• ROW_NUMBER function returns a unique integer value starting with one and incremented by one for every row.
• The ROW_NUMBER function enumerates the rows in the sort order defined in the OVER clause.
• An ORDER BY expression inside the OVER clause is mandatory.
• Optional PARTITION BY can divide data into smaller groups based on the column.
• All the window functions that depend on ORDER BY in the OVER clause are non-deterministic. Refer Microsoft article on Deterministic and Nondeterministic Functions.
• ROW_NUMBER is only for output reference.

Syntax : 

ROW_NUMBER() 
OVER([PARTITION BY <col1>[,<col2>,...<n>]] ORDER BY <col3>[,<col4])

OVER: How the ranking should order or split the data

PARTITION BY: To split the data into groups/partition based on column

ORDER BY: Ordering of the data

Consider the below sample data:

CREATE TABLE EMPLOYEE(ENAME VARCHAR(45),EDEPART VARCHAR(45))
INSERT INTO EMPLOYEE(ENAME,EDEPART) 
VALUES 
('KRISHNA','IT'),('RAMKRISHNA','CIVIL'),('PRASAD','IT'),
('RAMESH','ACCOUNTS'),('MAHESH','MANAGEMENT'),('KALYAN','IT'),
('SATISH','ACCOUNTS'),('ASHOK','CIVIL'),('GANESH','MANAGEMENT')

ROW_NUMBER() Function without Partition By clause
Without Partition By clause SQL Server considered all records in the result-set as single partition or group and then ranking functions are applied.
Example

SELECT ROW_NUMBER()
    OVER (ORDER BY EDEPART,ENAME) AS ID,EDEPART,ENAME
FROM EMPLOYEE

Output:

ROW_NUMBER() Function with Partition By clause
With the PARTITION BY clause, a column or set of columns specified will divide the result set into record partitions. Then, finally ranking functions are applied to each record partition separately.
Example

SELECT ROW_NUMBER()
    OVER (PARTITION BY EDEPART
ORDER BY  EDEPART,ENAME) AS ID,EDEPART,ENAME
FROM EMPLOYEE

Output

Refer : https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql
Let me know if you have any questions, suggestions or comments.