Index Magic: Rowstore, Columnstore and NoStore

Indexes allow SQL Server to access your data in the most efficient manner. In fact, indexes are the only tuning technique that can improve query performance by orders of magnitude, so that queries that might have taken hours to run can be reduced to sub-second execution time. Understanding exactly how your indexes are structured and stored internally can give you a deeper understanding of what indexes will be useful for your queries.   SQL Server 2014 provides three very different kinds of index structures, useful for different purposes in different kinds of applications. Knowing how indexes are useful, and how each type is stored and managed can allow us to generalize some best practice recommendations.  In this seminar we’ll look at the following topics:

• B-Tree Index Structures
  • Clustered vs nonclustered
  • Unique vs non-unique
  • Included columns
  • B-Tree maintenance: splits, fragmentation and defragmenting
  • Best practices
• Columnstore Index Storage        
  • Nonclustered columnstore        
  • Clustered updateable columnstore        
  • Management of columnstore indexes        
  • Best practices
• In-memory Index Structures        
  • Memory-optimized tables        
  • Storage and management of indexes for memory-optimized tables
  • Best practices        
• Compare and contrast        
• How to choose