CREATE FULLTEXT INDEX (Transact-SQL)

Artículo
06/18/2013

Crea un índice de texto completo en una tabla o una vista indizada de una base de datos. Solo se permite un índice de texto completo por cada tabla o vista indizada, y cada índice de texto completo se aplica a una única tabla o vista indizada.

El índice de texto completo puede contener hasta 1.024 columnas.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

CREATE FULLTEXT INDEX ON table_name
   [ ( { column_name 
             [ TYPE COLUMN type_column_name ]
             [ LANGUAGE language_term ] 
             [ STATISTICAL_SEMANTICS ]
        } [ ,...n] 
      ) ]
    KEY INDEX index_name 
    [ ON <catalog_filegroup_option> ]
    [ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]
[;]

<catalog_filegroup_option>::=
 {
    fulltext_catalog_name 
 | ( fulltext_catalog_name, FILEGROUP filegroup_name )
 | ( FILEGROUP filegroup_name, fulltext_catalog_name )
 | ( FILEGROUP filegroup_name )
 }

<with_option>::=
 {
   CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] } 
 | STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }
 | SEARCH PROPERTY LIST [ = ] property_list_name 
 }

Argumentos

table_name
Es el nombre de la tabla o vista indizada que contiene la columna o columnas incluidas en el índice de texto completo.
column_name
Es el nombre de la columna incluida en el índice de texto completo. Solo las columnas de tipo char, varchar, nchar, nvarchar, text, ntext, image, xml y varbinary(max) se pueden indizar para la búsqueda de texto completo. Para especificar varias columnas, repita la cláusula column_name del modo siguiente:

CREATE FULLTEXT INDEX ON table_name (column_name1 […], column_name2 […]) …
TYPE COLUMN type_column_name
Especifica el nombre de una columna de la tabla, type_column_name, que se usa para almacenar el tipo de un documento varbinary(max) o image. Esta columna, denominada columna de tipo, contiene una extensión de archivo proporcionada por el usuario (.doc, .pdf, .xls, etc.). La columna de tipo debe ser char, nchar, varchar o nvarchar.

Especifique TYPE COLUMN type_column_name únicamente si column_name especifica una columna de tipo varbinary(max) o image en la que los datos se almacenan como datos binarios; de lo contrario, SQL Server devuelve un error.

[!NOTA]

En el momento de la indización, el motor de texto completo utiliza la abreviatura de la columna de tipo de cada fila de la tabla para identificar el filtro de la búsqueda de texto completo que se va a utilizar para el documento en column_name. El filtro carga el documento como un flujo binario, quita la información del formato y envía el texto del documento al componente de separador de palabras. Para obtener más información, vea Configurar y administrar filtros para búsquedas.
LANGUAGE language_term
Es el idioma de los datos almacenados en column_name.

language_term es opcional y se puede especificar como una cadena, un entero o un valor hexadecimal correspondiente al identificador de configuración regional (LCID) de un idioma. Si no se especifica ningún valor, se utiliza el idioma predeterminado de la instancia de SQL Server.

Si se especifica language_term, el idioma que representa se utiliza para indizar los datos almacenados en las columnas de tipo char, nchar, varchar, nvarchar, text y ntext. Este es el idioma predeterminado que se utiliza en el momento de la consulta si language_term no se especifica como parte de un predicado de texto completo en relación con la columna.

Si se especifica como una cadena, language_term corresponde al valor de la columna alias de la tabla del sistema syslanguages. La cadena debe estar delimitada con comillas sencillas como en 'language_term'. Si el valor especificado es un entero, language_term es el LCID real que identifica el idioma. Si se especifica como un valor hexadecimal, language_term es 0x seguido del valor hexadecimal del LCID. El valor hexadecimal no puede superar los ocho dígitos, incluidos los ceros a la izquierda.

Si el valor está en formato DBCS (juego de caracteres de doble byte), SQL Server lo convertirá a Unicode.

Se deben habilitar recursos, como los separadores de palabras y lematizadores, para el idioma especificado como language_term. Si estos recursos no admiten el idioma especificado, SQL Server devuelve un error.

Utilice el procedimiento almacenado sp_configure para tener acceso a la información sobre el idioma de texto completo predeterminado de la instancia de Microsoft SQL Server. Para obtener más información, vea sp_configure (Transact-SQL).

Para las columnas no BLOB y no XML que contienen datos de texto en varios idiomas o en los casos en que se desconoce el idioma del texto almacenado en la columna, podría ser apropiado usar el recurso de idioma neutro (0x0). Sin embargo, primero debería entender las posibles consecuencias de usar el recurso de idioma neutro (0x0). Para obtener información sobre las soluciones y consecuencias posibles de utilizar el recurso de idioma neutro (0x0), vea Elegir un idioma al crear un índice de texto completo.

Para los documentos almacenados en columnas de tipo XML o BLOB, la codificación de idioma del documento se utilizará en el momento de la indización. Por ejemplo, en las columnas XML, el atributo xml:lang de los documentos XML identifica el idioma. En el momento de la consulta, el valor especificado previamente en language_term se convierte en el idioma predeterminado que se utiliza para las consultas de texto completo, a menos que language_term se especifique como parte de una consulta de texto completo.
STATISTICAL_SEMANTICS
Crea los índices adicionales de similitud de documentos y frases clave que forman parte de la indización semántica estadística. Para obtener más información, vea Búsqueda semántica (SQL Server).
KEY INDEX index_name
Es el nombre del índice de clave única de table_name. KEY INDEX debe ser una columna que no admita valores NULL con una sola clave única. Seleccione el índice de clave única más pequeño para la clave única de texto completo. Para obtener el máximo rendimiento, recomendamos un tipo de datos entero para la clave de texto completo.
fulltext_catalog_name
Se trata del catálogo de texto completo utilizado para el índice de texto completo. El catálogo debe existir en la base de datos. Esta cláusula es opcional. Si no se especifica, se utiliza un catálogo predeterminado. Si no hay ningún catálogo predeterminado, SQL Server devuelve un error.
FILEGROUP filegroup_name
Crea el índice de texto completo especificado en el grupo de archivos especificado. El grupo de archivos debe existir previamente. Si no se especifica la cláusula FILEGROUP, el índice de texto completo se coloca en el mismo grupo de archivos que la tabla base o la vista para una tabla sin particiones o en el grupo de archivos principal para una tabla con particiones.
CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [ , NO POPULATION ] }
Especifica si SQL Server propagará al índice de texto completo los cambios (actualizaciones, eliminaciones o inserciones) efectuados en las columnas de la tabla que cubre el índice de texto completo. Los cambios realizados en los datos con WRITETEXT y UPDATETEXT no se reflejan en el índice de texto completo y no se recopilan con el seguimiento de cambios.
- MANUAL
  Especifica que los cambios sometidos a seguimiento se deben propagar manualmente llamando a la instrucción de Transact-SQL ALTER FULLTEXT INDEX … START UPDATE POPULATION (rellenado manual). Puede utilizar el Agente SQL Server para llamar a esta instrucción de Transact-SQL de forma periódica.
- AUTO
  Especifica que los cambios sometidos a seguimiento se propagarán automáticamente cuando los datos se modifiquen en la tabla base (rellenado automático). Aunque los cambios se propagan de forma automática, podrían no reflejarse de inmediato en el índice de texto completo. AUTO es el valor predeterminado.
- OFF [ , NO POPULATION]
  Especifica que SQL Server no mantiene una lista de los cambios realizados en los datos indizados. Si no se especifica NO POPULATION, SQL Server rellena el índice por completo tras su creación.
  
  La opción NO POPULATION se puede utilizar únicamente si CHANGE_TRACKING es OFF. Si se especifica NO POPULATION, SQL Server no rellena el índice tras su creación. El índice solamente se rellena después de que el usuario ejecuta el comando ALTER FULLTEXT INDEX con la cláusula START FULL POPULATION o START INCREMENTAL POPULATION.
STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }
Asocia una lista de palabras irrelevantes de texto completo al índice. El índice no se rellena con ningún token que forme parte de la lista de palabras irrelevantes especificada. Si no se especifica STOPLIST, SQL Server asocia la lista de palabras irrelevantes de texto completo del sistema al índice.
- OFF
  Especifica que no se asocie al índice de texto completo ninguna lista de palabras irrelevantes.
- SYSTEM
  Especifica que la lista de palabras irrelevantes predeterminada de texto completo del sistema se debe usar para este índice de texto completo.
- stoplist_name
  Especifica el nombre de la lista de palabras irrelevantes que se va a asociar al índice de texto completo.
SEARCH PROPERTY LIST [ = ] property_list_name
Asocia una lista de propiedades de búsqueda al índice.
- OFF
  Especifica que no se asocie al índice de texto completo ninguna lista de propiedades.
- property_list_name
  Especifica el nombre de la lista de propiedades de búsqueda que se va a asociar al índice de texto completo.

Comentarios

Para obtener más información sobre los índices de texto completo, vea Crear y administrar índices de texto completo.

En las columnas xml, puede crear un índice de texto completo que indique el contenido de los elementos XML, pero omita el marcado XML. Los valores de los atributos se incluyen en el índice de texto completo a menos que sean valores numéricos. Las etiquetas de elemento se utilizan como límites de token. Se admiten fragmentos y documentos con formato XML o HTML correcto que contengan varios idiomas. Para obtener más información, vea Usar la búsqueda de texto completo con columnas XML.

Recomendamos que la columna de clave de índice sea de un tipo de datos entero. Esto proporciona optimizaciones en el momento de ejecución de la consulta.

Interacciones del seguimiento de cambios y del parámetro NO POPULATION

Que se rellene el índice de texto completo depende de si el seguimiento de cambios está habilitado y si se especifica WITH NO POPULATION en la instrucción ALTER FULLTEXT INDEX. En la tabla siguiente se resume el resultado de su interacción.

