sqlcmd.net validated sql reference

Command guide

advanced performance SQL Server PostgreSQL SQLite

Create a Partial (Filtered) Index

Index only the rows that satisfy a `WHERE` condition using `CREATE INDEX ... WHERE condition`, shrinking the index and speeding up queries that always filter on the same predicate.

Created Last updated 3/3 supported engines validation-green 1 example 2 scenarios
Docker-validated Not currently validation-green

Example 1

Index only active orders and query them by customer

The index idx_active_orders_customer stores only the three rows where status = 'active' (rows 1, 2, 5), skipping the two closed orders. On a production table with millions of rows and a small fraction active, this index would be dramatically smaller than a full index on customer_id. The query planner recognises that the query predicate status = 'active' AND customer_id = 1 implies the index condition status = 'active' and uses the partial index automatically — no query changes are needed. To enforce that each customer has at most one active order, use CREATE UNIQUE INDEX ... WHERE status = 'active'; the uniqueness constraint applies only to active rows and does not prevent the same customer from having multiple closed orders.

Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE orders (id INT, customer_id INT, status VARCHAR(20));

INSERT INTO
  orders
VALUES
  (1, 1, 'active'),
  (2, 2, 'active'),
  (3, 1, 'closed'),
  (4, 2, 'closed'),
  (5, 1, 'active');
Validated query Shared across supported engines.
SQL
CREATE INDEX idx_active_orders_customer ON orders (customer_id)
WHERE
  status = 'active';

SELECT
  id,
  customer_id
FROM
  orders
WHERE
  status = 'active'
  AND customer_id = 1
ORDER BY
  id;
Expected result Returned rows for the shared example.
idcustomer_id
11
51

MySQL and MariaDB do not support partial indexes; the example does not run on those engines.

Useful when

Where this command helps.

  • indexing only unprocessed or active rows when most of the table consists of completed or deleted records
  • enforcing a unique constraint on a subset of rows, such as one active record per user

Explanation

What the command is doing.

A partial index (called a filtered index in SQL Server) indexes only a subset of the table's rows — those satisfying a fixed WHERE predicate. This has two benefits over a full index: the index is smaller (faster to scan, less RAM needed to cache it) and it excludes rows that queries never need to find via the index. Classic examples include indexing only rows where status = 'active', deleted_at IS NULL, or is_verified = TRUE, since most operational queries filter on those values. The query planner uses the partial index automatically when the query's WHERE clause implies the index condition — for example, WHERE status = 'active' AND customer_id = 42 can use a partial index defined on customer_id WHERE status = 'active'. PostgreSQL, SQL Server, and SQLite all support partial indexes with this syntax. MySQL and MariaDB do not support a WHERE clause on CREATE INDEX; the closest workaround is a generated column plus a conditional expression, or simply a full index on the column (accepting the larger size).

Tags

indexpartial-indexfiltered-indexperformanceddl

Related commands

Keep moving through nearby tasks.

Official documentation