Starting with SQL Server 2005, the ROW_NUMBER function is available for sequence generation. Pre-2005, you can use the identity function to create a sequence. Following are example T-SQL scripts for results sequence numbering and sequence number generation.

-- SQL Server 2005 / SQL Server 2008 ROW_NUMBER()
SELECT ID=ROW_NUMBER() OVER (ORDER BY Name DESC),
       Department=Name
FROM AdventureWorks2008.HumanResources.Department
ORDER BY ID
/*
ID    Department
1     Tool Design
2     Shipping and Receiving
3     Sales
4     Research and Development
5     Quality Assurance
6     Purchasing
7     Production Control
8     Production
9     Marketing
10    Information Services
11    Human Resources
12    Finance
13    Facilities and Maintenance
14    Executive
15    Engineering
16    Document Control

*/
------------

-- SQL SELECT INTO temp table IDENTITY row numbering
SELECT

      IDENTITY(smallint, 1, 1) AS JobSeq,

      fname AS First,

      minit AS Middle,

      lname AS Last,

      job_lvl AS JobLevel,

      hire_date as HireDate

INTO #employee

FROM employee

------------

-- SQL sequence number generator table-valued function

CREATE FUNCTION fnGenerateSequentialNumbers

               (@Limit INT)

RETURNS @Sequence TABLE(SequentialNumber INT)

AS

  BEGIN

    DECLARE  @RunningValue INT

    SET @RunningValue = 1

    WHILE @RunningValue <= @Limit

      BEGIN

        INSERT @Sequence

        VALUES(@RunningValue)

        SET @RunningValue = @RunningValue + 1

      END

    RETURN

  END

GO

SELECT *  FROM   dbo.fnGenerateSequentialNumbers(10)

GO

/*

SequentialNumber

1

2

3

4

5

6

7

8

9

10

*/

------------