Categoria: Computacion:
Programación
- El Servicio Microsoft
Search - Catálogos de
texto - Índices de
texto - Creación de un
catálogo y un índice en 10 sencillos
pasos - Consultas a los catálogos
de texto - Predicado
CONTAINS - Predicado
FREETEXT - Bibliografía
En SQL Server
como en cualquier otro sistema gestor de
bases de datos
podemos realizar búsquedas en nuestras tablas a
través del lenguaje
SQL. Cuando
queremos encontrar una palabra en un campo ‘char’ o
‘varchar’ basta con realizar una consulta en la que
pongamos nuestra condición en la cláusula WHERE
para cualificar las filas deseadas. Esto casi siempre es
suficiente, pero hay casos en los que es necesario realizar
búsquedas más complicadas. Por ejemplo
búsquedas que incluyan dos o más palabras, o que
proporcionen resultados ordenados según el peso que le
damos a las palabras buscadas, o que busquen en catálogos
enormes con cientos de miles de filas…
En estos casos el motor de la
base de datos
que realiza estas búsquedas no es el más adecuado
por varias razones: el código
SQL para obtener los resultados deseados puede llegar a
complicarse mucho, las búsquedas se pueden eternizar
porque obligamos al motor de la base
de datos a analizar
cada fila y todas las palabras que hay en cada fila, hacer
búsquelas de grupos de
palabras es muy costoso…
Es aquí donde el servicio
Microsoft
Search viene en nuestra ayuda.
El Servicio Microsoft Search es un motor de
indexación y de búsqueda de texto que
permite al SQL Server
realizar consultas de texto eficaces
y sofisticadas sobre columnas que almacenan datos basados en
caracteres.
Entre las diferencias existentes entre este servicio y
las búsquedas que utilizan el operador LIKE podemos citar
las siguientes:
- Se almacenan en el sistema de
archivos y
no en la base de datos,
aunque es la base de datos quien los administra. - Sólo se permite un índice de texto por
cada tabla. - Si se desea agregar datos a los índices de
texto hay que realizar un llenado manual o
programado (También se pueden llenar
automáticamente en la inserción de
datos). - Son más rápidos y
flexibles.
La utilización del servicio Microsoft Search
tendrá dos vertientes. En primer lugar tenemos que crear
los índices de texto en las tablas deseadas y llenarlos, y
en segundo lugar el servicio realizará las
búsquedas y devolverá conjuntos de
resultados adecuados al criterio de búsqueda.
Pero antes de utilizar este servicio tenemos que
instalarlo. El motor de texto se ejecuta como un servicio
denominado Microsoft Search en Windows NT
Server, Windows 2000 y
Windows 2003
Server y también se puede instalar durante la
instalación del SQL Server (No está disponible para
la versión SQL Server Personal).
Para saber si este servicio está instalado en
nuestro equipo la manera más sencilla es recurrir al
‘Administrador de
Servicios’ del SQL Server
Donde podemos ver si el servicio está instalado y
además podemos iniciar o detenerlo a voluntad.
Se llama catálogo de texto al lugar físico
donde se almacenan los índices de texto de una base de
datos. Cada catálogo puede satisfacer las necesidades de
indexación de varias tablas de una base de
datos.
Estos catálogos de texto se deben ubicar en una
unidad de disco local asociada a la instancia de SQL Server (Las
unidades extraíbles, los disquetes y las unidades de
red no
sirven).
En cada servidor pueden
crearse hasta 256 catálogos de texto
Los índices de texto se vinculan a una columna de
una tabla. Un primer requisito es que la tabla en cuestión
tiene que tener un índice exclusivo, que debe ser lo
más pequeño posible para reducir los recursos
utilizados por el servicio Microsoft Search.
Este índice que creamos sobre una columna de
texto almacenará información sobre cada palabra que contiene
la columna y su ubicación dentro de la tabla.
Los índices se pueden actualizar (como los
índices normales del SQL Server) al modificar los datos de
la tabla, o se pueden llenar a intervalos regulares.
Estos procesos de
llenado se suelen realizar de forma asíncrona y en segundo
plano porque consumen tiempo y recursos.
Creación de
un catálogo y un índice en 10 sencillos
pasos
Para crear un catálogo de texto podemos utilizar
el ‘Administrador
Corporativo’ con los asistentes y el interfaz
gráfico proporcionado, o podemos hacerlo mediante
sentencias TSQL.
En este caso vamos a utilizar la segunda opción
porque es un método
más ilustrativo y porque si conocemos este camino usar los
asistentes es muy sencillo.
Vamos pues a ver paso a paso que sentencias y procedimientos
almacenados intervienen en la creación de nuestro
catálogo de texto.
1-Comprobar que el servicio Microsoft Search esta
funcionando
Ya hemos visto un modo sencillo de comprobar esto con el
‘Administrador de Servicios’ del SQL Server. Si no está
instalado hay que hacerlo y ponerlo en funcionamiento.
2-Crear una base de datos y habilitarla para utilizar
catálogos de texto
En vez de crear una base de datos por sencillez vamos a
utilizar la base de datos ‘Northwind’ que viene
instalada con el SQL Server.
Habilitarla para usar catálogos de texto se
traduce en ejecutar la sentencia
‘sp_fulltext_Database‘ en el Analizador de
Consultas:
use Northwind
go
sp_fulltext_Database 'enable'
3-Crear un catálogo vinculado con esta base de
datos
Utilizamos el procedimiento
almacenado ‘sp_fulltext_catalog’ que crea o quita un
catálogo de texto, e inicia o detiene la acción de
indización de un catálogo (luego lo
veremos).
Este procedimiento
recibe un par de parámetros. El primero es el nombre del
catálogo y el segundo indica la acción a
realizar.
Vamos a crear un catálogo que se llame
‘Ingredientes’.
sp_fulltext_catalog 'Ingredientes','create'
4-Habilitamos la tabla para usar índices de
texto
Ahora escogemos la tabla que queremos indizar y
habilitamos y añadimos esa tabla a nuestro
catálogo. Para hacerlo usamos el procedimiento almacenado
‘sp_fulltext_table’.
Este procedimiento funciona de la siguiente
manera:
sp_fulltext_table ‘nombre de la tabla’,
‘acción’, ‘catalogo’,
‘índice único de la tabla’
Lo que tenemos que ejecutar en el ‘Analizador de
Consultas’ si queremos indizar la tabla
‘Products’ es lo siguiente:
sp_fulltext_table 'Products', 'create', 'Ingredientes',
'PK_Products'
donde ‘PK_Products’ es una clave
única definida en nuestra tabla.
5-Habilitar la columna que contendrá las
palabras a buscar
El procedimiento almacenado
‘sp_fulltext_column’ especifica si una columna
particular de una tabla participa en la indización de
texto.
Habilitemos la columna
‘ProductName’
sp_fulltext_column 'Products', 'ProductName',
'add'
6-Activar la tabla
Con el procedimiento almacenado visto anteriormente
‘sp_fulltext_Table’ activamos la tabla con la que
estamos trabajando para poder
llenarla.
sp_fulltext_Table 'Products', 'Activate'
7-Llenar el catálogo
Y un último paso es rellenar el catálogo
que tenemos con los datos de la tabla que participa de la
indización.
El procedimiento almacenado que lo hace por nosotros es
‘sp_fulltext_catalog’ que ya comentamos
anteriormente. Se puede utilizar para rellenar un catálogo
completamente o para iniciar un rellenado incremental.
En este ejemplo queremos realizar un llenado completo
porque por ahora esta vacío.
sp_fulltext_catalog
'Ingredientes','start_full'
y por fin hemos acabado de crear el catálogo de
texto y lo tenemos operativo. Ahora sólo falta
utilizarlo.
8-Comprobar que funciona
Un último paso es ejecutar una sentencia sencilla
que utilice el catálogo recién creado para
comprobar que funciona.
SELECT * FROM products WHERE
CONTAINS(ProductName,'queso')
y esta sentencia nos devuelve todas las filas que
contienen la palabra queso.
9-Modificar las tablas
Insertemos un par de filas nuevas en la tabla productos.
INSERT INTO Products VALUES('Queso fresco de Burgos', 5,
4, '1 kg pkg', 12, 30, 0, 0, 0)
INSERT INTO Products VALUES('Quesitos de la abuela', 5,
4, '8 porciones', 41, 10, 0, 0, 0)
Si ahora repetimos la búsqueda de antes veremos
que este nuevo queso no aparece en los resultados. Hace falta
reconstruir el catalogo de texto
10-Rellenar el catalogo de manera
incremental
Ejecutamos el mismo procedimiento almacenado que en el
paso 7 pero ahora para rellenar el catálogo sólo
con las filas que han sido creadas desde entonces.
sp_fulltext_catalog 'Ingredientes',
'start_incremental'
y ahora con el catálogo actualizado podemos
ejecutar otra vez el
SELECT * FROM products WHERE
CONTAINS(ProductName,'queso')
para ver que ahora sí obtenemos la fila insertada
en el paso 9
Nuestro catálogo está creado y
funcionando. Ahora hay que averiguar como le sacamos partido a
estos catálogos de texto.
Consultas a los
catálogos de texto
Tenemos ahora que ejecutar consultas que utilicen este
catálogo, y distinguiremos dos tipos de consultas: las que
utilizan un predicado sencillo como CONTAINS o FREETEXT, y las
que utilizan funciones como
CONTAINSTABLE y FREETEXTTABLE.
En este texto nos quedaremos sólo con las
primeras por ser las más sencillas.
Es un predicado que se utiliza para buscar en columnas
que contengan tipos de datos de
tipo carácter
coincidencias exactas o aproximadas con palabras o
frases.
CONTAINS puede buscar una palabra o una frase, el
prefijo de una palabra, una palabra cerca de otra palabra, una
palabra que sea una inflexión de otra, una palabra que
tenga un peso especificado mayor que el de
otra…
Como vemos es muy flexible.
Vamos a ver su uso con un par de ejemplos que como
siempre es la mejor manera.
1-Buscar todas las filas que contengan la palabra
‘queso’
SELECT * FROM products WHERE
CONTAINS(ProductName,'queso')
2-Buscar todas las filas que contengan
‘queso’ y ‘burgos’
SELECT * FROM products WHERE CONTAINS(ProductName,'queso
AND burgos)
3-Buscar todas las filas que tienen ‘queso’,
‘quesitos’, ‘quesada’…
SELECT * FROM products WHERE CONTAINS(ProductName,
'"ques*"')
Además de estas hay otras formas un poco
más elaboradas de utilizar CONTAINS. Como siempre todo lo
tenéis en los BOL.
Se utiliza para buscar en columnas de tipo carácter
valores que
coincidan con el significado de la condición de
búsqueda. Cuando se utiliza FREETEXT se separa la cadena
buscada internamente en palabras que son términos de
búsqueda y se asigna a cada uno de los términos un
peso y se buscan las coincidencias.
Supongamos que queremos comer chocolate y queso. Pues al
argumento de FREETEXT puede ser algo como ‘quiero comer
chocolade con queso’ y nos dará las filas que
contienen palabras de nuestra frase (usamos chocolade porque es
así como está escrito en Northwind)
SELECT * FROM Products
WHERE FREETEXT(ProductName, 'quiero comer chocolade con
queso')
Y para terminar decir que todo esto lo podemos comprobar
de manera gráfica como decíamos al principio en el
‘Administrador Corporativo’. Dentro de la base de
datos Northwind hay una categoría que es
‘Catálogos de Texto’ y en ella está el
catálogo ‘Ingredientes’ que tiene indexada la
tabla ‘Productos’.
Como podéis ver la utilización de
catálogos de texto es una herramienta muy potente del SQL
Server y puede ser de gran ayuda a la hora de realizar
búsquedas.
Una aplicación directa podría ser un
buscador en una Web…
Espero que os sea útil.
BOL de SQL Server
Web de Microsoft-MSDN
Autor:
César Manivesa