Pořadová čísla

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed Instance

Tento článek vysvětluje, jak používat pořadová čísla v SQL Serveru, Azure SQL Database a Azure SQL Managed Instance. Sekvence je uživatelem definovaný objekt vázaný na schéma, který generuje posloupnost číselných hodnot podle specifikace, pomocí které byla sekvence vytvořena.

Přehled

Posloupnost číselných hodnot je generována vzestupně nebo sestupně v definovaném intervalu a může cyklovat (opakovat), pokud je to požadováno. Sekvence, na rozdíl od sloupců identity, nejsou přidružené k tabulkám. Aplikace odkazuje na sekvenční objekt, který získá další hodnotu. Aplikace řídí relaci mezi sekvencemi a tabulkami. Uživatelské aplikace můžou odkazovat na sekvenční objekt a koordinovat klíče hodnot v několika řádcích a tabulkách.

Sekvence se vytváří nezávisle na tabulkách pomocí příkazu CREATE SEQUENCE. Možnosti umožňují řídit přírůstek, maximální a minimální hodnoty, výchozí bod, funkci automatického restartování a ukládání do mezipaměti, aby se zlepšil výkon. Pro informace o možnostech viz CREATE SEQUENCE.

Na rozdíl od hodnot sloupců identity, které se generují při vložení řádků, může aplikace získat další pořadové číslo před vložením řádku voláním funkce NEXT VALUE FOR. Pořadové číslo je přiděleno, když je volán příkaz NEXT VALUE FOR, i když se číslo nikdy nevloží do tabulky. Funkci NEXT VALUE FOR lze použít jako výchozí hodnotu sloupce v definici tabulky. Pomocí sp_sequence_get_range můžete získat rozsah více pořadových čísel najednou.

Posloupnost může být definována jako libovolný datový typ pro celá čísla. Pokud není zadán datový typ, pořadí je standardně bigint.

Použití sekvencí

V následujících scénářích používejte sekvence místo sloupců identit:

• Aplikace vyžaduje číslo před vložením do tabulky.

• Aplikace vyžaduje sdílení jedné řady čísel mezi více tabulkami nebo více sloupci v tabulce.

• Aplikace musí restartovat číselnou řadu při dosažení zadaného čísla. Například po přiřazení hodnot 1 až 10 začne aplikace znovu přiřazovat hodnoty 1 až 10.

• Aplikace vyžaduje řazení sekvenčních hodnot podle jiného pole. Funkce NEXT VALUE FOR může použít klauzuli OVER na volání funkce. Klauzule OVER zaručuje, že vrácené hodnoty jsou generovány v pořadí určeném klauzulí ORDER BY klauzule OVER.

• Aplikace vyžaduje, aby bylo současně přiřazeno více čísel. Například aplikace musí rezervovat pět sekvenčních čísel. Žádosti o hodnoty identity mohou vést k mezerám v řadě, pokud byly čísla přidělena současně jinými procesy. Vyvolání sp_sequence_get_range může načíst několik čísel v posloupnosti najednou.

• Potřebujete změnit specifikaci sekvence, například hodnotu přírůstku.

Omezení

Na rozdíl od sloupců identit, jejichž hodnoty nelze změnit, nejsou po vložení do tabulky automaticky chráněny sekvenční hodnoty. Pokud chcete zabránit změnám sekvenčních hodnot, použijte trigger aktualizace v tabulce k vrácení změn zpět.

Jedinečnost se automaticky nevynucuje pro sekvenční hodnoty. Možnost opakovaného použití sekvenčních hodnot je navržená. Pokud musí být hodnoty sekvence v tabulce jedinečné, vytvořte jedinečné omezení sloupce. Pokud se hodnoty sekvence v tabulce vyžadují, aby byly jedinečné v celé skupině tabulek, vytvořte triggery, které brání duplicitám způsobeným příkazy update nebo cyklem pořadového čísla.

