WHAT

 

SQL Server 2014 introduced native compiled stored procedure. In this process code are converted to machine code that stored into DLL files stored in a specific folder of SQL Server. Memory optimized Machine codes can be directly executed by processor without further compilation or interpretation. So it is faster than T-SQL stored procedure that we are using. Memory optimized tables are only accessed by natively compiled stored procedure not disk based tables.

 

 

Why

 

1.       “Natively compiled stored procedure” is compiled when it is created.

 

2.       Easy to identify error like arithmetic overflow, type conversion, and divide-by-zero conditions when they are created.

 

3.       Faster and more efficient data access is occurred because of native compilation.

 

4.       ‘Natively Compiled Stored Procedure’ body must be consisting of exactly one atomic block.

 

5.       ‘Natively Compiled Stored Procedure’ can interact very efficiently with the In-Memory storage engine.

How:

--Step 1: Database Creation 

---------------------------------------------------------------

USE [master]

GO

CREATE DATABASE [DBInMemoryOLTP]

 CONTAINMENT = NONE

 ON  PRIMARY 

( NAME = N'DBInMemoryOLTP_data', FILENAME = N'c:\database\DBInMemoryOLTP_data.mdf'  ), 

 FILEGROUP [DBInMemoryOLTP_data] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT

( NAME = N'DBInMemoryOLTP_FG1', FILENAME = N'c:\database\DBInMemoryOLTP_FG1' , MAXSIZE = UNLIMITED),

( NAME = N'DBInMemoryOLTP_FG2', FILENAME = N'c:\database\DBInMemoryOLTP_FG2' , MAXSIZE = UNLIMITED)

 LOG ON 

( NAME = N'DBInMemoryOLTP_log', FILENAME = N'C:\database\DBInMemoryOLTP_log.ldf' )

GO

  

--Step 2: Now we are going to create a memory-optimized table:

---------------------------------------------------------------------

Use [DBInMemoryOLTP]

Go

CREATE TABLE [techforum_member_list]( 

[TfmID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 500000), 

[Name] NVARCHAR(50) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [IName] HASH WITH (BUCKET_COUNT = 500000), 

[JoiningDate] DATETIME NULL

) 

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); 

--Sample data input

Declare @i as bigint=1  

While ( @i < 50) 

Begin

    Insert into techforum_member_list values (@i,'techforum'+cast(@i as nvarchar(50)),GETDATE() ) 

    Set @i+=1 

End 

--Featch data from table

Select * from techforum_member_list

 

--Step 3: Now we are going to create 'Natively Compiled Stored Procedures':

------------------------------------------------------------------------------

USE [DBInMemoryOLTP]

GO

CREATE PROCEDURE Usp_Member_Profile 

 (  @TfmID int NOT NULL)

WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION

AS

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

SELECT [TfmID]

      ,[Name]

      ,[JoiningDate]

  FROM [dbo].[techforum_member_list]

     WHERE TfmID = @TfmID

END;

  

GO

--Step 4: Execute 'Natively Compiled Stored Procedures':

------------------------------------------------------------------------------

Exec Usp_Member_Profile '10'

§  NATIVE_COMPILATION: stored Procedure Needs to be compiled to Native Code during it’s creation.

 

§  SCHEMABINDING: This line of code dropping of the Tables referenced by the Stored Procedure

 

§  EXECUTE AS OWNER: The context in which the Stored Procedure Should Execute. It can be EXECUTE AS OWNER, EXECUTE AS user and EXECUTE AS SELF

 

§  BEGIN ATOMIC: Guarantees the Atomic Execution (Implicit Transaction) of the Stored Procedure (i.e. Either All Statement’s will succeed or fail together)

 

§  WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT,  LANGUAGE = ‘us_english’ ) TRANSACTION ISOLATION LEVEL: Transaction Isolation Level used for the Atomic Execution of the Stored Procedure. LANGUAGE: Language used for the Date Time Formats and System Messages in the Stored Procedure