With ten contacts every query is fast. With a hundred thousand it isn’t necessarily any more, and you don’t notice that during development but later in operation, when the contact list suddenly takes two seconds. This lesson shows how to see it beforehand: you measure how much a query reads, you read in the execution plan how the server proceeds, and you create indexes that save it work.

An index is the same thing as the index at the end of a book. Without an index you read the whole book to find every passage about “Kladde”. With an index you look it up and turn to the right pages. But an index also costs something: it takes space, and whoever changes the book has to change the index too. That is exactly how it is in the database.

The practice database

For this lesson you need more rows than in practice. The following script creates a database indexes with 100,000 invented contacts and 200,000 appointments. Save it as k2-practice/indexes/indexes.sql and run it in the extension’s editor, connected to “Kladde local”. It takes a few seconds.

SQL
-- indexes.sql: the practice database of lesson 2.4.5 with 100,000 invented contacts and 200,000 appointments.
IF DB_ID(N'indexes') IS NOT NULL
BEGIN
    ALTER DATABASE indexes SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE indexes;
END;
GO

CREATE DATABASE indexes;
GO

USE indexes;
GO

CREATE TABLE dbo.Contacts
(
    Id int NOT NULL CONSTRAINT PK_Contacts PRIMARY KEY,
    Name nvarchar(100) NOT NULL,
    Email nvarchar(254) NOT NULL,
    City nvarchar(100) NOT NULL
);

CREATE TABLE dbo.Appointments
(
    Id int NOT NULL CONSTRAINT PK_Appointments PRIMARY KEY,
    ContactId int NOT NULL CONSTRAINT FK_Appointments_Contacts REFERENCES dbo.Contacts (Id),
    Title nvarchar(200) NOT NULL,
    Start datetimeoffset(0) NOT NULL,
    [End] datetimeoffset(0) NOT NULL
);

INSERT INTO dbo.Contacts (Id, Name, Email, City)
SELECT value,
       CONCAT(CHOOSE(value % 8 + 1, N'Ada', N'Bernd', N'Clara', N'David', N'Emma', N'Felix', N'Greta', N'Hanna'), N' ',
              CHOOSE(value % 7 + 1, N'Sample', N'Example', N'Trial', N'Fictional', N'Invented', N'Template', N'Draft'), N' ', value),
       CONCAT(N'person', value, N'@example.com'),
       CHOOSE(value % 5 + 1, N'Hamburg', N'Bremen', N'Kiel', N'Lübeck', N'Lüneburg')
FROM GENERATE_SERIES(1, 100000);

INSERT INTO dbo.Appointments (Id, ContactId, Title, Start, [End])
SELECT value,
       (value - 1) % 100000 + 1,
       CHOOSE(value % 4 + 1, N'Intro call', N'Callback', N'Discuss quote', N'Maintenance'),
       DATEADD(minute, value % 20 * 30, DATEADD(day, value % 365, CAST('2025-01-01T08:00:00+01:00' AS datetimeoffset(0)))),
       DATEADD(minute, value % 20 * 30 + 30, DATEADD(day, value % 365, CAST('2025-01-01T08:00:00+01:00' AS datetimeoffset(0))))
FROM GENERATE_SERIES(1, 200000);
  • GENERATE_SERIES(1, 100000) returns a table with a column value and the numbers from 1 to 100,000. It has existed since SQL Server 2022.
  • CHOOSE(n, a, b, …) takes the nth value of the list. With value % 8 + 1 the first names run through in turn. Every name ends with its number and is therefore unique; all the people are invented.
  • Every contact gets two appointments of 30 minutes each, spread over the year. All times carry +01:00, even in summer; for the exercise that doesn’t matter.

Note: the tables have primary keys and a foreign key, but otherwise not a single index.