Sekvenční objekt generuje čísla podle jeho definice, ale sekvenční objekt neřídí způsob použití čísel. Pořadová čísla vložená do tabulky můžou mít mezery, když se transakce vrátí zpět, když je sekvenční objekt sdílen více tabulkami nebo když jsou pořadová čísla přidělena bez jejich použití v tabulkách. Při vytváření pomocí možnosti CACHE může neočekávané vypnutí, například selhání napájení, ztratit pořadová čísla v mezipaměti.

Pokud existuje více instancí funkce NEXT VALUE FOR určující stejný generátor sekvence v rámci jednoho příkazu Transact-SQL, vrátí všechny tyto instance stejnou hodnotu pro daný řádek zpracovaný tímto příkazem Transact-SQL. Toto chování je konzistentní se standardem ANSI.

Pořadová čísla se generují mimo rozsah aktuální transakce. Jsou spotřebovány bez ohledu na to, jestli je transakce s využitím pořadového čísla potvrzena nebo vrácena zpět. Duplicitní ověření probíhá pouze po úplném naplnění záznamu. Výsledkem může být v některých případech, kdy se stejné číslo používá pro více než jeden záznam během vytváření, ale pak se identifikuje jako duplikát. Pokud k tomu dojde a další hodnoty automatického čísla byly použity na další záznamy, může to vést k mezerě mezi hodnotami automatického číslování.

Typické použití

Chcete-li vytvořit celočíselné pořadové číslo, které se zvýší o 1 z -2 147 483 648 na 2 147 483 647, použijte následující příkaz.

CREATE SEQUENCE Schema.SequenceName
    AS int
    INCREMENT BY 1 ;

Pokud chcete vytvořit celočíselné pořadové číslo podobné sloupci identity, který se zvýší o 1 z 1 na 2 147 483 647, použijte následující příkaz.

CREATE SEQUENCE Schema.SequenceName
    AS int
    START WITH 1
    INCREMENT BY 1 ;

Správa sekvencí

Informace o sekvencích získáte dotazem na sys.sequences.

Příklady

Existují další příklady v článcích CREATE SEQUENCE, NEXT VALUE FORa sp_sequence_get_range.

A. Použití pořadového čísla v jedné tabulce

Následující příklad vytvoří schéma s názvem Test, tabulku s názvem Orders a sekvenci s názvem CountBy1 a potom vloží řádky do tabulky pomocí funkce NEXT VALUE FOR.

CREATE SCHEMA Test;
GO

CREATE TABLE Test.Orders
(
    OrderID INT PRIMARY KEY,
    Name VARCHAR (20) NOT NULL,
    Qty INT NOT NULL
);
GO

CREATE SEQUENCE Test.CountBy1
    START WITH 1
    INCREMENT BY 1;
GO

INSERT Test.Orders (OrderID, Name, Qty)
VALUES            ( NEXT VALUE FOR Test.CountBy1, 'Tire', 2);

INSERT test.Orders (OrderID, Name, Qty)
VALUES            ( NEXT VALUE FOR Test.CountBy1, 'Seat', 1);

INSERT test.Orders (OrderID, Name, Qty)
VALUES            ( NEXT VALUE FOR Test.CountBy1, 'Brake', 1);
GO

SELECT *
FROM Test.Orders;
GO

Tady je sada výsledků.

OrderID Name Qty

1 Tire 2

2 Seat 1

3 Brake 1

B. Volání funkce DALŠÍ_HODNOTA_PRO před vložením řádku

Pomocí tabulky Orders vytvořené v příkladu A následující příklad deklaruje proměnnou s názvem @nextIDa potom pomocí funkce NEXT HODNOTA FOR nastaví proměnnou na další dostupné pořadové číslo. Předpokládá se, že aplikace zpracuje určitou objednávku, například poskytne zákazníkovi OrderID číslo jeho potenciální objednávky a pak objednávku ověří. Bez ohledu na to, jak dlouho může zpracování trvat nebo kolik dalších objednávek se během procesu přidá, zachová se původní číslo pro použití tímto připojením. Nakonec příkaz INSERT přidá pořadí do tabulky Orders.

DECLARE @NextID AS INT;

SET @NextID =  NEXT VALUE FOR Test.CountBy1;

