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:

   1: -- CREATE OUR TABLE
   2: DECLARE @Data TABLE
   3: (
   4:          AccountId  int NOT NULL
   5:     ,    Name  varchar(10) NOT NULL
   6:     ,    SortOrder    tinyint NOT NULL
   7:     ,  UNIQUE (AccountId, Name)
   8:     ,  UNIQUE (AccountId, SortOrder)
   9: )
  10:  
  11: -- CREATE A COUPLE OF ACCOUNTS
  12: DECLARE   @Account1 int
  13:      ,    @Account2 int
  14:  
  15: SELECT    @Account1 = 1
  16:      ,    @Account2 = 2
  17:  
  18: -- SEED THE TABLE WITH OUR SAMPLE DATA
  19: INSERT    @Data (AccountId, Name, SortOrder)
  20: SELECT    @Account1, 'Bob', 1 UNION
  21: SELECT    @Account1, 'Bill', 2 UNION
  22: SELECT    @Account1, 'Barb', 3 UNION
  23: SELECT    @Account2, 'John', 1 UNION
  24: SELECT    @Account2, 'Jim', 2 UNION
  25: SELECT    @Account2, 'Bob', 3  -- This value exists for both accounts


Here's how the copy process came together as a single statement:

   1: -- COPY DATA FROM ACCOUNT 1 TO ACCOUNT 2
   2: INSERT    @Data (AccountId, Name, SortOrder)
   3: SELECT    AccountId = @Account2
   4:      ,    Source.Name
   5:      ,    SortOrder = (SELECT MAX(SortOrder) FROM @Data WHERE AccountId = @Account2)
   6:                     + ROW_NUMBER() OVER (ORDER BY SortOrder)
   7: FROM     @Data Source
   8: WHERE    Source.AccountId = @Account1
   9:   AND  NOT EXISTS(
  10:   SELECT   *
  11:   FROM     @Data
  12:   WHERE    AccountId = @Account2
  13:   AND  Name = Source.Name
  14:         )
  15: 


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:

AccountId Name SortOrder
1 Bob 1
1 Bill 2
1 Barb 3
2 John 1
2 Jim 2
2 Bob 3
2 Bill 4
2 Barb 5