Row Number Function in SQL Server

In this tutorial, I am going to explain you about Row Number Function in SQL Server. This is built-in Ranking Function provided by SQL Server for assigning a sequence number to records in a result set.

Row Number in SQL Server:

ROW_NUMBER is a built-in SQL Server function that is used to show sequence number for each record in a result set. ROW_NUMBER generates a run-time column that generates a Serial Number for each Record in the result set.

Syntax:

Row_Number() over (order by col1, col2, ....coln partition by col1, col2, ....coln)

Where;

Order By(Required): Row Number generates by the column provided in Order By clause.

Partition(Optional): It is similar to group by clause. It groups the result based on the column provided in the partition by clause. Where sequence starts with 1 for every group.

Example of Row Number:

Before starting Let’s create a table and insert some demo records.

Create Table:

CREATE TABLE [dbo].[Employee](
[EmployeeID] [bigint] IDENTITY(1,1) NOT NULL,
[EmployeeName] [varchar](100) NULL,
[Gender] [varchar](50) NULL,
[Designation] [varchar](50) NULL,
[Salary] [float] NULL,
[City] [varchar](100) NULL
) ON [PRIMARY]

GO

Insert Records:

SET IDENTITY_INSERT [dbo].[Employee] ON

GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Designation], [Salary], [City]) VALUES (1, N'Ahwin Roy', N'Male', N'Software Engineer', 800000, N'Varanasi')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Designation], [Salary], [City]) VALUES (2, N'Ritesh Shah', N'Male', N'Team Leader', 900000, N'New Delhi')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Designation], [Salary], [City]) VALUES (3, N'Richa Singh', N'Female', N'DBA', 1200000, N'Allahabad')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Designation], [Salary], [City]) VALUES (4, N'Tripti Sinha', N'Female', N'Tester', 700000, N'Mumbai')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [Designation], [Salary], [City]) VALUES (5, N'Nitin Pandey', N'Male', N'Web Developer', 8000000, N'Pune')
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

Example 1:

Below is an example that would assign Sequence Number for all Employees.

SELECT [EmployeeID], [EmployeeName], [Gender], [Designation],[Salary],[City]
, row_number() over (order by [EmployeeID]) as Seqence
FROM [dbo].[Employee]

Output:

Look at the output screen where a Sequence Number assign for each EmployeeID which is provided in Order By.

Let’s see an example for order by with multiple columns

SELECT [EmployeeID], [EmployeeName], [Gender], [Designation],[Salary],[City]
, row_number() over (order by [EmployeeName],[Designation]) as Seqence
FROM [dbo].[Employee]

Output:

Example 2:

In this example, we will see how to use Partition By clause with Row Number.

SELECT [EmployeeID], [EmployeeName], [Gender], [Designation],[Salary],[City]
, row_number() over (partition by Gender order by EmployeeID) as Seqence
FROM [dbo].[Employee]

Output:

Look at the output window where Sequence is started from 1 for each group i.e. for gender which is provided in the partition by clause.

View More:

• Like Operator in SQL Server.
• Foreign Key in SQL Server.
• Views in SQL Server.
• Triggers in SQL Server.

Conclusion:

I Hope you loved this post. I would happy to receive your feedback and comments.

Thank You.