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 ...