Row number is a nifty analytic function of MSSQL 2005, that allows you to assign a number to each row to which it is applied.

This is very useful when paging a result set, where you can nest a select, and return only rows within a specified range - something like so:

SELECT
  RowNum,
  Column
From
  (Select
      ROW_NUMBER() OVER (ORDER BY Column) AS RowNum,
      Column
  FROM
    Table)
AS
  Result
WHERE
  RowNum BETWEEN @Startrow AND (@Startrow + @Maxrows)

This is nice, but what if you want to keep an existing sort - and not sort it again when assigning row numbers, or if you don't need it to be sorted, and want a more effective execution plan?

The row number function wants the sort expression, so what to do?

You could sort by a constant, but the problem is that SQL Server doesn’t allow a constant in the row_number function’s order by clause.

By chance I realized that you can order by (SELECT 0) - and the optimizer will realize, that sorting is not needed:

SELECT
  RowNum,
  Column
From
  (Select
      ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,
      Column
  FROM
    Table)
AS
  Result
WHERE
  RowNum BETWEEN @Startrow AND (@Startrow + @Maxrows)

Its efficient and pretty elegant.