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.
--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
'DBInMemoryOLTP_FG1'
'c:\database\DBInMemoryOLTP_FG1'
, MAXSIZE = UNLIMITED),
'DBInMemoryOLTP_FG2'
'c:\database\DBInMemoryOLTP_FG2'
, MAXSIZE = UNLIMITED)
LOG
'DBInMemoryOLTP_log'
'C:\database\DBInMemoryOLTP_log.ldf'
)
--Step 2: Now we are going to create a memory-optimized table:
---------------------------------------------------------------------
Use [DBInMemoryOLTP]
Go
TABLE
[techforum_member_list](
[TfmID]
INT
NOT
NULL
KEY
NONCLUSTERED HASH
WITH
(BUCKET_COUNT = 500000),
[
Name
] NVARCHAR(50)
COLLATE
Latin1_General_100_BIN2
INDEX
[IName] HASH
[JoiningDate] DATETIME
(MEMORY_OPTIMIZED =
, 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
nvarchar(50)),GETDATE() )
Set
@i+=1
End
--Featch data from table
Select
*
from
--Step 3: Now we are going to create
'Natively Compiled Stored Procedures'
:
------------------------------------------------------------------------------
USE [DBInMemoryOLTP]
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
;
--Step 4: Execute
Exec
Usp_Member_Profile
'10'
Ref. http://www.aktechforum.blogspot.com/