Can you specify a subsequence of the table?

Question

I created an item history table for which I would like to automatically increase the VersionId divided by ItemId .

  ID ItemId VersionId ItemContent Created
 1 1 1 fooo 2015-02-24 12: 54: 00.11
 2 2 1 barr 2015-02-24 12: 54: 15.35
 3 1 2 foo 2015-02-24 12: 55: 00.61
 4 1 3 baz 2015-02-24 12: 55: 45.23
 5 2 2 bar 2015-02-24 12: 56: 00.03 

This VersionId installed in the trigger. I would like to find something like this (I know this doesn't actually work):

 create table ItemHistory ( Id int identity(1,1) not null primary key ,ItemId int not null references Item(Id) ,VersionId int not null default row_number() over (partition by ItemId order by Id) ,ItemContent varchar(max) ,Created datetime not null default getdate() ) 

As long as I have a working solution, I would like to know if I have enough built-in functions to handle this case.

Answer 1

If you want to use my comment as an answer

Why not just do it in terms? You can have row_number () in the view.

With the ItemId, Id index, this should be really fast.
Probably less overhead than a trigger.

I see that you added a read-only comment. More reasons to watch.