PlanLab – testovacia databáza pre recepty exekučných plánov

/* DeadlockCafe | PlanLab – testovacia databáza pre recepty exekučných plánov
   Účel:  Skript vytvorí malú databázu PlanLab (zákazníci, produkty, 300 000 objednávok, 900 000 riadkov) so zámerne nerovnomernými dátami. Na nej bežia ukážky všetkých receptov kuchárky plánov.
   Režim: len testovací server | SQL Server 2016+
*/
USE master;
GO
/* PlanLab – testovacia databáza pre recepty kuchárky exekučných plánov.
   Vytvorí (alebo znova naplní) len databázu PlanLab. Nič iné na serveri nemení.
   Trvanie: zvyčajne do minúty. Miesto: cca 150 MB dát. */
IF DB_ID(N'PlanLab') IS NULL
    CREATE DATABASE PlanLab;
GO
ALTER DATABASE PlanLab SET RECOVERY SIMPLE;
GO
USE PlanLab;
GO
DROP TABLE IF EXISTS dbo.OrderLines;
DROP TABLE IF EXISTS dbo.Orders;
DROP TABLE IF EXISTS dbo.Customers;
DROP TABLE IF EXISTS dbo.Products;
GO
CREATE TABLE dbo.Customers (
    CustomerID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Customers PRIMARY KEY,
    Name       nvarchar(100) NOT NULL,
    City       varchar(40)   NOT NULL,
    Email      varchar(200)  NOT NULL,      -- zámerne varchar (recept Implicitná konverzia)
    Country    char(2)       NOT NULL,
    CreatedAt  datetime2(0)  NOT NULL
);
CREATE TABLE dbo.Products (
    ProductID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Products PRIMARY KEY,
    Name      nvarchar(100) NOT NULL,
    Category  varchar(30)   NOT NULL,
    ListPrice decimal(10,2) NOT NULL
);
CREATE TABLE dbo.Orders (
    OrderID     int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED,
    CustomerID  int           NOT NULL,
    OrderDate   date          NOT NULL,
    Status      tinyint       NOT NULL,     -- 1 nová, 3 odoslaná, 4 zrušená
    Amount      decimal(12,2) NOT NULL,
    AccountCode varchar(20)   NOT NULL,     -- zámerne varchar
    Note        varchar(200)  NULL
);
CREATE TABLE dbo.OrderLines (
    OrderID   int           NOT NULL,
    LineNum    smallint      NOT NULL,
    ProductID int           NOT NULL,
    Qty       int           NOT NULL,
    Price     decimal(10,2) NOT NULL,
    CONSTRAINT PK_OrderLines PRIMARY KEY CLUSTERED (OrderID, LineNum)
);
GO
-- ---------- dáta (generátor čísel z katalógu, bez cyklov) ----------
WITH n AS (SELECT TOP (20000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
           FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b)
INSERT dbo.Customers WITH (TABLOCK) (Name, City, Email, Country, CreatedAt)
SELECT CONCAT(N'Zákazník ', n),
       CHOOSE(n % 5 + 1, 'Bratislava', 'Košice', 'Žilina', 'Nitra', 'Praha'),
       CONCAT('zakaznik', n, '@example.com'),
       CASE WHEN n % 5 = 4 THEN 'CZ' ELSE 'SK' END,
       DATEADD(DAY, -(n % 2000), CAST('2026-10-31' AS datetime2(0)))
FROM n;

WITH n AS (SELECT TOP (500) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_columns)
INSERT dbo.Products WITH (TABLOCK) (Name, Category, ListPrice)
SELECT CONCAT(N'Produkt ', n), CHOOSE(n % 4 + 1, 'kava', 'caj', 'pecivo', 'doplnky'), 2 + n % 40
FROM n;

-- 300 000 objednávok: každá tretia patrí zákazníkovi 1 (100 000), ostatné sú rozhádzané po pár kusoch
WITH n AS (SELECT TOP (300000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
           FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b)
INSERT dbo.Orders WITH (TABLOCK) (CustomerID, OrderDate, Status, Amount, AccountCode, Note)
SELECT CASE WHEN n % 3 = 0 THEN 1 ELSE 2 + (n * 7919) % 19999 END,
       DATEADD(DAY, -(n % 1460), CAST('2026-10-31' AS date)),
       CASE WHEN n % 50 = 0 THEN 4 WHEN n % 10 = 0 THEN 1 ELSE 3 END,
       CAST(10 + (n * 37) % 100000 / 100.0 AS decimal(12,2)),
       CONCAT('AC', RIGHT(CONCAT('000000', n % 50000), 6)),
       CASE WHEN n % 7 = 0 THEN 'expres' END
FROM n;

-- 3 riadky na objednávku = 900 000
INSERT dbo.OrderLines WITH (TABLOCK) (OrderID, LineNum, ProductID, Qty, Price)
SELECT o.OrderID, l.LineNum, 1 + (o.OrderID * 31 + l.LineNum * 17) % 500, 1 + (o.OrderID + l.LineNum) % 5, 5 + (o.OrderID * l.LineNum) % 200
FROM dbo.Orders AS o
CROSS JOIN (VALUES (CAST(1 AS smallint)), (CAST(2 AS smallint)), (CAST(3 AS smallint))) AS l(LineNum);
GO
-- ---------- indexy (OrderLines.ProductID zámerne bez indexu) ----------
CREATE INDEX IX_Orders_CustomerID  ON dbo.Orders (CustomerID);
CREATE INDEX IX_Orders_OrderDate   ON dbo.Orders (OrderDate);
CREATE INDEX IX_Orders_AccountCode ON dbo.Orders (AccountCode);
CREATE INDEX IX_Customers_Email    ON dbo.Customers (Email);
GO
-- ---------- kontrola ----------
SELECT t.name AS tabulka, SUM(p.rows) AS riadky
FROM sys.tables AS t
JOIN sys.partitions AS p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
GROUP BY t.name
ORDER BY t.name;

SELECT name, compatibility_level FROM sys.databases WHERE name = N'PlanLab';