ID 列のレプリケートReplicate Identity Columns

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

IDENTITY プロパティを列に割り当てると、 MicrosoftMicrosoft SQL ServerSQL Server では、その ID 列を含むテーブルに挿入された新しい行に対して連続する番号が自動的に生成されます。When you assign an IDENTITY property to a column, MicrosoftMicrosoft SQL ServerSQL Server automatically generates sequential numbers for new rows inserted in the table containing the identity column. 詳細については、「IDENTITY (プロパティ) (Transact-SQL)」を参照してください。For more information, see IDENTITY (Property) (Transact-SQL). ID 列は主キーの一部に含まれる場合があるため、ID 列の値が重複しないようにすることが重要です。Because identity columns might be included as a part of the primary key, it is important to avoid duplicate values in the identity columns. 複数のノードで更新されるレプリケーション トポロジで ID 列を使用するには、レプリケーション トポロジ内の各ノードで異なる範囲の ID 値を使用して、重複が生じないようにする必要があります。To use identity columns in a replication topology that has updates at more than one node, each node in the replication topology must use a different range of identity values, so that duplicates do not occur.

たとえば、パブリッシャーに 1 ~ 100 の範囲を、サブスクライバー A に 101 ~ 200 の範囲を、サブスクライバー B に 201 ~ 300 の範囲を、それぞれ割り当てることができます。For example, the Publisher could be assigned the range 1-100, Subscriber A the range 101-200, and Subscriber B the range 201-300. パブリッシャーに行が挿入され、ID 値がたとえば 65 の場合、この値が各サブスクライバーにレプリケートされます。If a row is inserted at the Publisher and the identity value is, for example, 65, that value is replicated to each Subscriber. レプリケーションによって各サブスクライバーにデータが挿入されると、サブスクライバー テーブル内の ID 列の値は増えずに、リテラル値 65 が挿入されます。When replication inserts data at each Subscriber, it does not increment the identity column value in the Subscriber table; instead, the literal value 65 is inserted. ID 列の値が増えるのは、ユーザーによる挿入のみで、レプリケーション エージェントの挿入では増えません。Only user inserts, but not replication agent inserts cause the identity column value to be incremented.

レプリケーションではすべての種類のパブリケーションおよびサブスクリプションの ID 列が処理され、列は手動で管理することも、レプリケーションで自動で管理することもできます。Replication handles identity columns across all publication and subscription types, allowing you to manage the columns manually or have replication manage them automatically.

注意

パブリッシュされたテーブルに ID 列を追加することはサポートされていません。これは、列がサブスクライバーにレプリケートされると集約されなくなる可能性があるからです。Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber. パブリッシャーの ID 列の値は、影響を受けるテーブルの行が物理的に格納されている順序に依存します。The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. サブスクライバーで行が同じように格納されているとは限らないため、同じ行で ID 列の値が異なる可能性があります。The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows.

ID 範囲の管理オプションの指定Specifying an Identity Range Management Option

レプリケーションには、次の 3 つの ID 範囲の管理オプションがあります。Replication offers three identity range management options:

  • 自動。Automatic. サブスクリプションでの更新を使用するマージ レプリケーションおよびトランザクション レプリケーションで使用されます。Used for merge replication and transactional replication with updates at the Subscriber. パブリッシャーおよびサブスクライバーのサイズの範囲を指定し、レプリケーションによって新しい範囲の割り当てを自動的に管理します。Specify size ranges for the Publisher and Subscribers, and replication automatically manages the assignment of new ranges. レプリケーションによって、サブスクライバーの ID 列に NOT FOR REPLICATION オプションが設定されるため、サブスクライバーではユーザーの挿入のみによって値が増えます。Replication sets the NOT FOR REPLICATION option on the identity column at the Subscriber, so that only user inserts cause the value to be incremented at the Subscriber.

    注意

    サブスクライバーは、パブリッシャーと同期して新しい範囲を受け取る必要があります。Subscribers must synchronize with the Publisher to receive new ranges. サブスクライバーには ID の範囲が自動的に割り当てられるため、サブスクライバーが新しい範囲を繰り返し要求すると、サブスクライバーは ID 範囲をすべて使用してしまう可能性があります。Because Subscribers are assigned identity ranges automatically, it is possible for any Subscriber to exhaust the entire supply of identity ranges if it repeatedly requests new ranges.

  • 手動。Manual. サブスクライバーでの更新を使用しないスナップショット レプリケーションおよびトランザクション レプリケーション、ピア ツー ピア トランザクション レプリケーション、またはアプリケーションがプログラムを介して ID 範囲を管理する必要がある場合に使用されます。Used for snapshot and transactional replication without updates at the Subscriber, peer-to-peer transactional replication, or if your application must control identity ranges programmatically. 手動による管理を指定した場合は、範囲をパブリッシャーおよび各サブスクライバーに割り当て、初期の範囲が使用された場合は新しい範囲を割り当てる必要があります。If you specify manual management, you must ensure that ranges are assigned to the Publisher and each Subscriber and that new ranges are assigned if the initial ranges are used. レプリケーションによって、サブスクライバーの ID 列に NOT FOR REPLICATION オプションが設定されます。Replication sets the NOT FOR REPLICATION option on the identity column at the Subscriber.

  • [なし] :None. このオプションは、 SQL ServerSQL Server の以前のバージョンとの互換性が必要な場合のみ使用することをお勧めします。また、このオプションは、トランザクション パブリケーションのストアド プロシージャ インターフェイスからのみ使用できます。This option is recommended only for backwards compatibility with earlier versions of SQL ServerSQL Server and is available only from the stored procedure interface for transactional publications.

