2

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()
  • What about SQL Server's GUID type? – Vérace Oct 18 '17 at 8:30
  • I was using GUID.. but it is degrading the performance with its large size and string Foreign key search – Tibin Thomas Oct 18 '17 at 9:15
  • OK - you have a 64 bit server? Use the first 5 bits for your server_1d - or 1 - 100 or whatever, of an integer for your server 1 - 16, 1 - 32, 1- 100 or whatever for your server_id, and the rest for your records. That way you can be sure that they will be unique for all sites! – Vérace Oct 18 '17 at 11:56
  • 1
    @Vérace GUIDs are terrible... just terrible... I have no qualms admitting my deep-seeded hatred for this data type because it's so often misused. Most importantly to this question though, they're not guaranteed to be unique, especially given the right circumstances. – John Eisbrener Oct 18 '17 at 14:47
  • @JohnEisbrener I'm saying OK, scrap the GUID idea. Use, say, the first two digits of a BIGINT 10 - 99 as the first two digits of a key and then use the other ~ 16 digits for the unique bit within each server! Aside: re. that article: it said 4 collisions per million for GUIDs in SQL Server 2008? Does that still hold? – Vérace Oct 18 '17 at 17:09
5

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.

  • I tried the same like this: ALTER TABLE dbo.Customers ADD CONSTRAINT Const_SQ1 DEFAULT FORMAT(NEXT VALUE FOR dbo.NextCustomerNumber,'10#' ) FOR CustomerNumber; but it needs to maintain the server id separate on each databases. Since my database is scaling, I am trying to avoid this had-coded server id on DB adn make it dynamic. Thanks for the support. – Tibin Thomas Oct 19 '17 at 9:59
  • 1
    @TibinThomas That's a simple enough fix. Check my update to see an approach that allows you to run the same code against different servers with the desired results. Depending on your needs, you may need to adjust how the leading digits of the BIGINT are being computed, but hopefully the general approach is what you're looking for. – John Eisbrener Oct 19 '17 at 14:44
  • Depending on your server/instance naming conventions, I'd be tempted to use a number from the server name (i.e. MSSQL01, MSSQL02, etc.) for an obvious identifier. – LowlyDBA Oct 19 '17 at 16:16
0

You might consider using an AFTER TRIGGER to populate the CustomerNumber. This logic still uses your custom server number which is retrieved from some setting and concatenates it to the front of the identity value that has already been generated.

CREATE TRIGGER dbo.Customers_GenerateCustomerNumber ON dbo.Customers
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ServerNumber INT = (
            SELECT ServerNumber
            FROM ServerInformation
            )

    UPDATE a
    SET a.CustomerNumber = (
            SELECT convert(BIGINT, convert(VARCHAR(20), @ServerNumber) --Value of Server retrieved from some setting
                    + convert(VARCHAR(20), b.CID))
            )
    FROM Customers a
    JOIN inserted b ON b.CID = a.CID
END
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.