INDEX with WHERE (Filtered Index)
INDEX with WHERE (Filtered Index) is a SQL statement in the Indexes category. SQL Server/PostgreSQL: filtered index only includes rows matching a WHERE clause. Smaller and more efficient. The syntax is CREATE INDEX idx_name ON table (col) WHERE condition;. It returns filtered index. A typical example: CREATE INDEX idx_active_customers ON customers (email) WHERE status = 'active'; -- Only indexes active customers -- Index is much smaller than full table index -- Queries that benefit: SELECT * FROM customers WHERE status = 'active' AND email = 'test@test.com'; -- Uses the filtered index -- Queries that do NOT benefit: SELECT * FROM customers WHERE email = 'test@test.com'; -- No status condition, index cannot be used -- Good for:… A close relative is CREATE INDEX, which creates an index on one or more columns to speed up query performance. A close relative is DROP INDEX, which removes an existing index from a table.