CREATE FULLTEXT INDEX (Transact-SQL)

Crea un índice de texto completo en una tabla o una vista indizada de una base de datos. Sólo 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.

Convenciones de sintaxis de Transact-SQL

Sintaxis

CREATE FULLTEXT INDEX ON table_name
      [ ( { column_name 
             [ TYPE COLUMN type_column_name ]
             [ LANGUAGE language_term ] 
        } [ ,...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 }
  }

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. Sólo las columnas de tipo char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary 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 utiliza para retener el tipo de documento para varbinary, varbinary(max) o el documento 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, 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 una secuencia binaria, 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 Filtros de búsqueda de texto completo.

• 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 de 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 MicrosoftSQL Server. Para obtener más información, vea sp_configure (Transact-SQL) y default full-text language (opción).

  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 Prácticas recomendadas para 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 consultas de texto completo a menos que language_term se especifique como parte de una consulta de texto completo.

• 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.

Notas

Para obtener más información sobre los índices de texto completo, vea Configurar índices y catálogos de texto completo para una base de datos.

En las columnas xml, puede crear un índice de texto completo que indice 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 Índice de texto completo en una columna 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 Rellenado de í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 ser un miembro de la función fija del servidor sysadmin o de las funciones fijas de la 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 índice de texto completo 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 el catálogo de texto completo predeterminado y 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 INDEX ON Production.ProductReview
( 
  ReviewerName
     Language 1033,
  EmailAddress
     Language 1033,
  Comments 
     Language 1033     
 ) 
KEY INDEX PK_ProductReview_ProductReviewID ; 
GO

C. Crear un índice de texto completo sin rellenarlo

En el ejemplo siguiente se crea un catálogo de texto completo, documents_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 la columna 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 la columna. 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. 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 CATALOG documents_catalog;
GO
CREATE FULLTEXT INDEX ON Production.Document
( Document 
    TYPE COLUMN FileExtension
    Language 1033 )
   KEY INDEX PK_Document_DocumentID
      ON documents_catalog
      WITH CHANGE_TRACKING OFF, NO POPULATION;
   GO

-- Posteriormente, durante horas de menor actividad, rellenar el índice:

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

Historial de cambios

Contenido actualizado
Se agregaron nuevos ejemplos a la sección "Ejemplos".
Se agregó la sección "Interacciones del seguimiento de cambios y del parámetro NO POPULATION".

Vea también

Tasks

Cómo iniciar el Asistente para indización de texto completo (SQL Server Management Studio)

Referencia

ALTER FULLTEXT INDEX (Transact-SQL)

DROP FULLTEXT INDEX (Transact-SQL)

GRANT (Transact-SQL)

sys.fulltext_indexes (Transact-SQL)

Conceptos

Búsqueda de texto completo (SQL Server)

Otros recursos

Configurar índices y catálogos de texto completo para una base de datos