ID 範囲の管理のオプションを指定するには、「ID 列の管理」を参照してください。To specify an identity range management option, see Manage Identity Columns.

ID 範囲の割り当てAssigning Identity Ranges

マージ レプリケーションとトランザクション レプリケーションでは、範囲の割り当てにさまざまな方法を使用します。これらの方法についてこのセクションで説明します。Merge replication and transactional replication use different methods for assigning ranges; these methods are described in this section.

ID 列をレプリケートする場合は 2 種類の範囲を考慮する必要があります。1 つはパブリッシャーおよびサブスクライバーに割り当てる範囲で、もう 1 つは列のデータ型の範囲です。There are two types of ranges to take into account when replicating identity columns: the ranges assigned to the Publisher and Subscribers, and the range of the data type in the column. 以下の表に、ID 列で通常使用されるデータ型で利用可能な範囲を示します。The following table shows the ranges available for the data types typically used in identity columns. この範囲は、トポロジ内のすべてのノードで使用されます。The range is used across all nodes in a topology. たとえば、1 から開始され、増分が 1 に設定された smallint を使用すると、挿入の最大数は、パブリッシャーとすべてのサブスクライバーで 32,767 になります。For example, if you use smallint starting at 1 with an increment of 1, the maximum number of inserts is 32,767 for the Publisher and all Subscribers. 実際の挿入数は、使用する値にギャップがあるかどうか、およびしきい値が使用されているかどうかによって変わります。The actual number of inserts depends on whether there are gaps in the values used and whether a threshold value is used. しきい値の詳細については、「マージ レプリケーション」「キュー更新サブスクリプションを使用するトランザクション レプリケーション」のセクションを参照してください。For more information about thresholds, see the following sections "Merge Replication" and "Transactional Replication with Queued Updating Subscriptions".

挿入が db_owner 固定データベース ロールのメンバーによって実行されている場合は、パブリッシャーがその挿入後に ID 範囲をすべて使用すると、新しい範囲が自動的に割り当てられます。If the Publisher exhausts its identity range after an insert, it can automatically assign a new range if the insert was performed by a member of the db_owner fixed database role. 挿入がそのロール以外のユーザー、ログ リーダー エージェント、マージ エージェントによって実行されている場合は、db_owner ロールのメンバーであるユーザーが sp_adjustpublisheridentityrange (Transact-SQL) を実行する必要があります。If the insert was performed by a user not in that role, the Log Reader Agent, Merge Agent, or a user who is a member of the db_owner role must run sp_adjustpublisheridentityrange (Transact-SQL). トランザクション パブリケーションの場合は、ログ リーダー エージェントを実行して新しい範囲を自動で割り当てる必要があります (既定ではエージェントは継続して実行されます)。For transactional publications, the Log Reader Agent must be running to automatically allocate a new range (the default is for the agent to run continuously).

警告

大規模な一括挿入処理中、レプリケーション トリガーが起動されるのは、挿入の行ごとではなく 1 度だけです。During a large batch insert the replication trigger is fired only once, not for each row of the insert. その結果、大規模な挿入処理中に ID 範囲が使い果たされると、 INSERT INTO ステートメントなど、挿入ステートメントが失敗する場合があります。This can lead to a failure of the insert statement if an identity range is exhausted during an large insert, such as an INSERT INTO statement.

