How to add an auto incrementing ID field in SQL server

So it turns out that SQL is really quick to pick up, once you have the (very) basics down everything you need to do fall intuitively into place as it is all based on the first principals of a select statement. And even if you do get stuck all answers are a very rapid google search.

The biggest trouble I have with starting off programming any new languages is that I expect to hit the ground running and just jump into the complex stuff without going through the basic tutorials. THe problem with this is it can them be tricky to actually google the right term to get the answer to your problem. So in light of this I've decided to log all the little bits and bobs which fall into this category as I come across them.

So stay tuned for further posts in the series under tag SQL.

One of the first things which fell into this category were row numbers. The normal way people tell you to deal with this it is to append a table with a new column with the row numbers in. Nice and easy:

ALTER TABLE #Table ADD AUTOID INT IDENTITY(1,1)

This is all well and good but what I need most regularly is to add row numbers into outputs of queries as I run them, I don't want to have to write the results to a table then append the row number on. Fortunately the code is simple and it will also let you order the results at the same time by using the OVER function:

Print'Add row numbers to an output'
select 
     ROW_NUMBER() over (order by [FirstColumn]) as AUTOID
    , *
from #Table