How to Format SQL CREATE INDEX Statements for Maximum Clarity

By Sharon Ben-Moshe · October 12, 2026 · 1 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Format CREATE INDEX statements cleanly. Learn conventions for composite indexes, partial/filtered indexes, INCLUDE covering columns, and concurrent builds.

Quick answer

Format CREATE INDEX statements across multiple indented lines: place table and index names on line one, column keys (and sort orders) on line two, INCLUDE covering columns on line three, and WHERE partial filter clauses on line four. Following consistent index naming conventions like idx_tablename_columns keeps database schemas organized.

FAQs

What naming convention should I use for SQL indexes?

Use `idx_{table}_{column1}_{column2}` for standard B-Tree indexes, and `uq_{table}_{column}` for unique indexes. Adding the column abbreviations helps identify the index's purpose during EXPLAIN analysis.

What is an INCLUDE column in an index?

An INCLUDE column (supported in PostgreSQL and SQL Server) stores non-key payload columns in the leaf nodes of the B-Tree. This creates a 'covering index' that satisfies queries entirely from the index without table heap lookups, without increasing B-Tree branch size.

Why is CREATE INDEX CONCURRENTLY important in PostgreSQL?

In PostgreSQL, standard CREATE INDEX locks the table against writes (INSERT, UPDATE, DELETE) until indexing completes. Using `CREATE INDEX CONCURRENTLY` builds the index without blocking production writes.

What is a partial index?

A partial index includes a `WHERE` clause (e.g., `WHERE status = 'active'`). It only indexes rows matching the condition, consuming dramatically less disk space and memory.

SQL Formatting