For TSQL developers ROW_NUMBER() is a nice, smart way to generate incrementing id for result set for set based TSQL queries.Consider following query.
SELECT ROW_NUMBER( ) OVER( ORDER BY ContactName), ContactName,ContactTitle FROM dbo.Customers
As you can see you need to order by any one of column names to get the ROW_NUMBER( ) function to work.
However sometimes you might need not to get result set by ordering by a column how ever without order by clause up to SQL server 2008 do not allow to use the ROW_NUMBER( ) function.
What is the solution? Solution to this is as following
SELECT ROW_NUMBER( ) OVER( ORDER BY (SELECT NULL) ), ContactName,ContactTitle FROM dbo.Customers
Passing sub query that return null will do the trick!….. this way row id is given to the rows in arbitrary order rather than ordering by a column in the result set.