Seguimiento de cambios	WITH NO POPULATION	Resultado
No se ha habilitado	No se ha especificado	Se realiza un rellenado completo en el índice.
No se ha habilitado	Se ha especificado	No se produce el rellenado del índice hasta que se emite una instrucción ALTER FULLTEXT INDEX...START POPULATION.
Habilitado	Se ha especificado	Se produce un error y no se altera el índice.
Habilitado	No se ha especificado	Se realiza un rellenado completo en el índice.

Para obtener más información acerca del rellenado de índices de texto completo, vea Rellenar índices de texto completo.

Permisos

El usuario debe tener el permiso REFERENCES en el catálogo de texto completo y el permiso ALTER en la tabla o vista indizada, o debe ser miembro del rol fijo de servidor sysadmin, o de los roles fijos de base de datos db_owner o db_ddladmin.

Si se especifica SET STOPLIST, el usuario debe tener el permiso REFERENCES en la lista de palabras irrelevantes especificada. El propietario de la lista de palabras irrelevantes puede conceder este permiso.

[!NOTA]

Los usuarios tienen el permiso REFERENCE para la lista de palabras irrelevantes predeterminada que se incluye con SQL Server.

Ejemplos

A.Crear un índice único, un catálogo de texto completo y un índice de texto completo

En el ejemplo siguiente se crea un índice único en la columna JobCandidateID de la tabla HumanResources.JobCandidate de la base de datos de ejemplo AdventureWorks. A continuación, se crea un catálogo de texto completo predeterminado, ft. Por último, se crea un índice de texto completo en la columna Resume mediante el catálogo ft y la lista de palabras irrelevantes del sistema.

USE AdventureWorks;
GO
CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) 
   KEY INDEX ui_ukJobCand 
   WITH STOPLIST = SYSTEM;
GO

B.Crear un índice de texto completo en varias columnas de la tabla

En el ejemplo siguiente se crea un catálogo de texto completo, production_catalog, en la base de datos de ejemplo AdventureWorks. A continuación, se crea el índice de texto completo que utiliza este nuevo catálogo. El índice de texto completo está en las columnas ReviewerName, EmailAddress y Comments de la tabla Production.ProductReview de la base de datos de ejemplo AdventureWorks. Para cada columna, en el ejemplo se especifica el LCID de inglés, 1033, que es el idioma de los datos de las columnas. Este índice de texto completo utiliza un índice de clave única existente, PK_ProductReview_ProductReviewID. Tal como se recomienda, esta clave de índice está en una columna de tipo entero, ProductReviewID.

USE AdventureWorks;
GO
CREATE FULLTEXT CATALOG production_catalog;
GO
CREATE FULLTEXT INDEX ON Production.ProductReview
 ( 
  ReviewerName
     Language 1033,
  EmailAddress
     Language 1033,
  Comments 
     Language 1033     
 ) 
  KEY INDEX PK_ProductReview_ProductReviewID 
      ON production_catalog; 
GO

C.Crear un índice de texto completo con una lista de propiedades de búsqueda sin rellenarlo

En el ejemplo siguiente se crea un índice de texto completo en las columnas Title, DocumentSummary y Document de la tabla Production.Document. En el ejemplo se especifica el LCID de inglés, 1033, que es el idioma de los datos de las columnas. Este índice de texto completo utiliza el catálogo de texto completo predeterminado y un índice de clave única existente, PK_Document_DocumentID. Tal como se recomienda, esta clave de índice está en una columna de tipo entero, DocumentID.

El ejemplo especifica la lista de palabras irrelevantes SYSTEM. También especifica una lista de propiedades de búsqueda, DocumentPropertyList; para obtener un ejemplo que crea esta lista de propiedades, vea CREATE SEARCH PROPERTY LIST (Transact-SQL).

En el ejemplo se especifica que el seguimiento de cambios está desactivado sin rellenado. Posteriormente, durante las horas de menor actividad, en el ejemplo se utiliza una instrucción ALTER FULLTEXT INDEX para iniciar un rellenado completo en el nuevo índice y habilitar el seguimiento automático de cambios.

USE AdventureWorks;
GO
CREATE FULLTEXT INDEX ON Production.Document
  ( 
  Title
      Language 1033, 
  DocumentSummary
      Language 1033, 
  Document 
      TYPE COLUMN FileExtension
      Language 1033 
  )
  KEY INDEX PK_Document_DocumentID
          WITH STOPLIST = SYSTEM, SEARCH PROPERTY LIST = DocumentPropertyList, CHANGE_TRACKING OFF, NO POPULATION;
   GO

Posteriormente, durante horas de menor actividad, el índice se rellena:

ALTER FULLTEXT INDEX ON Production.Document SET CHANGE_TRACKING AUTO;
GO

Vea también

Referencia

ALTER FULLTEXT INDEX (Transact-SQL)

DROP FULLTEXT INDEX (Transact-SQL)

GRANT (Transact-SQL)

sys.fulltext_indexes (Transact-SQL)

Conceptos

Crear y administrar índices de texto completo

Búsqueda de texto completo (SQL Server)

Buscar propiedades de documento con listas de propiedades de búsqueda

Compartir a través de