Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE Magazin_de_Antichitati
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_RulariTesteTabele_Tabele]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
- ALTER TABLE [RulariTesteTabele] DROP CONSTRAINT [FK_RulariTesteTabele_Tabele]
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_TesteTabele_Tabele]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
- ALTER TABLE [TesteTabele] DROP CONSTRAINT [FK_TesteTabele_Tabele]
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_RulariTesteTabele_RulariTeste]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
- ALTER TABLE [RulariTesteTabele] DROP CONSTRAINT FK_RulariTesteTabele_RulariTeste
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_RulariTesteViewuri_RulariTeste]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
- ALTER TABLE [RulariTesteViewuri] DROP CONSTRAINT FK_RulariTesteViewuri_RulariTeste
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_TesteTabele_Teste]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
- ALTER TABLE [TesteTabele] DROP CONSTRAINT FK_TesteTabele_Teste
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_TesteViewuri_Teste]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
- ALTER TABLE [TesteViewuri] DROP CONSTRAINT FK_TesteViewuri_Teste
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_RulariTesteViewuri_Viewuri]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
- ALTER TABLE [RulariTesteViewuri] DROP CONSTRAINT FK_RulariTesteViewuri_Viewuri
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_TesteViewuri_Viewuri]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
- ALTER TABLE [TesteViewuri] DROP CONSTRAINT FK_TesteViewuri_Viewuri
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[Tabele]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
- DROP TABLE [Tabele]
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[RulariTesteTabele]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
- DROP TABLE [RulariTesteTabele]
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[RulariTesteViewuri]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
- DROP TABLE [RulariTesteViewuri]
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[RulariTeste]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
- DROP TABLE [RulariTeste]
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[TesteTabele]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
- DROP TABLE [TesteTabele]
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[TesteViewuri]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
- DROP TABLE [TesteViewuri]
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[Teste]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
- DROP TABLE [Teste]
- GO
- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[Viewuri]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
- DROP TABLE [Viewuri]
- GO
- CREATE TABLE [Tabele] (
- [CodTabel] [INT] IDENTITY (1, 1) NOT NULL ,
- [Nume] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [RulariTesteTabele] (
- [CodRulareTest] [INT] NOT NULL ,
- [CodTabel] [INT] NOT NULL ,
- [IncepeLa] [datetime] NOT NULL ,
- [SeIncheieLa] [datetime] NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [RulariTesteViewuri] (
- [CodRulareTest] [INT] NOT NULL ,
- [CodView] [INT] NOT NULL ,
- [IncepeLa] [datetime] NOT NULL ,
- [SeIncheieLa] [datetime] NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [RulariTeste] (
- [CodRulareTest] [INT] IDENTITY (1, 1) NOT NULL ,
- [Descriere] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
- [IncepeLa] [datetime] NULL ,
- [SeIncheieLa] [datetime] NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [TesteTabele] (
- [CodTest] [INT] NOT NULL ,
- [CodTabel] [INT] NOT NULL ,
- [NrRanduri] [INT] NOT NULL ,
- [Pozitie] [INT] NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [TesteViewuri] (
- [CodTest] [INT] NOT NULL ,
- [CodView] [INT] NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [Teste] (
- [CodTest] [INT] IDENTITY (1, 1) NOT NULL ,
- [Nume] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [Viewuri] (
- [CodView] [INT] IDENTITY (1, 1) NOT NULL ,
- [Nume] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
- ) ON [PRIMARY]
- GO
- ALTER TABLE [Tabele] WITH NOCHECK ADD
- CONSTRAINT [PK_Tabele] PRIMARY KEY CLUSTERED
- (
- [CodTabel]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [RulariTesteTabele] WITH NOCHECK ADD
- CONSTRAINT [PK_RulariTesteTabele] PRIMARY KEY CLUSTERED
- (
- [CodRulareTest],
- [CodTabel]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [RulariTesteViewuri] WITH NOCHECK ADD
- CONSTRAINT [PK_RulariTesteViewuri] PRIMARY KEY CLUSTERED
- (
- [CodRulareTest],
- [CodView]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [RulariTeste] WITH NOCHECK ADD
- CONSTRAINT [PK_RulariTeste] PRIMARY KEY CLUSTERED
- (
- [CodRulareTest]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [TesteTabele] WITH NOCHECK ADD
- CONSTRAINT [PK_TesteTabele] PRIMARY KEY CLUSTERED
- (
- [CodTest],
- [CodTabel]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [TesteViewuri] WITH NOCHECK ADD
- CONSTRAINT [PK_TesteViewuri] PRIMARY KEY CLUSTERED
- (
- [CodTest],
- [CodView]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [Teste] WITH NOCHECK ADD
- CONSTRAINT [PK_Teste] PRIMARY KEY CLUSTERED
- (
- [CodTest]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [Viewuri] WITH NOCHECK ADD
- CONSTRAINT [PK_Viewuri] PRIMARY KEY CLUSTERED
- (
- [CodView]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [RulariTesteTabele] ADD
- CONSTRAINT [FK_RulariTesteTabele_Tabele] FOREIGN KEY
- (
- [CodTabel]
- ) REFERENCES [Tabele] (
- [CodTabel]
- ) ON DELETE CASCADE ON UPDATE CASCADE ,
- CONSTRAINT [FK_RulariTesteTabele_RulariTeste] FOREIGN KEY
- (
- [CodRulareTest]
- ) REFERENCES [RulariTeste] (
- [CodRulareTest]
- ) ON DELETE CASCADE ON UPDATE CASCADE
- GO
- ALTER TABLE [RulariTesteViewuri] ADD
- CONSTRAINT [FK_RulariTesteViewuri_RulariTeste] FOREIGN KEY
- (
- [CodRulareTest]
- ) REFERENCES [RulariTeste] (
- [CodRulareTest]
- ) ON DELETE CASCADE ON UPDATE CASCADE ,
- CONSTRAINT [FK_RulariTesteViewuri_Viewuri] FOREIGN KEY
- (
- [CodView]
- ) REFERENCES [Viewuri] (
- [CodView]
- ) ON DELETE CASCADE ON UPDATE CASCADE
- GO
- ALTER TABLE [TesteTabele] ADD
- CONSTRAINT [FK_TesteTabele_Tabele] FOREIGN KEY
- (
- [CodTabel]
- ) REFERENCES [Tabele] (
- [CodTabel]
- ) ON DELETE CASCADE ON UPDATE CASCADE ,
- CONSTRAINT [FK_TesteTabele_Teste] FOREIGN KEY
- (
- [CodTest]
- ) REFERENCES [Teste] (
- [CodTest]
- ) ON DELETE CASCADE ON UPDATE CASCADE
- GO
- ALTER TABLE [TesteViewuri] ADD
- CONSTRAINT [FK_TesteViewuri_Teste] FOREIGN KEY
- (
- [CodTest]
- ) REFERENCES [Teste] (
- [CodTest]
- ),
- CONSTRAINT [FK_TesteViewuri_Viewuri] FOREIGN KEY
- (
- [CodView]
- ) REFERENCES [Viewuri] (
- [CodView]
- )
- GO
- INSERT Tabele
- VALUES ('Sursa'),('Achizitie'),('Antichitate')
- GO
- INSERT Viewuri
- VALUES ('View_1Tabel'),('View_2Tabele'),('View_2Tabele_GroupBy')
- GO
- INSERT Teste
- VALUES ('view_1'),('view_2'),('view_3'),('add_sursa'),('add_antichitate'),('add_achizitie'),('delete_achizitie'),('delete_antichitate'),('delete_sursa')
- GO
- INSERT TesteViewuri
- VALUES (1,1),(2,2),(3,3)
- GO
- INSERT TesteTabele
- 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)
- GO
- --PROCEDURI
- --CREATE PROCEDURE view_1 AS BEGIN
- -- SELECT * FROM View_1Tabel
- --END
- --GO
- --CREATE PROCEDURE view_2 AS BEGIN
- -- SELECT * FROM View_2Tabele
- --END
- --GO
- --CREATE PROCEDURE view_3 AS BEGIN
- -- SELECT * FROM View_2Tabele_GroupBy
- --END
- --GO
- --CREATE PROCEDURE add_sursa AS BEGIN
- -- DECLARE @count INT = 10000
- -- WHILE @count <= 110000 BEGIN
- -- INSERT Sursa
- -- VALUES (@count,'nume_' + CAST(@count AS VARCHAR(30)),'prenume_' + CAST(@count AS VARCHAR(30)))
- -- SET @count = @count + 1
- -- END
- --END
- --GO
- --CREATE PROCEDURE add_antichitate AS BEGIN
- -- DECLARE @count INT = 10000
- -- WHILE @count <= 110000 BEGIN
- -- INSERT Antichitate
- -- VALUES (@count,'antichitatea_' + CAST(@count AS VARCHAR(30)),1,1,1)
- -- SET @count = @count + 1
- -- END
- --END
- --GO
- --CREATE PROCEDURE add_achizitie AS BEGIN
- -- DECLARE @count INT = 10000
- -- WHILE @count <= 110000 BEGIN
- -- INSERT Achizitie
- -- VALUES (@count,@count,'2017-12-12',1)
- -- SET @count = @count + 1
- -- END
- --END
- --GO
- --CREATE PROCEDURE delete_achizitie AS BEGIN
- -- DELETE FROM Achizitie
- -- WHERE CodA > 9999
- --END
- --GO
- --CREATE PROCEDURE delete_antichitate AS BEGIN
- -- DELETE FROM Antichitate
- -- WHERE CodA > 9999
- --END
- --GO
- --CREATE PROCEDURE delete_sursa AS BEGIN
- -- DELETE FROM Sursa
- -- WHERE CodS > 9999
- --END
- --GO
- --CREATE PROCEDURE run_achizitie AS BEGIN
- -- DECLARE @data1 DATETIME, @data2 DATETIME, @data3 DATETIME, @data4 DATETIME
- -- SET @data1 = GETDATE()
- -- EXEC delete_achizitie
- -- EXEC add_achizitie
- -- SET @data2 = GETDATE()
- -- SET @data3 = GETDATE()
- -- EXEC view_2
- -- SET @data4 = GETDATE()
- -- INSERT RulariTeste
- -- VALUES ('tabel_2 + view_2',@data1,@data4)
- -- INSERT RulariTesteTabele
- -- VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),2,@data1,@data2)
- -- INSERT RulariTesteViewuri
- -- VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),2,@data3,@data4)
- --END
- --GO
- --CREATE PROCEDURE run_antichitate AS BEGIN
- -- EXEC delete_achizitie
- -- DECLARE @data1 DATETIME, @data2 DATETIME, @data3 DATETIME, @data4 DATETIME
- -- SET @data1 = GETDATE()
- -- EXEC delete_antichitate
- -- EXEC add_antichitate
- -- SET @data2 = GETDATE()
- -- SET @data3 = GETDATE()
- -- EXEC view_3
- -- SET @data4 = GETDATE()
- -- INSERT RulariTeste
- -- VALUES ('tabel_3 + view_3',@data1,@data4)
- -- INSERT RulariTesteTabele
- -- VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),3,@data1,@data2)
- -- INSERT RulariTesteViewuri
- -- VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),3,@data3,@data4)
- -- EXEC add_achizitie
- --END
- --GO
- --CREATE PROCEDURE run_sursa AS BEGIN
- -- EXEC delete_achizitie
- -- DECLARE @data1 DATETIME, @data2 DATETIME, @data3 DATETIME, @data4 DATETIME
- -- SET @data1 = GETDATE()
- -- EXEC delete_sursa
- -- EXEC add_sursa
- -- SET @data2 = GETDATE()
- -- SET @data3 = GETDATE()
- -- EXEC view_1
- -- SET @data4 = GETDATE()
- -- INSERT RulariTeste
- -- VALUES ('tabel_3 + view_1',@data1,@data4)
- -- INSERT RulariTesteTabele
- -- VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),1,@data1,@data2)
- -- INSERT RulariTesteViewuri
- -- VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),1,@data3,@data4)
- -- EXEC add_achizitie
- --END
- --GO
- --CREATE PROCEDURE run_all AS BEGIN
- -- EXEC run_achizitie
- -- EXEC run_antichitate
- -- EXEC run_sursa
- -- SELECT * FROM RulariTeste
- --END
- --GO
- --EXEC add_sursa
- --EXEC add_antichitate
- --EXEC add_achizitie
- --EXEC delete_achizitie
- --EXEC delete_antichitate
- --EXEC delete_sursa
- EXEC run_all
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement