Usar colunas esparsasUse Sparse Columns

ESTE TÓPICO APLICA-SE A: simSQL Server (a partir de 2016)simAzure SQL DatabasesimAzure SQL Data Warehouse simParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Colunas esparsas são colunas comuns que têm um armazenamento otimizado para valores nulos.Sparse columns are ordinary columns that have an optimized storage for null values. Elas reduzem os requisitos de espaço para valores nulos às custas de maior sobrecarga para recuperar valores não nulos.Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Considere o uso de colunas esparsas quando o espaço salvo for pelo menos de 20 a 40 por cento.Consider using sparse columns when the space saved is at least 20 percent to 40 percent. As colunas esparsas e os conjuntos de colunas são definidos usando as instruções CREATE TABLE ou ALTER TABLE .Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

As colunas esparsas podem ser usadas com conjuntos de colunas e índices filtrados:Sparse columns can be used with column sets and filtered indexes:

  • Conjuntos de colunasColumn sets

    As instruções INSERT, UPDATE e DELETE podem referenciar colunas esparsas pelo nome.INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. Entretanto, você também pode exibir e trabalhar com todas as colunas esparsas de uma tabela, combinadas em uma única coluna XML.However, you can also view and work with all the sparse columns of a table that are combined into a single XML column. Essa coluna é denominada conjunto de colunas.This column is called a column set. Para obter mais informações sobre conjuntos de colunas, veja Usar conjuntos de colunas.For more information about column sets, see Use Column Sets.

  • Índices filtradosFiltered indexes

    Como as colunas esparsas têm muitas linhas de valor nulo, elas são especialmente apropriadas para índices filtrados.Because sparse columns have many null-valued rows, they are especially appropriate for filtered indexes. Um índice filtrado em uma coluna esparsa pode indexar somente as linhas com valores populados.A filtered index on a sparse column can index only the rows that have populated values. Isso cria um índice menor e mais eficiente.This creates a smaller and more efficient index. Para saber mais, confira Create Filtered Indexes.For more information, see Create Filtered Indexes.

    As colunas esparsas e os índices filtrados habilitam aplicativos, como o Windows SharePoint ServicesWindows SharePoint Services, para armazenar e acessar com eficiência um grande número de propriedades definidas pelo usuário usando o SQL Server 2016SQL Server 2016.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 Server 2016SQL Server 2016.

Propriedades das colunas esparsasProperties of Sparse Columns

As colunas esparsas têm as seguintes características:Sparse columns have the following characteristics:

  • O Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine usa a palavra-chave SPARSE em uma definição de coluna otimizar o armazenamento de valores naquela coluna.The Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Portanto, quando o valor da coluna for NULL para qualquer linha na tabela, os valores não exigirão armazenamento.Therefore, when the column value is NULL for any row in the table, the values require no storage.

  • As exibições do catálogo para uma tabela que tenha colunas esparsas são as mesmas que para uma tabela típica.Catalog views for a table that has sparse columns are the same as for a typical table. A exibição do catálogo sys.columns contém uma linha para cada coluna na tabela e inclui um conjunto de colunas, se houver um definido.The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined.

  • Colunas esparsas são uma propriedade da camada de armazenamento, não a tabela lógica.Sparse columns are a property of the storage layer, rather than the logical table. Portanto, uma instrução SELECT…INTO não copia sobre a propriedade de coluna esparsa em uma nova tabela.Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.

  • A função COLUMNS_UPDATED retorna um valor varbinary para indicar todas as colunas atualizadas durante uma ação DML.The COLUMNS_UPDATED function returns a varbinary value to indicate all the columns that were updated during a DML action. Os bits retornados pela função COLUMNS_UPDATED são os seguintes:The bits that are returned by the COLUMNS_UPDATED function are as follows:

    • Quando uma coluna esparsa é explicitamente atualizada, o bit correspondente àquela coluna esparsa é definido como 1 e o bit para o conjunto de colunas é definido como 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.

    • Quando um conjunto de colunas é explicitamente atualizado, o bit para o conjunto de colunas é definido como 1 e os bits para todas as colunas esparsas naquela tabela são definidos como 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.

    • Para operações de inserção, todos os bits são definidos como 1.For insert operations, all bits are set to 1.

      Para obter mais informações sobre conjuntos de colunas, veja Usar conjuntos de colunas.For more information about columns sets, see Use Column Sets.

    Os seguintes tipos de dados não podem ser especificados como SPARSE:The following data types cannot be specified as SPARSE:

geografiageography texttext
geometriageometry timestamptimestamp
imageimage tipos de dados definidos pelo usuáriouser-defined data types
ntextntext

Aumento de espaço estimado por tipo de dadosEstimated Space Savings by Data Type

As colunas esparsas exigem mais espaço de armazenamento para valores não nulos do que o espaço exigido para dados idênticos não marcados como SPARSE.Sparse columns require more storage space for nonnull values than the space required for identical data that is not marked SPARSE. As tabelas a seguir mostram o uso de espaço para cada tipo de dados.The following tables show the space usage for each data type. A coluna Percentual de NULL indica o percentual de dados que deve ser NULL para um aumento de espaço de 40 por cento.The NULL Percentage column indicates what percent of the data must be NULL for a net space savings of 40 percent.

Tipos de dados de comprimento fixoFixed-Length Data Types

Tipo de dadosData type Bytes não esparsosNonsparse bytes Bytes esparsosSparse bytes Percentual 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 dados de comprimento dependente de precisãoPrecision-Dependent–Length Data Types

Tipo de dadosData type Bytes não esparsosNonsparse bytes Bytes esparsosSparse bytes Percentual 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) Use o tipo decimal como uma estimativa conservadora.Use the decimal type as a conservative estimate.

Tipos de dados de comprimento dependente de dadosData-Dependent–Length Data Types

Tipo de dadosData type Bytes não esparsosNonsparse bytes Bytes esparsosSparse bytes Percentual de NULLNULL percentage
sql_variantsql_variant Varia de acordo com o tipo de dados subjacenteVaries with the underlying data type
varchar ou charvarchar or char 22 44 60%60%
nvarchar ou ncharnvarchar or nchar 22 4+4+ 60%60%
varbinary ou binaryvarbinary or binary 22 44 60%60%
xmlxml 22 44 60%60%
hierarchyidhierarchyid 22 44 60%60%

* O comprimento é igual à média dos dados que estão contidos no tipo, mais 2 ou 4 bytes.*The length is equal to the average of the data that is contained in the type, plus 2 or 4 bytes.

Sobrecarga na memória necessária para atualizações em colunas esparsasIn-Memory Overhead Required for Updates to Sparse Columns

Quando for criar tabelas com colunas esparsas, tenha em mente que uma sobrecarga adicional de 2 bytes é necessária para cada coluna esparsa não nula na tabela quando uma linha está sendo atualizada.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. Em resultado dessa necessidade de memória adicional, as atualizações podem falhar inesperadamente com o erro 576 quando o tamanho total da linha, incluindo essa sobrecarga de memória, excede 8019, e nenhuma coluna pode ser retirada da linha.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 o exemplo de uma tabela que tem 600 colunas esparsas do tipo bigint.Consider the example of a table that has 600 sparse columns of type bigint. Se houver 571 colunas não nulas, o tamanho total em disco será 571 * 12 = 6852 bytes.If there are 571 non-null columns, then the total size on disk is 571 * 12 = 6852 bytes. Depois de incluir a sobrecarga de linha adicional e o cabeçalho da coluna esparsa, isso aumenta para cerca de 6895 bytes.After including additional row overhead and the sparse column header, this increases to around 6895 bytes. A página ainda tem cerca de 1124 bytes disponíveis em disco.The page still has around 1124 bytes available on disk. Isso pode dar a impressão de que as colunas adicionais podem ser atualizadas com sucesso.This can give the impression that additional columns can be updated successfully. No entanto, durante a atualização, há uma sobrecarga adicional na memória que é 2*(o número de colunas esparsas não nulas).However, during the update, there is additional overhead in memory which is 2*(number of non-null sparse columns). Neste exemplo, incluir a sobrecarga adicional – 2 * 571 = 1142 bytes – aumenta o tamanho da linha no disco em torno de 8.037 bytes.In this example, including the additional overhead – 2 * 571 = 1142 bytes – increases the row size on disk to around 8037 bytes. Esse tamanho excede o tamanho máximo permitido de 8019 bytes.This size exceeds the maximum allowed size of 8019 bytes. Como todas as colunas têm tipos de dados de comprimento fixo, elas não podem ser retiradas da linha.Since all the columns are fixed-length data types, they cannot be pushed off the row. Portanto, a atualização falha com o erro 576.As a result, the update fails with the 576 error.

Restrições para o uso de colunas esparsasRestrictions for Using Sparse Columns