INSERT Test.Orders (OrderID, Name, Qty)
VALUES            (@NextID, 'Rim', 2);

C. Použití pořadového čísla ve více tabulkách

Tento příklad předpokládá, že proces monitorování výrobní linky obdrží oznámení o událostech, ke kterým dochází v průběhu workshopu. Každá událost obdrží jedinečné a monotónně rostoucí číslo EventID. Všechny události používají stejné EventID pořadové číslo, aby sestavy, které kombinují všechny události, mohly jedinečně identifikovat každou událost. Data události se ale ukládají do tří různých tabulek v závislosti na typu události. Příklad kódu vytvoří schéma s názvem Audit, posloupnost pojmenovanou EventCountera tři tabulky, které používají EventCounter posloupnost jako výchozí hodnotu. Pak příklad přidá řádky do tří tabulek a dotazuje výsledky.

CREATE SCHEMA Audit;
GO

CREATE SEQUENCE Audit.EventCounter
    AS INT
    START WITH 1
    INCREMENT BY 1;
GO

CREATE TABLE Audit.ProcessEvents
(
    EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED,
    EventTime DATETIME DEFAULT (getdate()) NOT NULL,
    EventCode NVARCHAR (5) NOT NULL,
    Description NVARCHAR (300) NULL
);
GO

CREATE TABLE Audit.ErrorEvents
(
    EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED,
    EventTime DATETIME DEFAULT (getdate()) NOT NULL,
    EquipmentID INT NULL,
    ErrorNumber INT NOT NULL,
    EventDesc NVARCHAR (256) NULL
);
GO

CREATE TABLE Audit.StartStopEvents
(
    EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED,
    EventTime DATETIME DEFAULT (getdate()) NOT NULL,
    EquipmentID INT NOT NULL,
    StartOrStop BIT NOT NULL
);
GO

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES                      (248, 0);

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES                      (72, 0);

INSERT Audit.ProcessEvents (EventCode, Description)
VALUES                    (2735, 'Clean room temperature 18 degrees C.');

INSERT Audit.ProcessEvents (EventCode, Description)
VALUES                    (18, 'Spin rate threshold exceeded.');

INSERT Audit.ErrorEvents (EquipmentID, ErrorNumber, EventDesc)
VALUES                  (248, 82, 'Feeder jam');

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES                      (248, 1);

INSERT Audit.ProcessEvents (EventCode, Description)
VALUES                    (1841, 'Central feed in bypass mode.');

SELECT EventID,
       EventTime,
       Description
FROM Audit.ProcessEvents
UNION
SELECT EventID,
       EventTime,
       EventDesc
FROM Audit.ErrorEvents
UNION
SELECT EventID,
       EventTime,
       CASE StartOrStop WHEN 0 THEN 'Start' ELSE 'Stop' END
FROM Audit.StartStopEvents
ORDER BY EventID;

Tady je sada výsledků.

EventID EventTime Description

1 2009-11-02 15:00:51.157 Start

2 2009-11-02 15:00:51.160 Start

3 2009-11-02 15:00:51.167 Clean room temperature 18 degrees C.

4 2009-11-02 15:00:51.167 Spin rate threshold exceeded.

5 2009-11-02 15:00:51.173 Feeder jam

6 2009-11-02 15:00:51.177 Stop

7 2009-11-02 15:00:51.180 Central feed in bypass mode.

D. Generování opakujících se pořadových čísel v sadě výsledků

Následující příklad ukazuje dvě funkce sekvenčních čísel: cyklistika a použití NEXT VALUE FOR v příkazu select.

CREATE SEQUENCE CountBy5
    AS TINYINT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 5
    CYCLE;
GO

SELECT  NEXT VALUE FOR CountBy5 AS SurveyGroup,
       Name
FROM sys.objects;
GO

E. Generování pořadových čísel pro sadu výsledků pomocí klauzule OVER

Následující příklad používá klauzuli OVER k seřazení sady výsledků podle Name před přidáním sloupce pořadového čísla.

