Demo Data for the Index Lessons

This script generates small, repeatable demo tables (increase the TOP values to 300000 and 50000 to test real performance):

dbo.BigOrdersData     300 orders  (2022 - 2025)
dbo.BigCustomersData   50 customers

The data is generated with simple math on a row number, so it is the SAME every time you run it (results in the lessons are repeatable). Runs in about a second.

Each index lesson copies these tables into its own table, so the lessons do not disturb each other and can be re-run any time.

The demo data on this site is deliberately small (300 orders and 50 customers) so the script runs instantly. With so few rows, SQL Server may choose a scan where a big table would use a seek, and the logical reads are tiny. To see the real performance difference, change TOP (300) to TOP (300000) and TOP (50) to TOP (50000) in the demo data script.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

DROP TABLE IF EXISTS dbo.BigOrdersData;
DROP TABLE IF EXISTS dbo.BigCustomersData;
GO

1. Customers (50 rows)

WITH Numbers AS
(
    SELECT TOP (50) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects a CROSS JOIN sys.all_objects b
)
SELECT CAST(n AS INT)                                                                   AS CustomerID,
       CAST(CONCAT('Customer ', n) AS VARCHAR(50))                                      AS CustomerName,
       CAST(CONCAT('customer', n, '@mail.com') AS VARCHAR(100))                         AS Email,
       CAST(CHOOSE(n % 8 + 1, 'Hyderabad', 'Bengaluru', 'Chennai', 'Mumbai',
                              'Pune', 'Delhi', 'Kolkata', 'Jaipur') AS VARCHAR(30))     AS City,
       CAST(CHOOSE(n % 3 + 1, 'Retail', 'Corporate', 'SMB') AS VARCHAR(20))             AS Segment,
       CAST(DATEADD(DAY, n % 1500, '2021-01-01') AS DATE)                               AS SignupDate
INTO dbo.BigCustomersData
FROM Numbers;

ALTER TABLE dbo.BigCustomersData ALTER COLUMN CustomerID INT NOT NULL;
ALTER TABLE dbo.BigCustomersData ADD CONSTRAINT PK_BigCustomersData PRIMARY KEY (CustomerID);
GO

(50 rows affected)

2. Orders (300 rows)

WITH Numbers AS
(
    SELECT TOP (300) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects a CROSS JOIN sys.all_objects b CROSS JOIN (VALUES (1), (2), (3)) c(x)
)
SELECT CAST(n AS INT)                                                         AS OrderID,
       CAST((n * 7919) % 50 + 1 AS INT)                                    AS CustomerID,
       CAST((n * 31) % 20 + 1 AS INT)                                         AS ProductID,
       CAST(DATEADD(DAY, (n * 13) % 1461, '2022-01-01') AS DATE)              AS OrderDate,
       CAST(CASE n % 20 WHEN 0 THEN 'Cancelled'
                        WHEN 1 THEN 'Pending'
                        WHEN 2 THEN 'Shipped'
                        ELSE 'Delivered' END AS VARCHAR(10))                  AS Status,        -- Pending = 5%
       CAST(CHOOSE(n % 4 + 1, 'North', 'South', 'East', 'West') AS VARCHAR(10)) AS Region,
       CAST(n % 10 + 1 AS INT)                                                AS Quantity,
       CAST((n * 37) % 5000 + 100 AS DECIMAL(10,2))                           AS UnitPrice,
       CAST((n % 10 + 1) * ((n * 37) % 5000 + 100) AS DECIMAL(12,2))          AS Amount,
       CAST(CASE WHEN n % 50 = 0 THEN 1 ELSE 0 END AS BIT)                    AS IsDeleted,     -- 2% deleted
       CAST(CASE WHEN n % 25 = 0 THEN CONCAT('SAVE', n % 100) END AS VARCHAR(10)) AS CouponCode, -- 4% have a coupon
       CAST(CONCAT('Order note for order number ', n) AS CHAR(80))            AS Notes          -- makes rows wider
INTO dbo.BigOrdersData
FROM Numbers;

ALTER TABLE dbo.BigOrdersData ALTER COLUMN OrderID INT NOT NULL;
ALTER TABLE dbo.BigOrdersData ADD CONSTRAINT PK_BigOrdersData PRIMARY KEY (OrderID);
GO

(300 rows affected)

Check

SELECT 'BigCustomersData' AS TableName, COUNT(*) AS TotalRows FROM dbo.BigCustomersData
UNION ALL
SELECT 'BigOrdersData', COUNT(*) FROM dbo.BigOrdersData;

SELECT Status, COUNT(*) AS Orders FROM dbo.BigOrdersData GROUP BY Status;

EXEC sp_spaceused 'dbo.BigOrdersData';
GO