How can I optimize searching a column for a sub-string in SQL Server?

I have a very basic table:

CREATE TABLE [obj_local] (
    [obj_id]     INT             NOT NULL,
    [value]      NVARCHAR (1000) NOT NULL
);

This table stores a lot of data, and I need to search the value column for a sequence of characters:

SELECT [obj_id] FROM [obj_local] WHERE [value] LIKE '%{substring}%'

This is extremely slow. My understanding is that an index won't help me due to the wildcards on either side, and a full text index also won't help me, because I'm not searching for tokens(words).

Is there anything I can do to optimize this search?

If you know the exact string, then using a binary collation for your search can help.

WHERE [value] LIKE '%{substring}%' COLLATE Latin1_General_BIN

, because it won't have to do case conversions and the like. This can make it a few times faster, but not lightning fast.

Another option is to consider blowing up your table and indexing that - using an indexed view over a john between a table of numbers and your table, leveraging the substring function.

For example, if you have a table of at least 1000 numbers, you could make a row for each character. Yes, it'll cost space, but it will quickly let you find every "H" in there.

Problem is - it's still not quite what you need, because there will be a lot of "H"s. Better might be to grab three-letter combinations. So if you're looking for 'Hotel', you know that either "Hot", "ote" or "tel" must be in the three-letter combo table. You could make an inline table-valued function to handle this. Naturally, when you search for "Hot", the next block for that obj_id must be like 'el%', and so on, plus you should check that your main table still successfully satisfies the main predicate.

It's an idea... if you can put up with the complexity of working around it like this.

StackExchange Database Administrators Q#109286, answer score: 5