To INCLUDE or Not INCLUDE in an Index

## Current Situation I am observing a simple statement that is querying one table and accessing multiple indexes to retrieve the data:

SELECT DISTINCT 
feld16,
zahl4,
feld12,
feld19 FROM 
object1 WHERE 
(deleted = 0 or deleted IS NULL)

The query execution plan can be found on Brent Ozar's Paste The Plan website and a graphical representation follows: The table is made up of 82 columns containing various data. The distribution of the data in the deleted column is:

language:none 
 deleted | Number of records
---------+-------------------
       0 |        71'620'068
    NULL |                10
 a value |            59'673

The result set contains approx. 64 million rows of the approx. 71 million rows that match the search predicate WHERE (deleted = 0 or deleted IS NULL). This is because the DISTINCT omits 7 million records. ## Going Forward In order to speed things up a bit, I am considering adding a new index. Initially I thought I was knowledgeable enough to add an adequate index, but I am starting to second-guess myself. ## Question Which of the following index definitions would (possibly) be an adequate solution? ### 1. Index without INCLUDE

CREATE NONCLUSTERED INDEX [IDXNew] ON [schema_owner].[object1]
(
	[deleted] ASC,
	[feld16] ASC,
	[zahl4] ASC,
	[feld12] ASC,
	[feld19] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

### 2. Index with INCLUDE

CREATE NONCLUSTERED INDEX [IDXNew] ON [schema_owner].[object1]
(
	[feld16] ASC,
	[zahl4] ASC,
	[feld12] ASC,
	[feld19] ASC
)
INCLUDE 
(
	[deleted] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

### 3. Index with INCLUDE, but the other way round

CREATE NONCLUSTERED INDEX [IDXNew] ON [schema_owner].[object1]
(
	[deleted] ASC
)
INCLUDE(
    [feld12],
    [zahl4],
    [feld16],
    [feld19]
) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

## My Initial Thoughts .. were to go with the first index definition and include all the columns. On reading up on the INCLUDE part of the index creation I thought of creating the second index. I then thought: _Why not add a filter to the index like deleted = 0 or deleted IS NULL_ and then started second guessing my knowledge about indexes. ## Additional Information There are some trace flags running on the SQL Server 2016 instance. Some were recommended to us by Microsoft PFE during a PTOC. -- Assume fixed amount of memory -- Updates statistics in linear mode -- Enable QO fixes And the CE is currently set to run in backwards compatibility mode: CardinalityEstimationModelVersion="70"