FULL TEXT Search com o SQL SERVER

Hoje vamos falar sobre uma funcionalidade do SQL Server, que tenho pouca familiaridade mas proporciona recursos muito interessantes para pesquisa em campos Text, NText, Varchar, NVarchar e os mais atuais Varchar(MAX). Estou falando da FULL TEXT Search, funcionalidade que permite realizar pesquisas com uma performance razoável e com diversas opções de pesquisa.

Para podermos usar as funcionalidades da FULL TEXT Search, devemos criar um FULL TEXT Catalog no banco de dados onde a pesquisa será realizada, assim com um FULL TEXT INDEX, fazendo referência a um índice do tipo UNIQUE na tabela onde a pesquisa será feita. Este índice será a nossa SEARCH KEY.

A SEARCH KEY deve ser do tipo UNIQUE, não nulo, ser composto de apenas uma coluna, não ser definido como não-determinístico (resultado de uma coluna calculada) e não possuir filtros.

Como acredito que o aprendizado é mais efetivo através do Learn by Doing, vamos a um rápido exemplo que pode ser executado em uma instância do SQL Server 2008 Express ou mais completa.

Primeiro, crie uma tabela de testes:

IF EXISTS(SELECT 0 FROM sys.objects WHERE object_id=OBJECT_ID('Paises'))

      DROP TABLE Paises

     

CREATE TABLE Paises(

      Id int identity(1,1) PRIMARY KEY NONCLUSTERED

      ,Nome NVarchar(50) NOT NULL

      ,Descricao varchar(max)   

)

Vamos acender a rivalidade com nossos hermanos, comparando o nosso Brasil com a Argentina:

INSERT INTO Paises(Nome) VALUES ('Brasil')

INSERT INTO Paises(Nome) VALUES ('Argentina')

Como a nossa SEARCH KEY precisa de um índice do tipo UNIQUE, vamos criá-lo no campo Nome da tabela Países.

CREATE UNIQUE CLUSTERED INDEX IX_NomePaises

ON Paises(Nome)

WITH (FILLFACTOR=80)

Agora vamos adicionar algumas informações obtidas através da Wikipedia, para humilharmos os hermanos compararmos os dois países:

UPDATE Paises

SET Descricao='Brasil, oficialmente República Federativa do Brasil,[nota 2][8] é uma república federativa presidencialista localizada na América do Sul, formada pela união de 26 estados federados e por um distrito federal, divididos em 5 565 municípios.[9] Faz fronteira a norte com a Venezuela, com a Guiana, com o Suriname e com o departamento ultramarino da Guiana Francesa; ao sul com o Uruguai; a sudoeste com a Argentina e com o Paraguai; a oeste com a Bolívia e com o Peru e, por fim a noroeste com a Colômbia. Os únicos países sul-americanos que não têm uma fronteira comum com o Brasil são o Chile e o Equador. O país é banhado pelo oceano Atlântico  ao longo de toda sua costa norte, nordeste, sudeste e sul. Além do território continental, o Brasil também possui alguns grandes grupos de ilhas no oceano Atlântico como os Penedos de São Pedro e São Paulo, Fernando de Noronha (território estadual de Pernambuco), Trindade e Martim Vaz, no Espírito Santo, e um complexo de pequenas ilhas e corais chamado Atol das Rocas (pertencente ao estado do Rio Grande do Norte).[10]

Com 8 514 876,599 quilômetros quadrados de área,[11] equivalente a 47% do território sul-americano, e com cerca de 190 milhões de habitantes,[1] o país possui a quinta maior área territorial do planeta e o quinto maior contingente populacional do mundo. O Brasil é o único país falante do português das Américas,[12] além de ser uma das nações mais multiculturais e etnicamente diversas do mundo, resultado da forte imigração vinda de muitos países.

