Full-Text Search – Resumo

E ai pessoas! Para encerrar esta primeira série de artigos sobre Full-Text Search do SQL Server, segue a relação de artigos escritos aqui no blog e também alguns links interessantes sobre o assunto.

Artigos do blog:

Introdução ao Full-Text Search – Configuração e primeiras consultas:
http://sqlfromhell.wordpress.com/2011/02/10/full-text-search-overview/

Função CONTAINS – Consultas simples e algumas boas práticas:
http://sqlfromhell.wordpress.com/2011/02/21/full-text-search-contains/

Função CONTAINS – Consultas complexas e trabalhando com sinônimos e siglas (tesauros):
http://sqlfromhell.wordpress.com/2011/03/14/full-text-search-contains-parte-2/

Função tabular CONTAINSTABLE – Consultas com rank de importância:
http://sqlfromhell.wordpress.com/2011/03/22/full-text-search-containstable/

Funções FREETEXT e FREETEXTTABLE – Todo o potencial do Full-Text Search de forma simplificada:
http://sqlfromhell.wordpress.com/2011/03/29/full-text-search-freetext/

Links externos (em inglês):

How Search Query Results Are Ranked / Como as consultas são ranqueadas:
http://technet.microsoft.com/en-us/library/ms142524.aspx

SQL Server 2008 Full-Text Search: Internals and Enhancements / Como funciona internamente o FTS
http://msdn.microsoft.com/en-us/library/cc721269.aspx

Full-Text Search – CONTAINS – Parte 2

E ai pessoas!

Ando meio sumido, visto que assumi alguns projetos grandes de infraestrutura e desenvolvimento que estão me roubando as poucas horas de descanso que utilizo para escrever no blog.

Mas vamos para a segunda parte do artigo dedicado a função CONTAINS! Como vimos no artigo anterior, para recuperar registros de uma tabela que possuem a palavra “azul” em qualquer posição, podemos utilizar da função CONTAINS:

O problema começa quando o que queremos recuperar são as variações de uma determinada palavra, assim temos o recurso do ‘*’ no caso dos sufixos:

Visto que questões de gênero (masculino/feminino) e grau (singular/plural) ainda não são consideradas, se torna necessário o uso de mais um predicado, FORMSOF(INFLECTIONAL …):

Por fim, podemos juntar as duas soluções em uma só:

Para complicar mais sua vida, surge a necessidade de considerar os sinônimos (ou tesauros http://pt.wikipedia.org/wiki/Tesauro)… Bem, você pode criar uma solução própria com uma tabela que relacione todos os sinônimos, ou utilizar o próprio Full-Text Search nisso.

A solução do Full-Text Search faz uso do predicado FORMSOF(THESAURUS…), mas enquanto não é configurado, não se pode esperar muito:

Cada instância do SQL Server possui seu Full-Text Data (ou simplesmente FTData), que possui uma relação de configurações do Full-Text Search para cada idioma no formato XML. Para encontrar estas configurações, siga o seguinte caminho:

C:\Program Files\Microsoft SQL Server\{Instância do SQL Server}\MSSQL\FTData

Conforme o meu cenário:

Nesta pasta, temos as configurações dos tesauros. Sendo tsglobal.xml utilizado por todos os idiomas, tsptb.xml para o idioma “pt-BR” e tsptg.xml para o idioma “pt”.

Para demonstrar como configurar estes arquivos, segue uma situação onde configurei no arquivo tsglobal.xml que “Azul” e “Azulado” são sinônimos e “AZ” é sigla de “Azul”.

Após configurar os arquivos XML, atualize as linguanges do Full-Text Search e identifique o código da linguagem Global (Neutral) e pt-BR (Brazilian)

Com o código da linguagem (lcid), será possível atualizar as configurações de tesauros do SQL Server, com a stored procedure “sp_fulltext_load_thesaurus_file”:

Por fim, executado o predicado de tesauros com a sigla “AZ” temos como resultado registros com a palavra “azul” e com a palavra “azul” temos os registros que possuem as palavras “azul” e “azulado”:

Full-Text Search é um recurso fantástico! Mas ainda não acabou, escreverei mais artigos sobre as funcionalidade do Full-Text Search, aguardem!

Artigos relacionados:

Full-Text Search – Overview:
http://sqlfromhell.wordpress.com/2011/02/10/full-text-search-overview

Full-Text Search – CONTAINS – Parte 1:
http://sqlfromhell.wordpress.com/2011/02/21/full-text-search-contains

Full-Text Search – CONTAINS – Parte 1

Continuando a série de artigos sobre Full-Text Search (Overview), vamos conhecer um pouco de como utilizar esta ferramenta para as consultas. Visto que dentre as funções desta feature, existem duas básicas para consultas (CONTAINS e FREETEXT), sendo a função CONTAINS a que abordarei primeiramente, visto que esta é a mais rápida e permite maior flexibilidade para customização.

Utilizando o exemplo do artigo anterior, abaixo como executar uma pesquisa com a função CONTAINS sobre um campo específico de uma tabela.

Também sendo possível consultar todos os campos de uma tabela específica:

Assim como especificar mais de um campo a ser pesquisado:

Visto que somente é possível executar a função CONTAINS sobre uma tabela de cada vez, para uma consulta com junções (JOIN) sobre duas ou mais tabelas, não utilize CONTAINS sem informar de que tabela são os campos a serem pesquisados.

Mas determine corretamente qual a tabela a ser pesquisada em cada função.

Quando for necessário relacionar o resultado de várias tabelas em uma única consulta, um simples UNION ALL poderá ser bem útil.

Visto que a função CONTAINS basicamente busca uma palavra de cada vez, não tente informar um texto ou frase como predicada da pesquisa.

Mas separe as palavras a serem pesquisadas, mas não utilize AND ou OR entre duas funções CONTAINS na clausula WHERE.

Por questões de desempenho, utilize a própria função CONTAINS com seus AND (ou &) e OR (ou |) ou também AND NOT (ou !&).

Se for necessário pesquisar por palavras que apareçam juntas em uma ordem específica, o uso de aspas duplas poderá ser útil.

No caso da ordem que elas apareçam não for importante, o uso do predicado NEAR (ou ~).

Por fim, aplicando prefixos.

No próximo artigo, veremos novamente a função CONTAINS, mas com foco nos predicados.

Full-Text Search – Overview

Bom dia pessoas!

Algumas conversas com o Rodrigo (at. http://rodrigodotnet.wordpress.com) sobre Full-Text Search (FTS) me animaram a escrever algumas coisas sobre esta feature, que tive a oportunidade de fazer uso de formas diversas em clientes e projetos.

Mas para que serve esta feature? Nestes primeiros artigos, veremos que ela é útil para indexar textos que normalmente utilizamos aquela já conhecida expressão “Campo LIKE ‘%valor%’” permitindo maior desempenho, além de outras facilidades, como pesquisar palavras independente de variação de gênero (masculino, feminino) ou número (singular, plural).

Para inicio de conversa, precisamos de uma tabela com campos textos (varchar, nvarchar, char e nchar – xml e varbinary também podem entrar na brincadeira, mas veremos em outra oportunidade) para indexar, lembrando que também será necessária uma chave única ou primária formada por um único campo nesta tabela.

Em seguida, criamos nosso catálogo:

Definimos que o catálogo será o padrão (“default”) e não terá sensibilidade a acentos (“accent insensitive”):

Depois de criado o catálogo, abra as propriedades e no menu Tables/Views, adicione as tabelas que serão indexadas, a chave única ou primária, as colunas e os idiomas utilizados na indexação. Por fim, defina “Track changes” com automático.

Criado o catálogo, adicione os registros na sua tabela:

INSERT INTO Textos VALUES
('Azul', 'Modelo de texto'),
('Verde', 'Segundo texto modelo'),
('Vermelho', 'Terceiro texto modelo'),
('Amarelo', 'Modelo'),
('Preto', 'Dados complementares de livros não lidos'),
('Branco', 'Leitura não obrigatória'),
('Azulado', 'Textos obrigatórios'),
('Azuis', 'Textos obrigatórios'),
('Verdes', 'Segunda versão dos textos'),
('Vermelha', 'Literatura'),
('Negro', 'Dados complementares de livros lidos'),
('Claro', 'Leitura obrigatória')

Depois execute suas primeiras consultas que substituirão o “Campo LIKE ‘%valor%’”:

SELECT * FROM Textos WHERE CONTAINS(Nome, 'texto')

SELECT * FROM Textos WHERE CONTAINS(Texto, 'texto')

Como o FTS é preenchido em um processo assíncrono, talvez demore um pouco para as palavras serem indexadas, e alguns fragmentos dos textos podem ser desconsiderados, como artigos, advérbios e pronomes.

O FTS possui duas funções básicas para as pesquisas, CONSTAINS e a FREETEXT, que têm sintaxes semelhantes, mas podem realizar tarefas bem diferentes, as quais eu explicarei no próximo artigo.