SQL Server – Custom Sequences with Replication and Auto-Increment

auto-incrementidentityreplicationsequencesql server

What is the best method to generate a Custom Auto-Generated Sequence with SQL Server?

Here is my requirement:

  • My application is hosted on 10 independent servers which are not
    interconnected.

  • Each instance should generate a sequence number, and all these data
    will be merged to all databases using SQL Server's merge replication.

  • The sequence generated should be unique across all the servers.

To achieve this, I have created a function to prefix a server id to the sequence number and used this as a computed column. But when I add data, I am getting the following exception:

"Maximum stored procedure, function, trigger, or view nesting level
exceeded (limit 32)."

Here is my table and function. Kindly suggest a better method.

create table Customers
(
    CID int identity not null primary key,
    CustomerName varchar(50)
)

ALTER function [dbo].[NextCustomerNumber]()
returns bigint
as
begin
    declare @lastval bigint
    declare @serverId int
    declare @newval bigint
    set @lastval = (select max(CustomerNumber) from Customers)
    set @serverId= 1 -- Server ID pulls from some settings
    set @newval=CONVERT(bigint, (CAST(@serverId as varchar(2))+CONVERT( varchar(10),(RIGHT(@lastval,(LEN(@lastval)-LEN(@serverId)))+1))))
    return @newval

end

Alter table Customers add CustomerNumber as dbo.NextCustomerNumber()

Best Answer

Why don't you just use an actual SEQUENCE which was introduced to SQL Server with 2012?

The following approach overloads the BIGINT value where the leading 10 digits represent the server IP address after being converted to a BIGINT (via the method outlined in this blog post by SQLDenis) and the right-most 10 digits represent the CustomerID local to the instance. A BIGINT can have up to a maximum of 19 digits, but because the leftmost digit will never be higher than 4 to start the sequence this allows for far more customer numbers than the max value of an INT allows for (the comments in the code explain it in a little more detail).

The only requirement for this approach is that this solution only be deployed to, at most, one instance per server (because IP won't be unique across instances hosted on the same server). As stated above, this approach still allows up to the INT maximum of 2,147,483,647 customers per instance in your solution. Because the CID column in the tables is defined as a INT in your question, I'm assuming this is acceptable. It's still about as fast of an approach as you'll find anywhere else.

-- This sequence will have a dynamic starting number **PER SERVER** based on IP
-- Max value for BigINT is 9,223,372,036,854,775,807
-- Max value for INT is                2,147,483,647
-- Max IP (255.255.255.255) to BIGINT: 4,294,967,295
-- IP to padded BIGINT:    4,294,967,295
--                       *             1,000,000,000
--                       ---------------------------
-- Max Starting Value =    4,294,967,295,000,000,000

-- Variables to capture server id (IP) and generate sequence DDL statement
DECLARE @serverid BIGINT, @sequenceDSQL VARCHAR(4000)

-- Convert IP to BIGINT
SELECT TOP(1) @serverid =
        (CONVERT(bigint, PARSENAME(local_net_address,1)) +
         CONVERT(bigint, PARSENAME(local_net_address,2)) * 256 +
         CONVERT(bigint, PARSENAME(local_net_address,3)) * 65536 +
         CONVERT(bigint, PARSENAME(local_net_address,4)) * 16777216)
FROM sys.dm_exec_connections AS c
WHERE c.local_net_address IS NOT NULL;

-- Shift Server IP value to left of the BIGINT value
SET @serverid = @serverid * 1000000000

-- Generate Dynamic SQL DDL for CREATE SEQUENCE Statement
SET @sequenceDSQL = '
CREATE SEQUENCE dbo.NextCustomerNumber
AS BIGINT
    START WITH ' + CAST(@serverid AS VARCHAR(19)) + '
    INCREMENT BY 1
    NO MAXVALUE
    NO CACHE -- Or switch to CACHE if you''re seeing performance issues on inserts'

-- Create the Sequence
EXEC(@sequenceDSQL)

-- Create the Table
create table dbo.Customers
(
    CID int identity not null primary key,
    CustomerName varchar(50),
    CustomerNumber bigint DEFAULT (NEXT VALUE FOR dbo.NextCustomerNumber)
)
GO

If you want to also factor in the port number for the @serverid value, you're going to reach a limit of what a BIGINT can hold, so I would suggest coming up with some other unique identifier for the server other than IP/port. However, my code should show a general approach that you can adjust as needed to solve your problem.

Related Question