Generate row number column in SQL Server

Sometimes we need our SELECT statement to return current row number. In the following script it is shown two ways you can perform this task.

-- Sample 1: Using SELF JOIN
SELECT
        (SELECT COUNT(TerritoryID)
        FROM dbo.Territories T2
        WHERE T2.TerritoryID <= T1.TerritoryID) AS RowNumber
        , T1.*
FROM dbo.Territories T1

-- Sample 2: Using IDENTITY
SELECT
    IDENTITY(int,1,1) as RowNumber, *
    INTO #TempTerritories
FROM dbo.Territories

SELECT * FROM #TempTerritories
DROP TABLE #TempTerritories

Anyway, most of the times you can avoid this practice, and build row number in the GUI part. By example, if you have a report and you want to add a row count column, you can insert a special field called “Record Number”: select Insert menu/Special Field/RecordNumber.

Your suggestions are welcomed.

Is this useful?