Row_Number()

 
 
 

RowNumber() is very useful in SQL. It allows you to get a unique number for every row in the table or you can partition by column and then get a unique number.

1) Here is an example of using RowNumber() with a simple Order By by Table_Schema and Table_Name. I assigned “RowNumber” to the new column but you can name it what makes sense. In the results you can see the RowNumber column increases by 1.

SELECT ROW_NUMBER() OVER(ORDER BY TABLE_SCHEMA, TABLE_NAME ASC) AS RowNumber, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
 
 

Results

 

2) Here is an example of adding in “Partition By” in order to restart the RowNumber for each new Table_Schema value that is in the result set. We still Order By TableName. In the results, you can see that 1, 2, 3 are assigned to schema “dbo” while 1-12 are assigned to schema “HumanResources”.

SELECT ROW_NUMBER() OVER(PARTITION BY TABLE_SCHEMA ORDER BY TABLE_NAME ASC) AS RowNumber, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
 
 
 

SQL Scripts

 
 
 

Results