SQL Server Auto Incrementing Identity Per Customer(Tenant) With No Gaps

Question

We have a multi-tenant database which holds multiple customers with each customer having a collection of users like so (Simplified example omitting foreign key specification from users to customers):

CREATE TABLE dbo.Customers
(
   CustomerId INT NOT NULL IDENTITY(1, 1),
   Name NVARCHAR(256) NOT NULL
)


CREATE TABLE dbo.Users
(
   User INT NOT NULL IDENTITY(1, 1),
   CustomerId INT NOT NULL,
)

As part of this design the users are required to have a membership number, when we designed this we decided to use the UserId as the membership number however as with all things this requirement has grown and this is no longer an option for two reasons:

1. After we upgraded to 2012 on each server restart the column is jumping by 1000 values, we have used the workaround shown here: http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is (-t272) to stop that happening but has made us realise that IDENTITY(1, 1) isn't good enough.

2. What we really want now is to ensure that the number is incremented per customer but it has to be permanent and cannot change once assigned.

Obviously a sequence will not work as again it needs to be per customer we also need to enforce a unique constraint on this per customer/user and ensure that the value is never changed once assigned and does not change if a user is deleted (although this shouldn't happen as we don't delete users but mark them as archived, however I want to guarantee this won't affect it).

Below is a sample of what I wrote which can generate the number, but what is the best way to use this or something similar which ensures a unique, sequential value per customer/user without a chance of any issues as users could be created at the same time from different sessions.

  ROW_NUMBER() OVER (ORDER BY i.UserId) + ISNULL((SELECT MAX(users.MembershipNumber)
                                                    FROM [User].Users users
                                                   WHERE users.Customers_CustomerId = i.Customers_CustomerId), 0)

EDIT: Clarification

I apologise I just re-read my question and I did not make this clear enough, we are not looking to replace UserId, we are happy with the gaps and unique per database identifier that is used on all foreign keys, what we are looking to add is a MembershipNumber that will be displayed to the User which is why it needs to be sequential per customer with no gaps as this membership number will be used on cards that are given to the user so needs to be unique.

Answer 1

I want to share my thoughts on it. Please see below.

1. Create seperate table which will holds CustomerID and Count columns like below.

   CREATE TABLE dbo.CustomerSequence
   (
      @CustomerID int,
      @Count  int
   );
   

2. Write some kind of stored proc like below.

   CREATE PROC dbo.usp_GetNextValueByCustomerID
         @CustomerID int,
         @Count int OUTPUT
   AS
   BEGIN
       UPDATE dbo.CustomerSequence
       SET @Count = Count += Count
       WHERE CustomerID = @CustomerID;
   
   END
   

3. Just call the above stored proc by passing CustomerID and get the next Sequence value from it.

Answer 2

Since you already found the problem with Identity columns and how to fix it, I wouldn't say it's not good enough.
However, it doesn't seem to suit your needs since you want the user number to increment per customer.

I would suggest keeping the User column as an Identity column and the primary key of the table, and add another column to specify the User number by customer. this column will also be an integer number with a default value of the result of a UDF that will calculate the next number per customer (see example in this post).

You can protect that value from ever changing by using an instead of update trigger on the users table.

This way to keep a single column primary key, any you have a unique, sequential user number per customer.

Update
Apparently, it is impossible to send column values to a default constraint.
But you can still use an instead of insert trigger to accomplish your goal.

Answer 3

It's because of the default caching sqlserver implements for the sequence objects. See this former thread Identity increment is jumping in SQL Server database

If the gaps are an issue, sql-server2012 has introduced the Sequence object. These you can declare with NOCACHE, so restarting the Server doesn't create gaps.

Answer 4

If you have several users adding new registers simultaneously, I think the best idea is to create a compound Primary key, where the user is a tiny byte (if you have less than 255 users) and the incremental number is an integer. Then, when adding a new register you create a string Primary Key, like 'NN.xxxxxx' . Assuming [Number] is your incremental number and [Code] is the user's code (or local machine assigned number), you assign the new UserId using the DMax function , as follows: NextNumber = Nz(DMax("Number", "clients", "Code=" & Me!code, 0) + 1

    UserId= code & "." & NextNumber

where NN is the user's code "." is used to separate both fields, and XXXX is the new Number