Usar columnas dispersasUse Sparse Columns

SE APLICA A: síSQL Server síAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Las columnas dispersas son columnas normales que disponen de un almacenamiento optimizado para los valores NULL.Sparse columns are ordinary columns that have an optimized storage for null values. 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.Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Considere la posibilidad de utilizar columnas dispersas si el ahorro de espacio se sitúa entre el 20 y el 40 por ciento.Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Las columnas dispersas y los conjuntos de columnas se definen mediante las instrucciones CREATE TABLE o ALTER TABLE .Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

Las columnas dispersas se pueden utilizar con conjuntos de columnas e índices filtrados:Sparse columns can be used with column sets and filtered indexes:

  • Conjuntos de columnasColumn sets

    Las instrucciones INSERT, UPDATE y DELETE pueden hacer referencia a las columnas dispersas por nombre.INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. 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.However, you can also view and work with all the sparse columns of a table that are combined into a single XML column. Esta columna se denomina conjunto de columnas.This column is called a column set. Para obtener más información sobre los conjuntos de columnas, vea Usar conjuntos de columnas.For more information about column sets, see Use Column Sets.

  • Índices filtradosFiltered indexes

    Dado que las columnas dispersas tienen muchas filas con valores NULL, son especialmente adecuadas para los índices filtrados.Because sparse columns have many null-valued rows, they are especially appropriate for filtered indexes. Un índice filtrado en una columna dispersa solo puede indizar las filas que contienen valores.A filtered index on a sparse column can index only the rows that have populated values. Esto permite crear un índice más pequeño y eficaz.This creates a smaller and more efficient index. Para obtener más información, consulte Create Filtered Indexes.For more information, see Create Filtered Indexes.

Las columnas dispersas y los índices filtrados permiten a las aplicaciones, como Windows SharePoint ServicesWindows 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 ServerSQL Server.Sparse columns and filtered indexes enable applications, such as Windows SharePoint ServicesWindows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL ServerSQL Server.

Propiedades de las columnas dispersasProperties of Sparse Columns

Las columnas dispersas tienen las características siguientes:Sparse columns have the following characteristics:

  • Motor de base de datos de SQL ServerSQL Server Database Engine utiliza la palabra clave SPARSE en una definición de columna para optimizar el almacenamiento de valores en dicha columna.The Motor de base de datos de SQL ServerSQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Por consiguiente, cuando el valor de la columna es NULL para cualquier fila de la tabla, los valores no requieren ningún almacenamiento.Therefore, when the column value is NULL for any row in the table, the values require no storage.

  • Las vistas de catálogo para una tabla con columnas dispersas son las mismas que para una tabla típica.Catalog views for a table that has sparse columns are the same as for a typical table. 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.The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined.

  • Las columnas dispersas son una propiedad del nivel de almacenamiento, en lugar de la tabla lógica.Sparse columns are a property of the storage layer, rather than the logical table. Por tanto, una instrucción SELECT...INTO no copia la propiedad de columna dispersa a una tabla nueva.Therefore a SELECT...INTO statement does not copy over the sparse column property into a new table.

  • La función COLUMNS_UPDATED devuelve un valor varbinary para indicar todas las columnas que se actualizaron durante una acción DML.The COLUMNS_UPDATED function returns a varbinary value to indicate all the columns that were updated during a DML action. Los bits devueltos por la función COLUMNS_UPDATED son los siguientes:The bits that are returned by the COLUMNS_UPDATED function are as follows:

    • 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.When a sparse column is explicitly updated, the corresponding bit for that sparse column is set to 1, and the bit for the column set is set to 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.When a column set is explicitly updated, the bit for the column set is set to 1, and the bits for all the sparse columns in that table are set to 1.

    • En las operaciones de inserción, todos los bits se establecen en 1.For insert operations, all bits are set to 1.

    Para obtener más información sobre los conjuntos de columnas, vea Usar conjuntos de columnas.For more information about columns sets, see Use Column Sets.

Los tipos de datos siguientes no se pueden especificar como SPARSE:The following data types cannot be specified as SPARSE:

geographygeography textotext
geometrygeometry timestamptimestamp
imagenimage tipos de datos definidos por el usuariouser-defined data types
ntextntext

Ahorro de espacio calculado para cada tipo de datosEstimated Space Savings by Data Type

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.Sparse columns require more storage space for nonnull values than the space required for identical data that is not marked SPARSE. Las tablas siguientes muestran el uso de espacio para cada tipo de datos.The following tables show the space usage for each data type. 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.The NULL Percentage column indicates what percent of the data must be NULL for a net space savings of 40 percent.

Tipos de datos de longitud fijaFixed-Length Data Types

Tipo de datosData type Bytes no dispersosNonsparse bytes Bytes dispersosSparse bytes Porcentaje de NULLNULL percentage
bitbit 0.1250.125 55 98%98%
tinyinttinyint 11 55 86%86%
smallintsmallint 22 66 76%76%
intint 44 88 64%64%
bigintbigint 88 1212 52%52%
realreal 44 88 64%64%
floatfloat 88 1212 52%52%
smallmoneysmallmoney 44 88 64%64%
moneymoney 88 1212 52%52%
smalldatetimesmalldatetime 44 88 64%64%
datetimedatetime 88 1212 52%52%
uniqueidentifieruniqueidentifier 1616 2020 43%43%
datedate 33 77 69%69%

Tipos de datos de longitud dependiente de la precisiónPrecision-Dependent-Length Data Types

Tipo de datosData type Bytes no dispersosNonsparse bytes Bytes dispersosSparse bytes Porcentaje de NULLNULL percentage
datetime2(0)datetime2(0) 66 1010 57%57%
datetime2(7)datetime2(7) 88 1212 52%52%
time(0)time(0) 33 77 69%69%
time(7)time(7) 55 99 60%60%
datetimetoffset(0)datetimetoffset(0) 88 1212 52%52%
datetimetoffset (7)datetimetoffset (7) 1010 1414 49%49%
decimal/numeric(1,s)decimal/numeric(1,s) 55 99 60%60%
decimal/numeric(38,s)decimal/numeric(38,s) 1717 2121 42%42%
vardecimal(p,s)vardecimal(p,s) Utilice el tipo decimal como un cálculo moderado.Use the decimal type as a conservative estimate.

Tipos de datos de longitud dependiente de los datosData-Dependent-Length Data Types

Tipo de datosData type Bytes no dispersosNonsparse bytes Bytes dispersosSparse bytes Porcentaje de NULLNULL percentage
sql_variantsql_variant Varía con el tipo de datos subyacenteVaries with the underlying data type
varchar o charvarchar or char 2*2* 4*4* 60%60%
nvarchar o ncharnvarchar or nchar 2*2* 4*+4*+ 60%60%
varbinary o binaryvarbinary or binary 2*2* 4*4* 60%60%
xmlxml 2*2* 4*4* 60%60%
hierarchyidhierarchyid 2*2* 4*4* 60%60%

*La longitud es igual a la media de los datos incluidos en el tipo, más 2 o 4 bytes.*The length is equal to the average of the data that is contained in the type, plus 2 or 4 bytes.

Sobrecarga en memoria necesaria para las actualizaciones de columnas dispersasIn-Memory Overhead Required for Updates to Sparse Columns

A la hora de diseñar tablas con columnas dispersas, tenga en cuenta que se necesita una sobrecarga adicional de 2 bytes para cada columna dispersa que no sea NULL de la tabla cuando se está actualizando una fila.When designing tables with sparse columns, keep in mind that an additional 2 bytes of overhead are required for each non-null sparse column in the table when a row is being updated. Debido a este requisito de memoria adicional, se puede producir inesperadamente un error 576 en las actualizaciones cuando el tamaño total de fila (incluida esta sobrecarga de memoria) es superior a 8019 y no se puede insertar ninguna columna de manera no consecutiva.As a result of this additional memory requirement, updates can fail unexpectedly with error 576 when the total row size, including this memory overhead, exceeds 8019, and no columns can be pushed off the row.

Considere el ejemplo de una tabla que tiene 600 columnas dispersas de tipo bigint.Consider the example of a table that has 600 sparse columns of type bigint. Si hay 571 columnas no NULL, el tamaño total en disco es de 571 * 12 = 6852 bytes.If there are 571 non-null columns, then the total size on disk is 571 * 12 = 6852 bytes. Después de incluir la sobrecarga de fila adicional y el encabezado de columna dispersa, asciende a unos 6895 bytes.After including additional row overhead and the sparse column header, this increases to around 6895 bytes. La página todavía tiene 1124 bytes disponibles en disco.The page still has around 1124 bytes available on disk. Esto puede dar la impresión de que se pueden actualizar correctamente columnas adicionales.This can give the impression that additional columns can be updated successfully. Pero durante la actualización hay una sobrecarga adicional en memoria de 2*(número de columnas dispersas no NULL).However, during the update, there is additional overhead in memory which is 2*(number of non-null sparse columns). En este ejemplo, incluida la sobrecarga adicional (2 * 571 = 1142 bytes) el tamaño de fila en disco aumenta hasta 8037 bytes.In this example, including the additional overhead - 2 * 571 = 1142 bytes - increases the row size on disk to around 8037 bytes. Este tamaño supera el tamaño máximo permitido de 8019 bytes.This size exceeds the maximum allowed size of 8019 bytes. Puesto que todas las columnas tienen tipos de datos de longitud fija, no se pueden insertar de manera no consecutiva.Since all the columns are fixed-length data types, they cannot be pushed off the row. Por tanto, se producirá el error 576 en la actualización.As a result, the update fails with the 576 error.

