Indexes are on-disk structures tied with a table/view that helps reduce I/O. Implementing a good indexing solution can have dramatic performance gains in the database. However, too many indexes will not only increase the database size but writing data will become slower, while not enough indexes mean slower query run times. Designing and implementing appropriate indexing solutions requires a good understanding of database structures and workload but also requires a good knowledge of different types of available SQL Server indexes.
Rowstore Indexes
Data is stored based on rows of data (horizontally) in rowstore indexing. Rowstore indexes are traditional indexes that are based on a B-Tree structure.
- Clustered rowstore indexes
Data rows are stored/sorted based on their index key values (columns included in the index definition). Normally the primary key of a table is the clustered index but it can be defined on a table with no primary key as well. A table that has clustered index is commonly called a clustered table and you can have only one clustered index per table. A table with no clustered index is called a heap where data is stored in an unordered fashion. - Nonclustered rowstore indexes
A nonclustered index stores the nonclustered index key values with a row locator (a pointer to a data row). If a table has clustered index, the row locator is a pointer to the clustered index key and for a heap, it points to the row.- Covering Indexes
A nonclustered index that has all the information to cover a query is a covering index. The included columns help avoid key lookups in the clustered index. - Filtered Indexes
A nonclustered index that helps reduce the size of a B-Tree structure by using a filter or in short an index with a where clause. This type of indexing is extremely helpful with sparse columns or when only a very small subset of rows in the table is desired.
- Covering Indexes
When do I use rowstore indexes?
Rowstore indexes are the best when queries are very selective or are looking for a specific value. Rowstore indexes are preferred in the OLTP structures because queries tend to be more selective requiring table seeks.
Columnstore Indexes
Columnstore indexes do not use the B-Tree structure. It stores data vertically in rowgroups based on column values. It provides great data compression and much better performance for data analytics queries.
- Clustered columnstore indexes
A clustered columnstore index is the physical storage for the entire table. It is now considered to be the standard for large warehouse tables. - Nonclustered columnstore indexes
A nonclustered columnstore index is a secondary index that is created on a rowstore table. It functions the same as clustered columnstore indexes. OLTP structures can benefit cases where concurrent data analysis is needed.
When do I use columnstore indexes?
When you have large tables such as fact tables in your warehouse and your data analysts fire queries for analytics, columnstore indexes give you the highest performance. Also remember, columnstore indexing has a higher level of data compression so for larger tables, you can save disc spaces. Or maybe your data analysts want to run their analytics directly from application databases, then leverage nonclustered columnstore indexing on a rowstore table.
Note: You can combine columnstore and rowstore indexes to fit your needs. An updateable nonclustered columnstore index can be created on a rowstore table. You can also create nonclustered rowstore index on a columnstore index.
