Sunday, September 27, 2009

High/Low generator with SQL

In current project, we use High/Low generator strategy to generate identifiers for entities. (Fabio Maulo wrote a good explanation why using identity is a bad thing). The question I sometimes see in NHibernate forums is what to do if some other applications also use the same database. Usual answer is - implement some service which would generate id, and use it in that applications.
Yesterday I met similar issue. What I needed to do is to insert some fake data using SQL only. For that purpose I wrote such stored procedure:

IF OBJECT_ID('dbo.GenerateHiLoIdRange') IS NOT NULL
  DROP PROCEDURE dbo.GenerateHiLoIdRange
GO
CREATE PROCEDURE dbo.GenerateHiLoIdRange (
    @IdsCount INT
)
AS
/* =============================================================== */
/* == Generates a range of identifiers using High/Low strategy. == */
/* =============================================================== */
BEGIN
    SET NOCOUNT ON;
    
    IF @IdsCount <= 0
    BEGIN
        RAISERROR('@IdsCount should be positive. But was %d', 16, -1, @IdsCount)
        RETURN(-100);
    END

    DECLARE @NextHi INT
    DECLARE @MaxLo  INT
    SET @MaxLo = 100  -- Hardcoded value used over all tables in a database.
 
    BEGIN TRANSACTION UpdateHiLoTable
        
        SET @NextHi = (
            SELECT next_hi 
            FROM hibernate_unique_key WITH (UPDLOCK, ROWLOCK)
        )
        
        UPDATE hibernate_unique_key 
        SET next_hi = @NextHi + 1 + (@IdsCount - 1) / @MaxLo
        WHERE next_hi = @NextHi
        
    COMMIT TRANSACTION UpdateHiLoTable
    
    SELECT @NextHi * @MaxLo + IntValue
    FROM   dbo.Integers(@IdsCount - 1)
END
GO

Main points of procedure:
  • It uses hard coded MaxLo value (100) , which is used over all entities in our application.
  • Calculates new NextHi value based on requested identifiers count and updates hibernate_unique_key table in transaction.
  • To return a result set containing generated identifiers procedure needs some sort of "Integers" table. In my case i simulate such table with a table-valued function.
IF OBJECT_ID('dbo.Integers') IS NOT NULL
  DROP FUNCTION dbo.Integers
GO
CREATE FUNCTION dbo.Integers (
    @MaxValue INT
)
RETURNS @Integers TABLE (IntValue INT NOT NULL)
AS
/* =============================================================== */
/* ==   Returns the [0,  @MaxValue] range of integer values.    == */
/* =============================================================== */
BEGIN
    IF @MaxValue > 9999
    BEGIN
      RETURN;
    END;
    
    DECLARE @Digits TABLE
    (
        Digit INT NOT NULL PRIMARY KEY
    )

    INSERT INTO @Digits (Digit) VALUES (0)
    INSERT INTO @Digits (Digit) VALUES (1)
    INSERT INTO @Digits (Digit) VALUES (2)
    INSERT INTO @Digits (Digit) VALUES (3)
    INSERT INTO @Digits (Digit) VALUES (4)
    INSERT INTO @Digits (Digit) VALUES (5)
    INSERT INTO @Digits (Digit) VALUES (6)
    INSERT INTO @Digits (Digit) VALUES (7)
    INSERT INTO @Digits (Digit) VALUES (8)
    INSERT INTO @Digits (Digit) VALUES (9)
    
    INSERT INTO @Integers
    SELECT Number
    FROM  
        (SELECT 
            Thousands.Digit * 1000 + 
            Hundreds.Digit * 100 + 
            Tens.Digit * 10 + 
            Ones.Digit AS Number
         FROM   
            @Digits Thousands
            CROSS JOIN @Digits Hundreds
            CROSS JOIN @Digits Tens
            CROSS JOIN @Digits Ones
        ) Integers
    WHERE Number <= @MaxValue
    ORDER BY Number
        
    RETURN;
END
GO
Note that number of integers in a function is limited to 10000. But that is what I need.

Now I can generate identifiers in t-sql batches:

CREATE TABLE #TempId (Id INT NOT NULL)

SELECT * FROM hibernate_unique_key

INSERT INTO #TempId
  EXEC dbo.GenerateHiLoIdRange 250

SELECT Id AS Id, 
    'PersonName' +  CAST(ROW_NUMBER() OVER(ORDER BY Id) - 1 AS VARCHAR(255)) AS PersonName
FROM #TempId

DROP TABLE #TempId

In my sandbox database I see such results:

No comments: