Curso de SQL

El principal requisito de dise�o de los �ndices, consultas y sincronizaci�n de texto es la presencia de una columna de clave exclusiva de texto (o clave principal de columna �nica) en todas las tablas que se registren para realizar b�squedas de texto. Un �ndice de texto realiza el seguimiento de las palabras significativas que m�s se usan y d�nde se encuentran.

Por ejemplo, imagine un �ndice de texto para una tabla llamada DevTools. Un �ndice de texto puede indicar que la palabra "Microsoft" se encuentra en la palabra n�mero 423 y en la palabra 982 de la columna Abstract para la fila asociada con el ProductID igual a 6. Esta estructura de �ndices admite una b�squeda eficiente de todos los elementos que contengan palabras indizadas y operaciones de b�squeda avanzadas, como b�squedas de frases y b�squedas de proximidad.

Para impedir que los �ndices de texto se inunden con palabras que no ayudan en la b�squeda, las palabras innecesarias (vac�as de significado), como "un", "y", "es" o "el", se pasan por alto. Por ejemplo, especificar la frase "los productos pedidos durante estos meses de verano" es lo mismo que especificar la frase "productos pedidos durante meses verano". Se devuelven las filas que contengan alguna de las cadenas.

En el directorio \Mssql7\Ftdata\Sqlserver\Config se proporcionan listas de palabras que no son relevantes en las b�squedas de muchos idiomas. Este directorio se crea, y los archivos de palabras no relevantes se instalan, cuando se instala SQL Server con la funcionalidad de b�squeda de texto. Los archivos de palabras no relevantes se pueden modificar. Por ejemplo, los administradores del sistema de las empresas de alta tecnolog�a podr�an agregar la palabra "sistema" a su lista de palabras no relevantes. (Si modifica un archivo de palabras no relevantes, debe volver a rellenar los cat�logos de texto para que los cambios surtan efecto). Consulte la ayuda de SQL-SERVER para conocer los correspondientes ficheros.

Cuando se procesa una consulta de texto, el motor de b�squeda devuelve a SQL Server los valores de clave de las filas que coinciden con los criterios de b�squeda. Imagine una tabla CienciaFicci�n en la que la columna N�mLibro es la columna de clave principal:

N�mLibro	Escritor	T�tulo
A025	Asimov	Los l�mites de la fundaci�n
A027	Asimov	Fundaci�n e imperio
C011	Clarke	El fin de la infancia
V109	Verne	La isla misteriosa

Suponga que desea usar una consulta de recuperaci�n de texto para buscar los t�tulos de los libros que incluyen la palabra Fundaci�n. En este caso, del �ndice de texto se obtienen los valores A025 y A027. SQL Server usa, a continuaci�n, estas claves y el resto de la informaci�n de los campos para responder a la consulta.

�Componentes de las consultas de texto de Transact-SQL

SQL Server proporciona estos componentes de Transact-SQL para las consultas de texto:

Predicados de Transact-SQL:

CONTAINS
FREETEXT

Los predicados CONTAINS y FREETEXT se pueden usar en cualquier condici�n de b�squeda (incluida una cl�usula WHERE) de una instrucci�n SELECT.

Funciones de conjuntos de filas de Transact-SQL:

CONTAINSTABLE
FREETEXTTABLE

Las funciones CONTAINSTABLE y FREETEXTTABLE se pueden usar en la cl�usula FROM de una instrucci�n SELECT.

Propiedades de texto de Transact-SQL:

�stas son algunas de las propiedades que se usan con las consultas de texto y las funciones que se utilizan para obtenerlas:

La propiedad IsFullTextEnabled indica si una base de datos est� habilitada para texto y se encuentra disponible mediante la funci�n DatabaseProperty.
La propiedad TableHasActiveFulltextIndex indica si una tabla est� habilitada para texto y se encuentra disponible mediante la funci�n ObjectProperty.
La propiedad IsFullTextIndexed indica si una columna est� habilitada para texto y se encuentra disponible mediante la funci�n ColumnProperty.
La propiedad TableFullTextKeyColumn proporciona el identificador de la columna de clave exclusiva de texto y se encuentra disponible mediante la funci�n ObjectProperty.

Procedimientos de texto almacenados del sistema de Transact-SQL:

Los procedimientos almacenados que definen los �ndices de texto e inician el relleno de los �ndices de texto, como, por ejemplo, sp_fulltext_catalog, sp_fulltext_table y sp_fulltext_column.
Los procedimientos almacenados que consultan los metadatos de los �ndices de texto que se han definido mediante los procedimientos almacenados del sistema mencionados anteriormente, como, por ejemplo, sp_help_fulltext_catalogs, sp_help_fulltext_tables, sp_help_fulltext_columns, y una variaci�n de �stos que permite utilizar cursores sobre los conjuntos de resultados devueltos.

Estos procedimientos almacenados se pueden usar en conjunci�n con la escritura de una consulta. Por ejemplo, puede usarlos para buscar los nombres de las columnas indizadas de texto de una tabla y el identificador de una columna de clave �nica de texto antes de especificar una consulta.

�Funciones de conjunto de filas CONTAINSTABLE y FREETEXTTABLE

Las funciones CONTAINSTABLE y FREETEXTTABLE se usan para especificar las consultas de texto que devuelve la clasificaci�n por porcentaje de aciertos de cada fila. Estas funciones son muy similares a los predicados de texto CONTAINS y FREETEXT, pero se utilizan de forma diferente.

Aunque tanto los predicados de texto como las funciones de conjunto de filas de texto se usan para las consultas de texto y la instrucci�n TRANSACT-SQL usada para especificar la condici�n de b�squeda de texto es la misma en los predicados y en las funciones, hay importantes diferencias en la forma en la que �stas se usan:

CONTAINS y FREETEXT devuelven ambos el valor TRUE o FALSE, con lo que normalmente se especifican en la cl�usula WHERE de una instrucci�n SELECT. S�lo se pueden usar para especificar los criterios de selecci�n, que usa SQL SERVER para determinar la pertenencia al conjunto de resultados.

CONTAINSTABLE y FREETEXTTABLE devuelven ambas una tabla de cero, una o m�s filas, con lo que deben especificarse siempre en la cl�usula FROM. Se usan tambi�n para especificar los criterios de selecci�n. La tabla devuelta tiene una columna llamada KEY que contiene valores de claves de texto. Cada tabla de texto registrada tiene una columna cuyos valores se garantizan como �nicos. Los valores devueltos en la columna KEY de CONTAINSTABLE o FREETEXTTABLE son los valores �nicos, procedentes de la tabla de texto registrada, de las filas que coinciden con los criterios de selecci�n en la condici�n de b�squeda de texto.

Adem�s, la tabla que producen CONTAINSTABLE y FREETEXTTABLE tiene una columna denominada RANK, que contiene valores de 0 a 1000. Estos valores se utilizan para ordenar las filas devueltas de acuerdo al nivel de coincidencia con los criterios de selecci�n.

Las consultas que usan las funciones CONTAINSTABLE y FREETEXTTABLE son m�s complejas que las que usan los predicados CONTAINS y FREETEXT porque las filas que cumplen los criterios y que son devueltas por las funciones deben ser combinadas expl�citamente con las filas de la tabla original de SQL SERVER.

�CONTAINSTABLE (T-SQL)

Devuelve una tabla con cero, una o m�s filas para aquellas columnas de tipos de datos car�cter que contengan palabras o frases en forma precisa o "aproximada" (menos precisa), la proximidad de palabras medida como distancia entre ellas, o coincidencias medidas. A CONTAINSTABLE se le puede hacer referencia en una cl�usula FROM de una instrucci�n SELECT como si fuera un nombre de tabla normal.

Las consultas que utilizan CONTAINSTABLE especifican consultas de texto contenido que devuelven un valor de distancia (RANK) por cada fila. La funci�n CONTAINSTABLE utiliza las mismas condiciones de b�squeda que el predicado CONTAINS.

Sintaxis

CONTAINSTABLE (tabla, {columna | *}, '<condici�nB�squedaContenido>')
<condici�nB�squeda> ::=
{
| <t�rminoGeneraci�n>
| <t�rminoPrefijo>
| <t�rminoProximidad>
| <t�rminoSimple>
| <t�rminoPeso>
}
| { (<condici�nB�squeda>)
{AND | AND NOT | OR} <condici�nB�squeda> [...n]
}
<t�rminoPeso> ::=
ISABOUT
( { {
<t�rminoGeneraci�n>)
| <t�rminoPrefijo>)
| <t�rminoProximidad>)
| <t�rminoSimple>)
}
[WEIGHT (valorPeso)]
} [,...n]
)
<t�rminoGeneraci�n> ::=
FORMSOF (INFLECTIONAL, <t�rminoSimple> [,...n] )
<t�rminoPrefijo> ::=
{ "palabra * " | "frase * " }
<t�rminoProximidad> ::=
{<t�rminoSimple> | <t�rminoPrefijo>}
{ {NEAR | ~} {<t�rminoSimple> | <t�rminoPrefijo>} } [_n]
<t�rminoSimple> ::=
palabra | " frase "

Argumentos

tabla

Es el nombre de la tabla que ha sido registrada para b�squedas de texto. tabla puede ser el nombre de un objeto de una base de datos de una sola parte o el nombre de un objeto de una base de datos con varias partes. Para obtener m�s informaci�n, consulte Convenciones de sintaxis de Transact-SQL.

columna

Es el nombre de la columna que se va a examinar, que reside en tabla. Las columnas de tipos de datos de cadena de caracteres son columnas v�lidas para b�squedas de texto.

*

Especifica que todas las columnas de la tabla que se hayan registrado para b�squedas de texto se deben utilizar en las condiciones de b�squeda.

<condici�nB�squeda>

Especifica el texto que se va a buscar en columna. En la condici�n de b�squeda no se puede utilizar variables.

palabra

Es una cadena de caracteres sin espacios ni signos de puntuaci�n.

frase

Es una o varias palabras con espacios entre cada una de ellas.

Nota: Algunos idiomas, como los orientales, pueden tener frases que contengan una o varias palabras sin espacios entre ellas.

<t�rminoPeso>

Especifica que las filas coincidentes (devueltas por la consulta) coincidan con una lista de palabras y frases a las que se asigna opcionalmente un valor de peso.

ISABOUT

Especifica la palabra clave <t�rminoPeso>.

WEIGHT (valorPeso)

Especifica el valor de peso como n�mero entre 0,0 y 1,0. Cada componente de <t�rminoPeso> puede incluir un valorPeso. valorPeso es una forma de modificar c�mo varias partes de una consulta afectan al valor de distancia asignado a cada fila de la consulta. El peso hace una medida diferente de la distancia de un valor porque todos los componentes de <t�rminoPeso> se utilizan para determinar la coincidencia. Se devuelven las filas que contengan una coincidencia con cualquiera de los par�metros ISABOUT, aunque no tengan un peso asignado.

AND | AND NOT | OR

Especifica una operaci�n l�gica entre dos condiciones de b�squeda. Cuando <condici�nB�squeda> contiene grupos entre par�ntesis, dichos grupos entre par�ntesis se eval�an primero. Despu�s de evaluar los grupos entre par�ntesis, se aplican las reglas siguientes cuando se utilizan estos operadores l�gicos con condiciones de b�squeda:

NOT se aplica antes que AND.
NOT s�lo puede estar a continuaci�n de AND, como en AND NOT. No se acepta el operador OR NOT. No se puede especificar NOT antes del primer t�rmino (por ejemplo, CONTAINS(mycolumn, 'NOT "fraseBuscada" ' )).
AND se aplica antes que OR.
Los operadores booleanos del mismo tipo (AND, OR) son asociativos y, por tanto, se pueden aplicar en cualquier orden.

<t�rminoGeneraci�n>

Especifica la coincidencia de palabras cuando los t�rminos simples incluyen variaciones de la palabra original que se busca.

INFLECTIONAL

Especifica que se acepten las coincidencias de las formas plurales y singulares de los nombres y los distintos tiempos verbales. Un <t�rminoSimple> dado dentro de un <t�rminoGeneraci�n> no coincide con nombres y verbos a la vez.

<t�rminoPrefijo>

Especifica la coincidencia de palabras o frases que comiencen con el texto especificado. Enmarque el prefijo entre comillas dobles ("") y un asterisco (*) antes de la segunda comilla doble. Coincide todo el texto que comience por el t�rmino simple especificado antes del asterisco. El asterisco representa cero, uno o varios caracteres (de la palabra o palabras ra�z de la palabra o la frase). Cuando <t�rminoPrefijo> es una frase, todas las palabras de dicha frase se consideran prefijos. Por tanto, una consulta que especifique el prefijo "local wine *" hace que se devuelvan todas las filas que contengan el texto "local winery", "locally wined and dined", etc.

<t�rminoProximidad>

Especifica la coincidencia de palabras o frases que est�n cercanas entre ellas. <t�rminoProximidad> opera de forma similar al operador AND: ambos requieren que existan varias palabras o frases en la columna examinada. Cuanto m�s pr�ximas est�n las palabras de <t�rminoProximidad>, mejor ser� la coincidencia.

NEAR | ~

Indica que la palabra o frase del lado izquierdo del operador NEAR o ~ tiene que estar bastante cerca de la palabra o frase del lado derecho del operador NEAR o ~. Se pueden encadenar varios t�rminos de proximidad, por ejemplo:

a NEAR b NEAR c

Esto significa que la palabra o frase a tiene que estar cerca de la palabra o frase b, que, a su vez, tiene que estar cerca de la palabra o frase c.

SQL Server mide la distancia entre la palabra o frase izquierda y derecha. Un valor de distancia bajo (por ejemplo, 0) indica una distancia grande entre las dos. Si las palabras o frases especificadas est�n lejos unas de las otras, satisfacen la condici�n de la consulta; sin embargo, la consulta tiene un valor de distancia muy bajo (0). Sin embargo, si <condici�nB�squeda> s�lo consta de uno o varios t�rminos de proximidad NEAR, SQL Server no devuelve filas con un valor de distancia de 0.

<t�rminoSimple>

Especifica la coincidencia con una palabra exacta (uno o varios caracteres sin espacios o signos de puntuaci�n en idiomas con caracteres de un solo byte) o una frase (una o varias palabras consecutivas separadas por espacios y signos de puntuaci�n opcionales en idiomas con caracteres de un solo byte). Ejemplos de t�rminos simples v�lidos son "blue berry", blueberry y "Microsoft SQL Server". Las frases tienen que ir entre comillas dobles (""). Las palabras de una frase tienen que aparecer en la columna de la base de datos en el mismo orden que el especificado en <condici�nB�squeda>. La b�squeda de caracteres en la palabra o la frase distingue entre may�sculas y min�sculas. Las palabras de una sola s�laba (como "un" o "la") de las columnas de texto indizadas no se almacenan en los �ndices de los textos. Si �nicamente se utiliza una de estas palabras en una b�squeda, SQL Server devuelve un mensaje de error indicando que en la consulta s�lo hay monos�labos. SQL Server incluye una lista est�ndar de palabras monos�labas en el directorio \Mssql7\Ftdata\Sqlserver\Config.

Los signos de puntuaci�n se omiten. Por lo tanto, el valor "�D�nde est� mi equipo? El fallo de la b�squeda ser�a grave." satisface la condici�n CONTAINS(testing, "fallo del equipo").

n

Es un marcador de posici�n que indica que se pueden especificar varias condiciones y t�rminos de b�squeda.

Observaciones

CONTAINS no se reconoce como palabra clave si el nivel de compatibilidad es menor de 70.

La tabla devuelta por la funcion CONTAINSTABLE tiene una columna llamada KEY que contiene valores de claves de texto. Todas las tablas con textos indizados tienen una columna cuyos valores se garantizan que son �nicos y los valores devueltos en la columna KEY son los valores de claves de textos de las filas que satisfacen los criterios de selecci�n especificados en la condici�n de b�squeda. La propiedad TableFulltextKeyColumn, obtenida mediante la funci�n OBJECTPROPERTY, proporciona la identidad de esta columna de clave �nica. Para obtener las filas de la tabla original que desee, especifique una combinaci�n con las filas de CONTAINSTABLE. La forma t�pica de la cl�usula FROM de una instrucci�n SELECT que utilice CONTAINSTABLE es:

SELECT select_list
FROM table AS FT_TBL INNER JOIN
CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
ON FT_TBL.unique_key_column = KEY_TBL.[KEY]

La tabla que produce CONTAINSTABLE incluye una columna llamada RANK. La columna RANK es un valor (entre 0 y 1000) que para cada fila indica lo bien que cada una de ellas satisface los criterios de selecci�n. Este valor de distancia se suele utilizar en las instrucciones SELECT de una de estas maneras:

En la cl�usula ORDER BY, para devolver las filas de mayor valor al principio.
En la lista de selecci�n, para ver el valor de distancia asignado a cada fila.
En la cl�usula WHERE, para filtrar las filas con valores de distancia bajos.

CONTAINSTABLE no se reconoce como palabra clave si el nivel de compatibilidad es menor de 70. Para obtener m�s informaci�n, consulte sp_dbcmptlevel.

Ejemplos

A. Devolver valores de distancia mediante CONTAINSTABLE
Este ejemplo busca todos los nombres de productos que contengan las palabras "breads", "fish" o "beers", y los distintos pesos asignados a cada palabra. Por cada fila devuelta que cumpla los criterios de la b�squeda, se muestra la precisi�n relativa (valor de distancia) de la coincidencia. Adem�s, las filas de mayor valor de distancia se devuelven primero.

USE Northwind
GO
SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE(Categories, Description,
'ISABOUT (breads weight (.8),
fish weight (.4), beers weight (.2) )' ) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

B. Devolver valores de distancia mayores que uno especificado mediante CONTAINSTABLE
Este ejemplo devuelve la descripci�n y el nombre de la categor�a de todas las categor�as de alimentos en las que la columna Description contenga las palabras "sweet" y "savory" cerca de la palabra "sauces" o de la palabra "candies". Todas las filas cuya categor�a sea "Seafood" no se devuelven. S�lo se devuelven las filas cuyo grado de coincidencia sea igual o superior a 2.

USE Northwind
GO
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
AND FT_TBL.CategoryName <> 'Seafood'
ORDER BY KEY_TBL.RANK DESC

C. Utilizar CONTAINS con <t�rminoSimple>
Este ejemplo busca todos los productos cuyo precio sea $15,00 que contengan la palabra "bottles".

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
AND CONTAINS(QuantityPerUnit, 'bottles')
GO

D. Utilizar CONTAINS y una frase en <t�rminoSimple>
Este ejemplo devuelve todos los productos que contengan la frase "sasquatch ale" o "steeleye stout".

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "Sasquatch ale" OR "steeleye stout" ')
GO

E. Utilizar CONTAINS con <t�rminoPrefijo>
Este ejemplo devuelve todos los nombres de productos que tengan al menos una palabra que empiece por el prefijo "choc" en la columna ProductName.

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "choc*" ')
GO

F. Utilizar CONTAINS y OR con <t�rminoPrefijo>
Este ejemplo devuelve todas las descripciones de categor�as que contengan las cadenas "sea" o "bread".

USE Northwind
SELECT CategoryName
FROM Categories
WHERE CONTAINS(Description, '"sea*" OR "bread*"')
GO

G. Utilizar CONTAINS con <t�rminoProximidad>
Este ejemplo devuelve todos los nombres de los productos que tengan la palabra "Boysenberry" cerca de la palabra "spread".

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, 'spread NEAR Boysenberry')
GO

H. Utilizar CONTAINS con <t�rminoGeneraci�n>
Este ejemplo busca todos los productos que tengan palabras derivadas de "dry": "dried", "drying", etc.

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ')
GO

I. Utilizar CONTAINS con <t�rminoPeso>
Este ejemplo busca todos los nombres de productos que contengan las palabras "spread", "sauces" o "relishes", y los distintos pesos asignados a cada palabra.

USE Northwind
GO
SELECT CategoryName, Description
FROM Categories
WHERE CONTAINS(Description, 'ISABOUT (spread weight (.8),
sauces weight (.4), relishes weight (.2) )' )
GO

�FREETEXTTABLE

Devuelve una tabla de cero, una o varias filas cuyas columnas contienen datos de tipo car�cter cuyos valores coinciden con el significado, no literalmente, con el texto especificado en cadenaTexto. Se puede hacer referencia a FREETEXTTABLE en las cl�usula FROM de las instrucciones SELECT como a otro nombre de tabla normal.

Las consultas que utilizan FREETEXTTABLE especifican consultas de texto que devuelven el valor de coincidencia (RANK) de cada fila.

Sintaxis

FREETEXTTABLE (tabla, {columna | *}, 'cadenaTexto')

Argumentos

tabla: Es el nombre de la tabla que se ha marcado para b�squedas de texto. tabla puede ser el nombre de un objeto de una base de datos de una sola parte o el nombre de un objeto de una base de datos con varias partes.
columna: Es el nombre de la columna de tabla en la que se va a buscar. Las columnas cuyos datos sean del tipo de cadena de caracteres son columnas v�lidas para buscar texto.
*: Especifica que todas las columnas que hayan sido registradas para la b�squeda de texto se tienen que utilizar para buscar la cadenaTexto dada.
cadenaTexto: Es el texto que se va a buscar en la columna especificada. No se pueden utilizar variables.

