(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) 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 *****************************************************