Migration Issues for Natively Compiled Stored Procedures
Topic Status: Some information in this topic is pre-release and subject to change in future releases. Pre-release information describes new features or changes to existing features in Microsoft SQL Server 2014.
This topic presents several issues related to creating natively compiled stored procedures.
For more information about natively compiled stored procedures, see Introduction to Natively Compiled Stored Procedures.
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 IDENTITY 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
Natively compiled stored procedures do not support the EXISTS clause, but there is a workaround:
DECLARE @exists BIT = 0 SELECT TOP 1 @exists = 1 FROM MyTable WHERE … IF @exists = 1
The In-Memory OLTP recovery algorithms require that there be a unique key associated with every table that allows the logical recovery process to uniquely identify a row. PRIMARY KEY constraints enforce this functionality. All tables created as memory-optimized tables with Durability set to SCHEMA_AND_DATA must have an associated PRIMARY KEY.
create table T1q ( [c1] integer not null primary key nonclustered hash with (bucket_count = 1000000), [c2] datetime not null, [c3] varchar(32) not null ) with (memory_optimized = on)
Alternatively, you can use table-level constraints to create multi-column keys:
create table T1 ( [c1] integer not null, [c2] datetime not null, [c3] varchar(32) collate Latin1_General_100_BIN2 not null constraint T1_ndx_c2c3 primary key nonclustered hash ([c2],[c3]) with (bucket_count = 1000000) ) with (memory_optimized = on)
A database may need to perform either an insert of an update, depending on whether a particular row already exists in the database.
Without using the MERGE statement, the following is one approach you can use in Transact-SQL:
UPDATE mytable SET col=@somevalue WHERE myPK = @parm IF @@ROWCOUNT = 0 INSERT mytable (columns) VALUES (@parm, @other values)
Another Transact-SQL method to implement a merge:
IF EXISTS (SELECT 1 FROM mytable WHERE myPK = @parm) UPDATE…. ELSE INSERT
For a natively compiled stored procedure
DECLARE @i int = 0 -- or whatever your PK data type is UPDATE mytable SET @i=myPK, othercolums = other values WHERE myPK = @parm IF @i = 0 INSERT….