· 8 years ago · Dec 14, 2017, 04:26 PM
1USE Magazin_de_Antichitati
2GO
3
4if exists (select * from dbo.sysobjects where id = object_id(N'[FK_RulariTesteTabele_Tabele]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
5
6ALTER TABLE [RulariTesteTabele] DROP CONSTRAINT [FK_RulariTesteTabele_Tabele]
7
8GO
9
10if exists (select * from dbo.sysobjects where id = object_id(N'[FK_TesteTabele_Tabele]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
11
12ALTER TABLE [TesteTabele] DROP CONSTRAINT [FK_TesteTabele_Tabele]
13
14GO
15
16if exists (select * from dbo.sysobjects where id = object_id(N'[FK_RulariTesteTabele_RulariTeste]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
17
18ALTER TABLE [RulariTesteTabele] DROP CONSTRAINT FK_RulariTesteTabele_RulariTeste
19
20GO
21
22if exists (select * from dbo.sysobjects where id = object_id(N'[FK_RulariTesteViewuri_RulariTeste]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
23
24ALTER TABLE [RulariTesteViewuri] DROP CONSTRAINT FK_RulariTesteViewuri_RulariTeste
25
26GO
27
28if exists (select * from dbo.sysobjects where id = object_id(N'[FK_TesteTabele_Teste]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
29
30ALTER TABLE [TesteTabele] DROP CONSTRAINT FK_TesteTabele_Teste
31
32GO
33
34if exists (select * from dbo.sysobjects where id = object_id(N'[FK_TesteViewuri_Teste]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
35
36ALTER TABLE [TesteViewuri] DROP CONSTRAINT FK_TesteViewuri_Teste
37
38GO
39
40if exists (select * from dbo.sysobjects where id = object_id(N'[FK_RulariTesteViewuri_Viewuri]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
41
42ALTER TABLE [RulariTesteViewuri] DROP CONSTRAINT FK_RulariTesteViewuri_Viewuri
43
44GO
45
46if exists (select * from dbo.sysobjects where id = object_id(N'[FK_TesteViewuri_Viewuri]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
47
48ALTER TABLE [TesteViewuri] DROP CONSTRAINT FK_TesteViewuri_Viewuri
49
50GO
51
52if exists (select * from dbo.sysobjects where id = object_id(N'[Tabele]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
53
54drop table [Tabele]
55
56GO
57
58if exists (select * from dbo.sysobjects where id = object_id(N'[RulariTesteTabele]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
59
60drop table [RulariTesteTabele]
61
62GO
63
64if exists (select * from dbo.sysobjects where id = object_id(N'[RulariTesteViewuri]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
65
66drop table [RulariTesteViewuri]
67
68GO
69
70if exists (select * from dbo.sysobjects where id = object_id(N'[RulariTeste]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
71
72drop table [RulariTeste]
73
74GO
75
76if exists (select * from dbo.sysobjects where id = object_id(N'[TesteTabele]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
77
78drop table [TesteTabele]
79
80GO
81
82if exists (select * from dbo.sysobjects where id = object_id(N'[TesteViewuri]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
83
84drop table [TesteViewuri]
85
86GO
87
88if exists (select * from dbo.sysobjects where id = object_id(N'[Teste]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
89
90drop table [Teste]
91
92GO
93
94if exists (select * from dbo.sysobjects where id = object_id(N'[Viewuri]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
95
96drop table [Viewuri]
97
98GO
99
100CREATE TABLE [Tabele] (
101
102 [CodTabel] [int] IDENTITY (1, 1) NOT NULL ,
103
104 [Nume] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
105
106) ON [PRIMARY]
107
108GO
109
110CREATE TABLE [RulariTesteTabele] (
111
112 [CodRulareTest] [int] NOT NULL ,
113
114 [CodTabel] [int] NOT NULL ,
115
116 [IncepeLa] [datetime] NOT NULL ,
117
118 [SeIncheieLa] [datetime] NOT NULL
119
120) ON [PRIMARY]
121
122GO
123
124CREATE TABLE [RulariTesteViewuri] (
125
126 [CodRulareTest] [int] NOT NULL ,
127
128 [CodView] [int] NOT NULL ,
129
130 [IncepeLa] [datetime] NOT NULL ,
131
132 [SeIncheieLa] [datetime] NOT NULL
133
134) ON [PRIMARY]
135
136GO
137
138CREATE TABLE [RulariTeste] (
139
140 [CodRulareTest] [int] IDENTITY (1, 1) NOT NULL ,
141
142 [Descriere] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
143
144 [IncepeLa] [datetime] NULL ,
145
146 [SeIncheieLa] [datetime] NULL
147
148) ON [PRIMARY]
149
150GO
151
152CREATE TABLE [TesteTabele] (
153
154 [CodTest] [int] NOT NULL ,
155
156 [CodTabel] [int] NOT NULL ,
157
158 [NrRanduri] [int] NOT NULL ,
159
160 [Pozitie] [int] NOT NULL
161
162) ON [PRIMARY]
163
164GO
165
166CREATE TABLE [TesteViewuri] (
167
168 [CodTest] [int] NOT NULL ,
169
170 [CodView] [int] NOT NULL
171
172) ON [PRIMARY]
173
174GO
175
176CREATE TABLE [Teste] (
177
178 [CodTest] [int] IDENTITY (1, 1) NOT NULL ,
179
180 [Nume] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
181
182) ON [PRIMARY]
183
184GO
185
186CREATE TABLE [Viewuri] (
187
188 [CodView] [int] IDENTITY (1, 1) NOT NULL ,
189
190 [Nume] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
191
192) ON [PRIMARY]
193
194GO
195
196ALTER TABLE [Tabele] WITH NOCHECK ADD
197
198 CONSTRAINT [PK_Tabele] PRIMARY KEY CLUSTERED
199
200 (
201
202 [CodTabel]
203
204 ) ON [PRIMARY]
205
206GO
207
208ALTER TABLE [RulariTesteTabele] WITH NOCHECK ADD
209
210 CONSTRAINT [PK_RulariTesteTabele] PRIMARY KEY CLUSTERED
211
212 (
213
214 [CodRulareTest],
215
216 [CodTabel]
217
218 ) ON [PRIMARY]
219
220GO
221
222ALTER TABLE [RulariTesteViewuri] WITH NOCHECK ADD
223
224 CONSTRAINT [PK_RulariTesteViewuri] PRIMARY KEY CLUSTERED
225
226 (
227
228 [CodRulareTest],
229
230 [CodView]
231
232 ) ON [PRIMARY]
233
234GO
235
236ALTER TABLE [RulariTeste] WITH NOCHECK ADD
237
238 CONSTRAINT [PK_RulariTeste] PRIMARY KEY CLUSTERED
239
240 (
241
242 [CodRulareTest]
243
244 ) ON [PRIMARY]
245
246GO
247
248ALTER TABLE [TesteTabele] WITH NOCHECK ADD
249
250 CONSTRAINT [PK_TesteTabele] PRIMARY KEY CLUSTERED
251
252 (
253
254 [CodTest],
255
256 [CodTabel]
257
258 ) ON [PRIMARY]
259
260GO
261
262ALTER TABLE [TesteViewuri] WITH NOCHECK ADD
263
264 CONSTRAINT [PK_TesteViewuri] PRIMARY KEY CLUSTERED
265
266 (
267
268 [CodTest],
269
270 [CodView]
271
272 ) ON [PRIMARY]
273
274GO
275
276ALTER TABLE [Teste] WITH NOCHECK ADD
277
278 CONSTRAINT [PK_Teste] PRIMARY KEY CLUSTERED
279
280 (
281
282 [CodTest]
283
284 ) ON [PRIMARY]
285
286GO
287
288ALTER TABLE [Viewuri] WITH NOCHECK ADD
289
290 CONSTRAINT [PK_Viewuri] PRIMARY KEY CLUSTERED
291
292 (
293
294 [CodView]
295
296 ) ON [PRIMARY]
297
298GO
299
300ALTER TABLE [RulariTesteTabele] ADD
301
302 CONSTRAINT [FK_RulariTesteTabele_Tabele] FOREIGN KEY
303
304 (
305
306 [CodTabel]
307
308 ) REFERENCES [Tabele] (
309
310 [CodTabel]
311
312 ) ON DELETE CASCADE ON UPDATE CASCADE ,
313
314 CONSTRAINT [FK_RulariTesteTabele_RulariTeste] FOREIGN KEY
315
316 (
317
318 [CodRulareTest]
319
320 ) REFERENCES [RulariTeste] (
321
322 [CodRulareTest]
323
324 ) ON DELETE CASCADE ON UPDATE CASCADE
325
326GO
327
328ALTER TABLE [RulariTesteViewuri] ADD
329
330 CONSTRAINT [FK_RulariTesteViewuri_RulariTeste] FOREIGN KEY
331
332 (
333
334 [CodRulareTest]
335
336 ) REFERENCES [RulariTeste] (
337
338 [CodRulareTest]
339
340 ) ON DELETE CASCADE ON UPDATE CASCADE ,
341
342 CONSTRAINT [FK_RulariTesteViewuri_Viewuri] FOREIGN KEY
343
344 (
345
346 [CodView]
347
348 ) REFERENCES [Viewuri] (
349
350 [CodView]
351
352 ) ON DELETE CASCADE ON UPDATE CASCADE
353
354GO
355
356ALTER TABLE [TesteTabele] ADD
357
358 CONSTRAINT [FK_TesteTabele_Tabele] FOREIGN KEY
359
360 (
361
362 [CodTabel]
363
364 ) REFERENCES [Tabele] (
365
366 [CodTabel]
367
368 ) ON DELETE CASCADE ON UPDATE CASCADE ,
369
370 CONSTRAINT [FK_TesteTabele_Teste] FOREIGN KEY
371
372 (
373
374 [CodTest]
375
376 ) REFERENCES [Teste] (
377
378 [CodTest]
379
380 ) ON DELETE CASCADE ON UPDATE CASCADE
381
382GO
383
384ALTER TABLE [TesteViewuri] ADD
385
386 CONSTRAINT [FK_TesteViewuri_Teste] FOREIGN KEY
387
388 (
389
390 [CodTest]
391
392 ) REFERENCES [Teste] (
393
394 [CodTest]
395
396 ),
397
398 CONSTRAINT [FK_TesteViewuri_Viewuri] FOREIGN KEY
399
400 (
401
402 [CodView]
403
404 ) REFERENCES [Viewuri] (
405
406 [CodView]
407
408 )
409
410GO
411
412INSERT Tabele
413 VALUES ('Sursa'),('Achizitie'),('Antichitate')
414GO
415
416INSERT Viewuri
417 VALUES ('View_1Tabel'),('View_2Tabele'),('View_2Tabele_GroupBy')
418GO
419
420INSERT Teste
421 VALUES ('view_1'),('view_2'),('view_3'),('add_sursa'),('add_antichitate'),('add_achizitie'),('delete_achizitie'),('delete_antichitate'),('delete_sursa')
422GO
423
424INSERT TesteViewuri
425 VALUES (1,1),(2,2),(3,3)
426GO
427
428INSERT TesteTabele
429 VALUES (7,2,10000,1),(8,3,10000,2),(9,1,10000,3),(4,1,10000,4),(6,3,10000,5),(5,2,10000,6)
430GO
431
432--PROCEDURI
433--CREATE PROCEDURE view_1 AS BEGIN
434-- SELECT * FROM View_1Tabel
435--END
436--GO
437
438--CREATE PROCEDURE view_2 AS BEGIN
439-- SELECT * FROM View_2Tabele
440--END
441--GO
442
443--CREATE PROCEDURE view_3 AS BEGIN
444-- SELECT * FROM View_2Tabele_GroupBy
445--END
446--GO
447
448--CREATE PROCEDURE add_sursa AS BEGIN
449-- DECLARE @count INT = 10000
450-- WHILE @count <= 110000 BEGIN
451-- INSERT Sursa
452-- VALUES (@count,'nume_' + CAST(@count AS VARCHAR(30)),'prenume_' + CAST(@count AS VARCHAR(30)))
453-- SET @count = @count + 1
454-- END
455--END
456--GO
457
458--CREATE PROCEDURE add_antichitate AS BEGIN
459-- DECLARE @count INT = 10000
460-- WHILE @count <= 110000 BEGIN
461-- INSERT Antichitate
462-- VALUES (@count,'antichitatea_' + CAST(@count AS VARCHAR(30)),1,1,1)
463-- SET @count = @count + 1
464-- END
465--END
466--GO
467
468--CREATE PROCEDURE add_achizitie AS BEGIN
469-- DECLARE @count INT = 10000
470-- WHILE @count <= 110000 BEGIN
471-- INSERT Achizitie
472-- VALUES (@count,@count,'2017-12-12',1)
473-- SET @count = @count + 1
474-- END
475--END
476--GO
477
478--CREATE PROCEDURE delete_achizitie AS BEGIN
479-- DELETE FROM Achizitie
480-- WHERE CodA > 9999
481--END
482--GO
483
484--CREATE PROCEDURE delete_antichitate AS BEGIN
485-- DELETE FROM Antichitate
486-- WHERE CodA > 9999
487--END
488--GO
489
490--CREATE PROCEDURE delete_sursa AS BEGIN
491-- DELETE FROM Sursa
492-- WHERE CodS > 9999
493--END
494--GO
495
496--CREATE PROCEDURE run_achizitie AS BEGIN
497-- DECLARE @data1 DATETIME, @data2 DATETIME, @data3 DATETIME, @data4 DATETIME
498-- SET @data1 = GETDATE()
499-- EXEC delete_achizitie
500-- EXEC add_achizitie
501-- SET @data2 = GETDATE()
502
503-- SET @data3 = GETDATE()
504-- EXEC view_2
505-- SET @data4 = GETDATE()
506
507-- INSERT RulariTeste
508-- VALUES ('tabel_2 + view_2',@data1,@data4)
509-- INSERT RulariTesteTabele
510-- VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),2,@data1,@data2)
511-- INSERT RulariTesteViewuri
512-- VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),2,@data3,@data4)
513--END
514--GO
515
516--CREATE PROCEDURE run_antichitate AS BEGIN
517-- EXEC delete_achizitie
518
519-- DECLARE @data1 DATETIME, @data2 DATETIME, @data3 DATETIME, @data4 DATETIME
520-- SET @data1 = GETDATE()
521-- EXEC delete_antichitate
522-- EXEC add_antichitate
523-- SET @data2 = GETDATE()
524
525-- SET @data3 = GETDATE()
526-- EXEC view_3
527-- SET @data4 = GETDATE()
528
529
530-- INSERT RulariTeste
531-- VALUES ('tabel_3 + view_3',@data1,@data4)
532-- INSERT RulariTesteTabele
533-- VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),3,@data1,@data2)
534-- INSERT RulariTesteViewuri
535-- VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),3,@data3,@data4)
536
537-- EXEC add_achizitie
538--END
539--GO
540
541--CREATE PROCEDURE run_sursa AS BEGIN
542-- EXEC delete_achizitie
543
544-- DECLARE @data1 DATETIME, @data2 DATETIME, @data3 DATETIME, @data4 DATETIME
545-- SET @data1 = GETDATE()
546-- EXEC delete_sursa
547-- EXEC add_sursa
548-- SET @data2 = GETDATE()
549
550
551-- SET @data3 = GETDATE()
552-- EXEC view_1
553-- SET @data4 = GETDATE()
554
555
556-- INSERT RulariTeste
557-- VALUES ('tabel_3 + view_1',@data1,@data4)
558-- INSERT RulariTesteTabele
559-- VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),1,@data1,@data2)
560-- INSERT RulariTesteViewuri
561-- VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),1,@data3,@data4)
562-- EXEC add_achizitie
563--END
564--GO
565
566--CREATE PROCEDURE run_all AS BEGIN
567-- EXEC run_achizitie
568-- EXEC run_antichitate
569-- EXEC run_sursa
570
571-- SELECT * FROM RulariTeste
572--END
573--GO
574--EXEC add_sursa
575--EXEC add_antichitate
576--EXEC add_achizitie
577
578--EXEC delete_achizitie
579--EXEC delete_antichitate
580--EXEC delete_sursa
581
582EXEC run_all