This is about 3 years too late, but it worked for me:

SELECT [Field1]

, ROW_NUMBER() OVER (ORDER BY [Field1]) AS 'Row Number'

FROM [dbo].[MyTable]