blob: a7eee14d431525dbe401836f4813f264cca45cb4 (
plain)
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
|
-----------------------
-- Кластерный индекс --
-----------------------
SELECT * INTO Customers_copy FROM Customers
GO
SELECT * FROM Customers WHERE id=12345;
GO
SELECT * FROM Customers_copy WHERE id=12345;
GO
--------------------------
-- Некластерные индексы --
--------------------------
-- составной
SELECT id, warehouse_id FROM Boxes
WHERE warehouse_id = 12465
GO
CREATE INDEX Boxes_Composite ON Boxes (order_id, warehouse_id);
GO
DROP INDEX Boxes_Composite ON Boxes;
GO
-- покрывающий
SELECT * FROM Orders
WHERE date > '2021-09-04'
GO
CREATE INDEX Orders_Index_Covering ON Orders (id)
INCLUDE (customer_id, city_id, date);
GO
DROP INDEX Orders_Index_Covering ON Orders;
GO
-- уникальный
SELECT email FROM Customers WHERE name=N'customer-12451';
CREATE UNIQUE INDEX Customers_Unique ON Customers (name);
GO
DROP INDEX Customers_Unique ON Customers;
GO
-- индекс с включеными столбцами
SELECT id, customer_id, date FROM Orders
WHERE customer_id = 12345
GO
CREATE INDEX Orders_Index_Columns ON Orders (customer_id)
INCLUDE (id, date);
GO
DROP INDEX Orders_Index_Columns ON Orders;
GO
-- отфильтрованный индекс
SELECT SUM(amount) FROM Payments WHERE date >= '2021-12-01';
CREATE INDEX Payments_Filtered ON Payments (amount)
WHERE date >= '2021-12-01';
GO
DROP INDEX Payments_Filtered ON Payments;
GO
|
