Denali CTP 1 – co nowego nie tylko dla deweloperów – cz. 3

Autor: Damian Widera, Łukasz Grala

Opublikowano: 2010-12-28

Deweloperskich nowości ciąg dalszy

Prezentujemy kolejną porcję informacji dotyczących nowości dostępnych w SQL Server „Denali” CTP1. Tym razem pokazane zostaną sposoby korzystania z sekwencji.

Sekwencje

Sekwencje są bardzo dobrze znane deweloperom i administratorom, którzy mieli lub mają do czynienia z silnikiem Oracle. Niestety, Oracle nie posiada znanego z SQL Server mechanizmu autonumeracji wierszy w tabelach, opartego na @@IDENTITY. Jedyną znaną nam drogą zapewniającą autonumerowanie wierszy jest wlaśnie wykorzystanie sekwencji.

Autonumerowanie wierszy  w Oracle

W SQL Server sprawa z autonumeracją wierszy jest w miarę prosta. Jedną z metod jest zadeklarowanie przy danej kolumnie, że będzie ona przechowywała informacje o autogenerowanym numerze wiersza – patrz przykład poniżej:

CREATE TABLE [USER] (         [U_ID] [int] IDENTITY (1, 1) NOT NULL,         [FNAME] [varchar] (100) NOT NULL,         [LNAME] [varchar] (100) NOT NULL ) GO ALTER TABLE [USER] WITH NOCHECK ADD         CONSTRAINT [PK_USER] PRIMARY KEY CLUSTERED         (                [U_ID]         ) GO

Nie wspominamy oczywiście o potencjalnych pułapkach związanych z używaniem tak zdefiniowanego mechanizmu autonumerowania wierszy.

W silniku Oracle, jak już wspomniano, nie można użyć tej metody. Zamiast tego należy wykonać trzy kroki:

• zadeklarować tabelę,
• utworzyć sekwencję,
• sterować wykonywaniem sekwencji, np. w wyzwalaczu.

Poniżej zamieszczamy kod, który opisuje powyższe trzy kroki.

CREATE TABLE [USER] (         [U_ID] [int] IDENTITY (1, 1) NOT NULL,         [FNAME] [varchar] (100) NOT NULL,         [LNAME] [varchar] (100) NOT NULL ) GO   ALTER TABLE [USER] WITH NOCHECK ADD         CONSTRAINT [PK_USER] PRIMARY KEY CLUSTERED         (                [U_ID]         ) GO CREATE SEQUENCE SEQ_USER_ID         MINVALUE 1         MAXVALUE 999999999999999999         START WITH 1         INCREMENT BY 1         NOCACHE ;   CREATE OR REPLACE TRIGGER TRG_USER_INSERT  BEFORE INSERT ON USER  FOR EACH ROW  BEGIN IF :new.U_ID IS NULL THEN  SELECT SEQ_USER_ID.nextvalINTO :new.U_ID FROM DUAL;  END IF;  END;  /  COMMIT;

Użycie sekwencji w SQL Server

SQL Server Denali pozwala na użycie sekwencji w bazie danych, nie usuwając poprzedniego mechanizmu autonumeracji. Ogólnie sekwencja może być przedstawiona jako licznik, który jest zwiększany bądź zmniejszany o zadaną liczbę. Sekwencja może być użyta w wielu tabelach, ponieważ jej widoczność jest na poziomie bazy danych, w której została utworzona.

Najprostsza definicja sekwencji wygląda następująco:

CREATE SEQUENCE Domyslna ;

Sekwencja ta rozpocznie nadawanie numerów od liczby -2147483648, a zakończy na 2147483647 z iteracją o 1. Podane wartości są odpowiednio minimalną i maksymalną wartością dla typu integer.

Zazwyczaj jednak sekwencje mają jako swój punkt początkowy liczbę 0 i inkrementowane są o 1. Jest to przypadek odpowiadający użyciu @@IDENTITY w tabeli:

CREATE SEQUENCE Zwiekszam_o_1

    START WITH 1

    INCREMENT BY 1 ;

GO

Nic nie stoi na przeszkodzie, aby sekwencja zawierała tylko liczby mniejsze od zera. Na poniższym przykładzie pokazano definicję sekwencji, która jako punkt początkowy ma wartość 0 i będzie zmniejszana o 1 po każdym wywołaniu.

CREATE SEQUENCE Zmniejszam_o_1

    START WITH 0

    INCREMENT BY -1 ;

GO

Można zdefiniować sekwencję inaczej, manipulując wartością, o jaką zostanie zwiększony lub zmniejszony licznik po wywołaniu sekwencji. Poniżej zaprezentowano przykład sekwencji, w której numerowanie rozpocznie się od liczby 1 i będzie rosło o 10:

CREATE SEQUENCE Zwiekszam_o_10

    START WITH 1

    INCREMENT BY 10 ;

GO

Sekwencje domyślnie posiadają typ danych integer i na nim przeprowadzają operację liczenia. Można jednak wybrać inny typ danych dla utworzenia sekwencji. Na przykładzie poniżej pokazano, jak zadeklarować sekwencję o typie danych smallint:

CREATE SEQUENCE SmallIntSequence

    AS smallint ;

Do tworzenie sekwencji można użyć takich typów danych, jak:

• smallint
• decimal
• numeric (skala = 0)
• tinyint
• int
• bigint

Próba użycia innego typu danych zakończy się wyświetleniem komunikatu błędu:

CREATE SEQUENCE VarcharSequence     AS varchar; ----- Msg 2724, Level 16, State 1, Line 1 Parameter or variable '' has an invalid data type. Msg 11702, Level 16, State 1, Line 1 The sequence object 'VarcharSequence' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, or any user-defined data type that is based on one of the above integer data types.

Zbudujmy zatem sekwencję FullSequence, która będzie zawierała pełną definicję, zgodną z Books Online. Definicja posiada kilka parametrów, które omówiono poniżej:

CREATE SEQUENCE FullSequence     AS decimal(3,0)     START WITH 90     INCREMENT BY 5     MINVALUE 60 MAXVALUE 105     CYCLE     CACHE 3  ;

Sekwencja FullSequence posiada następujące cechy:

• jest typu danych decimal o precyzji 3,
• punktem startowym sekwencji, od którego zostanie rozpoczęte liczenie, jest 90,
• sekwencja będzie zwiększana o 5,
• maksymalna wartość, do której zostanie zwiększona sekwencja, będzie równa 150, bo wynika to z parametru Maxvalue,
• po przekroczeniu wartości maksymalnej rozpocznie się liczenie od wartości MinValue, która wynosi 60,
• decyzję o ponownym liczeniu przechowuje parametr Cycle. Ustawienie go na wartość 0 (false) powoduje, że sekwencja wykona się tylko raz,
• parametr Cache określa, ile wygenerowanych liczb trzymanych będzie w pamięci. Zwiększa to szybkość działania sekwencji, ale powoduje ryzyko utraty większej ilości wygenerowanych numerów w przypadku np. restartu serwera.

Po zadeklarowaniu sekwencji pozostaje ją tylko użyć. Do użycia sekwencji służy polecenie SELECT NEXT VALUE FOR, które pokazano poniżej:

SELECT NEXT VALUE FOR FullSequence;

Sekwencje posiadają jeden dynamiczny widok zarządczy, w którym przechowywane są wszystkie informacje konfiguracyjne o danej sekwencji, a także aktualna wartość licznika:

SELECT * FROM sys.sequences

WHERE name = 'Domyslna' ;