複数データベースにまたがるクエリ

適用対象:SQL Server

SQL Server 2014 (12.x) 以降、メモリ最適化テーブルで複数データベースにまたがるトランザクションはサポートされません。 メモリ最適化テーブルにもアクセスする同じトランザクションまたは同じクエリから別のデータベースにアクセスすることはできません。 あるデータベースのテーブルから別のデータベースのメモリ最適化テーブルに、データを簡単にコピーすることはできません。

テーブル変数はトランザクション処理されません。 そのため、メモリ最適化テーブル変数を複数データベースにまたがるクエリで使用して、あるデータベースのデータを別のデータベースのメモリ最適化テーブルに簡単に移動できるようにすることができます。 2 つのトランザクションを使用できます。 最初のトランザクションで、リモート テーブルから変数にデータを挿入します。 2 つ目のトランザクションで、ローカルなメモリ最適化テーブルに変数からデータを挿入します。 メモリ最適化テーブルの変数の詳細については、「 Faster temp table and table variable by using memory optimization」 (メモリ最適化を使用した一時テーブルとテーブル変数の高速化) を参照してください。

例

この例は、あるデータベースから別のデータベースにあるメモリ最適化テーブルにデータを転送する方法を示しています。

1. テスト オブジェクトを作成します。 SQL Server Management Studio で、次の Transact SQL を実行します。

   
   USE master;
   GO
   
   SET NOCOUNT ON;
   
   -- Create simple database
   CREATE DATABASE SourceDatabase;
   ALTER DATABASE SourceDatabase SET RECOVERY SIMPLE;
   GO
   
   -- Create a table and insert a few records
   USE SourceDatabase;
   
   CREATE TABLE SourceDatabase.[dbo].[SourceTable] (
   	[ID] [int] PRIMARY KEY CLUSTERED,
   	[FirstName] nvarchar(8)
   	);
   
   INSERT [SourceDatabase].[dbo].[SourceTable]
   VALUES (1, N'Bob'),
   	(2, N'Susan');
   GO
   
   -- Create a database with a MEMORY_OPTIMIZED_DATA filegroup
   
   CREATE DATABASE DestinationDatabase
    ON  PRIMARY 
   ( NAME = N'DestinationDatabase_Data', FILENAME = N'D:\DATA\DestinationDatabase_Data.mdf',	SIZE = 8MB), 
    FILEGROUP [DestinationDatabase_mod] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
   ( NAME = N'DestinationDatabase_mod', FILENAME = N'D:\DATA\DestinationDatabase_mod', MAXSIZE = UNLIMITED)
    LOG ON 
   ( NAME = N'DestinationDatabase_Log', FILENAME = N'D:\LOG\DestinationDatabase_Log.ldf', SIZE = 8MB);
   
   ALTER DATABASE DestinationDatabase SET RECOVERY SIMPLE;
   GO
   
   USE DestinationDatabase;
   GO
   
   -- Create a memory-optimized table
   CREATE TABLE [dbo].[DestTable_InMem] (
   	[ID] [int] PRIMARY KEY NONCLUSTERED,
   	[FirstName] nvarchar(8)
   	)
   WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA );
   GO
   

2. 複数のデータベースにまたがるクエリを試行します。 SQL Server Management Studio で、次の Transact SQL を実行します。

   INSERT [DestinationDatabase].[dbo].[DestTable_InMem]
   SELECT * FROM [SourceDatabase].[dbo].[SourceTable]
   

   次のエラー メッセージが表示されます。

   メッセージ 41317、レベル 16、状態 5
   メモリ最適化テーブルまたはネイティブ コンパイル モジュールにアクセスするユーザー トランザクションは、複数のユーザー データベース、model データベース、および msdb データベースにはアクセスできません。また、master データベースに書き込むこともできません。

3. メモリ最適化テーブル型を作成します。 SQL Server Management Studio で、次の Transact SQL を実行します。

   USE DestinationDatabase;
   GO
   
   CREATE TYPE [dbo].[MemoryType]  
       AS TABLE  
       (  
   	[ID] [int] PRIMARY KEY NONCLUSTERED,
   	[FirstName] nvarchar(8)
       )  
       WITH  
           (MEMORY_OPTIMIZED = ON);  
   GO
   

4. 複数のデータベースにまたがるクエリを再試行します。 今度は、ソース データが最初にメモリ最適化テーブルの変数に転送されます。 次にテーブルの変数からメモリ最適化テーブルにデータが転送されます。

   -- Declare table variable utilizing the newly created type - MemoryType
   DECLARE @InMem dbo.MemoryType;
   
   -- Populate table variable
   INSERT @InMem SELECT * FROM SourceDatabase.[dbo].[SourceTable];
   
   -- Populate the destination memory-optimized table
   INSERT [DestinationDatabase].[dbo].[DestTable_InMem] SELECT * FROM @InMem;
   GO 
   

参照

インメモリ OLTP への移行