Identity columns for multitenant database schemas

Question

I'm creating a multitenant app where some of the tables need to have sequentially assigned integer values. The ordering is done independently for each tenant. As a concrete example, consider a Student table with a RegNumber column. RegNumber has to be assigned sequentially, but the sequence is local to each tenant.

The solution I'm thinking of involves using another table to hold the "next available" RegNumber value for each tenant, which leads me to a couple of questions:

1. Is there a better way?
2. What is the best way to do a "SELECT FROM tenant_studentid_sequence" and "INSERT INTO students" in a single transaction without excessive locking, and without the possibility of skipping or duplicating values?

In MySQL, I could use SELECT FOR UPDATE, but what about SQL Server 2008? There's quite a bit of discussion on this SO question, but it seems to be based on SQL Server 2005. Any changes in 2008? What's the recommended strategy?

Edit 1: I think I should clarify what I meant by "independently for each tenant". What I'm looking for is a way for each tenant to have a sequentially ordered set of student IDs. That is, tenant A will have students with IDs 1, 2, 3, ..., and so will tenant B. Think of them as business keys. I have GUIDs for global identity which is hidden from the customer.

Answer 1

Are you trying to guarantee that RegNumber is unique across all tenants? If so, here's what I've done in similar situations. Use the auto-incrementing IDENTITY property, use the seed to identify each tenant in the ones digit and then increment by 10.

CREATE TABLE Student (
   RegNumber INT IDENTITY(1,10),
   ...
)

Tenant 1

IDENTITY(1,10) - generates IDs: 1, 11, 21, 31, ...

Tenant 2

IDENTITY(2,10) - generates IDs: 2, 12, 22, 32, ...

Tenant 3

IDENTITY(3,10) - generates IDs: 3, 13, 23, 33, ...

etc.

This will work up to 10 tenants. If you need to expand beyond that, simply extend this concept by incrementing by 100 (or 1000 or 10000...) instead as needed.

Answer 2

If the number of IDs you need to generate is small (a few hundred), you can just use SELECT MAX(student_id)+1 FROM Student WHERE tenant_id = :tenant. If you add an index with the student and tenant IDs, the optimizer should use that instead of the main table and this will help keep locking to a minimum.

If you're talking thousands of IDs, then you might want to consider making an "ID server" that hands out ID values. At startup it would initialize itself with the max values for each tenant, then just return the next value in the sequence when you asked it for an ID. You'd want to have some way to make it reset itself (by re-reading the database) in case you wind up "wasting" an ID due to a transaction abort or something.