Usando colunas esparsas

Propriedades das colunas esparsas

As colunas esparsas têm as seguintes características:

• O Mecanismo de Banco de Dados do SQL Server usa a palavra-chave SPARSE em uma definição de coluna otimizar o armazenamento de valores naquela coluna. Portanto, quando o valor da coluna for NULL para qualquer linha na tabela, os valores não exigirão armazenamento.

• As exibições do catálogo para uma tabela que tem colunas esparsas são as mesmas de uma tabela comum. 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.

• Colunas esparsas são uma propriedade da camada de armazenamento, e não da tabela lógica. Assim, uma instrução SELECT...INTO não pode ser copiada pela propriedade da coluna esparsa em uma nova tabela.

• A função COLUMNS_UPDATED retorna um valor varbinary para indicar todas as colunas atualizadas durante uma ação DML. Os bits retornados pela função COLUMNS_UPDATED são os seguintes:

  • 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.

  • 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.

  • Para operações de inserção, todos os bits são definidos como 1.

  Para obter mais informações sobre conjuntos de colunas, consulte Usando conjuntos de colunas.

Os seguintes tipos de dados não podem ser especificados como SPARSE:

Aumento de espaço estimado por tipo de dados

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. As tabelas a seguir mostram o uso de espaço para cada tipo de dados. A coluna Porcentagem de NULL indica a porcentagem de dados que deve ser NULL para um aumento de espaço de 40 por cento.

Tipos de dados de comprimento fixo

Tipos de dados de comprimento dependente de precisão

Tipos de dados de comprimento dependente de dados

* O comprimento é igual à média dos dados que estão contidos no tipo, mais 2 ou 4 bytes.

Restrições para o uso de colunas esparsas

As colunas esparsas podem ser de qualquer tipo de dados do SQL Server e podem se comportar como qualquer outra coluna com as seguintes restrições:

• Uma coluna esparsa deve permitir valor nulo e não deve ter as propriedades ROWGUIDCOL ou IDENTITY. Uma coluna esparsa não pode ser nenhum destes tipos de dados: text, ntext, image, timestamp, tipo de dados definido pelo usuário, geometry ou geography; ou ter o atributo FILESTREAM.

• Uma coluna esparsa não pode ter um valor padrão.

• Uma coluna esparsa não pode estar associada a uma regra.

• Embora uma coluna computada possa conter uma coluna esparsa, uma coluna computada não pode ser marcada como SPARSE.

• Uma coluna esparsa não pode fazer parte de um índice clusterizado ou de um índice de chave primária exclusivo. Entretanto, as colunas computadas persistentes e não persistentes definidas em colunas esparsas podem fazer parte de uma chave clusterizada.

• Uma coluna esparsa não pode ser usada como uma chave de partição de um índice clusterizado ou heap. Porém, uma coluna esparsa pode ser usada como a chave de partição de um índice não clusterizado.

• Uma coluna esparsa não pode fazer parte de um tipo de tabela definido pelo usuário que é usado em variáveis de tabela e parâmetros com valor de tabela.

• As colunas esparsas são incompatíveis com a compactação de dados. Desse modo, as colunas esparsas não podem ser adicionadas às tabelas compactadas e nenhuma tabela contendo colunas esparsas pode ser compactada.

• 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. O Mecanismo de Banco de Dados do SQL Server usa o procedimento a seguir para executar essa alteração:

  1. Adiciona uma nova coluna à tabela no novo tamanho e formato de armazenamento.

  2. Para cada linha na tabela, atualiza e copia o valor armazenado na coluna antiga na coluna nova.

  3. Remove a coluna antiga do esquema da tabela.

  4. Recria a tabela para recuperar o espaço usado pela coluna antiga.

  Observação

  A etapa 2 pode falhar quando o tamanho dos dados na linha exceder o tamanho máximo de linha permitido. Esse tamanho inclui o tamanho dos dados armazenados na coluna antiga e os dados atualizados armazenados na coluna nova. 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. Esse erro poderá ocorrer até mesmo se todas as colunas elegíveis forem empurradas para fora da linha. Para obter mais informações, consulte Dados de estouro de linha excedendo 8 KB.

• Quando você altera uma coluna não esparsa para uma coluna esparsa, a coluna esparsa consome mais espaço para valores não nulos. Quando uma linha está próxima do limite de tamanho máximo de linha, pode haver falha na operação.

Tecnologias do SQL Server que oferecem suporte a colunas esparsas

Esta seção descreve como as colunas esparsas têm suporte nas tecnologias seguintes do SQL Server:

• Replicação de transação

  A replicação de transação oferece suporte a colunas esparsas, mas não oferece suporte a conjuntos de colunas que podem ser usados com colunas esparsas. Para obter mais informações sobre conjuntos de colunas, consulte Usando conjuntos de colunas.

  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 Studio. As versões anteriores do SQL Server não oferecem suporte a colunas esparsas. Se for necessário replicar dados para uma versão anterior, especifique que o atributo SPARSE não deve ser replicado.

  Para tabelas publicadas, não é possível adicionar novas colunas esparsas a uma tabela nem alterar a propriedade esparsa de uma coluna existente. Se tal operação for exigida, descarte e recrie a publicação.

• Replicação de mesclagem

  A replicação de mesclagem não oferece suporte a colunas esparsas ou conjuntos de colunas.

• Controle de alterações

  O controle de alterações oferece suporte a colunas esparsas e conjuntos de colunas. Quando um conjunto de colunas é atualizado em uma tabela, o controle de alterações trata isso como uma atualização em toda a linha. 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. 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.

• Change Data Capture

  O Change Data Capture oferece suporte a colunas esparsas, mas não a conjuntos de colunas.

Exemplos

Neste exemplo, uma tabela de documento contém um conjunto comum que tem as colunas DocID e Title. O grupo de Produção quer uma coluna ProductionSpecification e ProductionLocation para todos os documentos da produção. O grupo de Marketing quer uma coluna MarketingSurveyGroup para os documentos de marketing. O código neste exemplo cria uma tabela que usa colunas esparsas, insere duas linhas na tabela e, depois, seleciona dados da tabela.

Observação
Essa tabela tem somente cinco colunas para facilitar a exibição e a leitura. A declaração de colunas esparsas para aceitarem valores nulos será opcional se a opção ANSI_NULL_DFLT_ON estiver definida.

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

A seleção de todas as colunas da tabela retorna um conjunto de resultados comum.

SELECT * FROM DocumentStore ;

Conjunto de resultados.

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 desejam usar uma lista de colunas que retorne somente colunas de interesse, como mostrado na consulta a seguir.

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

Conjunto de resultados.

DocID  Title        ProductionSpecification  ProductionLocation

1      Tire Spec 1  AXZZ217                  27

Consulte também

Referência

Conceitos

Histórico de alterações