sql/Lab 10.sql at master · qeqer/sql · GitHub

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
USE Airport_Norm;

IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Today')
BEGIN
	DROP INDEX [IX_Today] ON [Aims_Large_Index];
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IXC_Today')
BEGIN
	DROP INDEX [IXC_Today] ON [Aims_Large];
END
SET STATISTICS TIME ON;
CREATE CLUSTERED INDEX [IXC_Today] ON [Aims_Large]
(
	 Birth_Year ASC
)

CREATE NONCLUSTERED INDEX [IX_Today] ON [Aims_Large_Index]
(
	Birth_Year ASC
)

GO
DBCC DROPCLEANBUFFERS
GO
INSERT [Aims_Large](Birth_Year) VALUES(2000), (1980), (1999), (1988), (1981), (2011), (2015), (2016), (2013), (1968),(1975), (2000), (2001), (2001), (2011), (2004), (1987), (1976), (1978), (1999);
INSERT [Aims_Large_Index](Birth_Year) VALUES (2000), (1980), (1999), (1988), (1981), (2011), (2015), (2016), (2013), (1968),(1975), (2000), (2001), (2001), (2011), (2004), (1987), (1976), (1978), (1999);
GO
DBCC DROPCLEANBUFFERS
GO
INSERT INTO Aims_Large(Birth_Year)
	SELECT Birth_Year FROM [Age_Of_People];
INSERT INTO Aims_Large_Index(Birth_Year)
	SELECT Birth_Year FROM [Age_Of_People];


GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS TIME ON;
SELECT * FROM Aims_Large_Index WHERE Birth_Year <= 1980;
SET STATISTICS TIME OFF;

GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS TIME ON;
SELECT * FROM Aims_Large WHERE Birth_Year <= 1980;
SET STATISTICS TIME OFF;

GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS TIME ON;
SELECT ID, info
	FROM [Aims_Large]
		WHERE Birth_Year = 2500;
SET STATISTICS TIME OFF;

GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS TIME ON;
SELECT ID
	FROM [Aims_Large_Index]
		WHERE Birth_Year = 2500;
SET STATISTICS TIME OFF;

GO
DBCC DROPCLEANBUFFERS
GO
INSERT [Aims_Large](Birth_Year) VALUES(2000), (3000), (2001), (3001), (0), (3004), (1), (1000), (10000), (40000),(2000), (3000), (2001), (3001), (0), (3004), (1), (1000), (10000), (40000);
INSERT [Aims_Large_Index](Birth_Year) VALUES(2000), (3000), (2001), (3001), (0), (3004), (1), (1000), (10000), (40000),(2000), (3000), (2001), (3001), (0), (3004), (1), (1000), (10000), (40000);
DBCC DROPCLEANBUFFERS
GO

GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS TIME ON;
SELECT ID, info
	FROM [Aims_Large_Index]
	JOIN [Passenger] ON Passenger.Passenger_ID = Aims_Large_Index.ID
		WHERE Country_ID = 1;
SET STATISTICS TIME OFF;

GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS TIME ON;
SELECT ID, info, Birth_Year
	FROM [Aims_Large_Index]
	JOIN [Flight] ON CONVERT(VARCHAR(4), Dep_Time, 111) = Birth_Year
	WHERE Birth_Year = 2004;
SET STATISTICS TIME OFF;