Observaciones

FREETEXTTABLE utiliza las mismas condiciones de b�squeda que el predicado FREETEXT. Al igual que en CONTAINSTABLE, la tabla devuelta tiene columnas llamadas KEY y RANK, a las que se hace referencia en la consulta para obtener las filas apropiadas y utilizar los valores de distancia. FREETEXTTABLE no se reconoce como palabra clave si el nivel de compatibilidad es menor que 70.

Ejemplos

En este ejemplo se devuelve el nombre y la descripci�n de todas las categor�as relacionadas con _sweet_, _candy_, _bread_, _dry_ y _meat_.

USE Northwind
SELECT FT_TBL.CategoryName,
FT_TBL.Description,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
FREETEXTTABLE(Categories, Description,
'sweetest candy bread and dry meat') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
GO

�Utilizar el predicado CONTAINS

Puede usar el predicado CONTAINS para buscar una determinada frase en una base de datos. Por supuesto, dicha consulta puede escribirse con el predicado LIKE. Sin embargo, algunas formas de CONTAINS proporcionan mayor variedad de consultas de texto que la que se puede obtener con LIKE. Adem�s, al contrario que cuando se utiliza el predicado LIKE, una b�squeda con CONTAINS no distingue entre may�sculas y min�sculas.

Nota.. Las consultas de b�squeda de texto se comportan de forma que no distinguen entre may�sculas y min�sculas en aquellos idiomas (mayoritariamente los latinos) en los que tiene sentido distinguir entre may�sculas y min�sculas. Sin embargo, en japon�s, hay muchas ortograf�as fon�ticas en las que el concepto de normalizaci�n ortogr�fica implica no distinguir las may�sculas de las min�sculas (por ejemplo, las letras kana no tienen may�sculas y min�sculas). Este tipo de normalizaci�n ortogr�fica no se admite.

Suponga que desea buscar en la base de datos Northwind la frase "bean curd". Si usa el predicado CONTAINS, �sta es una consulta bastante f�cil.

USE Northwind USE Northwind
GO
SELECT Description
FROM Categories
WHERE Description LIKE '%bean curd%'
GO

O, con CONTAINS:

USE Northwind
GO
SELECT Description
FROM Categories
WHERE CONTAINS(Description, ' "bean curd" ')
GO

El predicado CONTAINS usa una notaci�n funcional en la que el primer par�metro es el nombre de la columna que se est� buscando y el segundo par�metro es una condici�n de b�squeda de texto. La condici�n de b�squeda, en este caso "bean curd", puede ser bastante compleja y est� formada por uno o m�s elementos, que se describen posteriormente.

El predicado CONTAINS admite una sintaxis compleja para buscar en las columnas basadas en caracteres:

Una o m�s palabras y frases espec�ficas (t�rminos simples). Una palabra est� compuesta por uno o m�s caracteres sin espacios ni signos de puntuaci�n. Una frase v�lida consta de varias palabras con espacios y con o sin signos de puntuaci�n entre ellas. Por ejemplo, croissant es una palabra y caf� au lait es una frase. Las palabras y frases como �stas se llaman t�rminos simples.
Forma no flexionada de una palabra determinada (t�rmino de generaci�n). Por ejemplo, buscar la forma no flexionada de la palabra "conducir". Si hay varias filas en la tabla que incluyen las palabras "conducir", "conduce", "condujo", "conduciendo" y "conducido", todas estar�an en el conjunto de resultados porque cada una de estas palabras se puede generar de forma inflexiva a partir de la palabra "conducir".
Una palabra o frase en la que las palabras empiezan con un texto determinado (t�rmino prefijo). En el caso de una frase, cada palabra de la frase se considera un prefijo. Por ejemplo, el t�rmino "tran* auto" coincide con "transmisi�n autom�tica" y "transductor de autom�vil".
Palabras o frases que usan valores ponderados (t�rmino ponderado). Por ejemplo, podr�a desear encontrar una palabra que tuviera un peso designado superior a otra palabra. Devuelve resultados de consulta clasificados.
Una palabra o frase que est� cerca de otra palabra o frase (t�rmino de proximidad). Por ejemplo, podr�a desear encontrar las filas en las que la palabra "hielo" aparece cerca de la palabra "hockey" o en las que la frase "patinaje sobre hielo" se encuentra pr�xima a la frase "hockey sobre hielo".

Un predicado CONTAINS puede combinar varios de estos t�rminos si usa AND y OR, por ejemplo, podr�a buscar todas las filas con "leche" y "caf� al estilo de Toledo" en la misma columna de base datos habilitada para texto . Adem�s, los t�rminos se pueden negar con el uso de AND NOT, por ejemplo, "pastel AND NOT queso de untar".

Cuando use CONTAINS, recuerde que SQL Server rechaza las palabras vac�as de los criterios de b�squeda. Las palabras irrelevantes son aquellas como "un", "y", "es" o "el", que aparecen con frecuencia pero que, en realidad, no ayudan en la b�squeda de un texto determinado.

�Utilizar el predicado FREETEXT

Con un predicado FREETEXT, puede escribir cualquier conjunto de palabras o frases, e incluso una frase completa. El motor de consultas de texto examina este texto, identifica todas las palabras y frases de nombres significativas y construye internamente una consulta con esos t�rminos. En este ejemplo se usa un predicado FREETEXT en una columna llamada description.

FREETEXT (description, ' "The Fulton County Grand Jury said Friday 
an investigation of Atlanta's recent primary election produced no evidence that any 
irregularities took place." ')

El motor de b�squeda identifica palabras y frases nominales tales como las siguientes:

Palabras:: Fulton, county, grand, jury, Friday, investigation, Atlanta, recent, primary, election, produce, evidence, irregularities
Frases:: Fulton county grand jury, primary election, grand jury, Atlanta's recent primary election

Las palabras y frases de la cadena FREETEXT (y sus variaciones generadas de forma inflexiva) se combinan internamente en una consulta, ponderada para clasificarla adecuadamente y, a continuaci�n, se realiza la b�squeda real.

�Funciones de conjunto de filas CONTAINSTABLE y FREETEXTTABLE

Las funciones CONTAINSTABLE y FREETEXTTABLE se usan para especificar las consultas de texto que devuelve la clasificaci�n por porcentaje de aciertos de cada fila. Estas funciones son muy similares a los predicados de texto CONTAINS y FREETEXT, pero se utilizan de forma diferente.

�Los predicados de texto de las funciones

CONTAINS y FREETEXT devuelven ambos el valor TRUE o FALSE, con lo que normalmente se especifican en la cl�usula WHERE de una instrucci�n SELECT.
CONTAINSTABLE y FREETEXTTABLE devuelven ambas una tabla de cero, una o m�s filas, con lo que deben especificarse siempre en la cl�usula FROM.
CONTAINS y FREETEXT s�lo se pueden usar para especificar los criterios de selecci�n, que usa SQL SERVER para determinar la pertenencia al conjunto de resultados.
CONTAINSTABLE y FREETEXTTABLE se usan tambi�n para especificar los criterios de selecci�n. La tabla devuelta tiene una columna llamada KEY que contiene valores de claves de texto. Cada tabla de texto registrada tiene una columna cuyos valores se garantizan como �nicos. Los valores devueltos en la columna KEY de CONTAINSTABLE o FREETEXTTABLE son los valores �nicos, procedentes de la tabla de texto registrada, de las filas que coinciden con los criterios de selecci�n en la condici�n de b�squeda de texto.
Adem�s, la tabla que producen CONTAINSTABLE y FREETEXTTABLE tiene una columna denominada RANK, que contiene valores de 0 a 1000. Estos valores se utilizan para ordenar las filas devueltas de acuerdo al nivel de coincidencia con los criterios de selecci�n.

Este ejemplo devuelve la descripci�n y el nombre de categor�a de todas las categor�as de alimentos en las que la columna Description contenga las palabras "sweet and savory" cerca de la palabra "sauces" o de la palabra "candies". Todas las filas cuyo nombre de categor�a sea "Seafood" no se devuelven. S�lo se devuelven las filas cuyo valor de distancia sea igual o superior a 2.

USE Northwind
GO
SELECT FT_TBL.Description, FT_TBL.CategoryName, KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2 AND FT_TBL.CategoryName <> 'Seafood'
ORDER BY KEY_TBL.RANK DESC

Este ejemplo devuelve la descripci�n y el nombre de categor�a de las 10 categor�as superiores de alimentos donde la columna Description contenga las palabras "sweet and savory" cerca de la palabra "sauces" o de la palabra "candies".

SELECT FT_TBL.Description, FT_TBL.CategoryName, KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)', 10) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]

�Comparaci�n entre CONTAINSTABLE y CONTAINS

La funci�n CONTAINSTABLE y el predicado CONTAINS utilizan condiciones de b�squeda similares.

Sin embargo, en CONTAINSTABLE se especifica la tabla en la que tendr� lugar la b�squeda de texto, la columna (o todas las columnas) de la tabla en las que se buscar� y la condici�n de b�squeda. Un cuarto par�metro, opcional, hace posible que el usuario indique que se devuelva s�lo el n�mero m�s alto especificado de coincidencias. Para obtener m�s informaci�n, consulte la secci�n Limitar los conjuntos de resultados.

CONTAINSTABLE devuelve una tabla que incluye una columna denominada RANK. Esta columna RANK contiene un valor para cada fila que indica el grado de coincidencia de cada fila con los criterios de selecci�n.

En esta consulta se especifica la utilizaci�n de CONTAINSTABLE para devolver un valor de clasificaci�n por cada fila.

USE Northwind
GO
SELECT K.RANK, CompanyName, ContactName, Address
FROM Customers AS C
INNER JOIN
CONTAINSTABLE(Customers,Address,
'ISABOUT ("des*", Rue WEIGHT(0.5), Bouchers WEIGHT(0.9))') AS K
ON C.CustomerID = K.[KEY]

�Comparaci�n entre FREETEXTTABLE y FREETEXT

En la consulta siguiente se ampl�a una consulta FREETEXTTABLE para que devuelva primero las filas con clasificaci�n superior y agregue la clasificaci�n de cada fila a la lista de selecci�n. Para especificar la consulta, debe saber que CategoryID es la columna de clave �nica de la tabla Categories.

USE Northwind
GO
SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Categories AS FT_TBL
INNER JOIN
FREETEXTTABLE(Categories, Description,
'How can I make my own beers and ales?') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

La �nica diferencia en la sintaxis de FREETEXTTABLE y FREETEXT es la inserci�n del nombre de la tabla como el primer par�metro.

Esto es una ampliaci�n de la misma consulta que s�lo devuelve las filas con un valor de clasificaci�n de 10 o superior:

