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
START WITH ' + CAST(@serverid AS VARCHAR(19)) + '
INCREMENT BY 1
NO CACHE -- Or switch to CACHE if you''re seeing performance issues on inserts'
-- Create the Sequence
-- Create the Table
create table dbo.Customers
CID int identity not null primary key,
CustomerNumber bigint DEFAULT (NEXT VALUE FOR dbo.NextCustomerNumber)
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.