Multi Tenant Identity Is there any way to declare identity partitioned by tenant id?

For example I want to have this structure:

tenant_id | record_id (auto increment)
---------------------
      1   |   1
      1   |   2
      1   |   3
      1   |   4
      2   |   1
      2   |   2
No, there is no means of partitioning either an IDENTITY or SEQUENCE. In order to do this you would have to create a table to manage TenantID and RecordID for this table. Then you would need a function that accepts TenantID and will return RecordID. This means that you need to insert via a stored procedure that can grab that value and then do the INSERT, or maybe you can get away with an INSTEAD OF trigger, but I am not sure about that given that both TenantID and RecordID need to be part of the PK.

And if you want this pattern to apply to all tables then you would need to duplicate that custom sequence per each table. This is ok for maybe a few tables, but not more than that.

There are rare occasions when this type of behavior is desirable, but the current reason for requesting this seems a bit suspect (noted in a comment on the question):


  for capacity...1 mil record with id partitioned by 50 tenants will have highest id around 20k, while not partitioned will have highest id of 1 mil.


This is not (or at least should not be) a valid concern. The INT datatype has a capacity of just under 4.3 billion values. If you start the IDENTITY (i.e. the "seed") at 1, then you get half that range since this is a signed INT and not an unsigned INT. If you want all 4,294,967,296 values, then use IDENTITY(-2147483648, 1).