forked from ktaranov/sqlserver-kit
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathIN_Memory_Example_2014.sql
More file actions
116 lines (96 loc) · 3.46 KB
/
IN_Memory_Example_2014.sql
File metadata and controls
116 lines (96 loc) · 3.46 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
/*
Author: Konstantin Taranov
Link: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/IN_Memory_Example_2014.sql
*/
USE master;
GO
DECLARE @databaseFilePath NVARCHAR(1000) = N'';
SELECT @databaseFilePath = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1;
DECLARE @databaseName SYSNAME = N'ಠ ಠ 14 Test';
DECLARE @tsqlStatement NVARCHAR(4000) = N'';
DECLARE @debug BIT = 0;
SET @tsqlStatement = '
IF DB_ID(N''@databaseName'') IS NOT NULL
BEGIN
ALTER DATABASE [@databaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [@databaseName];
END;
CREATE DATABASE [@databaseName]
CONTAINMENT = NONE
ON PRIMARY
(NAME = N''@databaseName'', FILENAME = N''@databaseFilePath@databaseName.mdf'', SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP [@databaseName] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
( NAME = N''@databaseName_mod'', FILENAME = N''@databaseFilePath@databaseName_mod'', MAXSIZE = UNLIMITED)
LOG ON
( NAME = N''@databaseName_log'', FILENAME = N''@databaseFilePath@databaseName_log.ldf'', SIZE = 64MB, MAXSIZE = 2048MB, FILEGROWTH = 64MB);
ALTER DATABASE [@databaseName] SET COMPATIBILITY_LEVEL = 120;
';
SET @tsqlStatement = REPLACE(@tsqlStatement, '@databaseName', @databaseName);
SET @tsqlStatement = REPLACE(@tsqlStatement, '@databaseFilePath', @databaseFilePath);
IF @debug = 1 PRINT(@tsqlStatement)
ELSE
EXEC sp_executesql @tsqlStatement;
SET @tsqlStatement = '
USE [@databaseName];
--UNCOMMENT GO Statement if you want to execute statements after printing in debug mode!!!
--GO
-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
-- memory-optimized table
CREATE TABLE dbo.table1(
c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
c2 NVARCHAR(4000)
)
WITH (MEMORY_OPTIMIZED=ON);
-- non-durable table
CREATE TABLE dbo.temp_table1(
c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
c2 NVARCHAR(4000)
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);
-- memory-optimized table type
CREATE TYPE dbo.tt_table1 AS TABLE(
c1 INT IDENTITY,
c2 NVARCHAR(4000),
is_transient BIT NOT NULL DEFAULT (0),
INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON);
CREATE TABLE dbo.InMemTable1(
keyColumn INT IDENTITY PRIMARY KEY NONCLUSTERED
, description NCHAR(100) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
INSERT dbo.InMemTable1(description)
VALUES
(REPLICATE(''A'', 100))
,(REPLICATE(''B'', 100))
,(REPLICATE(''C'', 100))
,(REPLICATE(''D'', 100))
,(REPLICATE(''E'', 100))
,(REPLICATE(''F'', 100));
';
SET @tsqlStatement = REPLACE(@tsqlStatement, '@databaseName', @databaseName);
IF @debug = 1 PRINT(@tsqlStatement)
ELSE
EXEC sp_executesql @tsqlStatement;
-- https://stackoverflow.com/a/793362/2298061
DECLARE @UseAndExecStatment NVARCHAR(4000);
SET @UseAndExecStatment = N'USE [' + @databaseName + N']; EXEC sp_executesql @tsqlStatement';
SET @tsqlStatement = N'
CREATE PROCEDURE dbo.native_sp
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N''us_english'')
SELECT keyColumn
, description
FROM dbo.InMemTable1;
END;
--EXECUTE dbo.native_sp;';
IF @debug = 1 PRINT(@tsqlStatement)
ELSE
BEGIN
EXEC sp_executesql @UseAndExecStatment,
N'@tsqlStatement NVARCHAR(MAX)', @tsqlStatement = @tsqlStatement;
END;