Sql server generate serial number -

sql server query. remember found way generate serial number don't use row_number function when select data. don't remember now

it this:

declare @num int set @num = 0 select ??? [index], name product  result be: index    | name 1        | rug 2        | water 3        | oil 

i remember ??? @num = @num + 1 , in used function cast or isnull or convert or (i don't remember, i'm sure don't use row_number() function)

is there way that?

my problem when use row_number():

select id, case when [type] = 'abc' 1 else 0 end classification,        ???? displayorder product order classification, ..., ..., ... 

??? how use row_number() here? try row_number(over order (select 1)) 'order classification,...' statement run after row_number generated. mean serial number generated before order statement action.

try

use row_number()

select row_number() over(order newid()) [index], name product 

edit 2:

using identity column , temporary table

create table #x([index] bigint identity(1,1),name varchar(200))  insert #x(name) select name product  select * #x  drop table #x