Is it possible to write a Filtered Index where the column value is NOT LIKE 'X'?

Question

I'm trying to optimize a table lookup because the execution plan shows a pretty hefty parallelized table scan. The table is called Opportunity and the column I'm filtering on is Name. Specifically I want all rows that don't have "Supplement" as part of the Name:

WHERE ([Name] NOT LIKE '%Supplement%');

I was looking around for a way to optimize this and came across filtered indexes which is what I need, but they don't seem to like the LIKE keyword. Is there an alternate way to creating a filtered index like this?

The table has ~53k rows, and when directly querying the server it takes 4 seconds to get the data, but when I query it as a linked server (which is what I need) it takes 2 minutes. In an attempt to improve this time, I moved the query out of my script that was talking to the linked server and created a view on the remote server. Still takes forever.

Here's what I've tried so far, but the SSMS says it's invalid:

CREATE NONCLUSTERED INDEX [FX_NotSupplementOpportunities]
    ON      [Opportunity]([Name])
    WHERE   (([Name] NOT LIKE '%Supplement%')
            AND ([Name] NOT LIKE '%Suplement%')
            AND ([Name] NOT LIKE '%Supplament%')
            AND ([Name] NOT LIKE '%Suppliment%'));

Thanks in advance for any suggestions!

Answer 1

You might use a Indexes on Computed Columns an example would be:

CREATE TABLE [dbo].[MyTab](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Text] [varchar](max) NULL,
    [OK]  AS (case when NOT [text] like '%abc%' then (1) else (0) end) PERSISTED NOT NULL,
 CONSTRAINT [PK_MyTab] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [idx_OK] ON [dbo].[MyTab] 
(
    [OK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO