Is there a performance problem when using (ROW_NUMBER) function in getting a specific set of data?
The ultimate answer is to look at the query plan that is produced by a query with, and without, row_number. In practice row_number is such an effective clause that many feel (and have shown) that row_number seems to solve difficult problems (such as eliminating duplicates in table) more proficiently that any other method (start flame war here....). Since its introduction in SQL Server 2005 its become extremely popular for a reason. In general SQL Server orders tables very efficiently (once again dependent on the size of the table and if there is an index on the order by columns...).
But to restate the obvious it don't mean a thing until you look at the actual query plan that is produced with and without it.