Indexing Strategy for the query performance

Question

I have a question on indexing strategy as i am trying to do indexing for a database.

I understand the basic indexing strategy which are:

• Clustered index on PrimaryKey
• Add Non Clustered index for other columns as per requirement.
• Should have indexes for WHERE/JOIN/GROUP BY/ORDER BY columns
• Covering indexes for columns to avoid keylookup.
• Check the workload and avoid adding too many indexes
• Index Foreign Keys
• Index any other unique keys etc.. I still have some questions and i couldn't find an exact answer to my questions.

Here is an example of a table that i have.

USE [DEMODB]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employee_Header](
    [Id] [uniqueidentifier] NOT NULL,
    [PageId] [uniqueidentifier] NOT NULL,
    [Dept] [varchar](256) NULL,
    [DeptName] [varchar](256) NULL,
    [SubUnit] [varchar](256) NULL,
    [ShiftType] [varchar](256) NULL,
    [ReportDate] [varchar](256) NULL,
    [ReportTime] [varchar](256) NULL,
    [AccessNumber] [varchar](256) NULL,
    [AccessOnAfterDate] [varchar](256) NULL,
    [AccessOnAfterTime] [varchar](256) NULL,
    [EmpBand] [varchar](256) NULL,
    [WorkSite] [varchar](256) NULL,
    [Location] [varchar](256) NULL,
    [Location_FieldValue] [varchar](256) NULL,
    [Location_Description] [varchar](256) NULL,
 CONSTRAINT [PK_Employee_Header] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

As you can see [Id] is the primary key here.[PageID] is the foreign key and it is not unique. There are few queries which uses whereclause on `[Dept],[SubUnit], [ShiftType] and [ReportDate]'.

One query displays columns [Dept] and [ShiftType] and other query displays [Dept],[ReportDate],[ReportTime],[EmpBand]

1. I am confused as to how to create NonClustered indexes with Include columns so that same indexes can be used to both queries Or
2. should i create separate clustered indexes for where each columns
3. then create a separate single NonClustered index with PrimaryKey column as unique column and all the display columns in the include section.
4. Should i just keep the display columns in the keycolumn section?

Most of the examples available shows querying to a single table with an Id and one or more select column. Can someone help me to understand how you approach similar situations?

I created two indexes as shown below.

First one is for the where clauses.I have all the where clause fields added as key columns in this non clustered index

CREATE NONCLUSTERED INDEX [ix_Employee_Filters] ON [dbo].[Employee_Header] ( [Dept] ASC, [SubUnit] ASC, [ShiftType] ASC, [ReportDate] ASC, )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

The second one is for the select columns.Some of the select columns are already part of the first index so i have only two columns that needs to be covered. So i added them as Include fields with Id(Primary key field) as the key column.

CREATE NONCLUSTERED INDEX [ix_Employee_Include_Fields] ON [dbo].[Employee_Header] ( [Id] ASC ) INCLUDE ( [ReportTime], [EmpBand]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

Still when i run and see the execution plan, i get the message that there is a missing index.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[Employee_Header] ([ReportDate]) INCLUDE ([Id],[Dept],[ShiftType],[ReportTime],[EmpBand])

I don't understand why query optimizer is suggesting to a date filed as key field and other fields in the include section. I already have a Nonclustered index with ReportDate among other where fields.

Appreciate for sharing your approach.But can anyone answer/share your perspective on the specific example mentioned?

Answer 1

For a general-purpose or OLTP design, the initial index design should be more conservative:

• Clustered index on Primary Key.

• Unique non-clustered index any other unique keys.

• Index supporting each Foreign Key (where not already covered above).

Then, for very large tables, consider changing to a Nonclustered Primary Key and a Clustered Columnstore.

Then additional indexes as needed for optimization of the actual workload. These can be indexes with included columns, filtered indexes, non-clustered columnstore indexes, or indexed views on a case-by-case basis.