Pro generování ID (primárních klíčů) v aplikačních databázích nebo primárních klíčů – surrogate key  v datových skladech existuje řada přístupů. Většinou jsou používány GUIDy nebo numerická ID. Guidy jsou používány hlavně v aplikačních databázích a jsou generovány například pomocí funkce NEWID().

To má své výhody a nevýhody. Největší nevýhodou je výkon sql dotazů, protože generování GUID a obecně např. spojování tabulek je pomalejší ve srovnání s číselnými datovými typy. Řešením může být používání sequence – generátoru ID.

Pokud chceme vygenerovat číselná ID máme několik způsobů jak to udělat:

• Založení sloupce tabulky jako IDENTITY(1,1)
• Generování ID pomocí nějaké funkce např ROW_NUMBER()
• Generování ID pomocí SEQUENCE

Sekvence jsou tedy jedním ze způsobů, jak vygenerovat v databázové tabulce ID. Tento článek bude zaměřen na to, jak sekvenci založit a používat. Také nakonec podíváme na to, jak sekvence performuje ve srovnání s jinými metodami generování číselných klíčů.

Synaxe založení Sequence

Při založení sekvence stanovujeme jméno, datový typ, číslo od kterého sekvence začíná, increment, minimální/maximální hodnotu sekvence a případně CACHE se stanovením size (případně NO CACHE).

CREATE SEQUENCE [dbo].[Sequence_Name]
AS [INT]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100000
CACHE 1000

Zavolání Sekvence a získání další hodnoty

Poté co založíme sekvenci si můžeme zavoláním sekvence postupně vyžadovat následující pořadová čísla ze sequence a ta nám je vrátí.

SELECT NEXT VALUE FOR [dbo].[Sequence_Name];

První dotaz nám vrátí 1 (sequence startuje od 1). Druhý sql dotaz by nám vrátil 2 (1 již byla podána a increment je 1) a tak dále. Pokud bychom narazili na limit MAXVALUE 100000, dostaneme error hlášku “The sequence object ‘Sequence_Name’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated“.

Sekvence můžeme volat v kombinaci s FROM, v tomto případě očíslujeme již existující záznamy v nějaké tabulce. Současně můžeme při generování zohlednit seřazení (v tomto případě budeme číslování začínat od nejnižšího datumu).

SELECT
  NEXT VALUE FOR [dbo].[Sequence_Name] OVER (ORDER BY [Datum]) AS [ID]
  ,[Column]
FROM [Tabulka]

Restart Sekvence

Sekvenci můžeme pochopitelně restartovat celou nebo ji vrátit zpět k určité hodnotě.

Restartování celé Sequence:

Výsledkem bude 1

Stanovení nové starting value:

Výsledkem bude 151

Srovnání SEQUENCE, IDENTITY a ROW_NUMBER(), CACHE Performance Problem

Níže se můžete podívat jak performují jednotlivé metody generování ID. Testoval jsem generování ID do tabulky obsahující 400 000 záznamů (číslo INT) a ID byla přiřazena po seřazení ORDER BY. Výsledek je ve vteřinách, jedná se teda o velmi hrubé testování. Výsledky se mohou samozřejmě lišit s velikostí a koplexitou tabulek. Pro potřeby toho co chci ukázat to ale dostačuje.

Poznatky

1. Podle výsledků je nejrychlejší generování pomocí ROW_NUMBER OVER (ORDER BY). Tady bych rád upozornil, že tato metoda nebude performovat tak dobře nad rozsáhými tabulkami. Naopak dobře bude fungovat nad (velmi) jednoduchými tabulkami nebo dotazy, kde nebudeme používat složité ORDER BY operace.
2. IDENTITY bude v průměru fungovat dobře všude v případě, že performance generování klíčů nepotřebujeme řešit a nechceme se o ni moc aktivně starat.
3. U SEQUENCE toho můžeme hodně získat, ale i hodně zkazit. To, jak performuje SEQUENCE je závislé na nastavení CACHE Size. Je vidět, že Cache je při používání sekvencí povinnost a to platí dvojnásob v případě, kdy číslujeme větší množství záznamů v rámci jedné transakce.
4. CACHE je potřeba nastavit s argumentem Size (a tu je potřeba vhodně stanovit), protože pokud CACHE založíme bez něj, chová se sekvence jako by CACHE nastavena nebyla vůbec.

V dokumentaci Microsoft se píše, že Sequence je možné založit pouze s property CACHE a v tomto případě se Size sama nastaví, ale není tomu tak.

Závěr: SEQUENCE jsou skvělý způsob jak generovát klíče za předpokladu používání CACHE a současně je potřeba nastavit CACHE s dostatečnou Size. To platí zejména pro případ, kdy chceme jednorázově vygenerovat klíče pro velké množství záznamů.