データ型Data type 範囲Range
tinyinttinyint 自動管理ではサポートされません。Not supported for automatic management
smallintsmallint -2^15 (-32,768) ~ 2^15-1 (32,767)-2^15 (-32,768) to 2^15-1 (32,767)
intint -2^31 (-2,147,483,648) ~ 2^31-1 (2,147,483,647)-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
bigintbigint -2^63 (-9,223,372,036,854,775,808) ~ 2^63-1 (9,223,372,036,854,775,807)-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
decimal および numericdecimal and numeric -10^38+1 ~ 10^38-1-10^38+1 through 10^38-1

注意

複数のテーブルで使用できる自動的に増分する番号、またはテーブルを参照せずにアプリケーションから呼び出すことができる自動的に増分する番号を作成するには、「シーケンス番号」を参照してください。To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.

マージ レプリケーションMerge Replication

ID 範囲はパブリッシャーで管理されて、マージ エージェントによってサブスクライバーに反映されます (再パブリッシュ階層では、範囲はルートのパブリッシャーとリパブリッシャーで管理されます)。Identity ranges are managed by the Publisher and propagated to Subscribers by the Merge Agent (in a republishing hierarchy, ranges are managed by the root Publisher and the republishers). ID 値はパブリッシャーのプールから割り当てられます。The identity values are assigned from a pool at the Publisher. パブリケーションの新規作成ウィザードまたは sp_addmergearticle (Transact-SQL) を使用して ID 列を含むアーティクルをパブリケーションに追加する場合は、次の値を指定します。When you add an article with an identity column to a publication in the New Publication Wizard or by using sp_addmergearticle (Transact-SQL), you specify values for:

  • @identity_range パラメーター。パブリッシャーと、クライアント サブスクリプションを使用するサブスクライバーの両方に最初に割り当てる ID 範囲のサイズを制御します。The @identity_range parameter, which controls the identity range size initially allocated both to the Publisher and to Subscribers with client subscriptions.

    注意

    以前のバージョンの SQL ServerSQL Server を実行しているサブスクライバーの場合、このパラメーターは (@pub_identity_range パラメーターではなく)、再パブリッシュ サブスクライバーの ID 範囲のサイズも制御します。For Subscribers running previous versions of SQL ServerSQL Server, this parameter (rather than the @pub_identity_range parameter) also controls the identity range size at republishing Subscribers.

  • @pub_identity_range パラメーター。サーバー サブスクリプションを使用するサブスクライバーに割り当てる再パブリッシュ用の ID 範囲のサイズを指定します (データを再パブリッシュする場合は必須)。The @pub_identity_range parameter, which controls the identity range size for republishing allocated to Subscribers with server subscriptions (required for republishing data). サーバー サブスクリプションを使用するすべてのサブスクライバーは、実際にはデータを再パブリッシュしない場合でも、再パブリッシュ用の範囲を受け取ります。All Subscribers with server subscriptions receive a range for republishing, even if they don't actually republish data.

  • @threshold パラメーター。SQL Server CompactSQL Server Compact または SQL ServerSQL Server の以前のバージョンに対するサブスクリプションに、ID の新しい範囲が必要かどうかを判断するために使用されます。The @threshold parameter, which is used to determine when a new range of identities is required for a subscription to SQL Server CompactSQL Server Compact or a previous version of SQL ServerSQL Server.

たとえば、@identity_range に 10,000 を、@pub_identity_range に 500,000 を指定できます。For example, you could specify 10000 for @identity_range and 500000 for @pub_identity_range. SQL Server 2005 (9.x)SQL Server 2005 (9.x) 以降のバージョンを実行しているパブリッシャーとすべてのサブスクライバー (サーバー サブスクリプションを使用するサブスクライバーを含む) には、プライマリ範囲として 10,000 が割り当てられます。The Publisher and all Subscribers running SQL Server 2005 (9.x)SQL Server 2005 (9.x) or a later version, including the Subscriber with the server subscription, are assigned a primary range of 10000. サーバー サブスクリプションを使用するサブスクライバーには、500,000 のプライマリ範囲も割り当てられます。これは、再パブリッシュ サブスクライバーと同期するサブスクライバーで使用できます (再パブリッシュ サブスクライバーのパブリケーション内のアーティクルに対しては、@identity_range@pub_identity_range@threshold を指定する必要もあります)。The Subscriber with the server subscription is also assigned a primary range of 500000, which can be used by Subscribers that synchronize with the republishing Subscriber (you must also specify @identity_range, @pub_identity_range, and @threshold for the articles in the publication at the republishing Subscriber).

