create-and-access-tables-in-tempdb-from-stored-procedures.md

title	Create & access tempdb tables from stored procedures
description	TempDB doesn't support creating and accessing tables from natively compiled stored procedures. Use memory-optimized tables, or table types and table variables.
author	MikeRayMSFT
ms.author	mikeray
ms.date	03/07/2017
ms.service	sql
ms.subservice	in-memory-oltp
ms.topic	concept-article
monikerRange	=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Create and Access Tables in TempDB from Stored Procedures

[!INCLUDE 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