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 – FREETEXT e FREETEXTTABLE

Quem achou muito complicado este tal Full-Text Search com as funções CONTAINS e CONTAINSTABLE, pode ficar tranquilo, pois existe uma alternativa que simplifica toda a complexidade das pesquisas.

Esta alternativa é o uso da função FREETEXT, que possibilita pesquisas com todas as variações obtidas por clausulas (OR, AND), prefixação/sufixação e tesauros (sinônimos) de forma simplificada, deixando o Full-Text Search encarregado de recuperar todos os registros de uma determinada tabela que possuem alguma relação com alguma das palavras informadas.

Conforme o exemplo a seguir, para a pesquisa de todas as variações da palavra “Azul” com a função CONTAINS eram necessários os predicados INFLECTIONAL e THESAUROS, no caso da função FREETEXT, somente a palavra a ser pesquisada:

No caso de mais de uma palavra, a situação complicava mais ainda com a função CONTAINS, mas com FREETEXT, a solução ainda se mantém simples:

A função FREETEXT permite até a utilização de frases e textos como critérios para pesquisa:

E para aqueles que gostaram da função CONTAINSTABLE e querem ter a mesma facilidade da função FREETEXT, existe a função tabular FREETEXTTABLE:

Apesar de ser mais fácil utilizar a função FREETEXT, sempre verifique se a função CONTAINS é apta como solução ao seu cenário ao invés da FREETEXT, visto que a facilidade da função FREETEXT algumas vezes pode sair muito cara no quesito desempenho em comparação à função CONTAINS.

Espero que vocês tenham gostado deste quinto artigo sobre o Full-Text Search no SQL Server. Qualquer dúvida, crítica ou sugestão, sintam-se à vontade de comentar ou de me contatar por e-mail e Twitter.

Embora eu tenha material suficiente para mais artigos aprofundando sobre Full-Text Search, dedicarei os próximos artigos na abordagem de outras features e ferramentas úteis para o dia-a-dia do profissional de banco de dados.

Full-Text Search – CONTAINSTABLE

Nas situações que a função CONTAINS retorna muitos registros, como filtrar estes registros por importância? Para responder esta pergunta, temos a função tabular CONTAINSTABLE, que permite recuperar informações sobre importância do critério de seleção sob os registros recuperados.

A função CONTAINSTABLE utiliza da seguinte sintaxe:

CONTAINSTABLE(Tabela, Colunas, ‘TextoPesquisado’)

E o seu resultado não diferencia da função CONTAINS:

Mas como estamos com uma função tabular em mãos, mais informações podem ser obtidas, no caso as colunas KEY e RANK.

A coluna KEY é o valor do índice único que mapeamos na criação do catálogo, no caso a chave primária da tabela:

E temos a coluna RANK, que informa a importância do termo da pesquisa no resultado, onde, quanto maior a importância, maior o valor retornado.

Para melhorar a percepção da coluna RANK, o prefixo ISABOUT/WEIGHT permitirá estabelecer “pesos” de 0 a 1 para cada palavra encontrada, conforme o exemplo, onde “Texto” é mais importante que a palavra “Modelo” no critério de seleção:

Mas, se quero só recuperar alguns registros deste critério de seleção vou ter que utilizar um TOP/ORDER BY?

Ao invés do TOP/ORDER utilize a própria função CONTAINSTABLE:

Concluído mais um artigo sobre Full-Text Search, espero que tenham gostado!

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.