SQL Server 2005 (9.x)SQL Server 2005 (9.x) 以降のバージョンを実行している各サブスクライバーは、セカンダリ ID 範囲も受け取ります。Each Subscriber running SQL Server 2005 (9.x)SQL Server 2005 (9.x) or a later version also receives a secondary identity range. セカンダリ範囲のサイズはプライマリ範囲のサイズと同じです。プライマリ範囲がすべて使用されると、セカンダリ範囲が使用されて、マージ エージェントによって新しい範囲がサブスクライバーに割り当てられます。The secondary range is equal in size to the primary range; when the primary range is exhausted, the secondary range is used, and the Merge Agent assigns a new range to the Subscriber. 新しい範囲はセカンダリ範囲となり、サブスクライバーで ID 値が使用される限りこのプロセスは継続されます。The new range becomes the secondary range, and the process continues as the Subscriber uses identity values.

キュー更新サブスクリプションを使用するトランザクション レプリケーションTransactional Replication with Queued Updating Subscriptions

ID 範囲はディストリビューターで管理されて、ディストリビューション エージェントによってサブスクライバーに反映されます。Identity ranges are managed by the Distributor and propagated to Subscribers by the Distribution Agent. ID 値はディストリビューターのプールから割り当てられます。The identity values are assigned from a pool at the Distributor. プールのサイズは、データ型のサイズと、ID 列に対して使用される増分に基づいています。The pool size is based on the size of the data type and the increment used for the identity column. パブリケーションの新規作成ウィザードまたは sp_addarticle (Transact-SQL) を使用して ID 列を含むアーティクルをパブリケーションに追加する場合は、次の値を指定します。When you add an article with an identity column to a publication in the New Publication Wizard or by using sp_addarticle (Transact-SQL), you specify values for:

  • @identity_range パラメーター。すべてのサブスクライバーに最初に割り当てる ID 範囲のサイズを指定します。The @identity_range parameter, which controls the identity range size initially allocated to all Subscribers.

  • @pub_identity_range パラメーター。パブリッシャーに割り当てる ID 範囲のサイズを指定します。The @pub_identity_range parameter, which controls the identity range size allocated to the Publisher.

  • @threshold パラメーター。サブスクリプションに ID の新しい範囲が必要になるタイミングを決定するために使用します。The @threshold parameter, which is used to determine when a new range of identities is required for a subscription.

たとえば、@pub_identity_range には 10,000、@identity_range には 1,000 (サブスクライバーの更新数が少ないと仮定)、@threshold には 80% を指定できます。For example, you could specify 10000 for @pub_identity_range, 1000 for @identity_range (assuming fewer updates at the Subscriber), and 80 percent for @threshold. サブスクライバーの挿入数が 800 (1,000 の 80%) を超えると、サブスクライバーに新しい範囲が割り当てられます。After 800 inserts at a Subscriber (80 percent of 1000), a Subscriber is assigned a new range. パブリッシャーの挿入数が 8,000 を超えると、パブリッシャーに新しい範囲が割り当てられます。After 8000 inserts at the Publisher, the Publisher is assigned a new range. 新しい範囲が割り当てられると、テーブル内の ID 範囲値にはギャップが生じます。When a new range is assigned, there will be a gap in the identity range values in the table. 高いしきい値を指定すると、ギャップは小さくなりますが、システムのフォールト トレランスは低くなります。ディストリビューション エージェントが何らかの理由で実行できない場合、サブスクライバーで ID の消費がさらに進みやすくなります。Specifying a higher threshold results in smaller gaps, but the system is less fault-tolerant: if the Distribution Agent cannot run for some reason, a Subscriber could more easily run out of identities.

手動で ID 範囲を管理する場合の範囲の割り当てAssigning ranges for manual identity range management

手動による ID 範囲の管理を指定した場合は、パブリッシャーと各サブスクライバーがそれぞれ異なる ID 範囲を使用することが必要です。If you specify manual identity range management, you must ensure that the Publisher and each Subscriber use different identity ranges. たとえば、 IDENTITY(1,1)と定義されている ID 列を含むパブリッシャーのテーブルがあるとします。ID 列は 1 から開始し、行が挿入されるたびに 1 ずつ増えていきます。For example, consider a table at the Publisher with an identity column defined as IDENTITY(1,1): the identity column starts at 1 and is incremented by 1 each time a row is inserted. パブリッシャーのテーブルの行数が 5,000 で、アプリケーションを実行中にテーブルがある程度大きくなると考えられる場合、パブリッシャーでは範囲 1 ~ 10,000 を使用できます。If the table at the Publisher has 5,000 rows, and you expect some growth in the table over the life of the application, the Publisher could use the range 1-10,000. 2 つのサブスクライバーの場合、サブスクライバー A では 10,001 ~ 20,000 を使用し、サブスクライバー B では 20,001 ~ 30,000 を使用できます。Given two Subscribers, Subscriber A could use 10,001-20,000, and Subscriber B could use 20,001-30,000.

