Generate auto row number for a table

This is a really a great experience for me because I got an opportunity to work for mid level ASP.NET web application, actually it’s remodeling of winFrom application to standard ASP.NET 3.5 web application where I worked with almost 50 tables and I found that all the tables using Guid()/ NweId() ( return 36 digit alphanumeric character) for PK and FK key mapping. I discussed with my team lead about this issue because I want the data paging to be server side and data shorting will be client side where I must be concerned about application performance, each table contains more than 10 thousand of rows. So I finally discovered that a single SQL function could resolve these issues.

Transact- SQL

SELECT ROW_NUMBER() OVER(ORDER BY [col_1]) as RowNumber,
* FROM [Example].[dbo].[Table_A]