Memory optimized Tables:
SQL
Server 2014 support fully durable and delayed duration transaction by default
Memory-optimized tables are fully
durable like traditional/existing disk based tables; they support ACID property
in case of Default or fully durable setting.
The primary store for
memory-optimized tables is main memory and rows in the table are read from and
written to memory. The entire table resides in memory. A second copy of the
table data is maintained on disk, but only for durability purposes.
For delayed durability see link.
Besides the default durable
memory-optimized tables, SQL Server also supports non-durable memory-optimized
tables, which are not logged and their data is not persisted on disk. This
means that transactions on these tables do not require any disk IO, but the
data will not be recovered if there is a server failover.
In memory table rows versioned. Each
row has different versions which allow us concurrent reads and writes, Row
versions in memory-optimized tables are addressed using 8-byte memory pointers.
Data in memory-optimized tables is accessed in
two ways:
- Through natively compiled SP’s (only support memory optimized tables).
- Through traditional/interpreted SP’s and Ad-hoc queries.
Natively Compiled Stored
Procedures:
Natively
compiled stored procedures are compiles to native code that access
memory-optimized tables and table types only. Allows fast execution of the
queries and business logic in it, they get compiled as and when they created,
we cannot alter natively compiled sp’s
As they
compiled in first creation we comes to know the divide by zero, type conversion
and arithmetic over flow errors prior to executions.
Example:
Let us create memory optimized tables and natively compiled SP's
-- DROP DATABASE MyTestingMOT
CREATE DATABASE mytestingmot
GO
--------------------------------------
-- create database with a memory-optimized filegroup and a container.
-- to add the file we should have folder DB on drive C
ALTER DATABASE mytestingmot ADD filegroup mytestingmot_filegrp CONTAINS
memory_optimized_data
ALTER DATABASE mytestingmot ADD FILE (name='MyTestingMOT_File1', filename=
'c:\DB\MyTestingMOT_File1') TO filegroup mytestingmot_filegrp
ALTER DATABASE mytestingmot
SET memory_optimized_elevate_to_snapshot=ON
go
USE MyTestingMOT
go
-- create a durable (data will be persisted) memory-optimized table
-- two of the columns are indexed
CREATE TABLE dbo.moemployee
(
moemployeeid INT NOT NULL PRIMARY KEY NONCLUSTERED,
moemployeename VARCHAR(100),
deptid INT NOT NULL INDEX IX_DeptId NONCLUSTERED HASH WITH (BUCKET_COUNT=10000),
hiredate DATETIME2 NOT NULL,
salary MONEY
)WITH (MEMORY_OPTIMIZED=ON)
INSERT dbo.moemployee
VALUES (1,'Allen', 8798, Getdate(), NULL)
INSERT dbo.moemployee
VALUES (2,'Derren', 23, Getdate(), 4445.4)
INSERT dbo.moemployee
VALUES (3,'Sean', 80, Getdate(), NULL)
INSERT dbo.moemployee
VALUES (4,'Alex', 342, Getdate(), 6665.4) GO
-- verify table contents
SELECT * FROM dbo.moemployee
GO
-- update statistics on memory-optimized tables
UPDATE STATISTICS dbo.moemployee WITH fullscan, norecompute
GO
-- natively compiled stored procedure for to manipulate salary employee salary based on EmpId
-- DROP PROC uspMOEmployee
CREATE PROCEDURE dbo.uspMOEmployee @MOEmployeeId INT,@Salary MONEY
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
IF @MOEmployeeId IS NULL
THROW 51000, N'The MOEmployeeId does not exist.', 1
UPDATE dbo.MOEmployee SET Salary=@Salary WHERE MOEmployeeId=@MOEmployeeId
END
GO
/* Unit test Case
Pass paramters @MOEmployeeId and @Salary
EXEC uspMOEmployee @MOEmployeeId= 2, @Salary= 9900
*/
Execution OutPut:
| MOEmployeeId | MOEmployeeName | DeptId | HireDate | Salary |
| 1 | Allen | 8798 | 11:51.4 | NULL |
| 2 | Derren | 23 | 11:51.4 | 4445.4 |
| 3 | Sean | 80 | 11:51.4 | NULL |
| 4 | Alex | 342 | 11:51.4 | 6665.4 |
Note:
we can not alter Natively compiles sp's, we need to drop and create them. if we try to alter them it shows Error.
Msg 10794, Level 16, State 25, Procedure uspMOEmployee, Line 3
The operation 'ALTER PROCEDURE' is not supported with natively compiled stored procedures.
The operation 'ALTER PROCEDURE' is not supported with natively compiled stored procedures.
