Row_Number in sql server 2005

ROW_NUMBER function is to find the row number for the column which is specified in Order by clause.Its considered as one of the ranking function.

Code Snippet

declare @qry varchar(100), @StartLoc int create table venkitable(id int, names varchar(30),RELATIVENESS INT) insert into venkitable values(101,'dfVenkat',1) insert into venkitable values(23,'sdfVenkat2',1) insert into venkitable values(323,'sdfVenkat3',1) insert into venkitable values(4111,'sdfVenkat4',1) insert into venkitable values(5345,'rtVenkat5',1) -- ROW_NUMBER FUNCTION IS TO FIND THE ROW NUMBER OF A PARTICULAR-- COLUMN IF ITS SPECIFIED IN ORDER BY CLAUSE SELECT ROW_NUMBER () OVER (ORDER BY names) AS venkatid, names ,id FROM venkitable -- SHOULD DISPLAY AN ERROR INDICATING THAT ROW_NUMBER MUST HAVE AN ORDER BY CALUSE SELECT ROW_NUMBER () OVER() AS venkatid, names ,id FROM venkitable -- WILL INDICATE AN ERROR BECAUSE THE SUBQUERY WILL GIVE MORE THAN ONE VALUES SELECT ROW_NUMBER () OVER (ORDER BY (SELECT ID FROM venkitable)) AS ID, NAMES FROM venkitable -- THIS ONE WILL WORK EXACTLY BUT REFRAIN THE SORT OPERATION-- SUPPRESS THE ROW_NUMBER() FUNCTIONALITY SELECT ROW_NUMBER () OVER (ORDER BY (SELECT ID FROM venkitable WHERE ID=1)) AS NAMES, NAMES FROM venkitable