Capitulo 8 - Lección 2: Extender la funcionalidad de Microsoft SQL Server con Full-Text Search


SQL Server 2008 incluye un sistema plenamente integrado de motor de búsqueda en texto completo . Full-Text Search permite crear consultas avanzadas que van más allá de las capacidades de los comandos tradicionales SELECT con el argumento LIKE. Mediante el uso de los predicados CONTAINS y FREETEXT, así como las funciones CONTAINSTABLE y FREETEXTTABLE, se pueden escribir consultas que devuelven los siguientes (mas información AQUI y AQUI):
  • Formas de inflexión de un verbo que se le ingrese
  • Resultados coincidentes con los datos almacenados en un archivo pdf en una columna varbinary (max)
  • Sinónimos del término de búsqueda escrito a través de una búsqueda de sinónimos
Descripción general de búsqueda de texto completo
Las capacidades de búsquedas de texto completo se introdujeron en SQL Server 7.0 y continúan progresando y mejorando con cada nueva versión. En SQL Server 2008, el servicio de búsqueda de texto completo está totalmente integrado con el motor de base de datos y ya no requiere un servicio de búsqueda externos. Además, los índices de texto completo pueden existir como parte de la estructura de base de datos en lugar de en un archivo separado en el sistema de archivos.
Arquitectura de Búsqueda de texto completo (Más información AQUI)
Para poder solucionar mal funcionamiento de las consultas de texto, debe estar familiarizado con la arquitectura general y la configuración de búsqueda de texto completo. Los siguientes procesos que conforman la arquitectura de búsqueda de texto completo:
  • Proceso de SQL Server (sqlservr.exe): Contiene el motor de texto completo, que administra la indización de texto y consultas. Debido a esta integración completa con el motor de base de datos, el optimizador reconoce y mejora el rendimiento en las consultas de texto. (Nota importante: El Servicio MSFTESQL ya no existe en SQL Server 2008.
  • Filter daemon host process (Fdhost.exe): Se ejecuta como un proceso aislado para albergar los componentes de terceros, protegiendo así el proceso de SQL Server de los componentes.
  • SQL Full-Text Filter Daemon Launcher (Fdlauncher.exe): Inicia el proceso fdhost.exe cuando sea necesario.
Terminología de Búsqueda de texto completo (Más información AQUI y AQUI)
  • Término (Term): La palabra, frase o cadena de caracteres que incluye como entrada en la consulta de texto completo.
  • Catálogo de texto completo (Full-text catalog): un objeto virtual que representa a un grupo de índices de texto completo. Cuando se crea un catálogo de texto completo en un entorno de SQL Server 2008, el catálogo de texto no pertenece a los grupos de archivos.
  • Índice de texto completo (Full-text index): Un objeto que contiene palabras significativas y su ubicación dentro de las columnas incluidas en el índice. Cuando se define el índice de texto completo, se especifica un índice único que identifica a las filas de la tabla,las columnas que se van a incluir en el índice, el catálogo al que pertenece el índice,el grupo de archivos en el que se crea el índice, y opciones adicionales.
  • Divisor de palabras (Word breaker): un proceso que acorta el texto en función de las reglas léxicas de dicho idioma. Se puede definir el idioma de los datos en la columna cuando se crea un índice de texto completo sobre una tabla (Más información AQUI).
  • Testigo o Símbolo (Token): Palabra o cadena de caracteres identificada por el separador de palabras.
  • Analizador lingüístico  o Lematizador (Stemmer): Un proceso que conjuga los verbos sobre la base de las normas lingüísticas de la lengua de datos definidos.
  • Diccionario de sinónimos (Thesaurus): Lenguaje de marcado extensible (XML) que definen los sinónimos de un término en un idioma específico. Debe definir las asignaciones de diccionario de sinónimos para un idioma determinado antes de las consultas de texto pueden buscar sinónimos en ese idioma. Los archivos de diccionario de sinónimos están vacío por defecto.
  • Palabras de parada (Stopword): Una palabra que se utiliza comúnmente y no aporta ningún significado a la búsqueda, tales como a, an, y the. Palabras vacías en SQL Server 2008 proporciona una funcionalidad similar a las palabras irrelevantes de SQL Server 2005 (Más información en 2005 y 2008).
  • Lista de palabras irrelevantes (Stoplist): Un objeto de base de datos que se utiliza para administrar las palabras vacías. Hay una lista de palabras irrelevantes definida por el sistema y también puede crear sus propios listas de palabras irrelevantes. Una lista de palabras irrelevantes puede estar asociado con un índice de texto completo.
  • Filtro (Filter): Un componente que procesa un documento para extraer la información textual de los documentos almacenados en una columna varbinary(max) o la imagen y luego envía esa información al divisor de palabras. Cada tipo de archivo (como.Doc,. Xls y. Pdf) debe tener su propio filtro. Cuando se define un índice de texto completo en una columna varbinary(max) o columna de imagen que contiene los documentos, también debe definir un tipo de columna que contiene la extensión de archivo asociado con el tipo de archivo que figuran en la misma fila.
  • Población (rastreo) (Population (crawl)): El proceso de añadir datos a un índice durante la creación o cuando el índice se vuelve a crear. Esto puede ser iniciado de forma automática o manual.
  • Motor de texto completo (Full-Text Engine): Un componente integral del proceso de servicio de SQL Server que gestiona las tareas administrativas de texto completo, se encarga de la ejecución de consultas de texto completo , y gestiona el proceso de host del demonio(daemon) de filtro.
  • Demonio de filtro de proceso de host (fdhost.exe) (Filter daemon host process (Fdhost.exe)): Un proceso que administra los componentes de terceros, tales como filtros, separadores de palabras y lematizadores separado del proceso de SQL Server.
  • Iniciador de demonio de filtro de Texto completo (Fdlauncher.exe) (SQL Full-Text Filter Daemon Launcher (Fdlauncher.exe)): Un servicio que inicia los procesos de fdhost.exe cuando el motor de texto completo los requiere.Esta es la única función de este servicio.
Configurando Búsqueda de texto completo (Más información AQUI)
Aunque un administrador de base de datos normalmente configura y administra índices de texto completo, como un desarrollador de bases de datos, usted debe tener una comprensión básica de cómo activar y configurar búsquedas de texto completo. Puede realizar todos los pasos necesarios, ya sea con Transact-SQL (T-SQL) o a través de SSMS.
De manera predeterminada, al instalar el SQL Server 2008 Database Engine, la búsqueda de texto completo se incluye en la instalación. Si decide no instalar los componentes de búsqueda de texto completo durante la instalación de SQL Server, puede utilizar el Centro de instalación de SQL Server para agregar esta función.
Además, en SQL Server 2008, todas las bases de datos están habilitados automáticamente para apoyar la búsqueda de texto completo. Puede localizar el Centro de instalación de SQL Server haciendo clic en Inicio, Todos los programas,Microsoft SQL Server 2008 y Herramientas de configuración.
Para activar la función de las capacidades de búsqueda de texto, debe crear un catálogo de texto completo y un índice de texto completo, como se explica en las siguientes secciones.

La creación de catálogos de texto (Más información AQUI)
El primer paso para configurar la indización de texto en una base de datos es crear un catálogo de texto completo sobre la base de datos que desea consultar datos mediante el uso de texto completo las capacidades de búsqueda. Usted no puede crear los catálogos de texto en las bases de datos master, model o  tempdb. Puede utilizar el comando CREATE FULLTEXT CATALOG para crear el catálogo de texto completo.
La Sintaxis es la siguiente:
image
El comando CREATE FULLTEXT CATALOG incluye los siguientes argumentos:
  • Catalog_name: Especifica el nombre que el catálogo tendrá.
  • ON FILEGROUP filegroup: Se incluye por compatibilidad con versiones anteriores y no tiene ningún efecto en SQL Server 2008.
  • IN PATH ‘rootpath’: Se incluye por compatibilidad con versiones anteriores y no tiene ningún efecto en SQL Server 2008.
  • ACCENT_SENSITIVITY ON / OFF: Especifica si las búsquedas se distingue acentos. Si no se especifica esta opción, la distinción de acentos de la intercalación de base de datos se utiliza.
  • AS DEFAULT: Especifica que el catálogo que se está creando será el catálogo predeterminado para la base de datos. (Si se crea un índice de texto completo en la misma base de datos sin especificar un catálogo, el catálogo se utiliza por defecto.)
  • AUTHORIZATION Owner_name: Establece el propietario del catálogo de texto completo.
El siguiente código crea un catálogo denominado ftCatalog como catálogo predeterminado de texto completo sobre la base de datos AdventureWorks2008R2:
USE AdventureWorks2008R2;
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT

La creación de índices de texto completo (Más información AQUI)
Antes de crear un índice de texto utilizando el comando CREATE FULLTEXTINDEX, debe cumplir los siguientes requisitos:
  • Un índice de texto completo no puede existir sobre una tabla si ya existe uno. Sólo se puede crear un índice de texto completo por tabla.
  • Un índice de clave única debe existir en la tabla. Este índice de la clave debe estar basada en una única columna de clave que no permite valores NULL.
  • Un catálogo de texto completo debe existir en la base de datos respectivas. Si un catálogo predeterminado no existe en la base de datos de la tabla, debe especificar un nombre de catálogo en el comando CREATE FULLTEXT INDEX.
Nota: Si estás utilizando el Asistente para indización de texto completo en SSMS, puede crear un nuevo catálogo de texto completo, como parte de la definición del índice de texto completo.
La Sintaxis es la siguiente:
image
El comando CREATE FULLTEXT INDEX incluye los siguientes argumentos:
  • Table_name: Especifica la tabla o vista indizada en el que se crea el índice de texto completo.
  • Column_name: Especifica las columnas que se incluirán en el índice de texto completo. Si no se especifica ninguna columna, el comando se completa con éxito para crear el índice de texto completo, pero no las columnas se incluyen en el índice.Antes de que usted puede rellenar o consultar el índice de texto completo, debe agregar columnas.
  • TYPE COLUMN type_column_name: Define la columna que contiene la extensión que representa el tipo de archivo de la información incluida en un varbinary,varbinary (max), o columna de imagen. Si se especifica esta opción, pero no incluye una columna con datos binarios en el índice de texto completo, se genera un error.
  • LANGUAGE language_term: Define la cadena, entero o un valor hexadecimal que representa el identificador de configuración regional. Si una lengua no se especifica, el idioma predeterminado es el de la instancia de SQL Server que se utiliza.
  • KEY INDEX index_name: Define el nombre del índice único requerido por el índice de texto completo para identificar cada fila de la tabla. Este valor es necesario.
  • fulltext_catalog_name: Define el catálogo lógico de texto completo (logical full-text catalog) al crearse el índice. Si un catálogo predeterminado no existe, debe especificar una dirección válida del nombre del catálogo en la base de datos de la tabla.
  • FILEGROUP filegroup_name: Define el nombre válido de un grupo de archivos existentes en los que se almacena el índice de texto completo. Si no se especifica un grupo de archivos, el índice de texto completo se almacena en el mismo grupo de archivos que la tabla o vista especificada. Si la tabla tiene particiones, el índice de texto completo se almacena en el grupo de archivos principal para la tabla de particiones.
  • CHANGE_TRACKING: Especifica cómo los cambios en la tabla se propagan a los índices de texto completo. La configuración de este argumento son los siguientes:
    • AUTO: Especifica que la propagación de cambios se realiza automáticamente. Los cambios aún no se puede reflejar de inmediato en el índice de texto completo. Esta es la configuración predeterminada.
    • MANUAL: Especifica que la declaración ALTER FULLTEXT INDEX. . .START UPDATE POPULATION debe ser ejecutado de forma manual o mediante el uso de trabajos del Agente SQL Server para propagar los cambios en el índice de texto completo.
    • OFF: Especifica que SQL Server no seguir los cambios en la tabla. Una completa regeneración de la población debe ser realizada por cualquier cambio para que se propaguen a los índice de texto completo. A menos que la opción NO POPULATION se especifique, una población inicial en el índice texto completo se producirá de forma automática después de que el índice de texto completo se crea.
    • OFF, NO POPULATION: Especifica que la población inicial del índice de texto completo no se producirá después de que el índice de texto completo se crea. La opción NO POPULATION es válida sólo con la opción de seguimiento de cambios en OFF.

      Nota: Las modificaciones realizadas mediante el uso de comandos WRITETEXT y UPDATETEXT no se reflejan en el índice de texto completo y no son rastreados a través del seguimiento de cambios.
  • STOPLIST: Especifica una lista de palabras irrelevantes que se asocian con el índice de texto completo. Si esta opción no se especifica, el texto predeterminado lista de palabras irrelevantes del sistema se utiliza. Las opciones de este argumento son los siguientes:
    • OFF: Especifica que la lista de palabras irrelevantes no se asocian con este índice de texto completo.
    • SYSTEM: Especifica que la lista de palabras irrelevantes predeterminada del sistema está asociado a este índice de texto completo.
    • Stoplist_name: Especifica el nombre válido de una lista de palabras irrelevantes existentes que se asocia con este índice de texto completo.
El código siguiente crea un índice texto completo en la columna Description de la tabla Production.ProductDescription. El índice de texto completo utiliza elAW2008FullTextCatalog y utiliza la lista de palabras irrelevantes del sistema:
CREATE FULLTEXT INDEX ON Production.ProductDescription (Description)
KEY INDEX PK_ProductDescription_ProductDescriptionID
ON AW2008FullTextCatalog
WITH STOPLIST = SYSTEM;

Escribir consultas de texto completo
Cuando usted escribe las consultas de texto, puede elegir entre los predicados CONTAINS y FREETEXT y las funciones CONTAINSTABLE y FREETEXTTABLE. Estos comandos le proporcionaran una variedad de términos de búsqueda que le permiten devolver los datos de distintas maneras. En esta sección, usted aprenderá las funciones de estos predicados y funciones y examinar muestras de cada tipo de consulta.
Solución de problemas Búsquedas de texto completo
Como solo es de aprender y practicar las consultas de texto, deberá entender las herramientas disponibles para ayudarle a solucionar y entender los resultados de la consulta le devuelve.
Cuando se ejecuta una consulta de texto completo antes que el llenado de texto se ha completado, la consulta puede devolver sólo una parte de las filas coincidentes.Puede utilizar la función FULLTEXTCATALOGPROPERTY para determinar el estado del llenado (o poblacion) del catálogo de texto completo. Si un llenado está en progreso, un valor de 1 se devuelve normalmente.
Tienes que ejecutar el siguiente comando para verificar el estado de la población del catálogo AdvWksDocFTCat:
SELECT FULLTEXTCATALOGPROPERTY('AdvWksDocFTCat', 'PopulateStatus');
Además, el objeto de gestión dinámica sys.dm_fts_index_population devuelve el estado actual de la población. La figura siguiente muestra la funciónsys.dm_fts_index_population en su forma más simple.
image
Para que los resultados sean más fáciles de leer, puede crear procedimientos almacenados que devuelvan una salida más fácil de usar. El código que se muestra a continuación  utiliza los alias y las funciones del sistema para mejorar la legibilidad de la vista de administración dinámica (DMV)sys.dm_fts_index_population . La figura posterior muestra los resultados de esta consulta:
SELECT DB_NAME(database_id) AS 'Database Name'
, database_id AS 'DB_ID' , OBJECT_NAME(table_id) AS 'Table Name'
, table_id , population_type_description AS 'Population Desc.'
, status_description AS 'Status Desc.'
, completion_type_description AS 'Completion Desc.'
, start_time
FROM sys.dm_fts_index_population;
image

Para ver las columnas que se incluyen en un índice de texto completo, puede utilizarla vista de catálogo sys.fulltext_index_columns, como se muestra en el siguiente comando.
SELECT OBJECT_NAME (object_id) AS TableName
, object_id , COL_NAME(object_id, column_id) AS ColumnName
, column_id , COL_NAME(object_id, type_column_id) AS TypeColumn
, language_id
FROM sys.fulltext_index_columns;
lo que nos da este resultado:
image

Si una consulta texto completo no devuelve el conjunto de resultados esperados, se puede utilizar el DMF sys.dm_fts_parser para ver el resultado de la tokenización final de la consulta. El resultado de la tokenización se basa en el término de entrada y las condiciones, tales como el separador de palabras, sinónimos y listas de palabras irrelevantes utilizados.
La sintaxis de la función sys.dm_fts_parser es el siguiente:
sys.dm_fts_parser('query_string', lcid, stoplist_id, accent_sensitivity)
Los siguientes argumentos se utilizan con esta función:
  • QUERY_STRING: Define la cadena para la que desea ver la salida del divisor de palabras. Esta cadena puede incluir cualquier opción válida del predicado CONTAINS, incluyendo INFLECTIONAL, THESAURUS y operadores booleanos.
  • lcid: Define el identificador de ubicación que define el separador de palabras para ser utilizado. Para ver los idiomas disponibles para una instancia de servidor SQL en particular mediante la ejecución de la siguiente consulta (más información AQUI):
    SELECT * FROM sys.syslanguages ORDER BY lcid
  • stoplist (lista de palabras irrelevantes): Define el número de identificación (ID) de la lista de palabras irrelevantes relacionados con la consulta.
    Un valor de 0 especifica que la lista de palabras irrelevantes proporcionada por el sistema se va a utilizar, y un valor de NULL especifica que ninguna lista de palabras irrelevantes se utilizará.
  • accent_sensitivity: Especifica la distinción de acentos que se utilizará. Establece este argumento en 1 para hacerlo sensible a los acentos y 0 para las consultas sin sensibilidad hacia los acentos. Cuando una lengua como el francés incluye un acento, esta opción determina si una palabra como ou coincide con où. Con la distinción de acento establecida en 1, estas dos palabras no se considera iguales.
Cuando los datos de búsqueda incluye caracteres especiales como el signo o una barra diagonal, puede que no reciba el conjunto de resultados que usted espera.Esos caracteres y algunos otros tienen un significado especial para el motor de texto completo. La figura siguiente muestra los resultados de las siguientes consultas en para los valores n/a:
SELECT * FROM sys.dm_fts_parser
('n/a', 1033, 0, 0);
image
SELECT * FROM sys.dm_fts_parser
('n/a', 1033, NULL, 0);
image
SELECT * FROM sys.dm_fts_parser
('"n/a"', 1033, 0, 0);
image

La primera consulta utiliza la lista del sistema. El analizador de vistas de la entrada (QUERY_STRING) como dos términos separados, n y a. Las letras individuales se marcan como palabras irrelevantes, y en una consulta real con sus datos, no hay filas se devuelve porque las palabras irrelevantes se ignoran en el conjunto de resultados.
La segunda consulta utiliza la opción NULL para decirle al intérprete hacer caso omiso de todas las listas de palabras irrelevantes. Debido a esto, una consulta real con los datos de uso de estas opciones sería devolver todas las filas con la letra por sí sola y todas las filas que incluyen la letra a sí mismo.
La consulta final agrega las comillas dobles en todo el término y demuestra que a pesar de que la entrada se ve ahora como n/a, el analizador aún que se resuelva en n y a. (Más información sobe el tema AQUI).
El último ejemplo muestra el uso de comillas para definir una frase en lugar de un solo término. La segunda consulta también muestra el uso de operadores lógicos dentro de la consulta.
Los resultados se muestran en la figura después:
SELECT * FROM sys.dm_fts_parser
('"Backyard Playground"', 1033,0,0)
SELECT * FROM sys.dm_fts_parser
('"Backyard Playground" OR "Swing Set" ', 1033,0,0)
image


CONTAINS and CONTAINSTABLE
El predicado CONTAINS permite escribir consultas para devolver coincidencias exactas de su entrada, así como difusa (menos preciso) coincidente con el valor de entrada. La función CONTAINSTABLE tiene las condiciones de búsqueda que el predicado CONTAINS, pero también le permite devolver un valor de relevancia(RANK) y la clave de texto completo (KEY) para cada fila del conjunto de resultados.Con estos comandos, usted puede buscar lo siguiente:
  • Coincidencias exactas de una palabra o frase.
  • Un sinónimo (diccionario de sinónimos) de una palabra o frase. Por ejemplo, puede crear un diccionario de sinónimos personalizado que devuelva "maestro","educador" y "profesor" cuando se consulta sobre el termino maestro.
  • Las formas del verbo conjugado (formas de inflexión) de una palabra. Por ejemplo,si se consulta acerca del término escribir, una consulta de inflexión también devuelve resultados como "escribió," "por escrito" y "escribe".
  • Una serie de caracteres que aparecen al principio de una palabra o al inicio de cualquier palabra en una frase. Por ejemplo, puede definir un término prefijo, como la frase "local school." Búsqueda de texto completo en un término devuelve "local schools" y "locally schooled.".
  • Una palabra que se encuentra cerca de otra palabra.
El predicado CONTAINS (Más información AQUI):
tiene dos argumentos, un argumento InludedColumns y un argumento SearchCondition.
primero la sintaxis
image
El argumento IncludedColumns puede contener cualquiera de los siguientes componentes:
  • Column_name: Ajústelo en el nombre de la columna si desea buscar en una sola columna.
  • Column_list Ajústelo en los nombres de las columnas que se incluirán en la búsqueda si desea buscar en varias columnas.
  • * Un asterisco (*) significa que todas las columnas habilitadas en texto completo en la tabla en la cláusula FROM se incluyen en la búsqueda. Si más de una tabla se enumeran en la cláusula FROM, se debe especificar el nombre de la tabla (por ejemplo, production.productreview .*).
  • Language: Ajústelo en el lenguaje de la consulta. Este valor puede ser definido como el nombre de una lengua en la tabla syslanguages ​​(entre comillas simples), a un entero basado en identificador de configuración regional (LCID) el número o el valor hexadecimal de un LCID.
El argumento SearchCondition puede contener los siguientes componentes:
  • Simple Term (Término simple): Ajústelo en una palabra (una cadena de caracteres sin puntuación o espacios) o frase (una o más palabras, por lo general con un espacio entre cada palabra). Frases deben estar dentro de comillas dobles ("").
  • Prefix Term (Término de prefijo): Ajústelo en una palabra o frase que desea buscar palabras o frases que comienzan con el término de prefijo de entrada. Todos los términos prefijo debe ser seguido de un asterisco, y el término completo, incluyendo el asterisco debe estar contenido dentro de comillas dobles. Si utiliza comillas simples, SQL Server interpreta la consulta como un simple término, no un término prefijo. Por un término prefijo, el asterisco se interpreta como 0 o más caracteres.Por ejemplo, si su contains_search_condition_ es "text*", las filas con los valores de "text" y "textbook” son devueltos.
  • Generation Term (Término de generación): Ajústelo en INFLECTIONAL o THESAURUS para definir una consulta que depende del idioma. El término de inflexión utiliza el analizador lingüístico de un idioma determinado para encontrar las formas de los sustantivos o verbos. El término utiliza el diccionario de sinónimos para el idioma correspondiente para que coincida con el mayor patrón o patrones de la palabra o frase siempre en contains_search_condition para el archivo de sinónimos. Si una coincidencia no se encuentra en el archivo de sinónimos, el término de generación se ignora, y una búsqueda de simple término se realiza.
  • Proximity Term (Término de proximidad):  Ajústelo en NEAR o una tilde (~) para especificar que el término para la izquierda del operador de proximidad deba estar cerca del término de la derecha. Cuando se vincula a más de dos términos con los operadores de proximidad, todos los términos deben estar cerca el uno al otro. La palabra NEAR y la tilde (~) funcionan de forma idéntica.
  • Término ponderado: Ajústelo en ISABOUT para identificar el uso de un término ponderado. Además, se utiliza la palabra clave WEIGHT y un número entre 0,0 y 1,0para especificar el peso relativo de cada componente en el término ponderado. Sólo tiene sentido cuando se utiliza con la función CONTAINSTABLE.
  • Operadores lógicos:  Ajústelos en AND (or &), AND NOT (or &!), o OR (or |). Las palabras clave y los símbolos se pueden utilizar indistintamente. AND señala que ambas condiciones deben cumplirse, AND NOT indica que la primera condición debe ser verdadera y la segunda condición debe ser falsa. OR indica que una o ambas de las condiciones que deben cumplirse.
Orden de las operaciones
Cuando varios operadores lógicos se incluyen grupos entre paréntesis se evalúan primero, seguido de NOT, AND , a continuación, OR. NOT  debe seguir una AND , como en AND NOT.
El orden de los múltiples usos de un mismo operador no es importante (por ejemplo, 1 OR 2 OR 3 se evalúa como 2 OR 3 OR 1), porque los operadores booleanos son asociativos.
La función CONTAINSTABLE (Más información AQUI):
Primero la sintaxis:
image
incluye los argumentos adicionales:
  • Tabla: Especifica el nombre de una tabla para la que está habilitada la búsqueda de texto.
  • Top_n_by_rank: Especifica que sólo el número de filas con la mayor puntuación debe ser devuelto. Usted puede mejorar el rendimiento de las consultas mediante el uso de esta opción para limitar los resultados sólo a las filas más relevantes.
Ejemplos de CONTAINS y CONTAINSTABLE
La siguiente consulta devuelve filas de la tabla ProductReview donde se encuentra la palabra quality cerca de la palabra comfort. Para mostrar el nombre del producto en lugar de la identificación del producto, el ejemplo incluye una combinación de la tabla Product. Debido a que el predicado CONTAINS puede consultar sólo un índices de texto completo, debe especificar de qué tabla que desea consultar las columnas de la cláusula WHERE, como se muestra en negrita en la siguiente consulta:
SELECT ProductReviewID, Production.Product.Name AS 'Product Name'
, Rating, Comments, Production.ProductReview.ModifiedDate
FROM Production.Product
JOIN Production.ProductReview
ON Production.Product.ProductID = Production.ProductReview.ProductID
WHERE CONTAINS(Production.ProductReview.*,'Quality NEAR comfort')
El resultado de esto:
image
Lo siguiente consulta las filas de la consulta en la tabla ProductReview que incluyen las formas de la palabra bike, tales como biking:
SELECT ProductReviewID, Production.Product.Name AS 'Product Name'
, Rating, Comments, Production.ProductReview.ModifiedDate
FROM Production.Product
JOIN Production.ProductReview
ON Production.Product.ProductID = Production.ProductReview.ProductID
WHERE CONTAINS(Production.ProductReview.*,'FORMSOF(INFLECTIONAL , bike)')
El resultado:
image

El siguiente código devuelve las filas de la tabla ProductDescription que incluyen las palabras safety, performance o comfort. Cada palabra se le da un valor de peso relativo, y los resultados están ordenados de la partida de más alto rango a la baja:
SELECT FT_Table.ProductDescriptionID, FT_Table.[Description]
, KEY_TBL.RANK
FROM Production.ProductDescription AS FT_Table
INNER JOIN CONTAINSTABLE(production.ProductDescription, Description,
'ISABOUT (comfort weight (.8)
, Safety weight (.5)
, Performance weight(.2) )' ) AS KEY_TBL
ON FT_Table.ProductDescriptionID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
El resultado seria:
image


FREETEXT y FREETEXTTABLE (Más información AQUI y AQUI)
El predicado FREETEXT le permite escribir consultas que devuelven valores que coinciden con el significado de la condición de búsqueda, y no simplemente las palabras exactas o sinónimos de la condición de búsqueda.
El texto completo motor de búsqueda realiza las siguientes tareas cuando se ejecuta una consulta de texto completo mediante el uso del predicado FREETEXT:
  • Utiliza el separador de palabras para dividir el valor de la cadena introducida por la condición de búsqueda con palabras.
  • Utiliza derivados para crear formas de inflexión de las palabras creadas por el separador de palabras.
  • Utiliza el diccionario de sinónimos para encontrar ampliaciones adicionales oreemplazos de los términos de búsqueda.
El comando FREETEXT tiene los siguientes argumentos:
  • Column (Columna): Un nombre de columna individual, una lista de columnas separadas por comas y encerrados entre paréntesis, o un asterisco (*) para designar a todas las columnas de texto completo.
  • Free-text string (cadenas de texto Libres ): Utiliza las entradas de la cadena para buscar en las columnas listadas. Si se escribe la cadena entre comillas dobles, una frase de búsqueda se lleva a cabo. Con una frase de búsqueda, derivados y operaciones de búsqueda de sinónimos no se realizan.
  • Language: El lenguaje que se utiliza para la separación de palabras, derivadas, listas de palabras irrelevantes, y las búsquedas de diccionario de sinónimos.
El comando FREETEXTTABLE incluye una combinación de argumentos de los comandos FREETEXT y CONTAINSTABLE. El comando FREETEXTTABLE incluye la tabla, columna, cadena de texto libre, el lenguaje y los argumentos de los Top-n-by-rank definidos previamente.


Ejemplos de FREETEXT y FREETEXTTABLE
El comando siguiente utiliza el predicado FREETEXT para devolver filas con las descripciones que coinciden con el significado general de la frase "provides a light stiff ride." El conjunto de resultados de este comando devuelve 34 de las 762 filas de la tabla ProductDescription cuando un índice de texto completo se basa en la columna de descripción:
SELECT * FROM Production.ProductDescription
WHERE FREETEXT(*,'provides a light stiff ride')
el resultado:
image
Por el contrario, si se ejecuta el siguiente comando, sólo se devuelve una fila:
SELECT * FROM Production.ProductDescription
WHERE CONTAINS(*,'"provides a light stiff ride"')
El resultado:
image

El siguiente código utiliza el comando FREETEXTTABLE para que devuelva filas con un significado cercano a la entrada de texto. El motor de búsqueda texto completo asigna un rango en función de lo cerca que esta de la frase de entrada. Los resultados se devuelven a las filas de más alto rango aparece en primer lugar:
SELECT FT_TBL.ProductDescriptionID, FT_TBL.Description, KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL
INNER JOIN FREETEXTTABLE (Production.ProductDescription, description,
'light stiff ride') AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
los resultados:
image
Resumen de la lección
  • SQL Server 2008 ofrece totalmente integrada texto completo en las capacidades de búsqueda.
  • Los índices de texto se crean y se mantienen dentro de la base de datos y se organizan en virtuales de catálogos de texto.
  • Los predicados CONTAINS y FREETEXT, así como las funciones CONTAINSTABLE y FREETEXTTABLE, permiten texto en las consultas, XML, y ciertos tipos de datos binarios.

No hay comentarios:

Publicar un comentario