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

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
