SQL Server : autoincrement fields separately

Question

I have a table with two columns, where none of the columns is unique. I need to auto increment the column number separately for each user.

user | number
1    | 1
2    | 1
1    | 2 
3    | 1

The only idea I could come up with is to search for the last number used and manually increment by one. Is there a more efficient way?

Answer 1

Instead of the number field, You can create an auto increment field in the table (I call it id), and get the desired number via a query:

first adding id:

alter table table_name add id int not null IDENTITY(1,1)

you do not need the number field anymore:

alter table table_name drop column number

The query to get number (you can use it to create a view):

select user,
row_number() over(partition by user order by id) as number
from table_name