SQL Server 2008: Sparse Columns!

Una de las mejoras que introduce SQL Server 2008 en cuanto a tipos de datos se refiere es la de una mejor implementación de las denominadas sparse columns, es decir, aquellas que almacenan de manera frecuente valores nulos. Dos son las características fundamentales de las sparse columns en SQL Server 2008:

  • No consumen espacio en disco (vs versiones anteriores).
  • La clave está en el uso del atributo SPARSE en la definición de las columnas.

Para ver el comportamiento de las sparse columns, vamos a partir de un escenario en el que a priori tiene sentido la definición de este tipo de columnas: tablas con un número muy elevado de columnas (por ejemplo, 1000 columnas). Nuestra base de datos (BD) de pruebas tendrá dos tablas, una en la que no se usa el atributo SPARSE en la definición de las columnas, y otra en la que sí se utiliza:

  • Creamos en primer lugar la tabla en la que no utilizamos el atributo SPARSE en la definición de sus columnas.

CREATE TABLE TableWithoutSparseColumns (

   DocID INT IDENTITY, DocName VARCHAR (100), DocType INT,

   c0001 INT NULL, c0002 INT NULL, c0003 INT NULL, c0004 INT NULL, c0005 INT NULL,

….

c0991 INT NULL, c0992 INT NULL, c0993 INT NULL, c0994 INT NULL, c0995 INT NULL,

   c0996 INT NULL, c0997 INT NULL, c0998 INT NULL, c0999 INT NULL,c1000 INT NULL

   );

GO

  • Creamos a continuación la tabla en la que marcamos con el atributo SPARSE sus columnas

CREATE TABLE TableWithSparseColumns (

   DocID INT IDENTITY, DocName VARCHAR (100), DocType INT,

   c0001 INT SPARSE NULL, c0002 INT SPARSE NULL, c0003 INT SPARSE NULL, c0004 INT SPARSE NULL, c0005 INT SPARSE NULL,

….

   c0991 INT SPARSE NULL, c0992 INT SPARSE NULL, c0993 INT SPARSE NULL, c0994 INT SPARSE NULL, c0995 INT SPARSE NULL,

   c0996 INT SPARSE NULL, c0997 INT SPARSE NULL, c0998 INT SPARSE NULL, c0999 INT SPARSE NULL,c1000 INT SPARSE NULL

   );

GO

Una vez creadas las tablas, lo primero que vamos a comprobar es que efectivamente el espacio ocupado por la tabla que no utiliza el atributo SPARSE en la definición de las columnas ocupa más espacio que la tabla que si las utiliza. Para ello:

  • Insertamos datos en las dos tablas:

INSERT INTO TableWithSparseColumns (DocName, Doctype) VALUES (‘aaaa’, 1);

INSERT INTO TableWithSparseColumns (DocName, Doctype, c0945) VALUES (‘bbbb’, 2, 46);

INSERT INTO TableWithSparseColumns (DocName, Doctype, c0334) VALUES (‘cccc’, 3, 44);

INSERT INTO TableWithSparseColumns (DocName, Doctype, c0233, c0234) VALUES (‘dddd’, 4, 12, 34);

INSERT INTO TableWithSparseColumns (DocName, Doctype, c0233, c0234,c0235,c0236) VALUES (‘eeee’, 4, 12, 34, 46, 66);

GO

 

INSERT INTO TableWithoutSparseColumns (DocName, Doctype) VALUES (‘aaaa’, 1);

INSERT INTO TableWithoutSparseColumns (DocName, Doctype, c0945) VALUES (‘bbbb’, 2, 46);

INSERT INTO TableWithoutSparseColumns (DocName, Doctype, c0334) VALUES (‘cccc’, 3, 44);

INSERT INTO TableWithoutSparseColumns (DocName, Doctype, c0233, c0234) VALUES (‘dddd’, 4, 12, 34);

INSERT INTO TableWithoutSparseColumns (DocName, Doctype, c0233, c0234,c0235,c0236) VALUES (‘eeee’, 4, 12, 34, 46, 66);

GO

  • Comprobamos el espacio que ocupan los datos guardados mediante el siguiente script:

SELECT [avg_record_size_in_bytes], [page_count] FROM sys.dm_db_index_physical_stats (

   DB_ID (‘SPARSE_DB’), OBJECT_ID (‘TableWithoutSparseColumns’), NULL, NULL, ‘DETAILED’);

 

SELECT [avg_record_size_in_bytes], [page_count] FROM sys.dm_db_index_physical_stats (

   DB_ID (‘SPARSE_DB’), OBJECT_ID (‘TableWithSparseColumns’), NULL, NULL, ‘DETAILED’);

GO

