I just tackled a problem using SQL's ROW_NUMBER() function, and I'm happy with how it turned out. It would have been pretty tricky in SQL 2000.
The problem at hand was merging 2 sets of data, copying data from one account into another account, where the records don't already exist. The data contains a SortOrder field that must be unique within each account. When I merge data from one account to another, I need to include the SortOrder field, but prevent duplicate values in the new account.
Let's set up a sample of the problem:
Here's how the copy process came together as a single statement:
The trick was to combine the ROW_NUMBER() function with a sub-select that gets the previous max sort order for the target account.
Here's the result: