Desvendando o Full-text Search no SQL Server 2008 - Revista .Net Magazine 90

Falar sobre full-text é falar sobre um recurso do SQL Server que possibilita pesquisas complexas em conteúdos textuais. Com este recurso é possível pesquisar informações dentro de colunas com conteúdo do tipo char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary e varbinary(max) (imagine pesquisar informações em documentos armazenados no banco de dados, como um arquivo Word).

O full-text existe desde a versão 7.0 do SQL Server, mas foi na versão 2005 que este recurso foi amadurecido e pode-se dizer que caiu no gosto dos desenvolvedores e DBAs. Mas na versão 2005 ainda existiam problemas porque o full-text era um recurso externo (era tratado como arquivos do banco de dados) ao SQL Server, fato que dificultava o gerenciamento da segurança, por exemplo. Com o SQL Server 2008 o full-text passou a estar totalmente integrado à engine de banco de dados. Realmente existem serviços externos ao full-text, mas a engine do full-text search agora faz parte do serviço do SQL Server.

E se não for possível usar o full-text, como as consultas podem ser feitas? Sem o full-text a melhor maneira de efetuar consultas em campos do tipo string (char, varchar, nchar, nvarchar, text, ntext) era usando o operador LIKE, na cláusula WHERE do seu comando. Contudo o uso do operador LIKE é muito lento, possui poucas opções, não possibilita buscas muito complexas, e dependendo do uso este operador não utiliza os melhores planos de execução podendo ignorar os índices criados.

A pesquisa full-text é bem rápida porque é baseada em índices full-text. Estes índices não são como os outros índices criados na base de dados, não são baseados na estrutura B-Tree habitual, são baseados em tokens (pedaços de palavras) e possuem uma estrutura bem particular o que faz com que as suas consultas sejam muito rápidas mesmo quando existem necessidades complexas como pesquisar todas as linhas da tabela de produtos, onde na coluna descrição, a palavra “tênis”, esteja próxima da palavra “running”, ou então pesquisar todas as linhas da tabela de produtos onde na coluna descrição existam variações da palavra “corrida”. Estes dois tipos de consultas são quase impossíveis de serem feitas com o operador like, e se forem feitas, com certeza serão tão lentas e consomem tantos recursos que são consultas inviáveis.

Mais uma vez vou ressaltar que no SQL Server 2008 a pesquisa full-text (que é proveniente do Full-text Search Engine – responsável por manipular todas as funções de indexação e pesquisa) é totalmente integrada ao processador de consultas do SQL Server.

Componentes do Full-text

Um componente do full-text que merece destaque é o host daemon de filtro (serviço fdhost.exe), responsável por carregar os componentes usados pelo índice e consulta. Os principais componentes usados para indexação e consulta full-text são: decompositores de palavras (usados para localizar o limite das palavras), os identificadores de raiz de palavras (auxiliam na conjugação dos verbos) e os filtros de documento (usados para extrair informações textuais de dados binários).

Com o SQL Server 2008, existem decompositores de palavras para mais de 40 idiomas. Para obter a lista dos idiomas suportados basta executar a seguinte sentença SQL:

SELECT * FROM sys.fulltext_languages

O idioma escolhido para a coluna que será indexada determinará a maneira como o texto será analisado. Outro fato que merece ser lembrado, é que todas as colunas indexadas com um índice full-text e listadas em uma cláusula de uma das funções de pesquisa full-text (as funções de pesquisa, serão citadas logo mais neste artigo) devem possuir o mesmo idioma, ou a opção LANGUAGE deve ser usada.

E se você resolveu não atribuir nenhum idioma para a coluna que será indexada por um índice full-text? Neste caso, o idioma usado será o idioma padrão do full-text, ou seja, para versões traduzidas do SQL Server, o idioma padrão será o idioma do sistema operacional, para versões não traduzidas o idioma padrão é o inglês. Se for necessário alterar o idioma do full-text, é preciso primeiro verificar qual o ID do idioma que será setado como default. Isto é possível através da execução do comando apresentado anteriormente. De posse do ID do idioma é preciso usar a stored procedure SP_CONFIGURE para fazer esta alteração. Lembrando que ao alterar o idioma default do full-text da instância, não é preciso reiniciar o SQL Server.

Os filtros são componentes muito interessantes, uma vez que devido à existência deles é possível extrair dados de documentos armazenados no banco de dados. Podemos armazenar qualquer tipo de objeto em colunas do tipo varbinary(max) e image. De acordo com o tipo de objeto armazenado em colunas destes tipos, a engine do SQL Server escolhe o tipo de filtro correto. Mas como a engine sabe o tipo de arquivo que está armazenado? Na realidade a engine não sabe, uma vez que é possível armazenar uma infinidade de tipos de arquivo nas colunas varbinary(max) e image, e a extensão do arquivo não é visível para a engine (e nem para o DBA). Desta forma é preciso que a sua tabela que contém uma coluna que armazenará um documento (que será pesquisado e indexado através do full-text), possua uma coluna que indique o tipo de documento armazenado.

Com base na informação armazenada nesta coluna, o SQL Server escolherá o filtro correto para pesquisar as informações deste arquivo. A coluna que contém o tipo do arquivo pode ser de qualquer tipo de dados string (varchar, char, nvarchar, nchar) e deve conter a extensão do arquivo armazenado na coluna varbinary(max) ou image (por exemplo, se a coluna armazena um arquivo do tipo texto a extensão que será armazenada na coluna de tipo pode ser “.txt”).

Para verificar quais os tipos de documentos suportados pelo full-text basta executar a seguinte sentença SQL:

SELECT * FROM sys.fulltext_document_types

Catálogos Full-text

Um catálogo full-text no SQL Server 2008 nada mais é do que um container para vários índices full-text. O objetivo do uso de um catálogo é simplificar a administração dos índices full-text, e também manter a compatibilidade com o SQL Server 2005.

Ao criar um catálogo é preciso definir como ele deve ser preenchido. Existem as seguintes opções:

·         Preenchimento completo: Normalmente esta opção é escolhida somente na criação do catálogo, porque pode ser demorada. No preenchimento completo a engine de banco de dados constrói entradas de índice para todas as linhas em todas as tabelas ou views que utilizarão o catálogo full-text;"

ATENÇÃO! A exibição deste artigo foi interrompida.

  Este é um post disponível para assinantes MVP

Este post também está disponível para assinantes da .net Magazine DIGITAL ou para quem possui Créditos DevMedia.  Clique aqui para saber mais!

Saiba como acessar este post através de Créditos DevMedia:

Assinatura de Créditos – Aqui este post custa R$ 1,50
Com esse plano você compra R$ 180,00 em créditos e ganha, em média, 80% de desconto no preço do post.  
Assine agora

Plano ocasional - Aqui este post custa: R$ 2,00 (assinante) ou R$ 2,50 (não-assinante)
Você compra um mínimo de R$ 60,00 em créditos e ganha, em média, 50% de desconto no preço do post.  
Compre agora

Plano conveniência – Neste plano este post custa R$ 5,00
Neste plano você compra somente um post, pagando seu preço sem desconto.  
Compre agora