DP-900: understanding and using indexes in Azure SQL Database

I will teach the skill of understanding and applying indexes in Azure SQL Database — a key topic in "Relational data in Azure" of the DP-900. Knowing how indexes work matters both for answering the exam's conceptual questions and for optimizing queries in practice. Here I explain concepts, give concrete examples, and show how to validate the impact of an index in the real world.

What you need to know

An index is a structure that speeds up reading data, similar to a book index that points to pages where a topic appears. In relational databases like Azure SQL Database, indexes reduce the number of disk pages or blocks the system has to read to satisfy a query. In practical terms, a query that previously did 2,000 logical reads can go down to 20 reads when an appropriate index is used — a 99% reduction in read work in some scenarios.

Simple example: a table Customers(id, name, city). If you write many queries that filter by city, creating an index on city can make those queries much faster because the engine searches the index instead of reading the whole table (full table scan). If the table has 1 million rows and each disk page holds 8 KB, a full scan can require thousands of pages to be read; a selective index drastically reduces that number.

Essential concepts:

• Clustered index: determines the physical order of the rows in the table. A table can have only one clustered index (e.g., PRIMARY KEY by default). Choose a clustered key that is narrow, stable, and unique whenever possible (for example, an incremental ID).
• Non-clustered index: separate structure that maintains pointers to the table rows; you can have multiple per table. They can include additional columns (INCLUDE) to create a covering index.
• Covering index: an index that contains all the columns needed for a query, avoiding additional accesses to the table. For example, an index on (customer_id) INCLUDE (order_date, total) covers a query that only returns those columns.
• Selectivity: a measure of how unique a column is. Columns with high selectivity (e.g., > 10% distinct values in a large set) benefit more from an index; columns with low selectivity (e.g., gender with only M/F) rarely help.
• Overhead: indexes speed up reads but increase the cost on write operations (INSERT/UPDATE/DELETE) and consume space. As a rule of thumb, each non-clustered index can add 20–50% to storage cost depending on the included columns.

How it works in practice

Here is a practical step-by-step to create and validate an index in Azure SQL Database using T-SQL. Assume you have a table Sales(order_id INT, customer_id INT, sale_date DATE, amount DECIMAL(10,2)).

-- 1. Criar um non-clustered index na coluna sale_date
CREATE INDEX IX_Sales_SaleDate
ON Sales(sale_date);

-- 2. Ver plano de execução para uma query que filtra por sale_date
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT order_id, amount
FROM Sales
WHERE sale_date = '2025-01-15';

-- 3. Remover o índice se não for útil
DROP INDEX IX_Sales_SaleDate ON Sales;

Steps to consider when deciding to create an index:

1. Identify frequent queries and columns used in WHERE, JOIN, ORDER BY. Azure tools like Query Performance Insight and Intelligent Insights help see the most expensive queries.
2. Evaluate selectivity: if a column has many repeated values, the index may not be useful. E.g., if 90% of rows have status = 'Active', an index on status will have little effect.
3. Test with and without the index using execution plans, STATISTICS IO/TIME and measure logical reads. Watch metrics like CPU time and elapsed time; a 10–100x improvement is common in ideal cases.
4. Monitor impact on writes: indexes increase latency for INSERT/UPDATE/DELETE. In write-heavy workloads, each index can add 1–5% overhead per operation, depending on complexity.

Common mistakes

• Indexing every column: creating too many indexes (or indexes on low-selectivity columns) increases write overhead and consumes unnecessary space. In OLTP environments, keeping 3–6 indexes per table is common; >10 is usually problematic.
• Ignoring execution plans: creating indexes without analyzing the plan may not solve the problem; the Query Optimizer decides whether to use the index. A poorly designed index may not be used at all.
• Not maintaining statistics: outdated statistics lead the Query Optimizer to suboptimal choices; remember to UPDATE STATISTICS or configure auto_update_statistics. On large tables, a statistics update can dramatically improve plans.
• Forgetting maintenance: indexes fragment over time; maintenance procedures (REORGANIZE or REBUILD) should be planned. On active tables, a REBUILD weekly or monthly may be needed depending on fragmentation (e.g., >30%).

How to practice

Practice in Azure SQL Database with a sample database (e.g., WideWorldImporters or AdventureWorks) and try:

• Create/drop indexes and compare execution plans. Observe logical_reads before/after and record concrete numbers (e.g., from 2,500 to 150 reads).
• Measure STATISTICS IO/TIME before and after to quantify gains in I/O and CPU time.
• Simulate write load to observe overhead: use scripts that perform thousands of INSERT/UPDATE to measure latency and throughput with and without an index.

Note: Azure SQL Database has automatic tuning features that suggest and apply indexes; always review suggestions and test before applying in production. To prepare for DP-900 use the OFFICIAL Practice Assessment (free) from Microsoft and the official study guide (free). These resources help verify knowledge in the exam areas without resorting to prohibited materials.

In summary

• Indexes speed up reads; there are clustered and non-clustered types, each with trade-offs.
• Choose columns with high selectivity and frequent use in filters/join/order by. Consider including columns with INCLUDE to create covering indexes.
• Test changes with execution plans and metrics (STATISTICS IO/TIME) and monitor the impact on writes.
• Avoid excess indexes, keep statistics up to date and schedule maintenance (REORGANIZE/REBUILD) to avoid fragmentation.