From 6e1b88adf21930ce7d6ec44bbbe334d92625c986 Mon Sep 17 00:00:00 2001
From: Andrew <saintruler@gmail.com>
Date: Fri, 10 Dec 2021 21:45:12 +0400
Subject: =?UTF-8?q?=D0=94=D0=BE=D0=B1=D0=B0=D0=B2=D0=B8=D0=BB=20=D1=87?=
 =?UTF-8?q?=D0=B0=D1=81=D1=82=D1=8C=20=D1=87=D0=B5=D1=82=D0=B2=D1=91=D1=80?=
 =?UTF-8?q?=D1=82=D0=BE=D0=B3=D0=BE=20=D0=B7=D0=B0=D0=B4=D0=B0=D0=BD=D0=B8?=
 =?UTF-8?q?=D1=8F?=
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 task4_requests.sql | 75 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 75 insertions(+)
 create mode 100644 task4_requests.sql

(limited to 'task4_requests.sql')

diff --git a/task4_requests.sql b/task4_requests.sql
new file mode 100644
index 0000000..c364b95
--- /dev/null
+++ b/task4_requests.sql
@@ -0,0 +1,75 @@
+-----------------------
+-- Кластерный индекс --
+-----------------------
+
+SELECT amount FROM Payments WHERE order_id=12412;
+
+CREATE CLUSTERED INDEX Payments_Clustered ON Payments (order_id);
+GO
+DROP INDEX Payments_Clustered ON Payments;
+GO
+
+--------------------------
+-- Некластерные индексы --
+--------------------------
+
+-- составной (средне)
+
+SELECT Boxes.id, Orders.id, date
+FROM Boxes JOIN Orders
+ON Boxes.order_id = Orders.id
+WHERE Boxes.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 EXISTS(
+    SELECT * FROM Customers
+    WHERE customer_id = Customers.id
+        AND Customers.email LIKE N'%@mail.ru'
+);
+
+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 date FROM Orders
+WHERE EXISTS(
+    SELECT * FROM Customers
+    WHERE customer_id = Customers.id
+        AND Customers.email LIKE N'%@mail.ru'
+);
+
+CREATE INDEX Orders_Index_Columns ON Orders (id)
+    INCLUDE (customer_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
-- 
cgit v1.2.3