Page 116 - SQL

pointer to the row in the table which it represents.

This pointers are called a row locators. The structure of the row locator depends on whether the
data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the
row. For a clustered table, the row locator is the clustered index key.

An example of creating a non clustered index on table Employees and column
Employee_Surname:

CREATE NONCLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);

There can be multiple nonclustered indexes on the table. The read operations are generally slower
with non clustered indexes than with clustered indexes as you have to go first to index and than to
the table. There are no restrictions in write operations however.

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.

Read Indexes online: https://riptutorial.com/sql/topic/344/indexes

https://riptutorial.com/ 98