跨数据库查询Cross-Database Queries

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL Server 2014 (12.x)SQL Server 2014 (12.x)开始,内存优化表不支持跨数据库事务。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), memory-optimized tables do not support cross-database transactions. 不能从也访问某一内存优化表的相同事务或相同查询访问其他数据库。You cannot access another database from the same transaction or the same query that also accesses a memory-optimized table. 可以轻松地将来自一个数据库的某个表中的数据复制到其他数据库的内存优化表中。You cannot easily copy data from a table in one database, to a memory-optimized table in another database.

表变量不是事务性的。Table variables are not transactional. 因此,内存优化表变量可用于跨数据库查询中,并因此可以简化将数据从一个数据库中移到另一个数据库的内存优化表中的操作。Therefore, memory-optimized table variables can be used in cross-database queries, and can thus facilitate moving data from one database into memory-optimized tables in another. 可以使用两个事务。You can use two transactions. 在第一个事务中,将数据从远程表插入到变量中。In the first transaction, insert the data from the remote table into the variable. 在第二个事务中,将数据从变量插入到本地内存优化表中。In the second transaction, insert the data into the local memory-optimized table from the variable. 有关内存优化表变量的详细信息,请参阅 通过使用内存优化更快获得临时表和表变量For more information on memory-optimized table variables, see Faster temp table and table variable by using memory optimization.

示例Example

本示例说明了如何将数据从一个数据库传输到其他数据库中的内存优化表。This example illustrates a method to transfer data from one database into a memory-optimized table in a different database.

  1. 创建测试对象。Create Test Objects. Transact-SQLTransact-SQL 中执行以下 SQL Server Management StudioSQL Server Management StudioExecute the following Transact-SQLTransact-SQL in SQL Server Management StudioSQL Server Management Studio.

    
    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. 尝试跨数据库查询。Attempt cross-database query. Transact-SQLTransact-SQL 中执行以下 SQL Server Management StudioSQL Server Management StudioExecute the following Transact-SQLTransact-SQL in SQL Server Management StudioSQL Server Management Studio.

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

    会收到以下错误信息:You should receive the following error message:

    消息 41317,级别 16,状态 5Msg 41317, Level 16, State 5
    访问内存优化表或本机编译模块的用户事务无法访问多个用户数据库或数据库模型和 msdb,并且不能写入 master。A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.

  3. 创建内存优化表类型。Create a memory-optimized table type. Transact-SQLTransact-SQL 中执行以下 SQL Server Management StudioSQL Server Management StudioExecute the following Transact-SQLTransact-SQL in SQL Server Management StudioSQL Server Management Studio.

    USE DestinationDatabase;
    GO
    
    CREATE TYPE [dbo].[MemoryType]  
        AS TABLE  
        (  
        [ID] [int] PRIMARY KEY NONCLUSTERED,
        [FirstName] nvarchar(8)
        )  
        WITH  
            (MEMORY_OPTIMIZED = ON);  
    GO
    
  4. 再次尝试跨数据库查询。Re-attempt the cross-database query. 这一次源数据首先传输到内存优化表变量。This time the source data will first be transferred to a memory-optimized table variable. 然后表变量数据传输到内存优化表。Then the data from the tale variable will be transferred to the memory-optimized table.

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

另请参阅See Also

迁移到内存中 OLTPMigrating to In-Memory OLTP