Как сделать sql поисковый запрос более мощным?

Вы понимаете, я предполагаю, что Ваша схема примерно устраняет полноценность индексов для этих видов запросов?

Большая проблема Ваш "КАК '%spa %'". Любой "КАК" ключевой запуск с подстановочного знака - автоматическое сканирование таблицы.

---

Править: Я считал Ваш вопрос сказать, что существует единственное поле, Имя, со значениями полей что-то как "1 Space Company", "2 Спа-курорта", и т.д. с числом, сопровождаемым словами. И Вам был нужен подстановочный знак перед Вашим ключом поиска для заканчивания части числа. (Это должно разъяснить мой первый комментарий.) Я предполагаю правильно или нет?

Следующее должно сделать необходимое, но это неэффективно, делание двух полных таблиц выбирает, и это также полагается на Ваше точное совпадение, разграничиваемое пробелами. Я думаю, что Полнотекстовое индексирование помогло бы, но это имеет собственные издержки.

select distinct * from
(
Select * from TableName 
   where CHARINDEX('spa ', Name) > 0
   or CHARINDEX(' spa', Name) > 0
Union
Select * from TableName 
  where Name Like '%spa%'
)

Уходя главный пример, по крайней мере, в MSSQL2005, изменяющем КЛАСТЕРИЗОВАННОЕ на NONCLUSTERED, заставит его сделать сканирование таблицы. КЛАСТЕРИЗИРУЕМЫЙ дает Вам, индекс ищет. Похож это соответствует условиям вопроса.

CREATE TABLE tblTest(ID INT, colname VARCHAR(20) )
CREATE CLUSTERED INDEX tstidx1_tblTest ON tblTest(colname);
INSERT tblTest SELECT 1,'Space Company'
INSERT tblTest SELECT 2,'Spa Resort'
INSERT tblTest SELECT 3,'Spa Hotel'
INSERT tblTest SELECT 4,'Spare Parts'
INSERT tblTest SELECT 5,'WithoutTheKeyword'

SELECT * FROM tblTest WHERE colname LIKE 'Spa%'
ORDER BY DIFFERENCE(colname,'Spa') DESC;

DROP TABLE tblTest

В основном необходимо определить (точно), какова функция рейтинга действительно. Что, если у Вас есть строка, которая является "Спа". или "spa.com"? После того как Вам определили это, необходимо поместить ту логику в пункт ORDER BY. Например:

SELECT
    name
FROM
    Some_Table
WHERE
    name LIKE '%spa%'
ORDER BY
    CASE
        WHEN name LIKE '% ' + @search_word + ' %' THEN 1   -- Notice the spaces
        ELSE 2
    END,
    name

С другой стороны, Вы могли записать занимающую место функцию и использование что:

SELECT
    name
FROM
    Some_Table
WHERE
    name LIKE '%' + @search_word + '%'
ORDER BY
    dbo.GetNameMatchRank(name, @search_word)

Производительность на очень больших наборах результатов не может быть слишком большой, таким образом, этот подход зависит от Ваших ожидаемых размеров результата поиска.

Это должно работать:

Select * from TableName where Name Like '%spa%'
ORDER BY Name