Numeri di sequenza
Una sequenza è un oggetto associato a schema definito dall'utente che genera una sequenza di valori numerici in base alla specifica con la quale è stata creata la sequenza. La sequenza di valori numerici viene generata in ordine crescente o decrescente a un intervallo definito e può essere ripetuta (ciclicamente) in base alle esigenze. Le sequenze, a differenza delle colonne di identità, non sono associate a tabelle. In un'applicazione viene fatto riferimento a un oggetto sequenza per recuperare il relativo valore successivo. La relazione tra sequenze e tabelle è controllata dall'applicazione. È possibile che nelle applicazioni utente si faccia riferimento a un oggetto sequenza e vengano coordinate le chiavi dei valori di più righe e tabelle.
Una sequenza viene creata indipendentemente delle tabelle tramite l'istruzione CREATE SEQUENCE. Le opzioni consentono di controllare i valori di incremento, massimo e minimo, il punto iniziale, la funzionalità di riavvio automatico e la memorizzazione nella cache per migliorare le prestazioni. Per ulteriori informazioni sulle opzioni, vedere CREATE SEQUENCE.
A differenza dei valori delle colonne di identità, che vengono generati quando si inseriscono righe, un'applicazione è in grado di ottenere il numero di sequenza successivo prima di inserire la riga chiamando la funzione NEXT VALUE FOR. Il numero di sequenza viene allocato quando viene chiamata tale funzione, anche se il numero non viene mai inserito in una tabella. La funzione NEXT VALUE FOR può essere utilizzata come valore predefinito per una colonna in una definizione della tabella. Utilizzare sp_sequence_get_range per ottenere contemporaneamente un intervallo di più numeri di sequenza.
Una sequenza può essere definita come qualsiasi tipo di dati integer. Se il tipo di dati non viene specificato, viene utilizzata per impostazione predefinita la sequenza bigint.
Utilizzo di sequenze
Utilizzare sequenze anziché colonne di identità negli scenari seguenti:
Nell'applicazione è richiesto un numero prima che venga effettuato l'inserimento nella tabella.
Nell'applicazione è richiesta la condivisione di una singola serie di numeri tra più tabelle o più colonne all'interno di una tabella.
Nell'applicazione deve essere riavviata la serie di numeri quando viene raggiunto un numero specificato. Dopo che sono stati assegnati valori da 1 a 10, ad esempio, viene iniziata di nuovo l'assegnazione di valori da 1 a 10.
Nell'applicazione è richiesto l'ordinamento dei valori di sequenza in base a un altro campo. Dalla funzione NEXT VALUE FOR può essere applicata la clausola OVER alla chiamata di funzione. La clausola OVER garantisce che i valori restituiti vengano generati in base all'ordine della clausola ORDER BY della clausola OVER.
In un'applicazione è richiesta l'assegnazione contemporanea di più numeri. Devono essere riservati, ad esempio, cinque numeri sequenziali. La richiesta di valori di identità potrebbe comportare gap nella serie se sono stati emessi contemporaneamente numeri per altri processi. Una chiamata a sp_sequence_get_range consente di recuperare contemporaneamente diversi numeri della sequenza.
È necessario modificare la specifica della sequenza, ad esempio il valore di incremento.
Limitazioni
A differenza delle colonne di identità, i cui valori non possono essere modificati, i valori di sequenza non sono protetti automaticamente dopo l'inserimento nella tabella. Per evitare la modifica dei valori di sequenza, utilizzare un trigger di aggiornamento sulla tabella per eseguire il rollback delle modifiche.
L'univocità non viene applicata automaticamente per i valori di sequenza. Da progettazione, i valori di sequenza possono essere riutilizzati. Se è necessario che i valori di sequenza in una tabella siano univoci, creare un indice univoco nella colonna. Se è necessario che i valori di sequenza in una tabella siano univoci per un gruppo di tabelle, creare trigger per impedire duplicati a causa di istruzioni di aggiornamento o ripetizione di numeri di sequenza.
L'oggetto sequenza genera numeri in base alla definizione, ma non controlla come vengono utilizzati i numeri. Nei numeri di sequenza inseriti in una tabella possono essere presenti gap quando viene eseguito il rollback di una transazione, quando un oggetto sequenza è condiviso da più tabelle o quando i numeri di sequenza vengono allocati senza essere utilizzati nelle tabelle. In caso di creazione con l'opzione CACHE, un arresto imprevisto quale un'interruzione dell'alimentazione, può provocare la perdita dei numeri di sequenza nella cache.
Se sono presenti più istanze della funzione NEXT VALUE FOR tramite cui viene specificato lo stesso generatore di sequenze in un'unica istruzione Transact-SQL, mediante tutte le istanze viene restituito lo stesso valore per una determinata riga elaborata da questa istruzione Transact-SQL. Questo comportamento è coerente con lo standard ANSI.
Utilizzo tipico
Per creare un numero di sequenza intero che viene incrementato di 1 da -2.147.483.648 a 2.147.483.647, utilizzare l'istruzione seguente.
CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;
Per creare un numero di sequenza intero simile a una colonna di identità che viene incrementato di 1 da 1 a 2.147.483.647, utilizzare l'istruzione seguente.
CREATE SEQUENCE Schema.SequenceName
AS int
START WITH 1
INCREMENT BY 1 ;
Gestione di sequenze
Per informazioni sulle sequenze, eseguire una query in sys.sequences.
Esempi
Sono disponibili esempi aggiuntivi negli argomenti CREATE SEQUENCE (Transact-SQL), NEXT VALUE FOR (Transact-SQL) e sp_sequence_get_range.
A.Utilizzo di un numero di sequenza in una singola tabella
Nell'esempio seguente viene creato uno schema denominato Test, una tabella denominata Orders e una sequenza denominata CountBy1, quindi vengono inserite righe nella tabella utilizzando la funzione NEXT VALUE FOR.
--Create the Test schema
CREATE SCHEMA Test ;
GO
-- Create a table
CREATE TABLE Test.Orders
(OrderID int PRIMARY KEY,
Name varchar(20) NOT NULL,
Qty int NOT NULL);
GO
-- Create a sequence
CREATE SEQUENCE Test.CountBy1
START WITH 1
INCREMENT BY 1 ;
GO
-- Insert three records
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
-- View the table
SELECT * FROM Test.Orders ;
GO
Set di risultati:
OrderID Name Qty
1 Tire 2
2 Seat 1
3 Brake 1
B.Chiamata a NEXT VALUE FOR prima di inserire una riga
Utilizzando la tabella Orders creata nell'esempio A, nell'esempio seguente viene dichiarata una variabile denominata @nextID, quindi viene utilizzata la funzione NEXT VALUE FOR per impostare la variabile sul successivo numero di sequenza disponibile. Si presuppone che nell'applicazione vengano eseguite alcune operazioni di elaborazione dell'ordine, ad esempio fornire al cliente il numero di OrderID dell'ordine potenziale, quindi convalidare l'ordine. Indipendentemente dalla durata di tale elaborazione o dal numero di ordini aggiunti durante il processo, viene mantenuto il numero originale per l'utilizzo da parte di questa connessione. Infine, viene aggiunto l'ordine alla tabella Orders tramite l'istruzione INSERT.
DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Test.CountBy1;
-- Some work happens
INSERT Test.Orders (OrderID, Name, Qty)
VALUES (@NextID, 'Rim', 2) ;
GO
C.Utilizzo di un numero di sequenza in più tabelle
In questo esempio si presuppone che un processo di controllo di una linea di produzione riceva notifica di eventi che si verificano in tutta l'officina. Ogni evento riceve un numero EventID univoco e a incremento progressivo costante. Per tutti gli eventi viene utilizzato lo stesso numero di sequenza EventID, in modo che nei report in cui vengono combinati tutti gli eventi sia possibile identificare in modo univoco ogni evento. I dati degli eventi vengono tuttavia archiviati in tre tabelle diverse, a seconda del tipo di evento. Nell'esempio di codice viene creato uno schema denominato Audit, una sequenza denominata EventCounter e tre tabelle che utilizzano ognuna la sequenza EventCounter come valore predefinito. Nell'esempio vengono quindi aggiunte righe alle tre tabelle e vengono eseguite query sui risultati.
CREATE SCHEMA Audit ;
GO
CREATE SEQUENCE Audit.EventCounter
AS int
START WITH 1
INCREMENT BY 1 ;
GO
CREATE TABLE Audit.ProcessEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EventCode nvarchar(5) NOT NULL,
Description nvarchar(300) NULL
) ;
GO
CREATE TABLE Audit.ErrorEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EquipmentID int NULL,
ErrorNumber int NOT NULL,
EventDesc nvarchar(256) NULL
) ;
GO
CREATE TABLE Audit.StartStopEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
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 threashold 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.') ;
-- The following statement combines all events, though not all fields.
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 ;
GO
Set di risultati:
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.Generazione di numeri di sequenza ripetuti in un set di risultati
Nell'esempio seguente vengono illustrate due caratteristiche dei numeri di sequenza: ripetizione e utilizzo di NEXT VALUE FOR in un'istruzione 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.Generazione di numeri di sequenza per un set di risultati tramite la clausola OVER
Nell'esempio seguente viene utilizzata la clausola OVER per ordinare il set di risultati in base a Name prima che venga aggiunta la colonna di numeri di sequenza.
USE AdventureWorks2012 ;
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.Reimpostazione del numero di sequenza
Nell'esempio E sono stati utilizzati i primi 79 numeri di sequenza Samples.IDLabel. È possibile che la versione in uso di AdventureWorks2012 restituisca un numero di risultati diverso. Eseguire gli elementi seguenti per utilizzare i successivi 79 numeri di sequenza (da 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%' ;
Eseguire l'istruzione seguente per riavviare la sequenza Samples.IDLabel .
ALTER SEQUENCE Samples.IDLabel
RESTART WITH 1 ;
Eseguire di nuovo l'istruzione Select per verificare che la sequenza Samples.IDLabel sia stata riavviata con il numero 1.
SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product
WHERE Name LIKE '%nut%' ;
G.Modifica di una tabella da identità a sequenza
Nell'esempio seguente vengono creati uno schema e una tabella contenente tre righe per l'esempio. Viene quindi aggiunta una nuova colonna e viene eliminata la colonna precedente.
-- Create a schema
CREATE SCHEMA Test ;
GO
-- Create a table
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 three rows into the table
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
-- View the table that will be changed
SELECT * FROM Test.Department ;
GO
-- End of portion creating a sample table
--------------------------------------------------------
-- Add the new column that does not have the IDENTITY property
ALTER TABLE Test.Department
ADD DepartmentIDNew smallint NULL
GO
-- Copy values from the old column to the new column
UPDATE Test.Department
SET DepartmentIDNew = DepartmentID ;
GO
-- Drop the primary key constraint on the old column
ALTER TABLE Test.Department
DROP CONSTRAINT [PK_Department_DepartmentID];
-- Drop the old column
ALTER TABLE Test.Department
DROP COLUMN DepartmentID ;
GO
-- Rename the new column to the old columns name
EXEC sp_rename 'Test.Department.DepartmentIDNew',
'DepartmentID', 'COLUMN';
GO
-- Change the new column to NOT NULL
ALTER TABLE Test.Department
ALTER COLUMN DepartmentID smallint NOT NULL ;
-- Add the unique primary key constraint
ALTER TABLE Test.Department
ADD CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED
(DepartmentID ASC) ;
-- Get the highest current value from the DepartmentID column
-- and create a sequence to use with the column. (Returns 3.)
SELECT MAX(DepartmentID) FROM Test.Department ;
-- Use the next desired value (4) as the START WITH VALUE;
CREATE SEQUENCE Test.DeptSeq
AS smallint
START WITH 4
INCREMENT BY 1 ;
GO
-- Add a default value for the DepartmentID column
ALTER TABLE Test.Department
ADD CONSTRAINT DefSequence DEFAULT (NEXT VALUE FOR Test.DeptSeq)
FOR DepartmentID;
GO
-- View the result
SELECT DepartmentID, Name, GroupName
FROM Test.Department ;
-- Test insert
INSERT Test.Department (Name, GroupName)
VALUES ('Audit', 'Quality Assurance') ;
GO
-- View the result
SELECT DepartmentID, Name, GroupName
FROM Test.Department ;
GO
Le istruzioni Transact-SQL che utilizzano SELECT * riceveranno la nuova colonna come ultima colonna anziché come prima. Se ciò non è accettabile, sarà necessario creare una tabella completamente nuova, spostare i dati in tale tabella, quindi ricreare le autorizzazioni nella nuova tabella.