Hekaton is the new SQL Server 2014 In-Memory database technology. It is going to change the way we architect databases going forward. Decisions about which data or tables go into memory and which ones will function in the SQL Inter-Op mode. Until we get a ton of detail here is a simple Hekaton ST-SQL Script for creating a In-Memory table. In the example I provided below, it is assumed that you have a C:\data directory.
–Create Database with memory-optimized Data filegroup
–Drop Database HekatonDemoDB
CREATE DataBASE HekatonDemoDB
ON
PRIMARY(NAME = [HekatonDemoDB_Data],FILENAME = ‘C:\Data\HekatonDemoDB_Data.mdf’),
FILEGROUP [HekatonDemoDB_fg] CONTAINS MEMORY_OPTIMIZED_Data
(NAME = [HekatonDemoDB_dir],FILENAME = ‘C:\Data\HekatonDemoDB_dir’)
LOG ON (name = [HekatonDemoDB_log], Filename=‘C:\Data\HekatonDemoDB_log.ldf’,Size = 100MB)
GO
USE HekatonDemoDB;
GO
–Creation of a Simple Table to show Hekaton
Create Table HekatonT1
(
DataC1 int not Null,
DataC2 char(100) not null
Constraint [IdxT1DC1] Primary Key NonClustered Hash (DataC1) with (Bucket_Count =1000000)
) With (Memory_Optimized = ON, Durability = Schema_and_Data)
GO
–Insert 10,000 Rows into Hekaton1
Begin Tran
Declare @x int = 0
While (@x < 10000)
Begin
Insert into HekatonT1 Values(@x, replicate (‘1’,100))
Set @x = @X +1
End
Commit
–Exec Plan
Select * from HekatonT1
–Creation of a Simple Table to show Hekaton
Create Table HekatonT2
(
DataC1 int not Null,
DataC2 char(100) not null
Constraint [IdxT2DC1] Primary Key NonClustered Hash (DataC1) with (Bucket_Count =1000000)
) With (Memory_Optimized = ON, Durability = Schema_and_Data)
GO
–Insert 100,000 Rows into HekatonT2
Begin Tran
Declare @x int = 0
While (@x < 100000)
Begin
Insert into HekatonT2 Values(@x, replicate (‘1’,100))
Set @x = @X +1
End
Commit
–Exec Plan
Select * from HekatonT2
–Memory DMV
Select Object_name(object_ID) as TableName, * from sys.dm_db_xtp_table_memory_stats where Object_ID >0
–HekatonT1 309576141 1518 1406 8192 8192
–Delete Rows from HekatonT1
Declare @x int = 0
While (@x < 10000)
Begin
delete HekatonT1 where DataC1 = @X
Set @x = @X + 2
End
–Look at rows left
Select * from HekatonT1
–Creation of a Disk Table
CREATE TABLE [dbo].[DiskTable1](
[DataC1] [int] NOT NULL,
[DataC2] [char](100) NOT NULL,
CONSTRAINT [PK_DiskTable1] PRIMARY KEY CLUSTERED
(
[DataC1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
–Insert 100,000 Rows into DiskTable1
Begin Tran
Declare @x int = 0
While (@x < 100000)
Begin
Insert into DiskTable1 Values(@x, replicate (‘1’,100))
Set @x = @X +1
End
Commit
–Join Memory and Disk
Select *
from HekatonT2 ht2 with (Snapshot)
Inner Join DiskTable1 dt1
on ht2.DataC1 = dt1.DataC1
—————————————————————————————————————-
–Create natively compiled stored procedure
CREATE PROCEDURE GetAllRowHekatonT1
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’us_english’)
SELECT DataC1, DataC2 FROM [dbo].[HekatonT1]
END
GO
–Select from memory-optimized table through natively compiled stored procedure
EXECUTE GetAllRowHekatonT1
–Select from memory-optimized table through interpreted Transact-SQL
SELECT * FROM [HekatonT1] WITH (SNAPSHOT)