Search code examples
sql-serveruniqueidentifier

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

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.

Solution

  • 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.