Types of indexes in SQL Server 2012
Creating indexes on SQL Database tables will improve the application performance. In general index is structure with SQL that quickly locate (either seek/scan) row within the table. Different types of indexes are available in SQL Server 2012.
Clustered Index:
A clustered index is an index where the leaf level of the index contains the actual data rows of the table and It sorts and stores the data rows of the table or view in order based on the clustered index key. Index is defined for one or more columns called INDEX key. There can be only one clustered index per table and it can be combination of multiple columns.
When to use cluster indexes –
Queries that return a large percentage of the columns in the table
Queries that return a single row based on the clustered index key
Queries that return range-based data
Non-Clustered Index:
A non-cluster index does not contain the entire data row at the leaf level. It contains just the columns defined in the index and a pointer or key to the actual data row. Single table supports up to 999 non-cluster indexes.
When to use non-cluster indexes –
Queries that return few rows
Queries that can be covered by the index
Covering Index:
Covering indexes are those which improves the query performance and meets requirement of the query using non-cluster indexes. This is to encourage DBAs to create more non-cluster index rather than using cluster indexes and avoid pressure on cluster indexes.
Filtered Index:
A filtered index is an optimized non-clustered 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 as well as reduce index maintenance and storage costs compared with full-table indexes. Filtered indexes are also useful in conjunction with another feature called sparse column. By creating a filtered index and specifying that the WHERE column IS NOT NULL, you create B-tree with only those rows that contain data
Advantage of using Filtered indexes
improved query performance and plan quality
Reduced index maintenance costs
Reduced index storage costs
When to use filter indexes –
When combined with sparse columns to locate specific non-null rows
When queries that are a small subset of the rows are selected often
FULLTEXT Index -
Store the info about significant words and their location within the columns of a database table. IN SQL 2012 the full text engine is part of sql process rather than a separate service. Only one full text index can be created per table or index view. A full text index can contain up to 1024 columns.
SPATIAL Index :
A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data or Geometry data type) 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. This is useful when querying spatial data.
XML Index :
XML indexes can be created on xml data type columns. They index all tags, values and paths over the XML instances in the column and benefit query performance. XML indexes divided into two category i.e. Primary XML index and secondary XML index. The first index on the xml type column must be the primary XML index. Using the primary XML index, the following types of secondary indexes are supported: PATH, VALUE, and PROPERTY. Depending on the type of queries, these secondary indexes might help improve query performance.
Columnstore Index :
This is new feature of SQL 2012. This index does not use the B-Tree structure but rather uses a column-oriented storage that is very efficient for data. indexes have been added to provide performance improvements for the typical data warehouse– type queries that perform aggregations over large data sets. Those queries often
take minutes or hours to process by using traditional indexes. Online transaction processing (OLAP) cubes are commonly used to provide the performance levels required by businesses.
Data warehouse–type queries often use only a few of a table’s columns for each query.
Performance can be improved by using column-based index structures instead of the row based
indexes in these scenarios.
columnstore indexes organize data by columns rather than by rows, SQL Server
can optimize storage by compressing repeating data values. This higher level of compression,
which is approximately double the compression rate of PAGE compression, makes this index
type a very effective indexing tool.
