Auto-increment without the need of a table.

I came across a situation where I need unique numbers for both a large and small set of data. The standard approach is to create a table with an identity column and do your inserts. For a table with tens of thousands of records, this might be the best approach, but there is another one: ROW_NUMBER().

The key to using the ROW_NUMBER() function is to provide a constant so you will get a contiguous sequence.  Let's look at sample data.

Format
  AB
  AL
  CD
  CE
  GG
  LI

Let's look at the function:

ROW_NUMBER() OVER (Partition by <group> order by <columns>)

I can first construct the function as follows

 select Format,
         ROW_NUMBER() OVER(PARTITION BY Format ORDER BY FORMAT) as id

However this will give us the following results:

id Format
 1   AB
 1   AL
 1   CD
 1   CE
 1   GG
 1   LI

The reason is your PARTITION BY creates a unique number for each format. To solve this problem you need to put a value that will never change over the life of the select. I use 1.

 select Format,
         ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY FORMAT) as id

The new result set is:

id Format
 1   AB
 2   AL
 3   CD
 4   CE
 5   GG
 6   LI

Hope this helps improve your sql. As always, leave comments below.