How to Add the Row Index with Partition by Column on Existing Table in SQL Server

SQL Server Query to Add the Row Index with Partition by Column on Existing Table
We can do this by using the Common Table Expression (CTE's) . 
Before that you have to create a blank Row_Id column where we want to add the Row_Index

With CTE_RowIndex as
(
Select*,
New_Row_Id= ROW_NUMBER() OVER(PARTITION BY Dept_Name ORDER BY Dept_Name) from Tbl_RowIndex
)
Update CTE_RowIndex set Row_Id=New_Row_Id
Select*from Tbl_RowIndex
;

Lets suppose we have a Table with no values in Row_Id column, in which we want to update the Row_Index Partition by Dept_Name , as follows 

Alter Table Tbl_RowIndex Add Row_Id Int
Select* from Tbl_RowIndex

Next run the below Query, which will add the Row_Index Partition by Dept_Name into the Row_Id field. The output as follows :

With CTE_RowIndex as
(
Select*,
New_Row_Id= ROW_NUMBER() OVER(PARTITION BY Dept_Name ORDER BY Dept_Name) from Tbl_RowIndex
)
Update CTE_RowIndex set Row_Id=New_Row_Id
Select*from Tbl_RowIndex;

-------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------