Guide to SQL Server Table Indexes – Part 2

Index Types: In addition to identifying indexes as clustered or non-clustered, there are several index types. Types describe indexes based on their characteristics:

  • Simple (Clustered or Non-Clustered)
  • Composite Indexes
  • Covering Index (Indexes with Included columns)
  • Unique Indexes
  • Spatial Indexes
  • Partitioned
  • Filtered indexes
  • Column store indexes
  • Full-text
  • XML
  Simple indexes (Clustered or Non-Clustered)
  A simple index refers to an index based on a single key column.  You cannot drop the index key column, and you cannot change the data type of the column without dropping the index. These types of indexes are typically useful if you want to search the data from database that is based on this index key column value. The following is the general syntax for creating a simple index.     
USE [<Database_Name>]
GO
CREATE NONCLUSTERED INDEX [Index_Name]
 ON <Object_Name> ()
WITH (PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,SORT_IN_TEMPDB = OFF
,IGNORE_DUP_KEY = OFF
,DROP_EXISTING = OFF
,ONLINE = OFF
,ALLOW_ROW_LOCKS = ON
                       ,ALLOW_PAGE_LOCKS = ON) ON { partition_scheme_name ( column_name )
                                                             | filegroup_name
                                                             | default }
GO
For example, examine the following query that returns the list of account numbers starting with character ‘A’ from Purchasing.Vendor table of AdventureWork2012 database:   
SELECT [AccountNumber]
FROM [AdventureWorks2012].[Purchasing].[Vendor]
WHERE [AccountNumber] LIKE 'A%'
  As our search is based on this AccountNumber column, having the following simple index will improve the query execution time significantly:   
USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [AK_Vendor_AccountNumber]
ON [Purchasing].[Vendor] ([AccountNumber] ASC )
WITH (PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,SORT_IN_TEMPDB = OFF
,IGNORE_DUP_KEY = OFF
,DROP_EXISTING = OFF
,ONLINE = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
  Composite indexes
A composite index is defined from two or more columns. All of the index columns must be from the same table. You use a composite index when you have two or more columns that would be searched together. You typically place the most unique key (the key with the highest degree of selectivity) first in the key list. You can create fewer indexes for a table by using composite indexes. However, a query must reference the first column of a composite index for the database engine to consider using the index for data retrieval. For example, modify the above query as follow, which returns the list of name and account numbers from Purchasing.Vendor table where name and account number starts with character ‘A’:   
SELECT  [AccountNumber], [Name]
FROM [AdventureWorks2012].[Purchasing].[Vendor]
WHERE [AccountNumber] LIKE 'A%'
AND [Name] LIKE 'A%'
  If you look at the execution plan of this query without modify the existing indexes of the table you will notice that SQL Server query optimiser is using table’s clustered index to retrieve query result:     image001     As our search is based on Name and then AccountNumber columns, having the following composite index will improve the query execution time significantly:   
USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [AK_Vendor _ AccountNumber_Name]
ON [Purchasing].[Vendor] ([AccountNumber] ASC, [Name] ASC )
WITH (PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,SORT_IN_TEMPDB = OFF
,IGNORE_DUP_KEY = OFF
,DROP_EXISTING = OFF
,ONLINE = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
    Examine the query execution plan of this query once again after creating the above composite index on Purchasing.Vendor table:     image002     As you can see, SQL Server query optimiser is now doing index seek on this composite index to retrieve query result.     Covering Index with included columns
  SQL Server 2005 introduces included columns in indexes also known as covering indexes. Included columns are non-key columns. Query performance improves when all columns in a query are included in an index as either key or non-key columns. The SQL Server database engine stores non-key columns in the index leaf level, which is the bottom-most level of the index hierarchy, and not in the index row. Included columns are supported on non-clustered indexes only. Columns included as non-key columns:  
  • Aren’t included in the 900-byte index key limit.
  • Can be data types not allowed as key columns.
  • Can include computed columns, but require deterministic values.
  • Can’t include text, ntext, or image data types.
  • Can’t be used as both key and non-key columns.
For example, you now want to retrieve all columns from the Purchasing.Vendor table, based on values of Name and AccountNumber column, you execute the following query:     
SELECT [AccountNumber]
,[Name]
,[CreditRating]
,[PreferredVendorStatus]
,[ActiveFlag]
,[PurchasingWebServiceURL]
,[ModifiedDate]
FROM [AdventureWorks2012].[Purchasing].[Vendor]
WHERE [AccountNumber] IN ('AUSTRALI0001', 'JEFFSSP0001', 'MITCHELL0001')
AND [Name] IN ('Inner City Bikes', 'Hill Bicycle Center')
  Examine the execution plan of this query without modify the existing indexes of the table, you will notice that SQL Server query optimiser is using table’s clustered index to retrieve query result (see below):   image003     This is because query contains the columns that are not part of our non-clustered index, and that is why SQL Server optimiser used the clustered index to retrieve the query data. To improve the query performance, we can modify our non-clustered composite index on the Purchasing.Vendor table, which we create earlier to add remaining columns of the query as a non-key columns in this composite index:     
CREATE NONCLUSTERED INDEX [AK_Vendor_AccountNumber_Name]
ON [Purchasing].[Vendor] ([AccountNumber] ASC, [Name] ASC )
INCLUDE([CreditRating]
,[PreferredVendorStatus]
,[ActiveFlag]
,[PurchasingWebServiceURL]
,[ModifiedDate])
WITH (PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,SORT_IN_TEMPDB = OFF
,IGNORE_DUP_KEY = OFF
,DROP_EXISTING = OFF
,ONLINE = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
    Examine the query execution plan of this query once again after creating the above composite index with included columns on Purchasing.Vendor table:   image004     As you can see, SQL Server query optimiser is now doing index seek on this newly created composite index with included columns to retrieve query result.   For more information about covering indexes, see official resource on Microsoft Create Indexes with Included Columns web page.   Unique indexes
  You use a unique index to enforce uniqueness on the key columns. No two rows can have the same key values in a unique index. If you attempt to add rows or change data that generates duplicate data in a table indexed by a unique index, the operation aborts and SQL Server reports an error. A unique index:  
  • Can have one or more key columns.
  • Can be created as a clustered or non-clustered index.
  • Checks for duplicate values when the index is created or rebuilt.
  • Checks for duplicate values during data manipulation (INSERT or UPDATE).
    By default, SQL Server creates a unique clustered index when you define a primary key and unique non-clustered index when you define a unique constraint. However, you can override the default behaviour to define a non-clustered index on the primary key and clustered unique constraint. A unique index ensures the data integrity of the defined columns, and provides additional information helpful to the query optimizer that can produce more efficient execution plans.   For more information about unique indexes, refer to Create Unique Indexes.     Spatial Indexes
  SQL Server supports spatial data and spatial indexes. A spatial index is an extended index that allows you to index a spatial column. A spatial column is a data table column that contains spatial data type, such as geometry or geography.   A detailed discussion of spatial indexes is beyond the scope of this article series, so download the white paper, New Spatial Features in SQL Server 2012, for a detailed description and examples of the spatial feature and the effect of spatial indexes.     Continue to Part-3:  
In the next part, you will learn about remaining index types available in SQL Server, and what are these indexes advantages and dis-advantages. ]]>

Leave a comment

Your email address will not be published.