SQL Server: When to go for Composite non-cluster Index and when Cover Index

Question

I could create Non-Cluster index on multiple fields for a table and i also create a cover index where i point a main column and include other columns..

1) so please discuss few scenarios when people should use Non-Cluster composite index on multiple fields and when people should go for Cover Index ?

2) Non-Cluster composite index is good or Cover Index is Good ?

Please share the knowledge. Thanks

Answer 1

Composite index and a covering index are separate terms/concepts.

A composite key index is simply an index with multiple key columns. It may be clustered or non-clustered. Like all indexes, composite indexes are used to optimize queries. When all or most or all of the leftmost key columns are specified in query WHERE/JOIN clauses, the index facilitates reading only those rows needed by the query while avoiding others. Composite key indexes may additionally be used to guarantee uniqueness over multiple columns in order to guarantee data integrity, including indexes supporting composite primary key and unique constraints.

Any non-clustered index is said to cover a query when all of the table columns needed by a query are present in the index. The benefit of a covering index is avoiding additional reads to retrieve values from data rows. Columns available in a non-clustered index are:

• index key column(s)
• explictly included column(s)
• clustered index key column(s) (implictly included)
• partitioning column (implicitly included when table is partitioned)

The benefit of a covering index is that the data row itself does not need to be read because all columns needed by a query are already present in the index. Note that a given index may cover one query but not another; it depends on the columns needed by the query.

Below are a few examples using the AdventureWorks sample database Production.ProductInventory table, which has only a composite primary key index.

CREATE TABLE [Production].[ProductInventory](
    [ProductID] [int] NOT NULL,
    [LocationID] [smallint] NOT NULL,
    [Shelf] [nvarchar](10) NOT NULL,
    [Bin] [tinyint] NOT NULL,
    [Quantity] [smallint] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ProductInventory_ProductID_LocationID] PRIMARY KEY CLUSTERED 
    (
        [ProductID] ASC,
        [LocationID] ASC
    )
);

The clustered PK index is useful only for queries that specify at least ProductID in WHERE clauses. Consider this query for out-of-stock products at a specific location.

SELECT  
      ProductID
FROM Production.ProductInventory
WHERE 
    LocationID = @LocationID
    AND Quantity = 0;

A full scan is needed because ProductID is not specified in the WHERE clause. To avoid the scan, we create a composite index:

CREATE NONCLUSTERED INDEX idx_ProductInventory_Location
    ON Production.ProductInventory(LocationID);

The plan after this index is created shows a more efficient index seek on LocationID so that only rows for the specified location are touched. However, it includes a key lookup to read the Quantity value needed for the query filter. To further optimize this query with a covering index, we could either add Quantity to the index key or add it as an explicitly included column.

Adding the column as a key column is most beneficial to the select because only the rows with zero Qantity at the specified location are touched:

CREATE NONCLUSTERED INDEX idx_ProductInventory_Location_Quantity
    ON Production.ProductInventory(LocationID, Quantity);

Although the above index is most optimal for the out-of-stock query, the downside is the cost of maintaining the index b-tree every time quantity changes. The additional index maintenance cost is probably not justified when the query is only run once a day but Quantity is adjusted continuously. A better option may be an included column as below.

CREATE NONCLUSTERED INDEX idx_ProductInventory_Location
    ON Production.ProductInventory(LocationID) INCLUDE(Quantity);

One can make the argument that a covering index is overkill for a query that runs only daily per location. Personally I don't go out of my way to create covering indexes unless the query is performance sensitive or executed frequently. A good indexing strategy involves considering the overall workload.

Answer 2

If the sole purpose of having a column in the index so it overs the query, then have it as an included column. If the column can assist in finding the data, then include it in the key. Say you have below query and two indexes

SELECT firstname, lastname
FROM tbl
WHERE lastname = 'Smith' AND firstname = 'Joe'

Index options
CREATE INDEX a ON tbl(lastname) INCLUDE(firstname)
CREATE INDEX b ON tbl(lastname, firstname)

For the a index, SQL Server seeks for the first Smith and have to read all Smiths to see which are named Joe.
For the b index, SQL Srver can seek only only the lastname, but also the firstname. I.e., "directly" find all Joe Smith.

An included column is only in the leaf level of the index, it isn't sorted and there are relaxed rules for included column regarding number of columns, index row length and data types for included columns compared to key columns.

Answer 3

Indexes should be used to support queries. You should not blindly create indexes without a reason. Indexes require updating when data changes as well as stats maintenance and are used to evaluate query plans.

Answer 4

CREATE TABLE [Production].[ProductInventory](
    [ProductID] [int] NOT NULL,
    [LocationID] [smallint] NOT NULL,
    [Shelf] [nvarchar](10) NOT NULL,
    [Bin] [tinyint] NOT NULL,
    [Quantity] [smallint] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ProductInventory_ProductID_LocationID] PRIMARY KEY CLUSTERED 
    (
        [ProductID] ASC,
        [LocationID] ASC
    )
);