シーケンス番号Sequence Numbers

適用対象: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

シーケンスは、シーケンスが作成された仕様に従って数値のシーケンスを生成するユーザー定義のスキーマ バインド オブジェクトです。A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. 数値のシーケンスは、定義された間隔で昇順または降順に生成され、要求に応じて繰り返されます。The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. ID 列とは異なり、シーケンスはテーブルには関連付けられていません。Sequences, unlike identity columns, are not associated with tables. アプリケーションは、シーケンス オブジェクトを参照して、次の値を受け取ります。An application refers to a sequence object to receive its next value. シーケンスとテーブルの関係は、アプリケーションによって制御されます。The relationship between sequences and tables is controlled by the application. ユーザー アプリケーションは、シーケンス オブジェクトを参照し、複数の行およびテーブルにわたって値キーを調整できます。User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.

シーケンスは、テーブルとは別に、 CREATE SEQUENCE ステートメントを使用して作成されます。A sequence is created independently of the tables by using the CREATE SEQUENCE statement. オプションを使用することにより、増分、最大値、最小値、始点、自動再開機能、およびパフォーマンスを向上させるためのキャッシュ動作を制御できます。Options enable you to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance. オプションの詳細については、 「CREATE SEQUENCE」を参照してください。For information about the options, see CREATE SEQUENCE.

行の挿入時に生成される ID 列値とは異なり、アプリケーションは、 NEXT VALUE FOR 関数を呼び出すことにより、行を挿入する前に次のシーケンス番号を取得できます。Unlike identity column values, which are generated when rows are inserted, an application can obtain the next sequence number before inserting the row by calling the NEXT VALUE FOR function. 番号がテーブルに挿入されない場合でも、シーケンス番号は、NEXT VALUE FOR が呼び出されたときに割り当てられます。The sequence number is allocated when NEXT VALUE FOR is called even if the number is never inserted into a table. NEXT VALUE FOR 関数は、テーブル定義内の列の既定値として使用できます。The NEXT VALUE FOR function can be used as the default value for a column in a table definition. 一度に複数のシーケンス番号の範囲を取得するには、 sp_sequence_get_range を使用します。Use sp_sequence_get_range to get a range of multiple sequence numbers at once.

シーケンスは、任意の整数データ型として定義できます。A sequence can be defined as any integer data type. シーケンスのデータ型を指定しなかった場合、既定で bigint型が使用されます。If the data type is not specified, a sequence defaults to bigint.

シーケンスの使用Using Sequences

シーケンスは、次のシナリオで ID 列の代わりに使用します。Use sequences instead of identity columns in the following scenarios:

  • テーブルへの挿入を行う前に、アプリケーションが数値を必要とする。The application requires a number before the insert into the table is made.

  • アプリケーションが、複数のテーブル間またはテーブル内の複数の列間で、単一の番号シリーズを共有する必要がある。The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.

  • 指定した番号に達したときに、アプリケーションが番号シリーズを再開する必要がある。The application must restart the number series when a specified number is reached. たとえば、1 ~ 10 の値を割り当てた後、アプリケーションは再び 1 ~ 10 の値を割り当てます。For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.

  • アプリケーションが、シーケンス値を別のフィールドで並べ替える必要がある。The application requires sequence values to be sorted by another field. NEXT VALUE FOR 関数は、OVER 句を関数呼び出しに適用できます。The NEXT VALUE FOR function can apply the OVER clause to the function call. OVER 句によって、返される値は OVER 句の ORDER BY 句の順で生成されることが保証されます。The OVER clause guarantees that the values returned are generated in the order of the OVER clause's ORDER BY clause.

  • アプリケーションが、同時に複数の番号を割り当てる必要がある。An application requires multiple numbers to be assigned at the same time. たとえば、アプリケーションで 5 つの連続する番号を予約する必要がある場合などです。For example, an application needs to reserve five sequential numbers. ID 値を要求したときに他のプロセスが番号を同時に発行していた場合、非連続的な ID 値が生成される場合があります。Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. sp_sequence_get_range を呼び出すことにより、シーケンス内の番号を一度に取得できます。Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.

  • 増分値など、シーケンスの仕様を変更する必要がある。You need to change the specification of the sequence, such as the increment value.

制限事項Limitations

値を変更できない ID 列とは異なり、シーケンス値はテーブルへの挿入後に自動的に保護されません。Unlike identity columns, whose values cannot be changed, sequence values are not automatically protected after insertion into the table. シーケンス値が変更されるのを防止するには、テーブルで更新トリガーを使用して、変更をロールバックします。To prevent sequence values from being changed, use an update trigger on the table to roll back changes.

