DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 84--------------------------------------------------
       SQL Server 2014 . In Memory OLTP Demo Script
       By: srinivasma_exceldbp Date: October 15, 2014, 4:10 am
       ---------------------------------------------------------
       -- Listing 1: Code to Create an In-Memory OLTP Database
       CREATE DATABASE MyIMOLTP
       ON PRIMARY
       (NAME = [MyIMOLTP_data],
       FILENAME = 'C:\temp\MyIMOLTP_data.mdf', size=500MB),
       FILEGROUP [MyIMOLTP_fg] CONTAINS MEMORY_OPTIMIZED_DATA
       (NAME = [MyIMOLTP_dir],
       FILENAME = 'C:\temp\MyIMOLTP_dir')
       LOG ON
       (NAME = [MyIMOLTP_log],
       FILENAME='C:\temp\MyIMOLTP_log.ldf', size=500MB)
       COLLATE Latin1_General_100_BIN2
       go
       ---------------------------------------------------------
       - Listing 2: Code to Add a Memory-Optimized File Group to an
       Existing Database
       ALTER DATABASE AdventureWorks2012
       ADD FILEGROUP INMOLTP_fg CONTAINS MEMORY_OPTIMIZED_DATA;
       GO
       ALTER DATABASE AdventureWorks2012
       ADD FILE (NAME='INMOLTP_fg', FILENAME='c:\temp\INMOLTP_fg')
       TO FILEGROUP INMOLTP_fg;
       GO
       ----------------------------------------------------------------
       --------
       -- Listing 3: Code to Create a Simple Memory-Optimized Table
       use MyIMOLTP
       GO
       CREATE TABLE MyINMOLTPTable
       ( [Col1] int not null PRIMARY KEY
       NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
       [Col2] varchar(20) null )
       WITH (MEMORY_OPTIMIZED = ON,
       DURABILITY = SCHEMA_AND_DATA);
       GO
       ----------------------------------------------------------------
       -
       -- Listing 4: Code to Create and Execute a Memory-Optimized
       Stored Procedure
       USE MyIMOLTP
       GO
       IF EXISTS (SELECT * FROM sys.procedures
       WHERE name='usp_DemoINMOLTPsp')
       DROP PROCEDURE usp_DemoINMOLTPsp
       GO
       CREATE PROCEDURE dbo.usp_DemoINMOLTPsp
       WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
       AS
       BEGIN ATOMIC
       WITH (TRANSACTION ISOLATION LEVEL
       = SNAPSHOT,LANGUAGE = N'us_english')
       DECLARE @Col1ID int = 1
       DELETE dbo.MyINMOLTPTable
       WHILE @Col1ID < 1000
       BEGIN
       INSERT INTO dbo.MyINMOLTPTable VALUES
       (@Col1ID, 'Data ' + CAST(@Col1ID AS VARCHAR))
       SET @Col1ID += 1
       END
       SELECT COUNT(*) FROM dbo.MyINMOLTPTable
       END
       EXEC dbo.usp_DemoINMOLTPsp
       GO
       -- Listing 4: Code to Create and Execute a Memory-Optimized
       Stored Procedure
       USE MyIMOLTP
       GO
       IF EXISTS (SELECT * FROM sys.procedures
       WHERE name='usp_DemoINMOLTPsp')
       DROP PROCEDURE usp_DemoINMOLTPsp
       GO
       CREATE PROCEDURE dbo.usp_DemoINMOLTPsp
       WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
       AS
       BEGIN ATOMIC
       WITH (TRANSACTION ISOLATION LEVEL
       = SNAPSHOT,LANGUAGE = N'us_english')
       DECLARE @Col1ID int = 1
       DELETE dbo.MyINMOLTPTable
       WHILE @Col1ID < 1000
       BEGIN
       INSERT INTO dbo.MyINMOLTPTable VALUES
       (@Col1ID, 'Data ' + CAST(@Col1ID AS VARCHAR))
       SET @Col1ID += 1
       END
       SELECT COUNT(*) FROM dbo.MyINMOLTPTable
       END
       EXEC dbo.usp_DemoINMOLTPsp
       GO
       *****************************************************