As colunas esparsas podem ser de qualquer tipo de dados do SQL ServerSQL Server e podem se comportar como qualquer outra coluna com as seguintes restrições:Sparse columns can be of any SQL ServerSQL Server data type and behave like any other column with the following restrictions:

  • Uma coluna esparsa deve permitir valor nulo e não deve ter as propriedades ROWGUIDCOL ou IDENTITY.A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties. Uma coluna esparsa não pode ser nenhum dos seguintes tipos de dados: text, ntext, image, timestamp, tipo de dados definido pelo usuário, geometryou geographynem ter o 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.

  • Uma coluna esparsa não pode ter um valor padrão.A sparse column cannot have a default value.

  • Uma coluna esparsa não pode estar associada a uma regra.A sparse column cannot be bound to a rule.

  • Embora uma coluna computada possa conter uma coluna esparsa, uma coluna computada não pode ser marcada como SPARSE.Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE.

  • Uma máscara de dados pode ser definida em uma coluna esparsa, mas não em uma coluna esparsa que faz parte de um conjunto de colunas.A data mask can be defined on a sparse column, but not on a sparse column that is part of a column set.

  • Uma coluna esparsa não pode fazer parte de um índice clusterizado ou de um índice de chave primária exclusivo.A sparse column cannot be part of a clustered index or a unique primary key index. Entretanto, as colunas computadas persistentes e não persistentes definidas em colunas esparsas podem fazer parte de uma chave clusterizada.However, both persisted and nonpersisted computed columns that are defined on sparse columns can be part of a clustered key.

  • Uma coluna esparsa não pode ser usada como uma chave de partição de um índice clusterizado ou heap.A sparse column cannot be used as a partition key of a clustered index or heap. Porém, uma coluna esparsa pode ser usada como a chave de partição de um índice não clusterizado.However, a sparse column can be used as the partition key of a nonclustered index.

  • Uma coluna esparsa não pode fazer parte de um tipo de tabela definido pelo usuário, usado em variáveis de tabela e em parâmetros com valor de tabela.A sparse column cannot be part of a user-defined table type, which are used in table variables and table-valued parameters.

  • Colunas esparsas são incompatíveis com a compactação de dados.Sparse columns are incompatible with data compression. Portanto, colunas esparsas não podem ser adicionadas a tabelas compactadas, e nenhuma tabela que contenha colunas esparsas pode ser compactada.Therefore sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.

  • A alteração de uma coluna de esparsa para não esparsa ou de não esparsa para esparsa exige a alteração do formato de armazenamento da coluna.Changing a column from sparse to nonsparse or nonsparse to sparse requires changing the storage format of the column. O Mecanismo de Banco de Dados do SQL Server usa o procedimento a seguir para executar essa alteração:The SQL Server Database Engine uses the following procedure to accomplish this change:

    1. Adiciona uma nova coluna à tabela no novo tamanho e formato de armazenamento.Adds a new column to the table in the new storage size and format.

    2. Para cada linha na tabela, atualiza e copia o valor armazenado na coluna antiga na coluna nova.For each row in the table, updates and copies the value stored in the old column to the new column.

    3. Remove a coluna antiga do esquema da tabela.Removes the old column from the table schema.

    4. Reconstrói a tabela (se não houver nenhum índice clusterizado) ou reconstrói o índice clusterizado para recuperar o espaço usado pela coluna antiga.Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column.

    Observação

    A etapa 2 pode falhar quando o tamanho dos dados na linha exceder o tamanho máximo de linha permitido.Step 2 can fail when the size of the data in the row exceeds the maximum allowable row size. Esse tamanho inclui o tamanho dos dados armazenados na coluna antiga e os dados atualizados armazenados na coluna nova.This size includes the size of the data stored in the old column and the updated data stored in the new column. Esse limite é de 8060 bytes para tabelas que não contêm nenhuma coluna esparsa ou 8018 bytes para tabelas que contêm colunas esparsas.This limit is 8060 bytes for tables that do not contain any sparse columns or 8018 bytes for tables that contain sparse columns. Esse erro poderá ocorrer até mesmo se todas as colunas elegíveis forem empurradas para fora da linha.This error can occur even if all eligible columns have been pushed off-row.

  • Quando você alterar uma coluna não esparsa para uma coluna esparsa, a coluna esparsa consumirá mais espaço para valores não nulos.When you change a non-sparse column to a sparse column, the sparse column will consume more space for non-null values. Quando uma linha está próxima do limite de tamanho máximo de linha, pode haver falha na operação.When a row is close to the maximum row size limit, the operation can fail.