シーケンス値に対して、一意性は自動的には適用されません。Uniqueness is not automatically enforced for sequence values. シーケンス値を再利用する機能は仕様です。The ability to reuse sequence values is by design. テーブルのシーケンス値が一意の値になる必要がある場合は、列に一意なインデックスを作成します。If sequence values in a table are required to be unique, create a unique index on the column. テーブルのグループ全体でテーブルのシーケンス値が一意になる必要がある場合は、更新ステートメントやシーケンス番号の循環によって発生する重複を防ぐためのトリガーを作成します。If sequence values in a table are required to be unique throughout a group of tables, create triggers to prevent duplicates caused by update statements or sequence number cycling.

シーケンス オブジェクトは、定義に従って番号を生成しますが、その番号がどのように使用されるかについては制御しません。The sequence object generates numbers according to its definition, but the sequence object does not control how the numbers are used. トランザクションがロールバックされたとき、シーケンス オブジェクトが複数のテーブルで共有されているとき、またはテーブルのシーケンス番号を使用することなくシーケンス番号が割り当てられたときは、非連続的なシーケンス番号がテーブルに挿入される可能性があります。Sequence numbers inserted into a table can have gaps when a transaction is rolled back, when a sequence object is shared by multiple tables, or when sequence numbers are allocated without using them in tables. CACHE オプションを使用してシーケンス番号を作成する際に予期しないシャットダウン (電源障害など) が発生すると、キャッシュ内のシーケンス番号が失われる可能性があります。When created with the CACHE option, an unexpected shutdown, such as a power failure, can lose the sequence numbers in the cache.

1 つの ステートメント内で同じシーケンス ジェネレーターを指定する NEXT VALUE FOR Transact-SQLTransact-SQL 関数のインスタンスが複数ある場合、これらすべてのインスタンスは、その Transact-SQLTransact-SQL ステートメントによって処理される特定の行について同じ値を返します。If there are multiple instances of the NEXT VALUE FOR function specifying the same sequence generator within a single Transact-SQLTransact-SQL statement, all those instances return the same value for a given row processed by that Transact-SQLTransact-SQL statement. この動作は、ANSI 標準と一貫性があります。This behavior is consistent with the ANSI standard.

シーケンス番号は、現在のトランザクションの範囲外で生成されます。Sequence numbers are generated outside the scope of the current transaction. シーケンス番号を使用しているトランザクションがコミットまたはロールバックされるかどうかにかかわらず、シーケンス番号は使用されます。They are consumed whether the transaction using the sequence number is committed or rolled back. 重複の検証は、レコードが完全に設定された後にのみ実行されます。Duplicate validation only occurs once a record is fully populated. このため、いくつかのケースでは作成中に同じ番号が複数のレコードに使用されますが、その後重複していると識別されることになります。This can result in some cases where the same number is used for more than one record during creation, but then gets identified as a duplicate. このようになったときに他の autonumber 値が後に続くレコードに適用された場合、autonumber 値の間にギャップが生じることがあります。If this occurs and other autonumber values have been applied to subsequent records, this can result in a gap between autonumber values.

一般的な使用方法Typical Use

-2,147,483,648 ~ 2,147,483,647 まで 1 ずつ増分される整数のシーケンス番号を作成するには、次のステートメントを使用します。To create an integer sequence number that increments by 1 from -2,147,483,648 to 2,147,483,647, use the following statement.

CREATE SEQUENCE Schema.SequenceName  
    AS int  
    INCREMENT BY 1 ;  

ID 列のように 1 ~ 2,147,483,647 まで 1 ずつ増分される整数のシーケンス番号を作成するには、次のステートメントを使用します。To create an integer sequence number similar to an identity column that increments by 1 from 1 to 2,147,483,647, use the following statement.

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

シーケンスの管理Managing Sequences

シーケンスの詳細については、「 sys.sequences」を参照してください。For information about sequences, query sys.sequences.

使用例Examples

関連する例については、「CREATE SEQUENCE (Transact-SQL)」、「NEXT VALUE FOR (Transact-SQL)」、および「sp_sequence_get_range」を参照してください。There are additional examples in the topics CREATE SEQUENCE (Transact-SQL), NEXT VALUE FOR (Transact-SQL), and sp_sequence_get_range.

A.A. 1 つのテーブルでシーケンス番号を使用するUsing a sequence number in a single table

次の例では、Test という名前のスキーマ、Orders という名前のテーブル、および CountBy1 という名前のシーケンスを作成した後、NEXT VALUE FOR 関数を使用してテーブルに行を挿入します。The following example creates a schema named Test, a table named Orders, and a sequence named CountBy1, and then inserts rows into the table using the NEXT VALUE FOR function.

--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  

結果セットは次のようになります。Here is the result set.

OrderID Name Qty

1 Tire 2

2 Seat 1

3 Brake 1

B.B. 行を挿入する前に NEXT VALUE FOR を呼び出すCalling NEXT VALUE FOR before inserting a row

次の例では、例 A で作成した Orders テーブルを使用して、 @nextIDという名前の変数を宣言します。次に、NEXT VALUE FOR 関数を使用して、この変数を、次に使用できるシーケンス番号に設定します。Using the Orders table created in example A, the following example declares a variable named @nextID, and then uses the NEXT VALUE FOR function to set the variable to the next available sequence number. アプリケーションは、潜在的な注文の OrderID 番号を顧客に提示した後、注文を確認するなど、注文に関する処理を行うものと考えられます。The application is presumed to do some processing of the order, such as providing the customer with the OrderID number of their potential order, and then validates the order. この処理にどれだけの時間がかかろうとも、またこの処理中に他の注文がいくつ追加されようとも、この接続によって元の番号は保持されます。No matter how long this processing might take, or how many other orders are added during the process, the original number is preserved for use by this connection. 最後に、 INSERT ステートメントによって注文が Orders テーブルに追加されます。Finally, the INSERT statement adds the order to the Orders table.

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.C. 複数のテーブルでシーケンス番号を使用するUsing a sequence number in multiple tables

次の例では、生産ラインの監視プロセスが、ワークショップ全体で発生するイベントの通知を受信すると仮定します。This example assumes that a production-line monitoring process receives notification of events that occur throughout the workshop. 各イベントは、単調に増加する一意な EventID 番号を受け取ります。Each event receives a unique and monotonically increasing EventID number. すべてのイベントは、同じ EventID シーケンス番号を使用します。したがって、すべてのイベントが集約されたレポートで、それぞれのイベントを一意に識別できます。All events use the same EventID sequence number so that reports that combine all events can uniquely identify each event. ただし、イベント データは、イベントの種類に応じて、3 つの異なるテーブルに格納されます。However the event data is stored in three different tables, depending on the type of event. このコード例では、 Auditという名前のスキーマ、 EventCounterという名前のシーケンス、およびそれぞれが EventCounter シーケンスを既定値として使用する 3 つのテーブルを作成します。The code example creates a schema named Audit, a sequence named EventCounter, and three tables which each use the EventCounter sequence as a default value. 次に、3 つのテーブルに行を追加し、クエリを実行して結果を取得します。Then the example adds rows to the three tables and queries the results.

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  
  

結果セットは次のようになります。Here is the result set.

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.D. 結果セットで循環するシーケンス番号を生成するGenerating repeating sequence numbers in a result set

次の例は、シーケンス番号に関する 2 つの機能を示しています。これらは、サイクル処理の機能と、SELECT ステートメントで NEXT VALUE FOR を使用した機能です。The following example demonstrates two features of sequence numbers: cycling, and using NEXT VALUE FOR in a select statement.

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.E. OVER 句を使用して結果セットのシーケンス番号を生成するGenerating sequence numbers for a result set by using the OVER clause

次の例では、結果セットをシーケンス番号列に追加する前に、 OVER 句を使用して Name の順に並べ替えます。The following example uses the OVER clause to sort the result set by Name before it adds the sequence number column.

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.F. シーケンス番号をリセットするResetting the sequence number

例 E では、Samples.IDLabel のシーケンス番号の最初の 79 個の番号が使用されましたExample E consumed the first 79 of the Samples.IDLabel sequence numbers. (AdventureWorks2012 のバージョンによっては結果の数が異なる場合があります)。次のコードを実行すると、後続の 79 個のシーケンス番号 (80 ~ 158) が使用されます。(Your version of AdventureWorks2012 may return a different number of results.) Execute the following to consume the next 79 sequence numbers (80 though 158).

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

次のステートメントを実行すると、Samples.IDLabel シーケンスが最初から開始されます。Execute the following statement to restart the Samples.IDLabel sequence.

ALTER SEQUENCE Samples.IDLabel  
RESTART WITH 1 ;  

再び SELECT ステートメントを実行して、 Samples.IDLabel シーケンス番号が 1 から開始されていることを確認します。Execute the select statement again to verify that the Samples.IDLabel sequence restarted with number 1.

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

G.G. ID からシーケンスにテーブルを変更するChanging a table from identity to sequence

次の例では、1 つのスキーマと、サンプルの 3 つの行が含まれたテーブルを作成します。The following example creates a schema and table containing three rows for the example. 次に、新しい列を追加し、古い列を削除します。Then the example adds a new column and drops the old column.

-- 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  
  

SELECT * を使用する Transact-SQLTransact-SQL ステートメントは、最初の列としてではなく最後の列として、新しい列を受け取ります。Transact-SQLTransact-SQL statements that use SELECT * will receive the new column as the last column instead of the first column. この動作を許容できない場合は、新しいテーブルを作成してデータをそこに移動した後、新しいテーブルに対する権限を再作成する必要があります。If this is not acceptable, then you must create an entirely new table, move the data to it, and then recreate the permissions on the new table.

CREATE SEQUENCE (Transact-SQL)CREATE SEQUENCE (Transact-SQL)

ALTER SEQUENCE (Transact-SQL)ALTER SEQUENCE (Transact-SQL)

DROP SEQUENCE (Transact-SQL)DROP SEQUENCE (Transact-SQL)

IDENTITY (Property) (Transact-SQL)IDENTITY (Property) (Transact-SQL)