Sparse columns y column sets

Una de las limitaciones con las que algunas veces nos hemos encontrado es el límite máximo de 1024 columnas por tabla. Cuando nos encontramos con este problema en versiones anteriores a SQL Server 2008 debíamos recurrir a alternativas como el particionado vertical de tablas o esquemas abiertos con pivotado y despivotado (open-schema). Por otra parte, el límite máximo de tamaño de fila (8060 bytes) se suele alcanzar antes que el del número de columnas.

Con SQL Server 2008 disponemos de dos novedades, las “columnas dispersas” (sparse columns) y los conjuntos de columnas (column sets).

La primera de ellas nos permite el almacenar de forma eficiente tablas que contienen columnas con muchos valores a nulo. Esta situación suele darse habitualmente en los casos en los cuales tenemos muchas columnas. SQL Server almacena de forma compactada en mapas de bits aquellas columnas que son anulables (1 byte por cada 8 columnas anulables). Cuando el porcentaje de valores nulos en una columna es significativamente alto, el uso de columnas dispersas es más eficiente que el mapa de bits. Con las columnas dispersas los valores nulos no ocupan espacio mientras que cuando tenemos un valor asociado tenemos una penalización extra de 4 bytes cuando sí tenemos valor. Esto hace que sea útil únicamente cuando el porcentaje de nulos sea bastante significativo. Además, el trabajo con columnas dispersas implica cierta penalización de CPU que, aunque baja, habrá que considerar adecuadamente. Para crear columnas dispersas bastará con añadir el atributo “sparse” en la definición de la tabla:

CREATE TABLE sparse (    

c_1    int sparse,

    c_2    int sparse,

    c_3    int sparse,

    c_4    int sparse,

    c_5    int sparse,

    c_6    int sparse,

    c_7    int sparse,

    c_8    int sparse,

    c_9    int sparse

)

La segunda de las novedades, los conjuntos de columnas, nos permiten salvar el límite de 1024 columnas existente. Las columnas dispersas nos permiten que tablas con muchas columnas no sobrepasen el límite de tamaño de fila gracias a esta “compresión” que se produce al no necesitar espacio para los nulos. Los conjuntos de columnas son los que realmente nos permiten ir más allá en el número de columnas (hasta 30000). Este límite no afecta al número máximo de índices que está limitado a 1000 por tabla (250 en SQL Server 2005). Al crear el conjunto de columnas éstas se almacenarán internamente en un XML especializado. De hecho, esta es una alternativa que se ha utilizado en 2005 de forma manual por algunos usuarios para implementar esta funcionalidad. Pensad por ejemplo en una tabla “entidades” que contenga entidades de diferentes tipos y, por tanto, con diferentes propiedades. Esto obviamente va contra un diseño ER tradicional pero ofrece una gran flexibilidad a cambio de potenciales pérdidas de rendimiento. En SQL Server 2008 para crear este conjunto de columnas a partir de columnas dispersas la sintaxis es la siguiente:

CREATE TABLE sparse2 (    

c_1    int sparse,

    c_2    int sparse,

    c_3    int sparse,

    c_4    int sparse,

    c_5    int sparse,

    c_6    int sparse,

    c_7    int sparse,

    c_8    int sparse,

    c_9    int sparse,

c_xml xml column_set FOR all_sparse_columns

)

El resultado es que tenemos una columna (c_xml) que actúa como almacenamiento alternativo para los valores de las columnas. Curiosamente el uso de esta funcionalidad implica que la típica consulta “SELECT * FROM tabla WHERE 1=2” para obtener los metadatos deje de funcionar. Si lanzamos dicha consulta contra la tabla vacía obtendremos esto:

Esto hará que para obtener los metadatos deberemos utilizar consultas sobre las vistas apropiadas de sistema y no utilizar “trucos” de este tipo. Si creamos una tabla (sparse3) al límite de capacidad con 30000 columnas (29999 sparse + 1 column_set) e insertamos algunos datos de forma dispersa, el uso del SELECT * devuelve un XML y no las columnas como podríamos esperar:

INSERT INTO sparse3 (c_29999,c_10000) VALUES (1,2);

SELECT * FROM sparse3;

SELECT c_29999,c_10000,c_1,c_2 FROM sparse3;

Finalmente, al igual que hemos visto que el límite de índices se mantiene en 1000, el límite de columnas para la cláusula SELECT se fija en 4096 por lo que cualquier consulta que intente mostrar más ed 4096 columnas nos devolverá un error:

Msg 1056, Level 15, State 1, Line 1

The number of elements in the select list exceeds the maximum allowed number of 4096 elements.

Por tanto no debemos ver esta nueva funcionalidad como una ampliación en el número de columnas que puede manejar SQL Server por tabla en absoluto. Tanto las columnas dispersas como los conjuntos de columnas cubren unas necesidades muy específicas y poseen a su vez restricciones y limitaciones en su uso a tener en cuenta. Algunos escenarios típicos que se plantean en gestores de contenidos, como MOSS, pueden verse beneficiados por esta nueva funcionalidad.

Para más información podéis consultas las páginas de BOL correspondientes:

• Sparse columns: http://technet.microsoft.com/en-us/library/cc280604.aspx
• Column sets: http://technet.microsoft.com/en-us/library/cc280521.aspx