I got to make use of the SQL Server 2005 function ROW_NUMBER for the first time the other day.

I have a table named Country which is defined as:
CREATE TABLE [dbo].[Country]
(
[Id] [int] NOT NULL,
[CountryName] [nvarchar](255) NOT NULL,
[ContinentId] [int] NOT NULL,
[AreaInSquareKm] [int] NOT NULL,
[AlphaCode2] [char](2) NULL,
[AlphaCode3] [char](3) NULL,
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I wanted to get an incrementing row number for each country where the AreaInSquareKM is larger than 100000, ordered by AreaInSquareKM descending. To do this, I simply did:

SELECT Id, CountryName, AreaInSquareKM, ROW_NUMBER() OVER (Order By AreaInSquareKM DESC)
FROM Country
WHERE AreaInSquareKM > 100000

Far simpler than having to put the data into a cursor, or temporary table using an identity column.