Seek predicates with inequalities - Residual Predicate

Could someone please guide me to a better understanding of index seeks with multiple inequality predicates.
Consider the following table:

```
CREATE TABLE [dbo].table_1 NOT NULL,
[TYPE] nvarchar NULL,
[INV_NO] nvarchar NULL,
[ACCOUNT] nvarchar NULL,
[NAME] nvarchar NULL,
[ADDR1] nvarchar NULL,
[ADDR2] nvarchar NULL,
[ADDR3] nvarchar NULL,
[CONTACT] nvarchar NULL,
[GST_NO] nvarchar NULL,
[CR_TERMS] nvarchar NULL,
[OREF] nvarchar NULL,
[YREF] nvarchar NULL,
[SALESREP] nvarchar NULL,
[DISCOUNT] decimal NULL,
[TRANSPORT] decimal NULL,
[INS] decimal NULL,
[GST] decimal NULL,
[TOTAL] decimal NULL,
[DATE] [smalldatetime] NULL,
[LINE] [smallint] NULL,
[PSLIP] nvarchar NULL,
[VAT] nvarchar NULL,
[STORE] nvarchar NULL,
[STKSTR] nvarchar NULL,
[POST] [smalldatetime] NULL,
[PAID] decimal NULL,
[WEIGHT] decimal NULL,
[INSURE] nvarchar NULL,
[HAND] decimal NULL,
[CODC] decimal NULL,
[TOPAY] decimal NULL,
[IND1] nvarchar NULL,
[IND2] nvarchar NULL,
[IND3] nvarchar NULL,
[COST] decimal NULL,
[REPC] nvarchar NULL,
[DISPATCH] nvarchar NULL,
[TILLNO] nvarchar NULL,
[USER] nvarchar NULL,
[RCOMM] decimal NULL,
[DISP1] nvarchar NULL,
[DISP2] nvarchar NULL,
[PRNSEQ] [int] NULL,
[TIME] nvarchar NULL,
[ROUND] decimal NULL,
[MESSAGE1] nvarchar NULL,
[MESSAGE2] nvarchar NULL,
[MESSAGE3] nvarchar NULL,
[PRINT] nvarchar NULL,
[PDATE] [smalldatetime] NULL,
[FILENO] nvarchar NULL,
[sched6] nvarchar NULL,
[BUYACC] nvarchar NULL,
[DBCLW] nvarchar NULL,
[PRINTCODE] nvarchar NULL,
[INV_HOLD] nvarchar NULL,
[CURRATE] decimal NULL,
[CURCODE] nvarchar NULL,
[DEPOS] [smalldatetime] NULL,
[ROUTE] nvarchar NULL,
[DEL_METHOD] [nvar

How can I recreate this index so that there is no residual predicate?

It seems likely you would require the identity to be unique. This needs to be enforced:

CREATE UNIQUE CLUSTERED INDEX CX ON dbo.table_1 (DBINVHDID);

Then create a filtered index:

CREATE INDEX [Index_1] 
ON table_1 ([PRINT], [DEL_METHOD], [TYPE], [INV_NO]) 
INCLUDE (
    [DBINVHDID], [OSECT], [OSEC], [STORE], [DISP1], [SEATNO], [BUYACC],
    [NAME], [ROUTE], [CR_TERMS])
WHERE 
    OSECT <> N'KNP'
    AND OSEC <> N'1';

Express the query as:

SELECT 
    U.[DBINVHDID],
    U.[STORE],
    U.[INV_NO],
    U.[DISP1],
    U.[SEATNO], 
    U.[BUYACC],
    U.[NAME],
    U.[ROUTE],
    U.[CR_TERMS],
    U.[DEL_METHOD] 
FROM 
(
    SELECT T1.*
    FROM dbo.table_1 AS T1
    WHERE
        T1.[PRINT] = N'N'
        AND T1.DEL_METHOD = N'C'
        AND T1.[TYPE] = N'3'

    UNION ALL

    SELECT T2.*
    FROM dbo.table_1 AS T2
    WHERE
        T2.[PRINT] = N'N'
        AND T2.DEL_METHOD = N'C'
        AND T2.[TYPE] = N'5'
) AS U
WHERE
    U.OSECT <> N'KNP'
    AND U.OSEC <> N'1'
ORDER BY
    U.INV_NO ASC;

This will give an execution plan with no residual predicate and no sort:

You can omit the filtering portion of the index if those conditions might vary in different queries. The two inequality predicates on OSECT and OSEC will then be applied as residual predicates.

Is the excessive grant related to the residual predicate?

The memory grant is for the sort. It is estimated as being proportional to the number and size of rows requiring sorting. The predicates (seekable and residual) do affect cardinality estimation. It also seems your table is empty. SQL Server often emits a meaningless 'excessive memory' warning in that case, even though the memory reservation is tiny.

Without a sort, there is no memory requirement.

You should not create tables in the master database.

Your single table is a heap of unorganized data. A relational design with multiple tables to represent the different entities would likely be much easier to query. You should consider a redesign.

Related reading:

• When Do Non-SARGable Predicates Not Cause Performance Problems? by Erik Darling

• Avoiding Sorts with Merge Join Concatenation by me

• Rewriting Queries to Improve Performance by me

StackExchange Database Administrators Q#318389, answer score: 8