USE AdventureWorks2022;
GO

CREATE SCHEMA Samples;
GO

CREATE SEQUENCE Samples.IDLabel
    AS TINYINT
    START WITH 1
    INCREMENT BY 1;
GO

SELECT  NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID,
       ProductID,
       Name,
       ProductNumber
FROM Production.Product
WHERE Name LIKE '%nut%';

F. Resetování pořadového čísla

Příklad E spotřeboval prvních 79 pořadových čísel Samples.IDLabel. (Vaše verze AdventureWorks2022 může vrátit jiný počet výsledků.) Spuštěním následujícího příkazu spotřebujte další 79 pořadových čísel (80 až 158).

SELECT  NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID,
       ProductID,
       Name,
       ProductNumber
FROM Production.Product
WHERE Name LIKE '%nut%';

Spuštěním následujícího příkazu restartujte Samples.IDLabel sekvenci.

ALTER SEQUENCE Samples.IDLabel
RESTART WITH 1 ;

Spusťte příkaz select znovu a ověřte, že se sekvence Samples.IDLabel restartovala s číslem 1.

SELECT  NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID,
       ProductID,
       Name,
       ProductNumber
FROM Production.Product
WHERE Name LIKE '%nut%';

G. Změna tabulky z identity na sekvenci

Následující příklad vytvoří schéma a tabulku obsahující tři řádky pro příklad. Potom příklad přidá nový sloupec a zahodí starý sloupec.

CREATE SCHEMA Test;
GO

CREATE TABLE Test.Department
(
    DepartmentID SMALLINT IDENTITY (1, 1) NOT NULL,
    Name NVARCHAR (100) NOT NULL,
    GroupName NVARCHAR (100) NOT NULL CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED (DepartmentID ASC)
);
GO

INSERT Test.Department (Name, GroupName)
VALUES                ('Engineering', 'Research and Development');
GO

INSERT Test.Department (Name, GroupName)
VALUES                ('Tool Design', 'Research and Development');
GO

INSERT Test.Department (Name, GroupName)
VALUES                ('Sales', 'Sales and Marketing');
GO

SELECT *
FROM Test.Department;
GO

ALTER TABLE Test.Department
    ADD DepartmentIDNew SMALLINT NULL;
GO

UPDATE Test.Department
    SET DepartmentIDNew = DepartmentID;
GO

ALTER TABLE Test.Department DROP CONSTRAINT [PK_Department_DepartmentID];

ALTER TABLE Test.Department DROP COLUMN DepartmentID;
GO

EXECUTE sp_rename 'Test.Department.DepartmentIDNew', 'DepartmentID', 'COLUMN';
GO

ALTER TABLE Test.Department ALTER COLUMN DepartmentID SMALLINT NOT NULL;

ALTER TABLE Test.Department
    ADD CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED (DepartmentID ASC);

SELECT MAX(DepartmentID)
FROM Test.Department;

CREATE SEQUENCE Test.DeptSeq
    AS SMALLINT
    START WITH 4
    INCREMENT BY 1;
GO

ALTER TABLE Test.Department
    ADD CONSTRAINT DefSequence DEFAULT ( NEXT VALUE FOR Test.DeptSeq) FOR DepartmentID;
GO

SELECT DepartmentID,
       Name,
       GroupName
FROM Test.Department;

INSERT Test.Department (Name, GroupName)
VALUES                ('Audit', 'Quality Assurance');
GO

SELECT DepartmentID,
       Name,
       GroupName
FROM Test.Department;

Transact-SQL příkazy, které používají SELECT *, dostanou nový sloupec jako poslední sloupec místo prvního sloupce. Pokud to není přijatelné, musíte vytvořit zcela novou tabulku, přesunout do ní data a pak znovu vytvořit oprávnění pro novou tabulku.

Související obsah

• CREATE SEQUENCE (Transact-SQL)
• ALTER SEQUENCE (Transact-SQL)
• DROP SEQUENCE (Transact-SQL)
• CREATE TABLE (Transact-SQL) IDENTITY (Vlastnost)