Question

What are the different indexing options available and what columns do you typically index?  What is the value of indexing columns?

Answer 1

*    From a simple standpoint SQL Server offers two types of indexes clustered and non-clustered. In its simplest definition a clustered index is an index that stores the actual data and a non-clustered index is just a pointer to the data. A table can only have one Clustered index and up to 249 Non-Clustered Indexes. If a table does not have a clustered index it is referred to as a Heap.
*    To further clarify this let’s take a look at what indexes do and why they are important. The primary reason indexes are built is to provide faster data access to the specific data your query is trying to retrieve. This could be either a clustered or non-clustered index. Without having an index SQL Server would need to read through all of the data in order to find the rows that satisfy the query. If you have ever looked at a query plan the difference would be an Index Seek vs a Table Scan as well as some other operations depending on the data selected.
*    Indexes are typically on these columns:
   *    Primary keys
   *    Foreign keys
   *    Columns in WHERE, GROUP BY, ORDER BY, etc.