Tecnologias do SQL Server que oferecem suporte a colunas esparsasSQL Server Technologies That Support Sparse Columns

Esta seção descreve como as colunas esparsas têm suporte nas seguintes tecnologias do SQL ServerSQL Server :This section describes how sparse columns are supported in the following SQL ServerSQL Server technologies:

  • Replicação transacionalTransactional replication

    A replicação transacional oferece suporte a colunas esparsas, mas não oferece suporte a conjuntos de colunas que podem ser usados com colunas esparsas.Transactional replication supports sparse columns, but it does not support column sets, which can be used with sparse columns. Para obter mais informações sobre conjuntos de colunas, veja Usar conjuntos de colunas.For more information about column sets, see Use Column Sets.

    A replicação do atributo SPARSE é determinada por uma opção de esquema especificada usando sp_addarticle ou usando a caixa de diálogo Propriedades do Artigo no 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. As versões anteriores do SQL ServerSQL Server não dão suporte a colunas esparsas.Earlier versions of SQL ServerSQL Server do not support sparse columns. Se for necessário replicar dados para uma versão anterior, especifique que o atributo SPARSE não deve ser replicado.If you must replicate data to an earlier version, specify that the SPARSE attribute should not be replicated.

    Para tabelas publicadas, não é possível adicionar novas colunas esparsas a uma tabela nem alterar a propriedade esparsa de uma coluna 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. Se tal operação for exigida, descarte e recrie a publicação.If such an operation is required, drop and re-create the publication.

  • Replicação de mesclagemMerge replication

    A replicação de mesclagem não oferece suporte a colunas esparsas ou conjuntos de colunas.Merge replication does not support sparse columns or column sets.

  • Controle de alteraçõesChange tracking

    O controle de alterações oferece suporte a colunas esparsas e conjuntos de colunas.Change tracking supports sparse columns and column sets. Quando um conjunto de colunas é atualizado em uma tabela, o controle de alterações trata isso como uma atualização em toda a linha.When a column set is updated in a table, change tracking treats this as an update to the whole row. Nenhum controle de alteração detalhado é fornecido para obter o conjunto exato de colunas esparsas atualizadas pela operação de atualização de conjunto de colunas.No detailed change tracking is provided to obtain the exact set of sparse columns that are updated through the column set update operation. Se as colunas esparsas forem atualizadas explicitamente por uma instrução DML, o controle de alterações funcionará de forma comum e poderá identificar o conjunto exato das colunas alteradas.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.

  • Change Data CaptureChange data capture

    O Change Data Capture oferece suporte a colunas esparsas, mas não a conjuntos de colunas.Change data capture supports sparse columns, but it does not support column sets.

  • A propriedade esparsa de uma coluna não é preservada quando a tabela é copiada.The sparse property of a column is not preserved when the table is copied.

ExemplosExamples

Neste exemplo, uma tabela de documento contém um conjunto comum que tem as colunas DocID e Title.In this example, a document table contains a common set that has the columns DocID and Title. O grupo de Produção quer uma coluna ProductionSpecification e ProductionLocation para todos os documentos da produção.The Production group wants a ProductionSpecification and ProductionLocation column for all production documents. O grupo Marketing quer uma coluna MarketingSurveyGroup para os documentos de marketing.The Marketing group wants a MarketingSurveyGroup column for marketing documents. O código neste exemplo cria uma tabela que usa colunas esparsas, insere duas linhas na tabela e, depois, seleciona dados da tabela.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.

Observação

Essa tabela tem somente cinco colunas para facilitar a exibição e a leitura.This table has only five columns to make it easier to display and read. A declaração de colunas esparsas para aceitarem valores nulos será opcional se a opção ANSI_NULL_DFLT_ON estiver definida.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  

A seleção de todas as colunas da tabela retorna um conjunto de resultados comum.To select all the columns from the table returns an ordinary result set.

SELECT * FROM DocumentStore ;  

Aqui está o conjunto de resultados.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

Como o departamento de Produção não está interessado nos dados de marketing, eles querem usar uma lista de colunas que retorne somente colunas de interesse, como mostrado na consulta a seguir.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 ;  

Aqui está o conjunto de resultados.Here is the result set.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

Consulte tambémSee Also

Usar conjuntos de colunas 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)