How Would You Implement Sequences in Microsoft SQL Server

SQL Server - Implementing sequences

I thought I'd share my solution. I doesn't deadlock, nor does it produce duplicate values. An important difference between this and my original procedure is that it doesn't create the queue if it doesn't already exist;

ALTER PROCEDURE [dbo].[ReserveSequence]
(
@Name nvarchar(100),
@Count int,
@FirstValue bigint OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;

IF (@Count <= 0)
BEGIN
SET @FirstValue = NULL;
RETURN -1;
END

DECLARE @Result TABLE ([LastValue] bigint)

-- Update the sequence last value, and get the previous one
UPDATE [Sequences]
SET [LastValue] = [LastValue] + @Count
OUTPUT INSERTED.LastValue INTO @Result
WHERE [Name] = @Name;

-- Select the first value
SELECT TOP 1 @FirstValue = [LastValue] + 1 FROM @Result;
END

Are there a best practices of generate a increment sequence number in sql database?

Each RDBMS system usually has it own "auto-number" (usually used for PrimaryKeys) setup.

MySql, Ms-Sql-Server, ORACLE (the article shows the 3 different syntaxes for each...just at one url)

https://www.w3schools.com/sql/sql_autoincrement.asp

PostGres:

https://chartio.com/resources/tutorials/how-to-define-an-auto-increment-primary-key-in-postgresql/

Your question is tagged with (microsoft)sql-server, so I'll paste
that.

Syntax for SQL Server The following SQL statement defines the
"PersonKey" column to be an auto-increment primary key field in the
"Person" table:

CREATE TABLE dbo.Person (
PersonKey int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255)
);

Do not reinvent the wheel.

So unless you are (trying) to INSERT a million rows in < 2 seconds....use what is already there for you.

Opinions.

You should not care that each primary-key is perfectly in sequence. Aka, "gaps" should be Ok.

if you think you need perfect sequencing, you need to ask yourself "why".

a primary key needs to be unique. having "order" helps with indexing.

but

1,2,3,6,7,9,11 are ordered. (4,5,8 are missing, but does it really matter that they are missing?)

I will add that Ms Sql Server has added "sequences" since version 2016 (or was it 2014?)

https://docs.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers?view=sql-server-2016

There are reasons to pick one over the other.

https://www.sqlshack.com/difference-between-identity-sequence-in-sql-server/

The 2 cent explanation is sequence can provide a range of values. And it is not tied to a single table (like identity is).

But practically, you end up having more "gaps" in the values..because once a sequence is requested, the same value is never regenerated...even if the ~potential rows for INSERT do not actually make it as inserted-rows.

Oracle sequence but then in MS SQL Server

There is no exact match.

The equivalent is IDENTITY that you can set as a datatype while creating a table. SQLSERVER will automatically create a running sequence number during insert.
The last inserted value can be obtained by calling SCOPE_IDENTITY() or by consulting the system variable @@IDENTITY (as pointed out by Frans)

If you need the exact equivalent, you would need to create a table and then write a procedure to retun the next value and other operations. See Marks response on pitfalls on this.

Edit:

SQL Server has implemented the Sequence similar to the Oracle. Please refer to this question for more details.

How would you implement sequences in Microsoft SQL Server?

Generate Sequence Code A-Z then 0-9 on SQL Server

Perhaps this is what you want? Numbers have lower value than letters in T-SQL, so when you order them then '9' has a lower number than 'A'. What you could do, however, is check if the value is a valid int value or not, and then order the values that aren't first, and then by the character.

This gives you the following:

WITH Characters AS(
SELECT V.C
FROM (VALUES('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'),
('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'),
('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'),
('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')) V(C)),
Sequences AS(
SELECT CONCAT(C1.C,C2.C,C3.C) AS Sequence,
ROW_NUMBER() OVER (ORDER BY TRY_CONVERT(int,C1.C), C1.C, TRY_CONVERT(int,C2.C), C2.C, TRY_CONVERT(int,C3.C), C3.C) AS RN
FROM Characters C1
CROSS JOIN Characters C2
CROSS JOIN Characters C3)
SELECT *
FROM Sequences;

You didn't state where 0 goes, so I assumed 'AAZ','AA0', 'AA1'...



Related Topics



Leave a reply



Submit