CODEWITHSUNDEEP
sql-serverazureazure-sql-database
Kayleigh Swift
Kayleigh Swift

Reputation: 25

How to create a sequential auto-number field in Azure SQL without IDENTITY?

I've tried all sorts of things but so far I've been unable to find a solution to this...

We have an Orders table with an OrderID field that is currently an IDENTITY value. This has been causing problems for my customer because although the IDs are mostly sequential, there are occasionally big jumps of 1000 IDs. I researched this and discovered that it's a "feature" of SQL, and there isn't much I can do about it on Azure.

I want to therefore assign the OrderId values myself, but this is easier said than done. Orders come in from a number of sources, including a multi-threaded importer, so I have concurrency issues to deal with.

I have tried this stored procedure that uses a transaction and a table lock, but when I call SaveChanges() (in Entity Framework), I still get foreign key violations.

CREATE PROCEDURE orders.InsertOrder(
    ...
)
AS
BEGIN
    DECLARE @OrderId INT

    BEGIN TRANSACTION

        SELECT @OrderId = MAX(OrderId) + 1
        FROM orders.Orders
        WITH (TABLOCK, HOLDLOCK)

        INSERT INTO [orders].[Orders]
           ([OrderId],
            ...
           )
     VALUES
       (@OrderId,
        ...
        );
    COMMIT TRANSACTION
END

I'm lost as to what to try next...

Upvotes: 1

Views: 903

Answers (1)

usr
usr

Reputation: 171246

foreign key violations

I hope you mean "primary key" because foreign key violations have nothing to do with this problem...

TABLOCK is a shareable read lock. Use TABLOCKX for a quick fix.

I think you should be using UPDLOCK, ROWLOCK, HOLDLOCK which is a well-known standard lock hint sequence.

Note, that you don't need sprocs for this at all. You simply need to execute the statement

    SELECT MAX(OrderId)
    FROM orders.Orders
    WITH (UPDLOCK, ROWLOCK, HOLDLOCK)

and discard the result. Then, the proper locks are taken and you can use normal EF functionality to continue.

Upvotes: 1

Related Questions