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.
Let's look at the function:
ROW_NUMBER() OVER (Partition by <group> order by <columns>)
I can first construct the function as follows
ROW_NUMBER() OVER(PARTITION BY Format ORDER BY FORMAT) as id
However this will give us the following results:
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.
ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY FORMAT) as id
The new result set is:
Hope this helps improve your sql. As always, leave comments below.