サブスクライバーが、スナップショットまたは別の方法で初期化された後に、DBCC CHECKIDENT を実行してサブスクライバーに ID 範囲の開始位置を割り当てます。After a Subscriber is initialized with a snapshot or through another means, execute DBCC CHECKIDENT to assign the Subscriber a starting point for its identity range. たとえば、サブスクライバー A では、 DBCC CHECKIDENT('<TableName>','reseed',10001)を実行します。For example, at Subscriber A, you would execute DBCC CHECKIDENT('<TableName>','reseed',10001). サブスクライバー B では、 CHECKIDENT('<TableName>','reseed',20001)を実行します。At Subscriber B, you would execute CHECKIDENT('<TableName>','reseed',20001).

新しい範囲をパブリッシャーまたはサブスクライバーに割り当てるには、DBCC CHECKIDENT を実行し、新しい値を指定してテーブルを再作成します。To assign new ranges to the Publisher or Subscribers, execute DBCC CHECKIDENT and specify a new value to reseed the table. 新しい範囲を割り当てる必要がある時点を指定するには、いくつかの方法があります。You should have some way to determine when a new range must be assigned. たとえば、ノードがその範囲をすべて使用しそうになったことを検出するメカニズムをアプリケーションに用意し、DBCC CHECKIDENT を使用して新しい範囲を割り当てることができます。For example, your application could have a mechanism that detects when a node is about to use up its range and assign a new range using DBCC CHECKIDENT. また、CHECK 制約を追加して、使用される範囲 ID 値が不足しそうになると行を追加できなくなるようにすることもできます。You can also add a check constraint to ensure that a row cannot be added if it would cause an out of range identity value to be used.

データベース復元後の ID 範囲の処理Handling Identity Ranges after a Database Restore

自動 ID 範囲の管理を使用している場合、サブスクライバーがバックアップから復元されたときに、新しい ID 値の範囲が自動的に要求されます。If you are using automatic identity range management, when a Subscriber is restored from a backup, it automatically requests a new range of identity values. パブリッシャーをバックアップから復元する場合は、パブリッシャーに適切な範囲を割り当てる必要があります。If a Publisher is restored from a backup, you must ensure that the Publisher is assigned an appropriate range. マージ レプリケーション用に、sp_restoremergeidentityrange (Transact-SQL) を使用して新しい範囲を割り当てます。For merge replication, assign a new range using sp_restoremergeidentityrange (Transact-SQL). トランザクション レプリケーションの場合は、使用されている最も高い値を特定してから、新しい範囲の開始位置を設定します。For transactional replication, determine the highest value that has been used and then set the starting point for new ranges. パブリケーション データベースが復元された後に次の手順を実行します。Use the following procedure after the publication database has been restored:

  1. すべてのサブスクライバーのすべての操作を停止します。Stop all activity on all Subscribers.

  2. ID 列を含むパブリッシュされたテーブルごとに、以下を実行します。For each published table that includes an identity column:

    1. 各サブスクライバーのサブスクリプション データベースで IDENT_CURRENT('<TableName>')を実行します。In the subscription database at each Subscriber, execute IDENT_CURRENT('<TableName>').

    2. すべてのサブスクライバーで検出された最も高い値を記録します。Record the highest value found across all Subscribers.

    3. パブリッシャーのパブリケーション データベースで、 DBCC CHECKIDENT(<TableName>','reseed',<HighestValueFound+1>) を実行します。In the publication database at the Publisher, execute DBCC CHECKIDENT(<TableName>','reseed',<HighestValueFound+1>).

    4. パブリッシャーのパブリケーション データベースで、 sp_adjustpublisheridentityrange <PublicationName>, <TableName>を実行します。In the publication database at the Publisher, execute sp_adjustpublisheridentityrange <PublicationName>, <TableName>.

    注意

    ID 列の値が増分ではなく減分に設定されている場合は、検出された最も低い値を記録してから、その値から新しい範囲を設定します。If the value in the identity column is set to decrement rather than increment, record the lowest value found, and then reseed with that value.

参照See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
DBCC CHECKIDENT (Transact-SQL) DBCC CHECKIDENT (Transact-SQL)
IDENT_CURRENT (Transact-SQL) IDENT_CURRENT (Transact-SQL)
IDENTITY (Property) (Transact-SQL) IDENTITY (Property) (Transact-SQL)
sp_adjustpublisheridentityrange (Transact-SQL)sp_adjustpublisheridentityrange (Transact-SQL)