Generating an Incrementing Value from a SELECT Statement

Can I create a simple SELECT statement that generates an IDENTITY-type value by auto-numbering the rows that the SELECT statement retrieves?

Creating a SELECT statement that generates an incrementing value can be difficult because the SELECT statement doesn't permit variable assignment in combination with data retrieval. (This rule exists to prevent you from incrementing a variable within a SELECT statement.) However, if you only want to generate row uniqueness, you can use the NEWID() function that the following SELECT statement shows:

SELECT NEWID(), * FROM dbo.MyTab

If you want a monotonically increasing integer row count, you have a couple of choices: You can use a cursor and maintain a local count variable programmatically, or you can generate a temporary table to join to the base table in the query, as Listing 1 shows. If you want to use an ascending integer key for your row numbering, you need to include appropriate ORDER BY clauses to sort the data. The sample in Listing 1 uses a three-step process to avoid making a full copy of all the data; it just copies the primary key. (However, you could use any unique record identifier.) If you don't have much data and you have the disk space, or if your rows don't contain many columns, you could use a single-step process to select into a temporary table, then report off the temporary table.