Row order in Transact-SQL (SQL Server)

Including a column with the row number in the results of an SQL query is a pretty useful feature and the syntax is a bit hard to remember, so here is an example:


SELECT 
    ROW_NUMBER() OVER (ORDER BY <ColumnName> ASC) AS ROWID
    , * 
FROM <TableName>


This query returns all the rows of table <TableName>, including an additional column ROWID up front. The column <ColumnName> is used for determining the order in which the rows are numbered.

This feature works in SQL Server 2005 and later.