/**************************
DATABASE
**************************/
CREATE DATABASE InMemoryDemo
GO

USE InMemoryDemo
GO

ALTER DATABASE InMemoryDemo ADD FILEGROUP InMemoryDemoFileGroup CONTAINS MEMORY_OPTIMIZED_DATA
GO

ALTER DATABASE InMemoryDemo ADD FILE
	(
		NAME = InMemoryDemoFile
		,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SS2014\MSSQL\DATA\InMemoryDemoFile'
	)
TO FILEGROUP InMemoryDemoFileGroup
GO

/**************************
TABLE WITH 100000 ROWS
**************************/
CREATE TABLE dbo.InMemoryDemoTable
	(
		RowId INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000)
		,RowDate DATETIME2 NOT NULL DEFAULT(GETDATE())
		,StringValue VARCHAR(100) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [StringValue_IDX] HASH WITH (BUCKET_COUNT = 1000000)
	)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

INSERT dbo.InMemoryDemoTable (StringValue)
SELECT TOP (100000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS I
    FROM SYS.OBJECTS S1
	CROSS JOIN SYS.OBJECTS S2
	CROSS JOIN SYS.OBJECTS S3


/**************************
NATIVELY COMPILED
STORED PROCEDURE
**************************/
CREATE PROCEDURE dbo.InMemoryDemoNatCompSP (@RowId INT)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS 
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
	SELECT RowId, RowDate, StringValue FROM dbo.InMemoryDemoTable WHERE RowId = @RowId
END
GO


/**************************
TEST!
**************************/
EXEC dbo.InMemoryDemoNatCompSP 45