¿Alguna vez te has preguntado si es posible particionar una tabla temporal en SQL Server? Es una pregunta interesante que quería investigar. En este artículo, te mostraré cómo particionar una tabla temporal y discutiré los posibles casos de uso para esta característica.

Comencemos desmintiendo una idea errónea común: que las tablas temporales no pueden tener índices o restricciones. De hecho, puedes crear índices agrupados y no agrupados en una tabla temporal, así como restricciones de clave primaria y predeterminadas. Para demostrar esto, he incluido un script que crea una tabla temporal llamada #hubbabubba y agrega índices y restricciones a la misma.

SET NOCOUNT ON;
USE tempdb;
GO

IF OBJECT_ID('#hubbabubba','U') IS NOT NULL
BEGIN
    DROP TABLE #hubbabubba;
END

CREATE TABLE #hubbabubba (
    someint INT PRIMARY KEY NONCLUSTERED IDENTITY(1,1),
    somechar VARCHAR(50),
    somedate DATE,
    somebit BIT DEFAULT(0)
)

-- Agrega índices y restricciones a la tabla temporal

CREATE CLUSTERED INDEX idx_hubba_somedate ON #hubbabubba (somedate)

-- Agrega más índices y restricciones si es necesario

GO

Ahora, pasemos al tema principal: particionar una tabla temporal. Para particionar una tabla temporal, debes crear una función de particionamiento y un esquema de particionamiento, y luego asociar el esquema de particionamiento con un índice agrupado en la tabla temporal. Esto es similar a particionar una tabla regular en SQL Server.

IF EXISTS (SELECT name FROM sys.partition_schemes WHERE name = 'PartitionToPrimary')
BEGIN
    DROP PARTITION SCHEME PartitionToPrimary
END

IF EXISTS (SELECT name FROM sys.partition_functions WHERE name = 'PartitionByMonth')
BEGIN
    DROP PARTITION FUNCTION PartitionByMonth
END

CREATE PARTITION FUNCTION PartitionByMonth (DATE)
AS RANGE RIGHT 
FOR VALUES ('2014/01/01', '2014/02/01', '2014/03/01', '2014/04/01', '2014/05/01','2014/06/01',
            '2014/07/01', '2014/08/01', '2014/09/01', '2014/10/01', '2014/11/01', '2014/12/01');

CREATE PARTITION SCHEME PartitionToPrimary 
AS PARTITION PartitionByMonth 
ALL TO ([PRIMARY]);

CREATE CLUSTERED INDEX idx_hubba_somedate ON #hubbabubba (somedate)
ON PartitionToPrimary (somedate);

GO

Una vez creados el esquema de particionamiento, la función y el índice agrupado, puedes poblar la tabla temporal con datos. En el script de ejemplo, genero fechas aleatorias dentro del rango del año 2014 e las inserto en la tabla #hubbabubba. Esto asegura que los datos estén distribuidos en las particiones.

/* 
Establece un rango de fechas para la generación de fechas aleatorias y la población de la tabla
Solo tenemos la tabla particionada para el año actual, por lo que es esencial limitar las fechas a este
año
*/

DECLARE @BeginDate DATE = '2014-01-01',
        @EndDate DATE = '2014-12-31'

/* Pobla algunos datos */

INSERT INTO #hubbabubba (somechar, somedate)
SELECT '¿Se aplicó mi predeterminado?', DATEADD(DAY, RAND(CHECKSUM(NEWID())) * (1 + DATEDIFF(DAY, @EndDate, @BeginDate)), @EndDate)
GO 5000

Finalmente, para ver la distribución de datos en las particiones, puedes usar un script proporcionado por Kendra Little. Este script consulta las tablas del sistema para obtener información sobre las particiones y su distribución de datos.

SELECT  OBJECT_NAME(si.object_id) AS object_name,
        pf.name AS pf_name,
        ps.name AS partition_scheme_name,
        p.partition_number,
        rv.value AS range_value,
        SUM(CASE WHEN si.index_id IN (1, 0) THEN p.rows ELSE 0 END) AS num_rows,
        SUM(dbps.reserved_page_count) * 8 / 1024. AS reserved_mb_all_indexes,
        SUM(CASE ISNULL(si.index_id, 0) WHEN 0 THEN 0 ELSE 1 END) AS num_indexes
FROM    sys.destination_data_spaces AS dds
        INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
        INNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
        INNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
        LEFT OUTER JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
            AND dds.destination_id = CASE pf.boundary_value_on_right WHEN 0 THEN rv.boundary_id
                                    ELSE rv.boundary_id + 1 END
        LEFT OUTER JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
        LEFT OUTER JOIN sys.partitions AS p ON si.object_id = p.object_id
            AND si.index_id = p.index_id
            AND dds.destination_id = p.partition_number
        LEFT OUTER JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
            AND p.partition_id = dbps.partition_id
WHERE p.OBJECT_ID = OBJECT_ID('#hubbabubba','U')
GROUP BY p.partition_number, pf.name, ps.name, si.object_id, rv.value;
GO

Entonces, ¿por qué querrías particionar una tabla temporal? Honestamente, no puedo pensar en un escenario de producción donde sea beneficioso. Sin embargo, particionar una tabla temporal puede ser útil para fines de demostración o cuando necesitas probar el rendimiento de la partición en tu entorno.

Espero que este artículo haya arrojado algo de luz sobre el concepto de particionar tablas temporales en SQL Server. Si tienes alguna pregunta o conoces otros casos de uso para esta característica, por favor avísame.