Compartir a través de

  • Artículo

Usar columnas dispersas

Las columnas dispersas son columnas normales que disponen de un almacenamiento optimizado para los valores NULL. Este tipo de columnas reducen los requisitos de espacio de los valores NULL a costa de una mayor sobrecarga a la hora de recuperar valores no NULL. Considere la posibilidad de utilizar columnas dispersas si el ahorro de espacio se sitúa entre el 20 y el 40 por ciento. Las columnas dispersas y los conjuntos de columnas se definen mediante las instrucciones CREATE TABLE o ALTER TABLE.

Las columnas dispersas se pueden utilizar con conjuntos de columnas e índices filtrados:

  • Conjuntos de columnas

    Las instrucciones INSERT, UPDATE y DELETE pueden hacer referencia a las columnas dispersas por nombre. Sin embargo, también es posible ver y usar todas las columnas dispersas de una tabla que se han combinado en una única columna XML. Esta columna se denomina conjunto de columnas. Para obtener más información sobre los conjuntos de columnas, vea Utilizar conjuntos de columnas.

  • Índices filtrados

    Dado que las columnas dispersas tienen muchas filas con valores NULL, son especialmente adecuadas para los índices filtrados. Un índice filtrado en una columna dispersa solo puede indizar las filas que contienen valores. Esto permite crear un índice más pequeño y eficaz. Para obtener más información, vea Directrices generales para diseñar índices filtrados.

Las columnas dispersas y los índices filtrados permiten a las aplicaciones, como Windows SharePoint Services, almacenar y tener acceso de una forma más eficiente a un gran número de propiedades definidas por el usuario usando SQL Server 2008.

Propiedades de las columnas dispersas

Las columnas dispersas tienen las características siguientes:

  • SQL Server Database Engine (Motor de base de datos de SQL Server) utiliza la palabra clave SPARSE en una definición de columna para optimizar el almacenamiento de valores en dicha columna. Por consiguiente, cuando el valor de la columna es NULL para cualquier fila de la tabla, los valores no requieren ningún almacenamiento.

  • Las vistas de catálogo para una tabla con columnas dispersas son las mismas que para una tabla típica. La vista de catálogo sys.columns contiene una fila por cada columna de la tabla e incluye un conjunto de columnas si se ha definido alguno.

  • Las columnas dispersas son una propiedad de la capa de almacenamiento, en lugar de la tabla lógica. Por consiguiente, una instrucción SELECT...INTO no copia sobre la propiedad de columna dispersa en una nueva tabla.

  • La función COLUMNS_UPDATED devuelve un valor varbinary para indicar todas las columnas que se actualizaron durante una acción DML. Los bits devueltos por la función COLUMNS_UPDATED son los siguientes:

    • Cuando una columna dispersa se actualiza de forma explícita, el bit correspondiente para dicha columna se establece en 1 y el bit para el conjunto de columnas se establece en 1.

    • Cuando un conjunto de columnas se actualiza de forma explícita, el bit para dicho conjunto de columnas se establece en 1 y los bits para todas las columnas dispersas de la tabla se establecen en 1.

    • En las operaciones de inserción, todos los bits se establecen en 1.

    Para obtener más información acerca de los conjuntos de columnas, vea Utilizar conjuntos de columnas.

Los tipos de datos siguientes no se pueden especificar como SPARSE:

geography

text

geometry

timestamp

image

user-defined data types

ntext

Ahorro de espacio calculado para cada tipo de datos

Las columnas dispersas requieren más espacio de almacenamiento para los valores distintos de NULL que el requerido para datos idénticos no marcados como SPARSE. Las tablas siguientes muestran el uso de espacio para cada tipo de datos. La columna Porcentaje de NULL indica qué porcentaje de los datos deben ser NULL para un ahorro de espacio neto de un 40 por ciento.

Tipos de datos de longitud fija

Tipo de datos

Bytes no dispersos

Bytes dispersos

Porcentaje de NULL

bit

0.125

4.125

98%

tinyint

1

5

86%

smallint

2

6

76%

int

4

8

64%

bigint

8

12

52%

real

4

8

64%

float

8

12

52%

smallmoney

4

8

64%

money

8

12

52%

smalldatetime

4

8

64%

datetime

8

12

52%

uniqueidentifier

16

20

43%

date

3

7

69%

Tipos de datos con longitud dependiente de la precisión

Tipo de datos

Bytes no dispersos

Bytes dispersos

Porcentaje de NULL

datetime2(0)

6

10

57%

datetime2(7)

8

12

52%

time(0)

3

7

69%

time(7)

5

9

60%

datetimetoffset(0)

8

12

52%

datetimetoffset (7)

10

14

49%

decimal/numeric(1,s)

5

9

60%

decimal/numeric(38,s)

17

21

42%

vardecimal(p,s)

Utilice el tipo decimal como estimación conservadora.

Tipos de datos con longitud dependiente de los datos

Tipo de datos

Bytes no dispersos

Bytes dispersos

Porcentaje de NULL

sql_variant

Varía con el tipo de datos subyacente

varchar O bien char

2*

4*

60%

nvarchar O bien nchar

2*

4*+

60%

varbinary O bien binary

2*

4*

60%

xml

2*

4*

60%

hierarchyid

2*

4*

60%

*La longitud es igual a la media de los datos incluidos en el tipo, más 2 o 4 bytes.

Restricciones de uso de las columnas dispersas

Las columnas dispersas pueden adoptar cualquier tipo de datos de SQL Server y comportarse como cualquier otra columna, con las restricciones siguientes:

  • Deben aceptar valores NULL y no pueden tener las propiedades ROWGUIDCOL ni IDENTITY. No pueden adoptar los tipos de datos siguientes: text, ntext, image, timestamp, tipo de datos definido por el usuario, geometry ni geography; ni tener el atributo FILESTREAM.

  • No pueden tener un valor predeterminado.

  • No se pueden enlazar a una regla.

  • Aunque una columna calculada puede contener una columna dispersa, una columna calculada no se puede marcar como SPARSE.

  • Las columnas dispersas no pueden formar parte de un índice clúster o de un índice de clave principal único. Sin embargo, tanto las columnas calculadas persistentes como las no persistentes que se definen en columnas dispersas sí pueden formar parte de la clave de un índice clúster.

  • Las columnas dispersas no se pueden utilizar como clave de partición de un índice clúster o montón. Sin embargo, sí se pueden utilizar como la clave de partición de un índice no clúster.

  • Las columnas dispersas no pueden formar parte de los tipos de tabla definidos por el usuario que se utilizan en variables de tabla y parámetros con valores de tabla.

  • Las columnas dispersas son incompatibles con la compresión de datos. Por consiguiente, no se pueden agregar columnas dispersas a tablas comprimidas, ni se pueden comprimir tablas que contengan columnas dispersas.

  • Para cambiar una columna de dispersa a no dispersa o viceversa es preciso cambiar el formato de almacenamiento de la columna. El motor de base de datos de SQL Server usa el siguiente procedimiento para realizar este cambio:

    1. Agrega una nueva columna a la tabla con el nuevo tamaño y formato de almacenamiento.

    2. Para cada fila de la tabla, actualiza y copia el valor almacenado de la columna antigua en la columna nueva.

    3. Quita la columna antigua del esquema de la tabla.

    4. Vuelve a generar la tabla para reclamar el espacio utilizado por la columna antigua.

    [!NOTA]

    Pueden producirse errores en el paso 2 si el tamaño de los datos de la fila supera el tamaño máximo de fila permitido. Este tamaño incluye el tamaño de los datos almacenados en la columna antigua y los datos actualizados almacenados en la columna nueva. Este límite es de 8060 bytes para las tablas que no contienen ninguna columna dispersa o de 8018 bytes para las tablas que contienen columnas dispersas. Este error puede producirse aunque todas las columnas coincidentes se hayan insertado de manera no consecutiva. Para obtener más información, vea Datos de desbordamiento de fila superiores a 8 KB.

  • Cuando convierta una columna no dispersa en una columna dispersa, tenga en cuenta que esta consumirá más espacio para los valores distintos de NULL. Cuando una fila está cerca del límite de tamaño máximo, se puede producir un error en la operación.

