· 6 years ago · Nov 18, 2019, 09:52 AM
1SET ANSI_NULLS ON
2GO
3SET QUOTED_IDENTIFIER ON
4GO
5
6DROP PROCEDURE IF EXISTS CrudTest_TempTable;
7GO
8
9CREATE PROCEDURE CrudTest_TempTable @InsertsCount int, @UpdatesCount int, @DeletesCount int
10AS
11BEGIN
12
13 SET NOCOUNT ON;
14 BEGIN TRAN;
15
16 CREATE TABLE #tempTable
17 (
18 Col1 INT NOT NULL PRIMARY KEY CLUSTERED,
19 Col2 NVARCHAR(4000),
20 Col3 NVARCHAR(4000),
21 Col4 DATETIME2,
22 Col5 INT NOT NULL
23 );
24
25 DECLARE @cnt INT = 0;
26 DECLARE @currDate DATETIME2 = GETDATE();
27
28
29 WHILE @cnt < @InsertsCount
30 BEGIN
31 INSERT INTO #tempTable (Col1, Col2, Col3, Col4, Col5)
32 VALUES (@cnt,
33 'sdkfjsdjfksjvnvsanlknc kcsmksmk ms mvskldamvks mv kv al kvmsdklmsdkl mal mklasdmf kamfksam kfmasdk mfksamdfksafeowa fpmsad lak',
34 'msfkjweojfijm skmcksamepi eisjfi ojsona npsejfeji a piejfijsidjfai spfdjsidjfkjskdja kfjsdp fiejfisjd pfjsdiafjisdjfipjsdi s dfipjaiesjfijeasifjdskjksjdja sidjf pajfiaj pfsdj pidfe',
35 @currDate, 100);
36
37 SET @cnt = @cnt + 1;
38 END
39 SET @cnt = 0;
40
41 WHILE @cnt < @UpdatesCount
42 BEGIN
43 UPDATE #tempTable SET Col5 = 101 WHERE Col1 = cast ((rand() * @InsertsCount) as int);
44
45 SET @cnt = @cnt + 1;
46 END
47 SET @cnt = 0;
48
49 WHILE @cnt < @DeletesCount
50 BEGIN
51 DELETE FROM #tempTable WHERE Col1 = cast ((rand() * @InsertsCount) as int);
52
53 SET @cnt = @cnt + 1;
54 END
55
56 COMMIT;
57END
58GO
59
60DROP PROCEDURE IF EXISTS CrudTest_SpidFilter_memopt_hash;
61GO
62
63DROP SECURITY POLICY IF EXISTS tempTable_memopt_hash_SpidFilter_Policy;
64GO
65
66DROP TABLE IF EXISTS tempTable_SpidFilter_memopt_hash;
67GO
68
69DROP FUNCTION IF EXISTS fn_SpidFilter;
70GO
71
72CREATE FUNCTION fn_SpidFilter(@SpidFilter smallint)
73 RETURNS TABLE
74 WITH SCHEMABINDING , NATIVE_COMPILATION
75AS
76 RETURN
77 SELECT 1 AS fn_SpidFilter
78 WHERE @SpidFilter = @@spid;
79GO
80
81CREATE TABLE tempTable_SpidFilter_memopt_hash
82(
83 Col1 INT NOT NULL,
84 Col2 NVARCHAR(4000),
85 Col3 NVARCHAR(4000),
86 Col4 DATETIME2,
87 Col5 INT NOT NULL,
88
89 SpidFilter SMALLINT NOT NULL DEFAULT (@@spid),
90 INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),
91 INDEX ix_hash HASH (Col1, SpidFilter) WITH (BUCKET_COUNT=100000),
92 CONSTRAINT CHK_SpidFilter CHECK ( SpidFilter = @@spid )
93) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
94GO
95
96
97CREATE SECURITY POLICY tempTable_memopt_hash_SpidFilter_Policy
98 ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter)
99 ON dbo.tempTable_SpidFilter_memopt_hash
100 WITH (STATE = ON);
101GO
102
103CREATE PROCEDURE CrudTest_SpidFilter_memopt_hash @InsertsCount int, @UpdatesCount int, @DeletesCount int
104AS
105BEGIN
106
107 SET NOCOUNT ON;
108 BEGIN TRAN;
109
110 DECLARE @cnt INT = 0;
111 DECLARE @currDate DATETIME2 = GETDATE();
112
113 DECLARE @IdxStart INT = CAST ((rand() * 1000) AS INT);
114
115 WHILE @cnt < @InsertsCount
116 BEGIN
117 INSERT INTO tempTable_SpidFilter_memopt_hash(Col1, Col2, Col3, Col4, Col5)
118 VALUES (@IdxStart + @cnt,
119 'sdkfjsdjfksjvnvsanlknc kcsmksmk ms mvskldamvks mv kv al kvmsdklmsdkl mal mklasdmf kamfksam kfmasdk mfksamdfksafeowa fpmsad lak',
120 'msfkjweojfijm skmcksamepi eisjfi ojsona npsejfeji a piejfijsidjfai spfdjsidjfkjskdja kfjsdp fiejfisjd pfjsdiafjisdjfipjsdi s dfipjaiesjfijeasifjdskjksjdja sidjf pajfiaj pfsdj pidfe',
121 @currDate, 100);
122
123 SET @cnt = @cnt + 1;
124 END
125 SET @cnt = 0;
126
127 WHILE @cnt < @UpdatesCount
128 BEGIN
129 UPDATE tempTable_SpidFilter_memopt_hash SET Col5 = 101 WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);
130
131 SET @cnt = @cnt + 1;
132 END
133 SET @cnt = 0;
134
135 WHILE @cnt < @DeletesCount
136 BEGIN
137 DELETE FROM tempTable_SpidFilter_memopt_hash WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);
138
139 SET @cnt = @cnt + 1;
140 END
141
142 DELETE FROM tempTable_SpidFilter_memopt_hash;
143 COMMIT;
144END
145GO
146
147DROP PROCEDURE IF EXISTS CrudTest_memopt_hash;
148GO
149
150DROP TABLE IF EXISTS tempTable_memopt_hash;
151GO
152
153CREATE TABLE dbo.tempTable_memopt_hash
154(
155 Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000),
156 Col2 NVARCHAR(4000),
157 Col3 NVARCHAR(4000),
158 Col4 DATETIME2,
159 Col5 INT NOT NULL
160) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
161GO
162
163CREATE PROCEDURE CrudTest_memopt_hash @InsertsCount int, @UpdatesCount int, @DeletesCount int
164WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
165AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'english')
166 DECLARE @cnt INT = 0;
167 DECLARE @currDate DATETIME2 = GETDATE();
168
169 DECLARE @IdxStart INT = CAST ((rand() * 1000) AS INT);
170
171 WHILE @cnt < @InsertsCount
172 BEGIN
173 INSERT INTO dbo.tempTable_memopt_hash(Col1, Col2, Col3, Col4, Col5)
174 VALUES (@IdxStart + @cnt,
175 'sdkfjsdjfksjvnvsanlknc kcsmksmk ms mvskldamvks mv kv al kvmsdklmsdkl mal mklasdmf kamfksam kfmasdk mfksamdfksafeowa fpmsad lak',
176 'msfkjweojfijm skmcksamepi eisjfi ojsona npsejfeji a piejfijsidjfai spfdjsidjfkjskdja kfjsdp fiejfisjd pfjsdiafjisdjfipjsdi s dfipjaiesjfijeasifjdskjksjdja sidjf pajfiaj pfsdj pidfe',
177 @currDate, 100);
178
179 SET @cnt = @cnt + 1;
180 END
181 SET @cnt = 0;
182
183 WHILE @cnt < @UpdatesCount
184 BEGIN
185 UPDATE dbo.tempTable_memopt_hash SET Col5 = 101 WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);
186
187 SET @cnt = @cnt + 1;
188 END
189 SET @cnt = 0;
190
191 WHILE @cnt < @DeletesCount
192 BEGIN
193 DELETE FROM dbo.tempTable_memopt_hash WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);
194
195 SET @cnt = @cnt + 1;
196 END
197
198 DELETE FROM dbo.tempTable_memopt_hash;
199END
200GO
201
202DROP PROCEDURE IF EXISTS CrudTest_tableVar;
203DROP TYPE IF EXISTS dbo.tableVar;
204GO
205
206CREATE TYPE dbo.tableVar
207 AS TABLE
208 (
209 Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000),
210 Col2 NVARCHAR(4000),
211 Col3 NVARCHAR(4000),
212 Col4 DATETIME2,
213 Col5 INT NOT NULL
214 ) WITH (MEMORY_OPTIMIZED = ON);
215GO
216
217CREATE PROCEDURE CrudTest_tableVar @InsertsCount int, @UpdatesCount int, @DeletesCount int
218WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
219AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'english')
220
221 DECLARE @cnt INT = 0;
222 DECLARE @currDate DATETIME2 = GETDATE();
223
224 DECLARE @IdxStart INT = CAST ((rand() * 1000) AS INT);
225 DECLARE @tvTable dbo.tableVar;
226
227 WHILE @cnt < @InsertsCount
228 BEGIN
229 INSERT INTO @tvTable(Col1, Col2, Col3, Col4, Col5)
230 VALUES (@IdxStart + @cnt,
231 'sdkfjsdjfksjvnvsanlknc kcsmksmk ms mvskldamvks mv kv al kvmsdklmsdkl mal mklasdmf kamfksam kfmasdk mfksamdfksafeowa fpmsad lak',
232 'msfkjweojfijm skmcksamepi eisjfi ojsona npsejfeji a piejfijsidjfai spfdjsidjfkjskdja kfjsdp fiejfisjd pfjsdiafjisdjfipjsdi s dfipjaiesjfijeasifjdskjksjdja sidjf pajfiaj pfsdj pidfe',
233 @currDate, 100);
234
235 SET @cnt = @cnt + 1;
236 END
237 SET @cnt = 0;
238
239 WHILE @cnt < @UpdatesCount
240 BEGIN
241 UPDATE @tvTable SET Col5 = 101 WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);
242
243 SET @cnt = @cnt + 1;
244 END
245 SET @cnt = 0;
246
247 WHILE @cnt < @DeletesCount
248 BEGIN
249 DELETE FROM @tvTable WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);
250
251 SET @cnt = @cnt + 1;
252 END
253
254END
255GO