從預存程序建立及存取 TempDB 中的資料表Create and Access Tables in TempDB from Stored Procedures

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

不支援從原生編譯預存程序建立及存取 TempDB 中的資料表。Creating and accessing tables in TempDB from natively compiled stored procedures is not supported. 請改用 DURABILITY=SCHEMA_ONLY 的記憶體最佳化資料表,或改用資料表類型和資料表變數。Instead, use either memory-optimized tables with DURABILITY=SCHEMA_ONLY or use table types and table variables.

如需暫存資料表記憶體最佳化及資料表變數案例的詳細資料,請參閱:使用記憶體最佳化加快暫存資料表與資料表變數的速度For more details about memory-optimization of temp table and table variable scenarios see: Faster temp table and table variable by using memory optimization.

下列範例示範如何使用 dbo.OrderQuantityByProduct 類型的 @OrderQuantityByProduct 資料表變數取代暫存資料表 (其具有 id、ProductID、Quantity 這三個資料行):The following example shows how the use of a temp table with three columns (id, ProductID, Quantity) can be replaced using a table variable @OrderQuantityByProduct of type dbo.OrderQuantityByProduct:

CREATE TYPE dbo.OrderQuantityByProduct   
  AS TABLE   
   (id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=100000),   
    ProductID INT NOT NULL,   
    Quantity INT NOT NULL) WITH (MEMORY_OPTIMIZED=ON)  
GO  
CREATE PROCEDURE dbo.usp_OrderQuantityByProduct   
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
    LANGUAGE = N'ENGLISH'  
)  
  -- declare table variables for the list of orders   
  DECLARE @OrderQuantityByProduct dbo.OrderQuantityByProduct  
  
  -- populate input  
  INSERT @OrderQuantityByProduct SELECT ProductID, Quantity FROM dbo.[Order Details]  
  end  

另請參閱See Also

原生編譯預存程序的移轉問題 Migration Issues for Natively Compiled Stored Procedures
記憶體中的 OLTP 不支援 Transact-SQL 建構Transact-SQL Constructs Not Supported by In-Memory OLTP