Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

FREE Webcast - Delivering Microsoft SQL Server High Availability on AWS
 

SQL Server Index Tutorial Overview



By:
Overview

Although there are many different things that can be done to improve the performance of a database system, creating an index is the main tool in every DBA's toolbox that is used when we are trying to improve the performance of a query.  Having the right index on a table for a query can make the difference between the query taking seconds versus hours to be executed.  With more and more features being added to SQL Server with each release, we now have many different types of indexes that we can create in order to make queries execute as fast as possible. 

This tutorial will try to go through each type of index available in SQL Server and explain why/when each one could be used to improve your query performance.

Explanation

This first couple sections of this tutorial will dive into the main index types that even the newest database professional would see with almost any table that is created.  It will also get into some lesser known options for these indexes that can sometimes really improve performance.  These include:

  • Clustered Indexes
  • Non-Clustered Indexes

Next, we will look at some of the more specialized index types.  Some of these indexes can be created on regular tables and datatypes and some work only on specific datatypes.  These include:

  • ColumnStore Indexes
  • Full Text Indexes
  • XML Indexes
  • Spatial Indexes
  • Filtered Indexes

Finally, we'll look at index maintenance and usage and provide some tools that you can use to make sure that your indexes are improving the performance of your database system.

  • Index Fragmentation
  • More Information on Indexes

NOTE: All the example code in each of the following sections was written and tested against the Adventureworks2014 sample database.


Last Update: 7/25/2018




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools