generate sequence in sql select

Question

I need to write a query that will generate a sort of sequenced ID for each record... so for example:

ID      Customer Name
-------------------------
C1000   customer #1
C1010   customer #2
C1020   customer #3
C1030   customer #4

now, these "C1000" ids don't exist... only the customer names. I need to generate them when I do the select... so I can save off the output, and then import into the new system.

how can I do a:

select 
   'C' + (some kinda code/math that generates the count based on a sequence?  or row number?),
   name
from Customers

================================================

I ended up doing the following (so I could configure start# and increment size):

DECLARE @start int; 
DECLARE @inc int; 
set @start = 1000; 
set @inc = 10; 

Select 'C' + CAST(@start + (@inc * (ROW_NUMBER() OVER (ORDER BY name))) as varchar) as NewID, Name
from customer

Answer 1

Use ROW_NUMBER() as in this example.