Include row number in query result (SQL Server)

Question

I think each row in sql server is given a unique number. How can I include that in my SQL query results?

Answer 1

If you are referring to the row number provided by Management Studio when you run a query, there is no way to get that because it does not really exist. Management Studio generates that on the fly. You can however, recreate a sequential number using the ROW_NUMBER ranking function if you are using SQL Server 2005 or later. Note you should never assume the database will return the rows in a specified order unless you include an Order By statement. So your query might look like:

Select ....
    , Row_Number() Over ( Order By T.SomeColumn ) As Num
From Table As T
Order By T.SomeColumn

The Order By in the Over clause is used to determine the order for creating the sequential numbers. The Order By clause at the end of the query is used to determine the order of the rows in the output (i.e. the order for the sequence number and the order of the rows can be different).

Answer 2

The uniqueness in your result will always be your primary key.

But there is something called ROW_NUMBER function that can be used as unique identity of row in a specific result.

SELECT zip,city,state,latitude,longitude,timezone,dst,
          ROW_NUMBER() OVER (ORDER BY zip) AS num
          FROM dbo.zipcode;