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

T.Zacks 3,986 Reputation points
2022-04-21T06:03:28.697+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,320 questions
0 comments No comments
{count} votes

Accepted answer
  1. Dan Guzman 9,226 Reputation points
    2022-04-21T12:11:03.167+00:00

    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.

    4 people found this answer helpful.
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. tibor_karaszi@hotmail.com 4,306 Reputation points
    2022-04-21T12:04:39.003+00:00

    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.

    2 people found this answer helpful.

  2. Tom Phillips 17,721 Reputation points
    2022-04-22T19:29:38.807+00:00

    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.

    0 comments No comments

  3. Tursunboy Rahimjonov 0 Reputation points
    2023-08-29T12:28:48.63+00:00
    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
        )
    );
    
    0 comments No comments