O Brasil foi uma colônia do Império Português desde o desembarque de Pedro Álvares Cabral em 1500 até 1815, quando se tornou um reino unido com Portugal. Em 1822 o país se tornou independente, formando o Império do Brasil, época em que esteve sob a soberania da família imperial brasileira, um dos ramos da Casa de Bragança, por quem era governado desde 1500, no Brasil Colônia. Em 1889 torna-se uma república, embora a legislatura bicameral, agora chamada de Congresso, remonte à ratificação da primeira Constituição em 1824. Desde a proclamação da república brasileira em 1889, o Brasil tem sido governado por três poderes, o judiciário, legislativo e o executivo, em que o chefe do último, eleito a cada quatro anos pelo voto popular, é o presidente do Brasil.

Nona maior economia do planeta em paridade do poder de compra (2008), oitava maior em PIB nominal (2009) e maior economia latino-americana,[13] o Brasil tem hoje forte influência internacional, seja em âmbito regional ou global.[14] Em 2005, encontrava-se na 39ª posição entre os países com melhor qualidade de vida do planeta,[15] além de possuir entre 15 e 20% de toda biodiversidade mundial,[16] sendo exemplo desta riqueza a Floresta Amazônica, com 3,6 milhões de km², a Mata Atlântica, o Pantanal e o Cerrado. O Brasil é membro fundador da Organização das Nações Unidas, do G20, do Mercosul, da CPLP, da União de Nações Sul-Americanas e é um dos países BRIC.'

WHERE

Id=1

UPDATE Paises

SET Descricao='Argentina, oficialmente República Argentina (em espanhol: República Argentina, AFI: [repuβlika arxentina]), é o segundo maior país da América do Sul, constituída como uma federação de 23 províncias e uma cidade autônoma, Buenos Aires. É o oitavo maior país do mundo em área territorial e o maior entre as nações de língua espanhola, embora México, Colômbia e Espanha sejam mais populosos.

A área continental da Argentina está entre a Cordilheira dos Andes a oeste e o Oceano Atlântico, a leste. Faz fronteira com Paraguai e Bolívia ao norte, Brasil e Uruguai a nordeste e com o Chile a oeste e sul. A Argentina reivindica o território britânico ultramarino das Ilhas Geórgia do Sul e Sandwich do Sul. O país também reivindica uma parte da Antártida, sobrepondo as reivindicações do Chile e do Reino Unido no continente antártico, mesmo após todas as reivindicações terem sidos suspensas pelo Tratado da Antártida de 1961.

A Argentina possui o segundo mais alto Índice de Desenvolvimento Humano e PIB per capita em paridade do poder de compra da América Latina. O país é um dos membros do G20 maiores economias, com o 30º maior PIB nominal do mundo e o 23º maior quando a paridade do poder de compra é considerada. O país está classificado como de renda média alta ou como um mercado emergente secundário pelo Banco Mundial.'

WHERE

Id=2

Agora vamos criar o nosso FULL TEXT Catalog.

CREATE FULLTEXT CATALOG PaisesCatalog AS DEFAULT;

E agora o nosso índice FULL TEXT.

CREATE FULLTEXT INDEX ON Paises(Descricao)

KEY INDEX IX_NomePaises;

E agora podemos realizar pesquisas utilizando o FREE TEXT, que realiza pesquisas no campo especificado em qualquer parte do texto. Este tipo de pesquisa, é similar a uma busca na internet utilizando um search engine como o Google.

SELECT * FROM Paises

WHERE FREETEXT(Descricao, 'espanhol brasil')

Ou através do comando CONTAINS que permite pesquisar fragmentos de frases no texto. Se quisermos pesquisa registros que no campo descrição tem palavras com o fragmento FEDE, como FEDERAÇÃO ou FEDERATIVA, podemos executar a seguinte query:

SELECT * FROM Paises

WHERE CONTAINS(Descricao, ' "*fede*" ')

Até a próxima e "Long live SQL SERVER!".