declare @I int
set @I=0
select @I=@I+1, SomeField from SomeTable

Только в такой конструкции нельзя использовать Order By! А SergSuper настоятельно рекомендует подобными конструкциями вообще не пользоваться (если я правильно его понял).

Пардон, я и вправду впопыхах фигню-с спорол-с. Похожий прием прокатывает только с командой Update. А для этого в таблице нужен вспомогательный столбец (например, Counter)
declare @I int
set @I=0
Update SomeTable set Counter=0 -- Обнуление нумерации попредыдущим выборкам
Update SomeTable set @I=Counter=@I+1
where ...
select * from SomeTable

Правда, я не уверен, что этот вариант лучше варианта с временной таблицей. Скорее, даже наоборот.