USE Northwind
GO
SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Categories FT_TBL
INNER JOIN
FREETEXTTABLE (Categories, Description,
'How can I make my own beers and ales?') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK >= 10
ORDER BY KEY_TBL.RANK DESC
GO

�Identificaci�n del nombre de la columna de la clave �nica

Las consultas que usan funciones que toman valores de conjuntos de filas son complicadas porque es necesario saber el nombre de la columna de clave exclusiva. Cada tabla habilitada para texto tiene la propiedad TableFulltextKeyColumn que contiene el n�mero de ID de la columna que ha sido seleccionada para tener filas �nicas en la tabla. En este ejemplo se muestra c�mo se puede obtener el nombre de la columna de clave y usarse en la programaci�n.

USE Northwind
GO
DECLARE @key_column sysname
SET @key_column = Col_Name(Object_Id('Categories'),
ObjectProperty(Object_id('Categories'),
'TableFulltextKeyColumn')
)
print @key_column
EXECUTE ('SELECT Description, KEY_TBL.RANK
FROM Categories FT_TBL
INNER JOIN
FreetextTable (Categories, Description,
''How can I make my own beers and ales?'') AS KEY_TBL
ON FT_TBL.'
+ @key_column
+' = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK >= 10
ORDER BY KEY_TBL.RANK DESC
')
GO

Puede evitar la complejidad de la utilizaci�n de CONTAINSTABLE y FREETEXTTABLE si escribe procedimientos almacenados que acepten unos cuantos supuestos acerca de la consulta y, a continuaci�n, creen y ejecuten la consulta adecuada. A continuaci�n se muestra un procedimiento simplificado que emite una consulta FREETEXTTABLE. La tabla muestra los par�metros del procedimiento (todas las entradas).

Par�metros	Opcional	Descripci�n
@additional_predicates	Opcional	Si hay alg�n predicado adicional, �ste se agrega con AND detr�s del predicado FREETEXT. KEY_TBL.RANK se puede usar en expresiones.
@freetext_column	SI	�
@freetext_search	SI	Condici�n de B�squeda
@from_table	SI	�
@order_by_list	Opcional	KEY_TBL.RANK puede ser una de las columnas especificadas.
@select_list	SI	KEY_TBL.RANK puede ser una de las columnas especificadas.

El c�digo del procedimiento es el siguiente:

CREATE PROCEDURE freetext_rank_proc
@select_list nvarchar(1000),
@from_table nvarchar(517),
@freetext_column sysname,
@freetext_search nvarchar(1000),
@additional_predicates nvarchar(500) = '',
@order_by_list nvarchar(500) = ''
AS
BEGIN
DECLARE @table_id integer,
@unique_key_col_name sysname,
@add_pred_var nvarchar(510),
@order_by_var nvarchar(510)

-- Get the name of the unique key column for this table.
SET @table_id = Object_Id(@from_table)
SET @unique_key_col_name =
Col_Name( @table_id,
ObjectProperty(@table_id, 'TableFullTextKeyColumn') )

-- If there is an additional_predicate, put AND() around it.
IF @additional_predicates <> ''
SET @add_pred_var = 'AND (' + @additional_predicates + ')'
ELSE
SET @add_pred_var = ''

-- Insert ORDER BY, if needed.
IF @order_by_list <> ''
SET @order_by_var = 'ORDER BY ' + @order_by_var
ELSE
SET @order_by_var = ''

-- Execute the SELECT statement.
EXECUTE ( 'SELECT '
+ @select_list
+ ' FROM '
+ @from_table
+ ' AS FT_TBL, FreetextTable('
+ @from_table
+ ','
+ @freetext_column
+ ','''
+ @freetext_search
+ ''') AS KEY_TBL '
+ 'WHERE FT_TBL.'
+ @unique_key_col_name
+ ' = KEY_TBL.[KEY] '
+ @add_pred_var
+ ' '
+ @order_by_var
)
END

Este procedimiento se puede usar para emitir la consulta:

USE Northwind
GO
EXECUTE freetext_rank_proc
'Description, KEY_TBL.RANK', -- Select list
'Categories', -- From
'Description', -- Column
'How can I make my own beers and ales?', -- Freetext search
'KEY_TBL.RANK >= 10', -- Additional predicate
'KEY_TBL.RANK DESC' -- Order by
GO

�Limitar los conjuntos de resultados

En muchas consultas de texto, el n�mero de elementos que coinciden con la condici�n de b�squeda es muy grande. Para evitar que las consultas devuelvan demasiadas coincidencias, utilice el argumento opcional, top_n_by_rank, en CONTAINSTABLE y FREETEXTTABLE para especificar el n�mero de coincidencias, ordenadas, que desea que se devuelvan.

Con esta informaci�n, SQL SERVER ordena las coincidencias y devuelve s�lo hasta completar el n�mero especificado. Esta opci�n puede aumentar significativamente el rendimiento. Por ejemplo, una consulta que por lo general devolver�a 100.000 filas de una tabla de 1 mill�n se procesar� de forma m�s r�pida si s�lo se piden las 100 primeras filas.

Si s�lo se desea que se devuelvan las 3 coincidencias mayores del ejemplo anterior, mediante CONTAINSTABLE, la consulta tendr� esta forma:

USE Northwind
GO
SELECT K.RANK, CompanyName, ContactName, Address
FROM Customers AS C
INNER JOIN
CONTAINSTABLE(Customers,Address, 'ISABOUT ("des*",
Rue WEIGHT(0.5),
Bouchers WEIGHT(0.9))', 3) AS K
ON C.CustomerID = K.[KEY]

�Buscar palabras o frases con valores ponderados (t�rmino ponderado)

Puede buscar palabras o frases y especificar un valor ponderado. El peso, un n�mero entre 0,0 y 1,0, indica el grado de importancia de cada palabra o frase en un conjunto de palabras y frases. El valor 0,0 es el peso m�s peque�o disponible, y el valor 1,0 es el peso m�s grande. Por ejemplo, en esta consulta se buscan todas las direcciones de los clientes, con valores ponderados, en los que cualquier texto que comience con la cadena "des" est� cerca de Rue o Bouchers. SQL SERVER da una clasificaci�n superior a aquellas filas que contienen la mayor cantidad de palabras especificadas. Por tanto, SQL SERVER da una clasificaci�n superior a una fila que contiene des Rue Bouchers que a una fila que contiene des Rue.

USE Northwind
GO
SELECT CompanyName, ContactName, Address
FROM Customers
WHERE CONTAINS(Address, 'ISABOUT ("*des*",
Rue WEIGHT(0.5),
Bouchers WEIGHT(0.9)
) ' )
GO

Un t�rmino ponderado se puede usar en conjunci�n con cualquiera de los otros cuatro tipos de t�rminos.

�Combinar predicados de texto con otros predicados de TRANSACT-SQL

Los predicados CONTAINS y FREETEXT se pueden combinar con el resto de predicados de TRANSACT-SQL, como, por ejemplo, LIKE y BETWEEN; tambi�n se pueden usar en una subconsulta. En este ejemplo se buscan descripciones cuya categor�a no sea Seafood y que contengan la palabra "sauces" y la palabra "seasonings".

USE Northwind
GO
SELECT Description
FROM Categories
WHERE CategoryName <> 'Seafood' AND
CONTAINS(Description, ' sauces AND seasonings ')
GO

En la siguiente consulta se usa CONTAINS dentro de una subconsulta. Con la base de datos pubs, la consulta obtiene el valor del t�tulo de todos los libros de la tabla titles del publicador que se encuentra pr�ximo al platillo volante de Moonbeam, Ontario. (Esta informaci�n acerca del publicador se encuentra en la columna pr_info de la tabla pub_info y s�lo hay uno de estos publicadores.)

USE pubs
GO
-- Add some interesting rows to some tables.
INSERT INTO publishers
VALUES ('9970', 'Penumbra Press', 'Moonbeam', 'ON', 'Canada')
INSERT INTO pub_info (pub_id, pr_info)
VALUES ('9970',
'Penumbra press is located in the small village of Moonbeam. Moonbeam 
is well known as the flying saucer capital of Ontario. You will often find one 
or more flying saucers docked close to the tourist information centre on the 
north side of highway 11.')

INSERT INTO titles
VALUES ('FP0001', 'Games of the World', 'crafts', '9970', 9.85,
0.00, 20, 213, 'A crafts book! A sports book! A history book! The fun and 
excitement of a world at play - beautifully described and lavishly illustrated', 
'1977/09/15')
GO
-- Given the full-text catalog for these tables is pubs_ft_ctlg,
-- repopulate it so new rows are included in the full-text indexes.
sp_fulltext_catalog 'pubs_ft_ctlg', 'start_full'
WAITFOR DELAY '00:00:30' -- Wait 30 seconds for population.
GO
-- Issue the query.
SELECT T.title, P.pub_name
FROM publishers P,
titles T
WHERE P.pub_id = T.pub_id
AND P.pub_id = (SELECT pub_id
FROM pub_info
WHERE CONTAINS (pr_info,
' moonbeam AND
ontario AND
"flying saucer" '))
GO

�Utilizar predicados de texto para consultar columnas de tipo IMAGE

Los predicados CONTAINS y FREETEXT pueden utilizarse para buscar columnas IMAGE indizadas.

En una sola columna IMAGE es posible almacenar muchos tipos de documentos. SQL SERVER admite ciertos tipos de documento y proporciona filtros para los mismos. Esta versi�n proporciona filtros para documentos de Office, archivos de texto y archivos HTML.

Cuando una columna IMAGE participa en un �ndice de texto, el servicio de texto comprueba las extensiones de los documentos de la columna IMAGE y aplica el filtro correspondiente, para interpretar los datos binarios y extraer la informaci�n de texto necesaria para la indizaci�n y la consulta.

As�, cuando configure la indizaci�n de texto sobre una columna IMAGE de una tabla, deber� crear una columna separada para que contenga la informaci�n relativa al documento. Esta columna de tipo debe ser de cualquier tipo de datos basado en caracteres y contendr� la extensi�n del archivo, como por ejemplo DOC para los documentos de Microsoft Word. Si el tipo de columna es NULL, el servicio de texto asumir� que el documento es un archivo de texto.

En el Asistente para indizaci�n de texto, si selecciona una columna IMAGE para la indizaci�n, deber� especificar tambi�n una Columna de enlace para que contenga el tipo de documento.
El procedimiento almacenado sp_fulltext_column acepta tambi�n un argumento para la columna que contendr� los tipos de documento.
El procedimiento almacenado sp_help_fulltext_columns devuelve tambi�n el nombre de columna y el Id. de columna de la columna de tipo de documento.

Una vez indizada, podr� consultar la columna IMAGE como lo har�a con cualquier otra columna de la tabla, mediante los predicados CONTAINS y FREETEXT.