Restricciones de uso de las columnas dispersasRestrictions for Using Sparse Columns

Las columnas dispersas pueden adoptar cualquier tipo de datos de SQL ServerSQL Server y comportarse como cualquier otra columna, con las restricciones siguientes:Sparse columns can be of any SQL ServerSQL Server data type and behave like any other column with the following restrictions:

  • Deben aceptar valores NULL y no pueden tener las propiedades ROWGUIDCOL ni IDENTITY.A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties. No pueden adoptar los tipos de datos siguientes: text, ntext, image, timestamp, tipo de datos definido por el usuario, geometryni geography; ni tener el atributo FILESTREAM.A sparse column cannot be of the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography; or have the FILESTREAM attribute.

  • No pueden tener un valor predeterminado.A sparse column cannot have a default value.

  • No se pueden enlazar a una regla.A sparse column cannot be bound to a rule.

  • Aunque una columna calculada puede contener una columna dispersa, una columna calculada no se puede marcar como SPARSE.Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE.

  • Se puede definir una máscara de datos en una columna dispersa, pero no en una columna dispersa que forma parte de un conjunto de columnas.A data mask can be defined on a sparse column, but not on a sparse column that is part of a column set.

  • Las columnas dispersas no pueden formar parte de un índice clúster o de un índice de clave principal único.A sparse column cannot be part of a clustered index or a unique primary key index. 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.However, both persisted and nonpersisted computed columns that are defined on sparse columns can be part of a clustered key.

  • Las columnas dispersas no se pueden utilizar como clave de partición de un índice clúster o montón.A sparse column cannot be used as a partition key of a clustered index or heap. Sin embargo, sí se pueden utilizar como la clave de partición de un índice no clúster.However, a sparse column can be used as the partition key of a nonclustered index.

  • 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.A sparse column cannot be part of a user-defined table type, which are used in table variables and table-valued parameters.

  • Las columnas dispersas son incompatibles con compresión de datos.Sparse columns are incompatible with data compression. Por consiguiente las columnas dispersas no se pueden agregar a las tablas comprimidas, ni se puede comprimir ninguna tabla que contenga las columnas dispersas.Therefore sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.

  • Para cambiar una columna de dispersa a no dispersa o viceversa es preciso cambiar el formato de almacenamiento de la columna.Changing a column from sparse to nonsparse or nonsparse to sparse requires changing the storage format of the column. El motor de base de datos de SQL Server usa el siguiente procedimiento para realizar este cambio:The SQL Server Database Engine uses the following procedure to accomplish this change:

    1. Agrega una nueva columna a la tabla con el nuevo tamaño y formato de almacenamiento.Adds a new column to the table in the new storage size and format.

    2. Para cada fila de la tabla, actualiza y copia el valor almacenado de la columna antigua en la columna nueva.For each row in the table, updates and copies the value stored in the old column to the new column.

    3. Quita la columna antigua del esquema de la tabla.Removes the old column from the table schema.

    4. Vuelve a compilar la tabla (si no hay índice clúster) o el índice clúster para reclamar el espacio que usa la columna antigua.Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column.

    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.Step 2 can fail when the size of the data in the row exceeds the maximum allowable row size. Este tamaño incluye el tamaño de los datos almacenados en la columna antigua y los datos actualizados almacenados en la columna nueva.This size includes the size of the data stored in the old column and the updated data stored in the new column. 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.This limit is 8060 bytes for tables that do not contain any sparse columns or 8018 bytes for tables that contain sparse columns. Este error puede producirse aunque todas las columnas coincidentes se hayan insertado de manera no consecutiva.This error can occur even if all eligible columns have been pushed off-row.

  • Cuando convierta una columna no dispersa en una columna dispersa, esta consumirá más espacio para los valores distintos de NULL.When you change a non-sparse column to a sparse column, the sparse column will consume more space for non-null values. Cuando una fila está cerca del límite de tamaño máximo, se puede producir un error en la operación.When a row is close to the maximum row size limit, the operation can fail.