El resultado que se obtiene es el esperado: la tabla en la que hemos utilizado el atributo SPARSE ocupa mucho menos espacios en bytes y número de páginas que la tabla en la que no se ha utilizado.

image

Si hacemos un SELECT de algunas de las columnas con valores nulos, vemos en cambio que ambas tablas almacenan el valor nulo. Sin embargo, en el caso de la tabla en la que se ha usado el atributo SPARSE, estos valores nulos no ocupan espacio en disco.

image

Finalmente, otra de las novedades que viene con las sparse columns de SQL Server 2008 es la posibilidad de marcar todas las columnas marcadas con el atributo SPARSE con el atributo XML COLUMN_SET, lo que nos permitirá devolver de manera sencilla los valores no nulos que tengamos insertados en la tabla. La definición de la tabla sería la siguiente:

drop TABLE TableWithSParseColumns

 

CREATE TABLE TableWithSparseColumns (

   DocID INT IDENTITY, DocName VARCHAR (100), DocType INT,

   c0001 INT SPARSE NULL, c0002 INT SPARSE NULL, c0003 INT SPARSE NULL, c0004 INT SPARSE NULL, c0005 INT SPARSE NULL,

   c0991 INT SPARSE NULL, c0992 INT SPARSE NULL, c0993 INT SPARSE NULL, c0994 INT SPARSE NULL, c0995 INT SPARSE NULL,

   c0996 INT SPARSE NULL, c0997 INT SPARSE NULL, c0998 INT SPARSE NULL, c0999 INT SPARSE NULL,c1000 INT SPARSE NULL,

   SparseColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

GO

Para comprobar la utilidad de XML COLUMN_SET FOR ALL_SPARSE_COLUMNS:

  • Insertamos datos en la tabla.

INSERT INTO TableWithSparseColumns (DocName, Doctype) VALUES (‘aaaa’, 1);

INSERT INTO TableWithSparseColumns (DocName, Doctype, c0945) VALUES (‘bbbb’, 2, 46);

INSERT INTO TableWithSparseColumns (DocName, Doctype, c0334) VALUES (‘cccc’, 3, 44);

INSERT INTO TableWithSparseColumns (DocName, Doctype, c0233, c0234) VALUES (‘dddd’, 4, 12, 34);

INSERT INTO TableWithSparseColumns (DocName, Doctype, c0233, c0234,c0235,c0236) VALUES (‘eeee’, 4, 12, 34, 46, 66);

GO

  • Ejecutamos la siguiente sentencia T-SQL.

select * from TableWithSparseColumns

Y este es el resultado obtenido:

image

Y hasta aquí llega lo que os quería contar sobre las SPARSE COLUMNS en SQL Server 2008. Espero que el post os haya resultado interesante.

Publicado por

Juan Carlos González

Juan Carlos es Ingeniero de Telecomunicaciones por la Universidad de Valladolid y Diplomado en Ciencias Empresariales por la Universidad Oberta de Catalunya (UOC). Cuenta con más de 12 años de experiencia en tecnologías y plataformas de Microsoft diversas (SQL Server, Visual Studio, .NET Framework, etc.), aunque su trabajo diario gira en torno a SharePoint & Office 365. Juan Carlos es MVP de Office Servers & Services desde 2015 (anteriormente fue reconocido por Microsoft como MVP de Office 365 y MVP de SharePoint Server desde 2008 hasta 2015), coordinador del grupo de usuarios .NET de Cantabria (Nuberos.Net, www.nuberos.es), co-fundador y coordinador del Grupo de Usuarios de SharePoint de España (SUGES, www.suges.es), así como co-director de la revista gratuita en castellano sobre SharePoint CompartiMOSS (www.compartimoss.com). Hasta la fecha, ha publicado 8 libros sobre SharePoint & Office 365 y varios artículos en castellano y en inglés sobre ambas plataformas.

3 comentarios en “SQL Server 2008: Sparse Columns!”

  1. Que tal Andrechi

    Pues segun he leido, la propiedad de SPARSE no se puede aplicar en todas las columnas existentes en las tablas de las DB ya que tiene algunas restricciones para su uso.

    Por ejemplo no se puede habilitar para:
    – las columnas con tipos de datos TEXT, NTEXT, IMAGE, TIMESTAMP, GEOMETRY, GEOGRAPHY, y los definidos por el usuario.
    – columnas calculadas
    – columnas con propiedad de identity o rowguidcol
    – columnas que formen parte de llaves primarias o indice cluster.
    – columnas con tipo dato varbinary(max) con la propiedad FILESTREAM.

    Y por otro lado, SQL Server 2008 permite la compresion de registros y paginas para indices, vistas indexadas y tablas que no tengan columnas con la propiedad SPARSE.

    P.D. Excelente articulo 🙂

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *