I saw a list of database interview questions, and one was to name the different types of indexes available in SQL Server. Once you start thinking, there are a lot of different types and options available.
At the highest level, there are these groupings for indexes.
— Row Store: The most popular type. These can be broken down into different types as well, which will be covered in the next section.
— Column Store: Used most often in data warehousing. Data is physically stored by column in this type of index, as opposed to by row.
— XML: Special index type for the XML data type.
— Full Text: Optimized for finding words or group of words in data, as well as more complicated word searches, like for different forms of a word.
— Memory-Optimized: Used for tables with the MEMORY_OPTIMIZED option set to ON (Sometimes referred to as In-Memory tables). These can be either Hash or Non-Clustered indexes.
— Spatial: Used for the spatial (Geometry or Geography) data types.
Within those grouping, indexes can be further described with these sub-types. All of these apply to Row Store, but some of these sub-types apply to other index types as well. Also, an index can combine some of these options.
A Row store index will be one of two types:
— Clustered: The data values for the specified columns are physically stored in the order specified by the index. This limits you to one clustered index for a table.
— Non-Clustered: This index will store values for the specified columns, as well as a pointer back to the actual data row.
Other index types:
— Unique: Each set of index values must be unique for the table. An error will occur if an insert for a duplicate is attempted.
— Filtered: Creates an index on a subset of the table’s data. In the index definition, a WHERE clause will determine which rows are included.
— Composite: An index with more than one column.
— Included: An index can be defined with INCLUDED columns, where values for those columns will be included in the leaf level of the index. This is useful for values that aren’t part of the WHERE clause in a query but the values are specified in the SELECT.
If all of the columns needed for a query are in the index (Either as a key or included columns) the index is referred to as a Covering index.