Почему в SQL Server нет разделенной целочисленной опции каталогизации?

Question

Я что-то упускаю?

Я пытаюсь создать таблицу индексов, разделенных пробелами (или любого другого типа, который вам нравится). Я понимаю, что полнотекстовый поиск был бы невозможен только для столбцов данных типа int, потому что он понимает "пробелы" как разделитель для отдельных данных, которые должны быть проиндексированы по всему каталогу.

Я понимаю, что это позволяет мне индексировать varbinary введите данные, но почему бы не просто int Данные разделены пробелами, а не включают целочисленные И текстовые данные для поиска. IE,

 SEARCH * FROM MyTable
 WHERE CONTAINS(indexedcolumn, '1189')

с полнотекстовым индексом / каталогом, определенным для таблицы, которая выглядит следующим образом:

 indexedColumn      secondDelimitedIntColumn
 1189               34 34209 1989 3 5

не возможно, но

 SEARCH * FROM MyTable
 WHERE CONTAINS(uniqueColumn, 'a1189')

РАБОТАЕТ, используя полнотекстовый индекс для таблицы со следующими столбцами:

 uniqueColumn secondDelimitedIntColumn
 a1189        b34 b34209 b1989 b3 b5  

так в основном выполняет CONTAINS() поиск по любому столбцу с полнотекстовым индексом будет работать только в том случае, если к целой строке прикреплен какой-либо текст.

Но мой вопрос: "Почему я не могу просто использовать строки целых чисел, разделенных пробелами, что избавляет меня от необходимости добавлять фиктивный текст только для того, чтобы научить SQL Server выполнять полнотекстовый поиск по индексированным целочисленным строкам"?"

Заранее спасибо!

Answer 1

Это на самом деле не вопрос. Нет сведений о запросе, который вы пытаетесь выполнить, или о схеме, из которой вы его выполняете. Я не совсем уверен, что сказать вам здесь. Я мог бы помочь вам, если есть некоторые подробности. Это больше похоже на то, что у вас есть жалоба, чем вопрос.

Я полностью осознаю, что это должно быть в разделе комментариев и не отвечать, но у меня нет баллов за это при переполнении. Я живу на.dba.

Answer 2

Обновлен на примере XML ниже

Ваш текущий дизайн нарушает первую нормальную форму.

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

Оглядываясь назад сейчас, я легко могу определить один атрибут, которым они все поделились. Это было абсолютным самым большим препятствием для оптимизации и расширения этих систем: базовая "реляционная" база данных нарушила первую нормальную форму. Практически каждая техническая "проблема", практически каждая проблема с производительностью, была основной причиной. Расщепление струн. Создание ложной системы типов данных для их проверки. Создание дополнительных атрибутов с разделителями для их описания. Создание специальных правил для каждого "местоположения" с разделителями и необходимость реализации функции EVAL во многих системах для обеспечения их соблюдения. Использование динамического SQL или хуже, чтобы найти все это. Для реализации того, что казалось концептуально простыми функциями, потребовалось больше "умного" программирования, чем я хочу вспомнить.

Может быть, ваша система отличается. Возможно, более 40 лет исследований в области реляционных баз данных не относятся к вашей ситуации. Ради тебя, я искренне на это надеюсь. Единственная проблема заключается в том, что вы используете реляционную базу данных нереляционным способом. Точно так же, как вы можете вбить винты молотком, и вы можете потянуть лодку на мотоцикле (не нажимайте на тормоза, если вы действительно его заводите), вы можете создать индекс (полнотекстовый или b-дерево) для текста это представляет целые числа.

Но зачем тебе делать что-то из этого? Почему бы вам не хранить целые числа как целые и не наслаждаться безопасностью типов? Почему бы вам не нормализовать это в две связанные таблицы, чтобы использовать преимущества меньших транзакций и больше возможностей индексации? Если вы унаследовали систему, которую вы не можете изменить, то, пожалуйста, скажите об этом, и люди могли бы помочь с альтернативами (TVP и XML были справедливо упомянуты). Но я не вижу, чтобы в этой ситуации говорилось, что ваш молоток и мотоцикл сломаны, потому что они не очень хорошо управляют винтами и тянут лодки.

Все, что сказал (может быть, кто-то где-то переосмысливает опрометчивый дизайн), я положил LIKE хорошо использовать при поиске строк с разделителями:

-- Setup demo data
declare @delimitedInts table (
    data varchar(max) not null
)
insert into @delimitedInts select '0,1,2'
insert into @delimitedInts select '1,2,3,4'
insert into @delimitedInts select '5,10'

-- Create a search term
declare @searchTerm int = 2

-- Get all rows that contain the searchTerm
select data
from @delimitedInts
where ',' + data + ',' like '%,' + cast(@searchTerm as varchar(11)) + ',%'

-- Create many search terms
declare @searchTerms table (
    searchTerm int not null primary key
)
insert into @searchTerms select 2
insert into @searchTerms select 3
insert into @searchTerms select 4

-- Get all rows that contain ANY of the searchTerms
select distinct a.data
from @delimitedInts a
    join @searchTerms b on ',' + a.data + ',' like '%,' + cast(b.searchTerm as varchar(11)) + ',%'

-- Get all rows that contain ALL of the searchTerms
select a.data
from @delimitedInts a
    join @searchTerms b on ',' + a.data + ',' like '%,' + cast(b.searchTerm as varchar(11)) + ',%'
group by a.data
having count(*) = (select count(*) from @searchTerms)

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

Обновление: XML

Я провел небольшое тестирование по преобразованию столбца, разделенного пробелом, в столбец XML и его запросов, в том числе с помощью индексов XML. К сожалению, вы не можете поместить индекс XML в вычисляемый столбец, поэтому я использую триггер для автоматического обновления столбца XML. Вот некоторые интересные результаты (обратите внимание на комментарии SQL):

-- Create a demo table
create table MyTable (
      ID int not null primary key identity
    , SpaceSeparatedInts varchar(max) not null
    --, ComputedIntsXml as cast('<ints><i>' + replace(SpaceSeparatedInts, ' ', '</i><i>') + '</i></ints>' as xml) persisted -- Can't use XML index
    , IntsXml xml null
)
go
-- Create trigger to update IntsXml
create trigger MyTable_Trigger on MyTable after insert, update as begin
    update m
    set m.IntsXml = cast('<ints><i>' + replace(m.SpaceSeparatedInts, ' ', '</i><i>') + '</i></ints>' as xml)
    from MyTable m
        join inserted i on m.ID = i.ID
end
go
-- Add some demo data
insert into MyTable (SpaceSeparatedInts) select '1'
insert into MyTable (SpaceSeparatedInts) select '1 2'
insert into MyTable (SpaceSeparatedInts) select '2 3 4'
insert into MyTable (SpaceSeparatedInts) select '5 6 7 10'
insert into MyTable (SpaceSeparatedInts) select '100 10 1000'
go

-- Search for the number 10 (and use this same query in subsequent testing, below)
select *
from MyTable
where IntsXml.exist('/ints/i[. = "10"]') = 1
-- This query spends virtually all of its time running an XML Reader and an XPath filter

-- Add a primary xml index
create primary xml index IX_MyTable_IntsXml on MyTable (IntsXml)
-- The query now uses a clustered index scan and clustered index seek on PrimaryXML

-- Add secondary xml index for value
create xml index IX_MyTable_IntsXml_Value on MyTable (IntsXml) using xml index IX_MyTable_IntsXml for value
-- No change

-- Add secondary xml index for path
create xml index IX_MyTable_IntsXml_Path on MyTable (IntsXml) using xml index IX_MyTable_IntsXml for path
-- No change

-- Add secondary xml index for property
create xml index IX_MyTable_IntsXml_Property on MyTable (IntsXml) using xml index IX_MyTable_IntsXml for property
-- The query now replaces the clustered index scan on PrimaryXML with an index seek on SecondaryXML

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

Answer 3

Я не уверен, что понимаю, что вы ищете, но если вы хотите хранить несколько значений в одном столбце, лучше всего будет использовать XML.

Смотрите этот пост для получения дополнительной информации о концепции.

Запрос столбцов XML в SQLServer 2005