Types of Indexes (or Indices)

Types of indexes (Types of Indices) see Video below! 

DBAs are constantly facing challenges with making queries run faster.  One of the top tuning procedures is to add an index to the table or tables in order to increase the optimizer's accessibility to the requested data.
*Note - The first three definitions are excerpts from lyrics in my index song.  The remaining definitions come from the referenced link below.  Again, my intentions are to inspire listeners to learn through my songs.  For example - The different types of indexes in SQL Server 2016 are now ingrained in my memory!

1. Unique – “A unique index will contain – no duplicates – no row is the same – according to the key that is… Every row in the table or view – it’s gotta be new!
   Uniqueness can be a property of clustered and nonclustered indices.”
2. Clustered – “..and speaking of Clustered – it is implemented as a B-tree index structure. There can only be one per table – sorted in order… and I don’t mean sort of.  So without it all you have is a heap – with data pages that aren’t even linked…
3. Non-Clustered – Not we got a locator (woo!) – pointing to the data row’s key value.  It can be in any order – have as many as you wanna - On a table or a view - or a clustered index too.”
4.  Hash - With a hash index, data is accessed through an in-memory hash table. Hash indexes consume a fixed amount of memory, which is a function of the bucket count.
5. Filtered - An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
6. Memory-optimized non-clustered indexes - For memory-optimized nonclustered indexes, memory consumption is a function of the row count and the size of the index key columns
7. Included columns - A nonclustered index that is extended to include nonkey columns in addition to the key columns.
8. Computed columns - An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.
9. Spatial - A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.
10. XML - A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.
11. Full-text - A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.
12. Columnstore - An in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing.
    Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.

 
ONLINE REFERENCE: https://msdn.microsoft.com/en-us/library/ms175049.aspx
​

---