Transact SQL – In Memory Programmable Objects

Background

On a couple of SQL Server Instances that was recently grandfathered in to our group, there are a few In-Memory tables.

And, so I find myself using these precious hours in between Christmas and the forthcoming New Year to catch up on In-Memory Tables.

What good are In-Memory tables, without In-Memory Programmable Objects such as Stored Procedures.

Let us quickly create a couple of Stored Procedures.

 

Code

Programmable Objects

Stored Procedures

dbo.usp_Emp_Get_FilteredOnSSN

 




if object_id('[dbo].[usp_Emp_Get_FilteredOnSSN]') is not null
begin
	drop procedure [dbo].[usp_Emp_Get_FilteredOnSSN]
end
go

create procedure [dbo].[usp_Emp_Get_FilteredOnSSN]
(
	@ssn char(9)
)  
with 
		  native_compilation
		, schemabinding
		, execute as owner  
as  
begin atomic  

	with 
	(
		  transaction isolation level=snapshot
		, language=N'us_english'
	)  

	select
			  tblEmp.[oe_emp_ssn]
			, tblEmp.[oe_emp_location]

	from   [dbo].[oe_emp] tblEmp

	where  tblEmp.[oe_emp_ssn] = @ssn


end  
go  

dbo.usp_Emp_Set_FilteredOnSSN

Code


if object_id('[dbo].[usp_Emp_Set_FilteredOnSSN]') is not null
begin
	drop procedure [dbo].[usp_Emp_Set_FilteredOnSSN]
end
go

create procedure [dbo].[usp_Emp_Set_FilteredOnSSN]
(
	  @ssn      char(9)
	, @location char(2)
)  
with 
		  native_compilation
		, schemabinding
		, execute as owner  
as  
begin atomic  

	with 
	(
		  transaction isolation level=snapshot
		, language=N'us_english'
	)  

	update [dbo].[oe_emp]

	set	[oe_emp_location] = @location
		
	where  [oe_emp_ssn] = @ssn

end  
go  


Unit Test

set nocount on;
set XACT_ABORT on;
go

declare @ssn char(9)
declare @location char(2)
declare @location_Temp char(2)
declare @location_v2 char(2)


begin tran

	select top 1
				@ssn = [oe_emp_ssn]
			, @location = [oe_emp_location]
	from   [dbo].[oe_emp] with (SNAPSHOT)

	set @location_Temp = 'NA'

	exec [dbo].[usp_Emp_Set_FilteredOnSSN]
			@ssn = @ssn
		, @location = @location_Temp


	select @location_v2 = [oe_emp_location]
	from   [dbo].[oe_emp] with (SNAPSHOT)
	where  [oe_emp_ssn] = @ssn


	select 
			  [@location] = @location
			, [@location_Temp] = @location_Temp
			, [@location_v2] = @location_v2	

rollback tran

 

Restrictions

There are a little restrictions placed on Stored Procedures marked for Native Compilation.

The restrictions includes

  1. SQL Syntax
    • Can not use like Statement
      • If we attempt to do so, we will get the error pasted below
        • Sample SQL Statement
          • select tblEmp.[oe_emp_ssn],  tblEmp.[oe_emp_location] from [dbo].[oe_emp] tblEmp where tblEmp.[oe_emp_ssn] like @ssn
        • Error Message
          • Msg 10794, Level 16, State 62, Procedure usp_Emp_Get_FilteredOnSSN, Line 42
            The operator ‘LIKE’ is not supported with natively compiled stored procedures.
    • Can not use wildcard (*) for Column Names
      • If we attempt to do so, we will get the error pasted below
        • Sample SQL Statement
          • select tblEmp.* from [dbo].[oe_emp] tblEmp where tblEmp.[oe_emp_ssn] like @ssn
        • Error Message
          • Msg 1054, Level 15, State 2, Procedure
            Syntax ‘*’ is not allowed in schema-bound objects.
    • Can not use Begin Transaction / Rollback-Commit within Native Compiled Stored Procedures
      • The same effect is handled declaratively, as Native Compile SP are designated as atomic
        • Database Features > In-Memory OLTP (In-Memory Optimization)  > Natively Compiled Stored Procedures
          Link

          • BEGIN ATOMIC is part of the ANSI SQL standard.
          • SQL Server supports atomic blocks at the top-level of natively compiled stored procedures, as well as for natively compiled, scalar user-defined functions.
            • Every natively compiled stored procedure contains exactly one block of Transact-SQL statements. This is an ATOMIC block.
            • Non-native, interpreted Transact-SQL stored procedures and ad hoc batches do not support atomic blocks.
        • Sample Code
          • begin atomic
            —– source code —
            end

 

Table of Errors

Msg Error Number Error Details Remediation
 1054 Syntax ‘*’ is not allowed in schema-bound objects.  Explicitly list the Column Names
 10794  The operator ‘LIKE’ is not supported with natively compiled stored procedures.  The Like Operator can not be used in natively compile Stored Procedure.
Please use regular Stored Procedure
41320 EXECUTE AS clause is required, and EXECUTE AS CALLER is not supported, with natively compiled stored procedures  Replace “with native_compilation, schemabinding” with “with native_compilation, schemabinding, , execute as owner

 

 

References

  1. Database Features > In-Memory OLTP (In-Memory Optimization) > Natively Compiled Stored Procedures > Atomic Blocks in Native Procedures
    Link
  2. Database Engine Features and Tasks > Database Features > In-Memory OLTP (In-Memory Optimization) > Scalar User-Defined Functions for In-Memory OLTP
    Link

One thought on “Transact SQL – In Memory Programmable Objects

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s