Create and Access Tables in TempDB from Stored Procedures
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Creating and accessing tables in TempDB from natively compiled stored procedures isn't supported. Instead, use either memory-optimized tables with DURABILITY=SCHEMA_ONLY or use table types and table variables.
For more information about memory-optimization of temp table and table variable scenarios, see: Faster temp table and table variable by using memory optimization.
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
Transact-SQL Constructs Not Supported by In-Memory OLTP
Phản hồi
https://aka.ms/ContentUserFeedback.
Sắp ra mắt: Trong năm 2024, chúng tôi sẽ dần gỡ bỏ Sự cố với GitHub dưới dạng cơ chế phản hồi cho nội dung và thay thế bằng hệ thống phản hồi mới. Để biết thêm thông tin, hãy xem:Gửi và xem ý kiến phản hồi dành cho