Export (0) Print
Expand All

Creating and Accessing Tables in TempDB from Natively Compiled Stored Procedures

SQL Server 2014

Creating and accessing tables in TempDB from natively compiled stored procedures is not supported. Instead, use table types and table variables. For example:

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 = 'english'
)
  -- declare table variables for the list of orders 
  DECLARE @Input dbo.OrderQuantityByProduct

  -- populate input
  INSERT @Input SELECT ProductID, Quantity FROM dbo.[Order Details]
  end
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft