Guía Completa para Implementar FULLTEXT en SQL

Fragmentación FULLTEXT, es una actividad y característica poderosa de SQL que permite realizar búsquedas eficientes y relevantes en grandes volúmenes de datos textuales. A continuación, te presentamos ejemplos detallados y explicados sobre cómo crear y gestionar catálogos e índices FULLTEXT en SQL Server.

Creación de un Catálogo FULLTEXT

Un catálogo FULLTEXT es un contenedor lógico que organiza los índices FULLTEXT. El primer paso para utilizar FULLTEXT es crear un catálogo.

Crear un Catálogo FULLTEXT

La sintaxis para crear un catálogo FULLTEXT es la siguiente:

CREATE FULLTEXT CATALOG [catalog_name] WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo];
GO

• [catalog_name]: Nombre del catálogo FULLTEXT.
• ACCENT_SENSITIVITY: Define si el catálogo es sensible a acentos. En este caso, está desactivado (OFF).
• AUTHORIZATION [dbo]: Define el propietario del catálogo. El propietario por defecto es dbo.

Ejemplo de Creación de Catálogo FULLTEXT

CREATE FULLTEXT CATALOG [t1_texto] WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo];
GO

En este ejemplo, creamos un catálogo llamado t1_texto con sensibilidad a acentos desactivada y dbo como propietario.

Creación de un Índice FULLTEXT

Una vez creado el catálogo, el siguiente paso es crear un índice FULLTEXT en una tabla específica.

Crear un Índice FULLTEXT

La sintaxis para crear un índice FULLTEXT es la siguiente:

CREATE FULLTEXT INDEX ON [schema_name].[table_name] 
KEY INDEX [PK_name] 
ON ([catalog_name]) 
WITH (CHANGE_TRACKING AUTO);
GO

USE [database_name];
GO

ALTER FULLTEXT INDEX ON [schema_name].[table_name] ADD ([column_name]);
GO

ALTER FULLTEXT INDEX ON [schema_name].[table_name] ENABLE;
GO

• [schema_name].[table_name]: Esquema y nombre de la tabla.
• KEY INDEX [PK_name]: Nombre de la clave primaria utilizada como clave única para el índice FULLTEXT.
• [catalog_name]: Nombre del catálogo FULLTEXT donde se almacenará el índice.
• CHANGE_TRACKING AUTO: Habilita el seguimiento automático de cambios para mantener actualizado el índice FULLTEXT.
• [database_name]: Nombre de la base de datos.
• [column_name]: Nombre de la columna a añadir al índice FULLTEXT.

Ejemplo de Creación de Índice FULLTEXT

CREATE FULLTEXT INDEX ON [dbo].[t1] 
KEY INDEX [PK_c1] 
ON ([t1_texto]) 
WITH (CHANGE_TRACKING AUTO);
GO

USE [BBDD_donde_se_creo];
GO

ALTER FULLTEXT INDEX ON [dbo].[t1] ADD ([c2]);
GO

ALTER FULLTEXT INDEX ON [dbo].[t1] ENABLE;
GO

En este ejemplo, creamos un índice FULLTEXT en la tabla t1 del esquema dbo utilizando la clave primaria PK_c1. El índice se almacenará en el catálogo t1_texto y tendrá seguimiento automático de cambios. Luego, añadimos la columna c2 al índice y lo habilitamos.

Mantenimiento de Índices FULLTEXT

Con el tiempo, los índices FULLTEXT pueden fragmentarse, lo que puede afectar el rendimiento de las consultas. SQL Server proporciona comandos para reorganizar y reconstruir índices FULLTEXT.

Reorganizar y Recrear Índices FULLTEXT

Para reorganizar y reconstruir índices FULLTEXT, utiliza las siguientes consultas:

Reorganizar el Catálogo

La reorganización de un catálogo FULLTEXT compacta el índice y mejora su rendimiento.

ALTER FULLTEXT CATALOG [catalog_name] REORGANIZE;
GO

• [catalog_name]: Nombre del catálogo FULLTEXT.

Reconstruir el Catálogo

La reconstrucción de un catálogo FULLTEXT recrea completamente el índice, eliminando la fragmentación.

ALTER FULLTEXT CATALOG [catalog_name] REBUILD;
GO

• [catalog_name]: Nombre del catálogo FULLTEXT.

Ejemplo de Reorganización y Recreación

Para identificar los fragmentos en los índices FULLTEXT, puedes ejecutar la siguiente consulta:

SELECT t.name, f.* 
FROM sys.fulltext_index_fragments f, sys.tables t 
WHERE t.object_id = f.table_id;
GO

Luego, para reorganizar y reconstruir el catálogo t1_texto, ejecuta:

ALTER FULLTEXT CATALOG [t1_texto] REORGANIZE;
GO

ALTER FULLTEXT CATALOG [t1_texto] REBUILD;
GO

Comprobación de la Fragmentación en Índices FULLTEXT en SQL Server

La fragmentación en los índices FULLTEXT puede afectar significativamente el rendimiento de las consultas. Por eso, es importante monitorear y gestionar la fragmentación de estos índices. A continuación, presentamos una consulta detallada para comprobar la fragmentación de todos los índices FULLTEXT en una base de datos.

Consulta para Comprobar la Fragmentación FULLTEXT

La siguiente consulta recupera información sobre la fragmentación de todos los índices FULLTEXT en la base de datos, incluyendo el ID del catálogo, el nombre del catálogo, el estado de seguimiento de cambios, el ID del objeto, el nombre del objeto, el número de fragmentos, el tamaño total del índice en MB, el tamaño del fragmento más grande en MB y el porcentaje de fragmentación.

SELECT 
    c.fulltext_catalog_id, 
    c.name AS fulltext_catalog_name, 
    i.change_tracking_state,
    i.object_id, 
    OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS object_name,
    f.num_fragments, 
    f.fulltext_mb, 
    f.largest_fragment_mb,
    100.0 * (f.fulltext_mb - f.largest_fragment_mb) / NULLIF(f.fulltext_mb, 0) AS fulltext_fragmentation_in_percent
--INTO #fulltextFragmentationDetails
FROM 
    sys.fulltext_catalogs c
JOIN 
    sys.fulltext_indexes i ON i.fulltext_catalog_id = c.fulltext_catalog_id
JOIN (
    -- Computa los datos de fragmentación para cada tabla con un índice FULLTEXT
    SELECT 
        table_id,
        COUNT(*) AS num_fragments,
        CONVERT(DECIMAL(9,2), SUM(data_size / (1024.0 * 1024.0))) AS fulltext_mb,
        CONVERT(DECIMAL(9,2), MAX(data_size / (1024.0 * 1024.0))) AS largest_fragment_mb
    FROM 
        sys.fulltext_index_fragments
    GROUP BY 
        table_id
) f ON f.table_id = i.object_id;

Explicación de la Consulta

1. sys.fulltext_catalogs: Esta tabla del sistema contiene información sobre los catálogos FULLTEXT en la base de datos.
2. sys.fulltext_indexes: Esta tabla del sistema contiene información sobre los índices FULLTEXT en la base de datos.
3. sys.fulltext_index_fragments: Esta tabla del sistema contiene información sobre los fragmentos de los índices FULLTEXT.

La consulta realiza los siguientes pasos:

• Selecciona el ID del catálogo, el nombre del catálogo y el estado de seguimiento de cambios desde sys.fulltext_catalogs y sys.fulltext_indexes.
• Realiza una unión con una subconsulta que calcula el número de fragmentos, el tamaño total del índice en MB y el tamaño del fragmento más grande en MB para cada tabla con un índice.
• Calcula el porcentaje de fragmentación utilizando la fórmula: 100.0 * (f.fulltext_mb - f.largest_fragment_mb) / NULLIF(f.fulltext_mb, 0). Esta fórmula determina qué tan fragmentado está el índice al comparar el tamaño total del índice con el tamaño del fragmento más grande.

Uso de la Información

La información obtenida de esta consulta puede ayudarte a identificar los índices FULLTEXT que necesitan ser reorganizados o reconstruidos.

• Número de Fragmentos (num_fragments): Un alto número de fragmentos puede indicar que el índice está fragmentado y necesita mantenimiento.
• Tamaño Total del Índice (fulltext_mb): Indica el tamaño total del índice en megabytes.
• Tamaño del Fragmento Más Grande (largest_fragment_mb): Indica el tamaño del fragmento más grande en el índice.
• Porcentaje de Fragmentación (fulltext_fragmentation_in_percent): Un alto porcentaje de fragmentación indica que el índice está desfragmentado y puede beneficiarse de una reorganización o reconstrucción.

Ejemplo de Mantenimiento de Índices, Fragmentación FULLTEXT

Basado en la información de fragmentación, puedes decidir reorganizar o reconstruir los catálogos FULLTEXT. A continuación se muestran ejemplos de cómo realizar estas operaciones:

Reorganizar un Catálogo

ALTER FULLTEXT CATALOG [nombre_del_catalogo] REORGANIZE;
GO

Reconstruir un Catálogo

ALTER FULLTEXT CATALOG [nombre_del_catalogo] REBUILD;
GO

Estos comandos ayudan a mantener los índices FULLTEXT optimizados, mejorando el rendimiento de las consultas de texto completo.

Conclusión

FULLTEXT es una herramienta poderosa para mejorar la búsqueda y gestión de grandes volúmenes de datos textuales en SQL. Al seguir estos pasos y ejemplos, puedes crear, gestionar y mantener eficientemente tus catálogos e índices. Mantén tus índices optimizados y actualizados para garantizar un rendimiento óptimo y resultados de búsqueda precisos.

Monitorear y gestionar la fragmentación de los índices es crucial para mantener el rendimiento óptimo de tus consultas SQL. Utiliza la consulta proporcionada para obtener una visión detallada de la fragmentación y aplica las acciones necesarias para reorganizar o reconstruir los índices según sea necesario.

Procedimientos Almacenados Temporales en SQL Server

Generando Script de creación de Usuarios en SQL Server

Descarga de SQL Server Management Studio (SSMS)

Sacar permisos de Base de Datos SQL scripts

Convertir una Fecha y Hora a Solo Fecha en SQL

¿Qué son las vistas SQL Server? Una Guía Completa