-----------------------
-- Кластерный индекс --
-----------------------

SELECT * INTO Customers_copy FROM Customers
GO

SELECT * FROM Customers WHERE id=12345;
GO
SELECT * FROM Customers_copy WHERE id=12345;
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