Tecnologías de SQL Server que admiten columnas dispersasSQL Server Technologies That Support Sparse Columns

En esta sección se describe la compatibilidad de las columnas dispersas en las siguientes tecnologías de SQL ServerSQL Server :This section describes how sparse columns are supported in the following SQL ServerSQL Server technologies:

  • Replicación transaccionalTransactional replication

    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.Transactional replication supports sparse columns, but it does not support column sets, which can be used with sparse columns. Para obtener más información sobre los conjuntos de columnas, vea Usar conjuntos de columnas.For more information about column sets, see Use Column Sets.

    La replicación del atributo SPARSE viene determinada por una opción de esquema especificada con sp_addarticle o el cuadro de diálogo Propiedades del artículo de SQL Server Management StudioSQL Server Management Studio.The replication of the SPARSE attribute is determined by a schema option that is specified by using sp_addarticle or by using the Article Properties dialog box in SQL Server Management StudioSQL Server Management Studio. Las versiones anteriores de SQL ServerSQL Server no admiten columnas dispersas.Earlier versions of SQL ServerSQL Server do not support sparse columns. Si tiene que replicar los datos a una versión anterior, no olvide especificar que el atributo SPARSE no se debe replicar.If you must replicate data to an earlier version, specify that the SPARSE attribute should not be replicated.

    En las tablas que se publican, no es posible agregar nuevas columnas dispersas ni cambiar la propiedad SPARSE de una columna existente.For tables that are published, you cannot add any new sparse columns to a table or change the sparse property of an existing column. Si fuera necesario realizar este tipo de operación, quite la publicación y vuelva a crearla.If such an operation is required, drop and re-create the publication.

  • Replicación de mezclaMerge replication

    La replicación de mezcla no admite el uso de columnas dispersas ni de conjuntos de columnas.Merge replication does not support sparse columns or column sets.

  • seguimiento de cambiosChange tracking

    El seguimiento de cambios admite el uso de columnas dispersas y de conjuntos de columnas.Change tracking supports sparse columns and column sets. Cuando se actualiza un conjunto de columnas en una tabla, el seguimiento de cambios lo considera como una actualización de la fila completa.When a column set is updated in a table, change tracking treats this as an update to the whole row. 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.No detailed change tracking is provided to obtain the exact set of sparse columns that are updated through the column set update operation. 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.If the sparse columns are updated explicitly through a DML statement, change tracking on them will work ordinarily and can identify the exact set of changed columns.

  • captura de datos modificadosChange data capture

    La captura de datos modificados admite el uso de columnas dispersas, pero no de conjuntos de columnas.Change data capture supports sparse columns, but it does not support column sets.

  • La propiedad sparse de una columna no se conserva al copiar la tabla.The sparse property of a column is not preserved when the table is copied.

EjemplosExamples

En este ejemplo, una tabla de documentos contiene un conjunto común que tiene las columnas DocID y Title.In this example, a document table contains a common set that has the columns DocID and Title. El grupo de producción desea tener una columna ProductionSpecification y una columna ProductionLocation para todos los documentos de producción.The Production group wants a ProductionSpecification and ProductionLocation column for all production documents. El grupo de marketing desea tener una columna MarketingSurveyGroup para los documentos de marketing.The Marketing group wants a MarketingSurveyGroup column for marketing documents. 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.The code in this example creates a table that uses sparse columns, inserts two rows into the table, and then selects data from the table.

Nota

Esta tabla solo tiene cinco columnas para facilitar su visualización y lectura.This table has only five columns to make it easier to display and read. Si se establece la opción ANSI_NULL_DFLT_ON, es opcional declarar las columnas dispersas como columnas que aceptan valores NULL.Declaring the sparse columns to be nullable is optional if the ANSI_NULL_DFLT_ON option is set.

USE AdventureWorks2012;  
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.To select all the columns from the table returns an ordinary result set.

SELECT * FROM DocumentStore ;  

El conjunto de resultados es el siguiente.Here is the result set.

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.Because the Production department is not interested in the marketing data, they want to use a column list that returns only columns of interest, as shown in the following query.

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

El conjunto de resultados es el siguiente.Here is the result set.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

Consulte tambiénSee Also

Usar conjuntos de columnas Use Column Sets
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
sys.columns (Transact-SQL)sys.columns (Transact-SQL)