Covering Index – Include columns

nonclustered-index

I have a table :

[sch].[Parent]
(
     ID Identity [INT] (PK),
     Name VARCHAR(100),
     Email VARCHAR(250),
     Active TINYINT,
     InsertedBy VARCHAR(100),
     InsertedDTM DATETIME,
     UpdatedBy VARCHAR(100),
     UpdatedDTM DATETIME,
)

This table has PK on the ID column and a UNIQUE NONCLUSTERED CONSTRAINT on Name.

When I query a normal SQL without a WHERE clause:

SELECT ID, Name 
FROM sch.Parent

This query uses a Index Scan and this is the case when I use a INNER JOIN with another CHILD table which has an FK in another table

What covering index should I use so that there is Index Seek in case of normal query and INNER JOIN with another table as well?

SELECT
     b.Id AS 'ParentsChildId'
    , b.[Name] AS 'ParentsChildName'
    , a.Id AS 'ParentId'
    , a.[Name] AS 'ParentName' 
FROM 
    [sch].[ParentsChild] b 
INNER JOIN 
    [sch].[Parent] a 
ON  a.Id = b.Id 
WHERE 
    a.Active = 1 AND b.Active = 1

Best Answer

(I'm assuming SQL Server in this answer, please clarify which RDBMS you're using if it's not SQL Server)

The index you want is:

CREATE INDEX [IX_Parent_Id]
ON [sch].[Parent] (Id) INCLUDE (Name)

You use INCLUDE when a column isn't being filtered on (isn't in the WHERE clause) but is being selected. INCLUDE means that the column will only be included at the leaf level of the index (it's a small efficiency saving, essentially). Also as noted, if you have a SELECT statement without a where clause, indexes will never be used (faster to just scan the table rather than muck about with an index). Index seek is only used when you're returning a comparatively small number of rows, for your sample query if most of your ParentsChild and Parents rows are marked as Active then the optimiser may well decide to index scan instead of seek. A query that would be more likely to get an index seek instead of scan would be something like:

SELECT
     b.Id AS 'ParentsChildId'
    , b.[Name] AS 'ParentsChildName'
    , a.Id AS 'ParentId'
    , a.[Name] AS 'ParentName' 
FROM 
    [sch].[ParentsChild] b 
INNER JOIN 
    [sch].[Parent] a 
ON  a.Id = b.Id 
WHERE 
    a.Active = 1 AND
    b.Active = 1 AND
    a.Id = 2

(getting the children of Parent ID 2)

Related Question