Blog del CIIN

Blog del Centro de Innovación en Integración de Cantabria

This Blog

Syndication

Search

Tags

Community

Email Notifications

Archives

Enlaces Recomendados

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.

Published 24/11/2008 23:02 por Juan Carlos González Martín

Archivado en:
Comparte este post:

Comentarios

# re: SQL Server 2008: Sparse Columns!@ Tuesday, November 25, 2008 9:04 PM

¿Qué desventaja tiene usar SPARSE? Porque si solo son ventajas, ¿por qué no se definen todos los campos así?

andrechi

# re: SQL Server 2008: Sparse Columns!@ Tuesday, November 25, 2008 9:12 PM

Buenas Andrechi,

La verdad es que las SPARSE COLUMNS son un recurso muy elegante para tablas de grandes dimensiones.

Un saludo

JC's

Juan Carlos González Martín

# re: SQL Server 2008: Sparse Columns!@ Friday, May 15, 2009 10:27 PM

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 :)

Nicolas Eduardo Neri Hernandez