USE master;

GO

CREATE DATABASE TestHekaton;

GO

ALTER DATABASE TestHekaton

ADD FILEGROUP HekatonFG CONTAINS MEMORY_OPTIMIZED_DATA;

GO

--执行下面语句之后会在C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA路径下创建一个文件夹
--文件夹名为HekatonFile
--请不要预先创建好这个文件夹
ALTER DATABASE TestHekaton
ADD FILE
(
   NAME = 'HekatonFile',
   FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\HekatonFile'
)
TO FILEGROUP [HekatonFG];
GO

ALTER DATABASE [test99]
ADD FILE
(
   NAME = 'HekatonFile',
   FILENAME ='D:\DataBase\xtp\'
)
TO FILEGROUP [HekatonFG];
GO

USE TestHekaton;
GO

CREATE TABLE Reseller
    (
      [ResellerID] INT NOT NULL
                       PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
      [ResellerName] NVARCHAR(50) NOT NULL ,
      [ResellerType] NVARCHAR(20) NOT NULL
    )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

INSERT  INTO Reseller
VALUES  ( 1, 'A Bike Store', 'Value Added Reseller' );

CREATE PROCEDURE GetResellerType ( @id INT )
    WITH NATIVE_COMPILATION,
         SCHEMABINDING,
         EXECUTE AS OWNER
AS
    BEGIN
  ATOMIC WITH(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
        SELECT  ResellerName ,
                ResellerType
        FROM    dbo.Reseller
        WHERE   ResellerID = @id
    END;
GO

use [test99]

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [ResellerID]
      ,[ResellerName]
      ,[ResellerType]
  FROM [test99].[dbo].[Reseller] WITH (SNAPSHOT) where [ResellerID]>0 and [ResellerID] <60 

USE TestHekaton;
GO

--内存优化表
CREATE TABLE testmemory1
    (
      [ID] FLOAT NOT NULL
                       PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
      [Name] NVARCHAR(50) NOT NULL 
    )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

USE TestHekaton;
GO

--聚集索引表
CREATE TABLE testmemory2
    (
      [ID] FLOAT NOT NULL PRIMARY KEY,
      [Name] NVARCHAR(50) NOT NULL 
    )

SET STATISTICS IO ON 
SET STATISTICS TIME ON
INSERT into testmemory1([id],[name])  SELECT [id] ,[name] from sysobjects
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Table 'sysschobjs'. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 20 ms.
(90 row(s) affected)
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SET STATISTICS IO ON 
SET STATISTICS TIME ON
INSERT into testmemory2([id],[name])  SELECT [id] ,[name] from sysobjects
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Table 'testmemory2'. Scan count 0, logical reads 183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 10 ms.
(90 row(s) affected)
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SET STATISTICS IO ON 
SET STATISTICS TIME ON
SELECT * FROM  testmemory1  ORDER BY [ID] DESC
SET STATISTICS IO ON
SET STATISTICS TIME ON

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
(90 row(s) affected)
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SET STATISTICS IO ON 
SET STATISTICS TIME ON
SELECT * FROM  testmemory2  ORDER BY [ID] DESC
SET STATISTICS IO ON
SET STATISTICS TIME ON

(91 row(s) affected)
Table 'testmemory2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

CHECKPOINT
GO

SELECT Context ,
Operation,
AllocUnitName
FROM sys.fn_dblog(NULL, NULL)

--（1）不打开MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 要显式使用with(snapshot)
ALTER DATABASE [test] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=off

BEGIN TRANSACTION;  

   
SELECT * FROM  [dbo].[users] AS A INNER JOIN  [dbo].[Reseller]   AS B with(snapshot)
ON A.ID=B.RESELLERID 
 

COMMIT TRANSACTION; 


--（2）打开MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 
ALTER DATABASE [test] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=on

BEGIN TRANSACTION;  

   
SELECT * FROM  [dbo].[users] AS A INNER JOIN  [dbo].[Reseller]   AS B 
ON A.ID=B.RESELLERID 
 

COMMIT TRANSACTION;