SQL Server : autoincrement fields separately -

i have table 2 columns, none of columns unique. need auto increment column number separately each user.

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

the idea come search last number used , manually increment one. there more efficient way?

instead of number field, can create auto increment field in table (i call id), , desired number via query:

first adding id:

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

you not need number field anymore:

alter table table_name drop column number

the query number (you can use create view):

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

Alconcel