-- Таблиця з 10 мільйонами користувачів БЕЗ індексу
CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    email NVARCHAR(100),
    first_name NVARCHAR(50),
    last_name NVARCHAR(50),
    registration_date DATE
);

-- Пошук користувача за email
SELECT * FROM Users WHERE email = 'ivan@example.com';
-- ⏱️ Час виконання: ~15 секунд
-- 🔍 Метод: TABLE SCAN (перегляд ВСІХ 10 млн рядків)

-- Створюємо індекс на стовпець email
CREATE INDEX IX_Users_Email ON Users(email);

-- Той самий запит
SELECT * FROM Users WHERE email = 'ivan@example.com';
-- ⏱️ Час виконання: ~0.001 секунди (у тисячі разів швидше!)
-- 🔍 Метод: INDEX SEEK (використання індексу)

CREATE TABLE Employees (
    employee_id INT,          -- 4 байти
    first_name NVARCHAR(50),  -- до 100 байт (Unicode)
    last_name NVARCHAR(50),   -- до 100 байт
    salary DECIMAL(10,2),     -- 5 байт
    hire_date DATE            -- 3 байти
);
-- Приблизно 212 байт на рядок

Page size = 8192 байти
- Header = 96 байт
- Row Offset Array = 36 байт
= 8060 байт для даних

Рядків на page: 8060 / 212 ≈ 38 рядків

┌──────────────────────────────────┐
│  PAGE HEADER (96 bytes)          │
├──────────────────────────────────┤
│ • Page Type (data/index)         │ ← Тип сторінки
│ • Object ID                      │ ← Якій таблиці належить
│ • Free Space (bytes)             │ ← Скільки вільного місця
│ • Previous Page ID               │ ← Подвійно зв'язаний список
│ • Next Page ID                   │ ← для швидкого сканування
│ • Slot Count                     │ ← Кількість рядків
└──────────────────────────────────┘

┌─────────────────────────────────────────────────┐
│  EXTENT = 64 KB                                 │
├──────┬──────┬──────┬──────┬──────┬──────┬──────┤
│ Page │ Page │ Page │ Page │ Page │ Page │ Page │
│  1   │  2   │  3   │  4   │  5   │  6   │  7   │
│ 8KB  │ 8KB  │ 8KB  │ 8KB  │ 8KB  │ 8KB  │ 8KB  │
└──────┴──────┴──────┴──────┴──────┴──────┴──────┘

CREATE TABLE Products (
    product_id INT,
    name NVARCHAR(100),
    description NVARCHAR(MAX),  -- ⚠️ До 2 ГБ!
    specs NVARCHAR(MAX),        -- ⚠️ До 2 ГБ!
    price DECIMAL(10,2)
);

Рядок з NVARCHAR(MAX):
- Маленька description (500 байт) → зберігається IN-ROW (в page)
- Велика description (10 KB)      → зберігається OFF-ROW (окремо)

┌──────── DATA PAGE ────────┐    ┌─── LOB PAGES ────┐
│ product_id: 1             │    │ description:     │
│ name: "Laptop"            │───→│ (10 KB текст)    │
│ description: [pointer]    │    │                  │
│ price: 1299.99            │    └──────────────────┘
└───────────────────────────┘

-- Скільки pages займає таблиця?
SELECT
    t.name AS TableName,
    p.rows AS RowCount,
    SUM(a.used_pages) AS UsedPages,
    SUM(a.used_pages) * 8 / 1024.0 AS UsedMB,
    SUM(a.used_pages) / 8.0 AS UsedExtents
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name = 'Users'
GROUP BY t.name, p.rows;

TableName  RowCount  UsedPages  UsedMB   UsedExtents
Users      1,000,000 26,316     205.28   3,289

Крок 1: Root Page
        → 72 > 50 AND 72 < 100
        → Іде middle child (L1B)

Крок 2: Intermediate Page (L1B)
        → 72 > 60 AND 72 < 75
        → Іде right child (Leaf5)

Крок 3: Leaf Page (Leaf5)
        → Знайдено: 72 ✅

Всього операцій читання: 3 pages (замість сканування всіх pages!)

-- Приклад: Замовлення за датами
CREATE CLUSTERED INDEX IX_Orders_Date ON Orders(order_date);

-- Дані на диску:
Page 1: [2024-01-01] [2024-01-01] [2024-01-02]  ← січневі разом
Page 2: [2024-01-15] [2024-01-16] [2024-01-20]
Page 3: [2024-02-01] [2024-02-02] [2024-02-05]  ← лютневі разом

SELECT * FROM Orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

-- SQL Server:
-- 1. Знаходить першу січневу page (B-Tree)
-- 2. Читає pages ПОСЛІДОВНО доки order_date <= '2024-01-31'
-- 3. Зупиняється на лютневій page

-- Результат: Читаємо лише релевантні pages! ⚡

SELECT user_id, name FROM Users WHERE email = 'alice@example.com';

CREATE TABLE Products (
    product_id INT PRIMARY KEY CLUSTERED,         -- Clustered Index #1
    category_id INT,
    name NVARCHAR(200),
    price DECIMAL(10,2),
    stock_quantity INT
);

-- Non-Clustered Indexes
CREATE NONCLUSTERED INDEX IX_Products_Category
    ON Products(category_id);                     -- Index #2

CREATE NONCLUSTERED INDEX IX_Products_Name
    ON Products(name);                            -- Index #3

CREATE NONCLUSTERED INDEX IX_Products_Price
    ON Products(price);                           -- Index #4

CREATE INDEX IX_Users_LastName_FirstName
ON Users(last_name, first_name);

-- Запит, який часто виконується
SELECT email, first_name, last_name
FROM Users
WHERE email = 'ivan@example.com';

-- ❌ Без INCLUDE
CREATE INDEX IX_Users_Email ON Users(email);
-- Процес: Index Seek (email) → Key Lookup (first_name, last_name) 💥 ПОВІЛЬНО

-- ✅ З INCLUDE (Covering Index)
CREATE INDEX IX_Users_Email_Covering
ON Users(email)
INCLUDE (first_name, last_name);

-- Процес: Index Seek (email) → ВСІ ДАНІ в індексі! ⚡ ШВИДКО
-- Не потрібен Key Lookup!

CREATE UNIQUE INDEX IX_Users_Email_Unique
ON Users(email);

-- Тепер неможливо вставити дублікат
INSERT INTO Users (email) VALUES ('test@example.com');  -- ✅ OK
INSERT INTO Users (email) VALUES ('test@example.com');  -- ❌ ERROR!

-- Проблема: тільки 1% замовлень активні, але ми часто їх шукаємо
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    status NVARCHAR(20),  -- 'Active', 'Completed', 'Cancelled'
    order_date DATE
);

-- ❌ Звичайний індекс (індексує ВСІ 10 млн рядків)
CREATE INDEX IX_Orders_Status ON Orders(status);

-- ✅ Filtered Index (індексує лише активні ~ 100K рядків)
CREATE INDEX IX_Orders_Active
ON Orders(customer_id, order_date)
WHERE status = 'Active';

-- Виграш: менший розмір індексу, швидше обслуговування

SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'DETAILED'
) AS ips
INNER JOIN sys.indexes AS i
    ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10  -- Більше 10% фрагментації
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Часто виконуваний запит
SELECT product_id, name, price
FROM Products
WHERE category_id = 5
  AND price > 100
ORDER BY price DESC;

-- ✅ Оптимальний індекс
CREATE INDEX IX_Products_Category_Price
ON Products(category_id, price DESC)
INCLUDE (name);

-- Таблиця
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    status NVARCHAR(20)
);

-- Повільний запит (10 секунд на 10 млн рядків)
SELECT order_id, customer_id, total_amount
FROM Orders
WHERE customer_id = 12345
  AND order_date >= '2024-01-01'
  AND status = 'Completed';