Autonumeracja – Sekwencje w MsSQL

1. Autonumeracja w SQL Server za pomocą Sekwencji

Do czego służą sekwencje w SQL Server? Sekwencje w SQL Server służą do numeracji wartości w kolumnie. Wyobraź sobie prostą sytuację. Tworzysz nową tabelę. Podczas insertu każdego nowego rekordu w jednej z kolumn wstawia się kolejny numer dla każdego nowego rekordu np 1,2,3,4 (…) lub też 1, 11, 21(…). Możesz też wstawić liczby w kolejności odwrotnej np: 999, 998, 997 (…). Poniżej szczegółowy schemat tworzenia sekwencji w SQL Server.

--tomaszkenig.pl
CREATE SEQUENCE [schema_name . ] sequence_name  
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]  
    [ START WITH <constant> ]  
    [ INCREMENT BY <constant> ]  
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ <constant> ] } | { NO CACHE } ]  
    [ ; ]

Jakie atrybuty możemy przypisać do sekwencji?

• built_in_integer_type – określenie typu danych. Musisz wybrać spośród typów rzeczywistych (integer). Jeśli nie ustawisz nic, sekwencja przyjmie typ danych bigint i tak powienieneś na początku postępować. Możesz też użyć zdefiniowanego przez Ciebie typu danych.
• START WITH – określenie wartości początkowej sekwencji. Zazwyczaj będzie to u nas liczba 1.
• INCREMENT BY – określasz, co ile wartości sekwencja będzie się zwiększać. Np jeśli ustawisz 10, sekwencja będzie przeskakiwać właśnie o tyle wartości. Jeśli wprowadzisz tutaj liczbę ujemną, sekwencja będzie podawać coraz to niższe liczby np 999, 998, 997 (…).
• MINVALUE – minimalna liczba generowana przez sekwencję.
• MAXVALUE – wartość maksymalna liczb generowanej przez sekwencję. Jeśli nie określisz tej wartości, SQL Server przyjmie za maksimum granicę typu danych.
• CYCLE / NO CYCLE – określasz, czy po uzyskaniu wartości maksymalnej sekwencja ma się tworzyć od nowa. Pamiętaj, że jeśli wartości w sekwencji się skończą, a sekwencja będzie miała wartość NO CYCLE – SQL Server zwróci błąd przy pobieraniu wartości.
• CACHE – określasz liczbę wartości przechowywanych przez sekwencję do użycia „na szybko”. Jeśli wprowadziśz tu np liczbę 100, sekwencja będzie już przechowywać 100 wartości, których nie musi tworzyć na bieżąco.

2. Przykład sekwencji w SQL Server

Stwórzmy prostą sekwencję. Wartość początkowa to liczba 100000. Sekwencja powinna przechowywać 50 wartości w cach’u. Za każdym wywołaniem wartość generowana powinna wzrastać o 10. Powinna także powtarzać się w cyklu po osiągnięciu wartości maksymalnej równej 1000000.

--tomaszkenig.pl
CREATE SEQUENCE MY_SEQ 
START WITH 100000
INCREMENT BY 10
MINVALUE 0
MAXVALUE 1000000
CYCLE 
CACHE 50
;

W jaki sposób wywołać wartości z sekwencji i wstawić je np do tabeli? Do tego celu możemy użyć prostego skryptu. Pierwszy skrypt podaje bieżącą wartość w sekwencji. Drugi skrypt zwraca wartość kolejną.

--tomaszkenig.pl
--wartość bieżąca
select current_value from sys.sequences
where name = 'my_seq';

--wartość kolejna
select next value for my_seq;

Jeśli chcemy, by dana kolumna w tabeli była automatycznie uzupełniana wartością z sekwencji podczas każdego insertu, powinniśmy ustawić wartość DEFAULT na kolumnie. Poniżej przykład tworzenia takiej tabeli i insertu wartości utworzonej z sekwencji:

--tomaszkenig.pl
CREATE TABLE MY_TABLE (COLUMN_1 BIGINT default next value for my_seq,
COLUMN_2 NVARCHAR(255));
go

INSERT INTO MY_TABLE (COLUMN_2) VALUES ( 'sql server tutorial 1');
INSERT INTO MY_TABLE (COLUMN_2) VALUES ( 'sql server tutorial 2');
INSERT INTO MY_TABLE (COLUMN_2) VALUES ( 'sql server tutorial 3');

select * from MY_TABLE;

3. Sekwencje w kolumnie z kluczem głównym PRIMARY KEY

Jeśli chcesz, by sekwencja była wprowadzana do kolumny określonej jako Klucz Główny tabeli, jedyne, co powinieneś zrobić to dodać do tabeli Constraint PRIMARY KEY. Przykład tworzenia takiej tabeli dla wcześniej utworzonej sekwencji poniżej.

--tomaszkenig.pl
DROP TABLE MY_TABLE;

CREATE TABLE MY_TABLE (COLUMN_1 BIGINT constraint prm_key PRIMARY KEY default next value for my_seq,
COLUMN_2 NVARCHAR(255));
go

INSERT INTO MY_TABLE (COLUMN_2) VALUES ( 'sql server tutorial 1');
INSERT INTO MY_TABLE (COLUMN_2) VALUES ( 'sql server tutorial 2');
INSERT INTO MY_TABLE (COLUMN_2) VALUES ( 'sql server tutorial 3');

select * from MY_TABLE;

Artykuł stanowi częśc kursu SQL Server. Jeśli masz jakieś pytania, możesz je zamieścić na forum bez logowania lub zostawić komentarz. Artykuł zamieszczam także w wersji angielskiej na tej stronie: SQL Server CREATE SEQUENCE.