Separator-Free Strategy

Question

I have faced many problems facing the identification column, and I no longer want to use it. I want a different strategy. Ultimately, I want a brushless column of integers that increment on each row insert. I am not sure how to achieve this. Is it possible to draw from a table that contains a sequence of integers or a function that creates them on the fly? Thanks for helping me with this technical issue.

Answer 1

Why do you need a brushless sequence of values?

The reason SQL Server - and the database as a whole - does not just support this functionality, is hard to do. Essentially, you need to lock the entire table for inserts, slowing everything down.

In general, it is enough to have a primary key identity. Although you get spaces, you can easily calculate the sequence using:

select row_number() over (order by <pkid>)

where <pkid>is the identity, primary key column.

If you insist on hassle-free, then you will need to implement the trigger and perform the calculation yourself. I think that the sequence (without caching) will be enough to produce the next number. However, you need to be very careful about failed inserts.

Answer 2

Gaps are a purely logical problem.

, ( ),
2 , . -

- 13.

1 14.
2 15.
1 ().
2 ().

13 15.

, , , .
, , - , .

Answer 3

. , , . . ROWNUMBER() LEAD() LAG(). , row_number() over ( ID) gaplessID, . :

• , , ROWNUMBER,

• , -

Answer 4

[1] " , " ​​

"":

CREATE TABLE dbo.CustomSequence (
    FullTableName NVARCHAR(257) NOT NULL
        CONSTRAINT PK_CustomSequence_FullTableName
        PRIMARY KEY(FullTableName), -- This means a unique clustered index on this column
    LastID INT NULL,
    Start INT NOT NULL,
    Seed INT NOT NULL
)

/ "" :

INSERT dbo.CustomSequence (FullTableName, LastID, Start, Seed)
VALUES 
(N'dbo.Invoice', NULL, 1, 1),
(N'dbo.InvoiceDetail', NULL, 1, 1);

(!) Tx () UPDATE:

SET XACT_ABORT ON;
BEGIN TRAN;
    ...
    -- Generating new "custom" IDs
    DECLARE @InvNumOfIDs INT = 1, @InvFirstGeneratedIdentityValue INT;
    UPDATE cs
    SET @InvFirstGeneratedIdentityValue = LastID = IIF(cs.LastID IS NULL, cs.Start + (@InvNumOfIDs - 1) * cs.Seed, cs.LastID + @InvNumOfIDs * cs.Seed)
    FROM dbo.CustomSequences cs
    WHERE cs.FullTableName = N'dbo.Invoice';
    ...
    -- Using above IDs
    INSERT dbo.Invoice (InvoiceID, CreateDate, ...)
    VALUES (@InvFirstGeneratedIdentityValue, GETDATE(), ...)
    ...
COMMIT;

. ( - UPDATE - INSERT) Tx. , - INSERT - , Tx , ROLLBACK, - UPDATE, - .

№2: SET XACT_ABORT ON - Tx /.

№ 3: : , ? A: : READ UNCOMMITTED, READ COMMITTED ( SQL Server), REPETABLE READ, SERIALIZABLE.

# 4: Q: A: Tx (. ), (, dbo.Invoice), Tx [e]X[clusive] lock 'dbo.Invoice' dbo.CustomSequence, X loc k Tx → Tx, UPDATE ( dbo.Invoice table) Tx ( COMMIT ROLLBACK).

, Tx (s), (, dbo.Invoice), → INSERT (, dbo.Invoice ).

№ 5: Q: , Tx ( Tx) ? A: Tx (ROLLBACK) → UPDATE dbo.CustomSequence (ROLLBACK) → Tx Tx.

# 6: Q: , Tx ( Tx) ? A: Tx COMMITED → , UPDATE dbo.CustomSequence, → Tx Tx.

# 7: Q: , (UPDATE dbo.CustomSequence INSERT dbo.Invoice) Tx (s)? INSERT , UPDATE dbo.CustomSequence → .

# 8: Q: , Tx → .

# 9: : Tx (s), dbo.Invoice dbo.InvoiceDetail, /.

[2] "" Gap-less (. )

allow concurrent INSERTs vs. block concurrent INSERTs (same target table)
sped                     vs. slow
gaps                     vs. no gaps (see #7 and #8)

[3] , .

Answer 5

. , , . - , script, , . db, , , - , Identity. 47 , - 2024. - .

, . ​​, , . , . , . , :

1 db ( ) 2 id ( 1 1 ) 3 id ( Identity ) NEXT VALUE FOR 4 id NEXT VALUE FOR 5 , , 4, id ,
6 , 7 8 id 9 sp_rename 10

The only part I could not do programmatically was the reordering of the fields that I did in Designer in SSMS. In the future I will use sequences in my databases. I am pretty happy with the solution.