sql - Is it possible to Auto-increment an id and reset to 1 depending on another id in Visual studio 2013 database? -

here's example:

user_id  listing_id  1           1  1           2  1           3  2           1  2           2  3           1  3           2  4           1 

i thought better having unique ids every listing, read around , saying better use unique identifier listing alone because 1 less information using, in example user , listing id primary key.

but in 2009 , wonder if changed.....

so question is, possible? , if is, advisable

you could want using trigger, wouldn't recommend it. have unique id each row. then, if want incrementing number:

select t.*, row_number() on (partition user_id order id) table t; 

you can enumeration when need value rather on insert.

i strong advocate of integer, identity columns tables. here reasons:

• a single column more practical foreign key relationships.
• integers (of whatever size) efficient comparisons , indexes.
• maintaining column requires lot of overhead, because have sure there no gaps, multiple simultaneous inserts, updates, , deletes.