Understanding Database Indexes – (Part – 2)

Author: Basit A. Farooq

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 the 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 nonclustered indexes only. Columns included as non-key columns:

• Are not 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.
• Cannot include text, ntext, or image data types.
• Cannot be used as both key and non-key columns.

For example, consider that you now want to retrieve all columns from the Purchasing.Vendor table, based on values of Name and AccountNumber columns. To accomplish this, you execute the following query:

USE [AdventureWorks2012];

GO

SELECT [AccountNumber]

,[Name]

,[CreditRating]

,[PreferredVendorStatus]

,[ActiveFlag]

,[PurchasingWebServiceURL]

,[ModifiedDate]

FROM [Purchasing].[Vendor]

WHERE [AccountNumber] IN (N’AUSTRALI0001′, N’JEFFSSP0001′, N’MITCHELL0001′)

AND [Name] IN (N’Inner City Bikes’, N’Hill Bicycle Center’);

GO

Examine the execution plan of this query without modify the existing indexes of the table, you will notice that SQL Server is using table’s clustered index to retrieve query result (see below):

This is because; the query contains the columns that are not part of our nonclustered index. Therefore, SQL Server uses the clustered index to retrieve the query results. To improve this query performance, we can modify our nonclustered 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:

USE [AdventureWorks2012];

GO

CREATE NONCLUSTERED INDEX [AK_Vendor_AccountNumber_Name]

ON [Purchasing].[Vendor] ([AccountNumber] ASC, [Name] ASC )

INCLUDE([CreditRating]

,[PreferredVendorStatus]

,[ActiveFlag]

,[PurchasingWebServiceURL]

,[ModifiedDate]) ON [PRIMARY];

GO

After creating the above composite index with included columns, run the query and examine its query execution plan:

Unique indexes

You use a unique index to enforce uniqueness on the key columns. 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 nonclustered 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 nonclustered index when you define a unique constraint. However, you can override the default behavior to define a nonclustered 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 SQL Server Books Online topic “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. For help with this, 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.

Partitioned indexes

Partitioned indexes are indexes created on partitioned tables. Partitioned indexes are partitioned on the same horizontal filter and the same ranges as the table on which they are based. You specify the table partition scheme, how the table is partitioned, when creating partitioned indexes. You can also create partition index on existing non-partition table, but for this, you first have to convert the existing non-partitioned table into the partition table. To do this you first need to add appropriate partition filegroups, then create partition function and partition scheme inside database. Once done, rebuild the desired table indexes/indexes on this partition.

Partitioned indexes not only help to optimize queries that include only data from a single partition, but also help to make index management operation easier because you can also rebuild the partition of an index that is fragmented individually.

SQL Server 2016 supports up to 15,000 partitions by default. In versions earlier than SQL Server 2012, the number of partitions was limited to 1,000 by default. On x86-based systems, creating a table or index with more than 1000 partitions is possible, but is not supported.

A detailed discussion about partitioned tables and indexes is outside the scope of this article. For more information, see SQL Server Books Online topic: “Partitioned Tables and Indexes”.

Filtered Index

Beginning with SQL Server 2008, Microsoft introduced the new type of nonclustered known as filtered index. Filtered index is an optimized nonclustered index that only contains the specified subset of data, specified by the filter predicate. Filtered indexes are especially useful to cover those queries that frequently needs access to the well-defined subset of data. Having a well-designed filtered index can improve query performance, reducing the overall index maintenance costs, and index storage costs compared with full-table indexes.

For example, if you have the following query that returns the all orders from Sales.SalesOrderDetail that are placed on or after January, 1st 2008:

USE [AdventureWorks2012]

GO

SELECT [SalesOrderID] ,

[SalesOrderDetailID] ,

[OrderQty] ,

[ProductID] ,

[SpecialOfferID] ,

[UnitPrice] ,

[UnitPriceDiscount] ,

[LineTotal] ,

[ModifiedDate]

FROM [Sales].[SalesOrderDetail]

WHERE [ModifiedDate] >= ‘2008-01-01 00:00:00.000’;

GO

By creating the following filtered index, you can significantly improve the query response time. This is because, SQL Server will do the index seek on this filtered index to retrieve the qualifying data.

USE [AdventureWorks2012];

GO

CREATE NONCLUSTERED INDEX IXNC_SalesOrderDetail_ModifiedDate

ON [Sales].[SalesOrderDetail] ([ModifiedDate])

INCLUDE ([SalesOrderID]

,[SalesOrderDetailID]

,[OrderQty]

,[ProductID]

,[SpecialOfferID]

,[UnitPrice]

,[UnitPriceDiscount]

,[LineTotal])

WHERE [ModifiedDate] >= ‘2007-01-01 00:00:00.000’;

GO

Continue to Part-3…