Tecnologías de SQL Server que admiten columnas dispersas

En esta sección se describe la compatibilidad de las columnas dispersas en las siguientes tecnologías de SQL Server:

  • Replicación transaccional

    La replicación transaccional admite el uso de columnas dispersas, pero no admite los conjuntos de columnas, que se pueden usar con las columnas dispersas. Para obtener más información sobre los conjuntos de columnas, vea Utilizar conjuntos de columnas.

    La replicación del atributo SPARSE viene determinada por una opción de esquema especificada mediante sp_addarticle o el cuadro de diálogo Propiedades del artículo de SQL Server Management Studio. Las versiones anteriores de SQL Server no admiten columnas dispersas. Si tiene que replicar los datos a una versión anterior, no olvide especificar que el atributo SPARSE no se debe replicar.

    En las tablas que se publican, no es posible agregar nuevas columnas dispersas ni cambiar la propiedad SPARSE de una columna existente. Si fuera necesario realizar este tipo de operación, quite la publicación y vuelva a crearla.

  • Replicación de mezcla

    La replicación de mezcla no admite el uso de columnas dispersas ni de conjuntos de columnas.

  • Seguimiento de cambios

    El seguimiento de cambios admite el uso de columnas dispersas y de conjuntos de columnas. Cuando se actualiza un conjunto de columnas en una tabla, el seguimiento de cambios lo considera como una actualización de la fila completa. No se proporciona ningún seguimiento de cambios detallado que permita obtener el número exacto de columnas dispersas que se actualizan mediante la operación de actualización del conjunto de columnas. Si las columnas dispersas se actualizan de forma explícita mediante una instrucción DML, el seguimiento de cambios funcionará normalmente en ellas y permitirá identificar el número exacto de columnas modificadas.

  • Captura de datos modificados

    La captura de datos modificados admite el uso de columnas dispersas, pero no de conjuntos de columnas.

Ejemplos

En este ejemplo, una tabla de documentos contiene un conjunto común que tiene las columnas DocID y Title. El grupo de producción desea tener una columna ProductionSpecification y una columna ProductionLocation para todos los documentos de producción. El grupo de marketing desea tener una columna MarketingSurveyGroup para los documentos de marketing. El código de este ejemplo crea una tabla que usa columnas dispersas, inserta dos filas en dicha tabla y, a continuación, selecciona datos en ella.

[!NOTA]

Esta tabla solo tiene cinco columnas para facilitar su visualización y lectura. Si se establece la opción ANSI_NULL_DFLT_ON, es opcional declarar las columnas dispersas como columnas que aceptan valores NULL.

USE AdventureWorks
GO

CREATE TABLE DocumentStore
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL ) ;
GO

INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27)
GO

INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35')
GO

La selección de todas las columnas de la tabla devuelve un conjunto de resultados normal.

SELECT * FROM DocumentStore ;

Éste es el conjunto de resultados.

DocID  Title        ProductionSpecification  ProductionLocation  MarketingSurveyGroup

1      Tire Spec 1  AXZZ217                  27                  NULL

2      Survey 2142  NULL                     NULL                Men 25-35

Dado que el departamento de producción no está interesado en los datos de marketing, desean usar una lista de columnas que devuelva solo las columnas de interés, como se muestra en la consulta siguiente.

SELECT DocID, Title, ProductionSpecification, ProductionLocation 
FROM DocumentStore 
WHERE ProductionSpecification IS NOT NULL ;

Éste es el conjunto de resultados.

DocID  Title        ProductionSpecification  ProductionLocation

1      Tire Spec 1  AXZZ217                  27

Historial de cambios

Contenido actualizado

Se han corregido los tamaños de datos que se indican en la tabla de tipos dependientes de los datos.