SQL Indexes Partial or Filtered Index

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

SQL Server and SQLite allow to create indexes that contain not only a subset of columns, but also a subset of rows.

Consider a constant growing amount of orders with order_state_id equal to finished (2), and a stable amount of orders with order_state_id equal to started (1).

If your business make use of queries like this:

SELECT id, comment
  FROM orders
 WHERE order_state_id =  1
   AND product_id = @some_value;

Partial indexing allows you to limit the index, including only the unfinished orders:

CREATE INDEX Started_Orders
          ON orders(product_id)
       WHERE order_state_id = 1;

This index will be smaller than an unfiltered index, which saves space and reduces the cost of updating the index.



Got any SQL Question?