SQLServer Indexes
Sorted Index
If you use an index that is sorted the way you would retrieve it, the SELECT statement would not do additional sorting when in retrieval.
CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);When you execute the query
SELECT * FROM scoreboard ORDER BY score DESC;The database system would not do additional sorting, since it can do an index-lookup in that order.
Partial or Filtered Index
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.