Usar colunas esparsas
Aplica-se a:
SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada SQL do Azurebanco de dados SQL no Microsoft Fabric
Colunas esparsas são colunas comuns que têm um armazenamento otimizado para valores nulos. Colunas esparsas reduzem os requisitos de espaço para valores nulos ao custo de mais sobrecarga para recuperar valores não NULL. Considere o uso de colunas esparsas quando o espaço economizado for de pelo menos 20% a 40%. Colunas esparsas e conjuntos de colunas são definidos usando as instruções CREATE TABLE ou ALTER TABLE.
Colunas esparsas podem ser usadas com conjuntos de colunas e índices filtrados:
Conjuntos de colunas
As instruções INSERT, UPDATE e DELETE podem fazer referência às colunas esparsas pelo nome. No entanto, você também pode exibir e trabalhar com todas as colunas esparsas de uma tabela que são combinadas em uma única coluna XML. Esta coluna é chamada de conjunto de colunas. Para obter mais informações sobre conjuntos de colunas, consulte Usar conjuntos de colunas.
Índices filtrados
Como colunas esparsas têm muitas linhas com valor nulo, elas são especialmente apropriadas para índices filtrados. Um índice filtrado em uma coluna esparsa pode indexar apenas as linhas que têm valores preenchidos. Isso cria um índice menor e mais eficiente. Para obter mais informações, consulte Criar índices filtrados.
Colunas esparsas e índices filtrados permitem que aplicativos, como o Windows SharePoint Services, armazenem e acessem com eficiência um grande número de propriedades definidas pelo usuário usando o SQL Server.
Propriedades de 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 para otimizar o armazenamento de valores nessa coluna. Portanto, quando o valor da coluna é NULL para qualquer linha na tabela, os valores não exigem armazenamento.
As exibições de catálogo para uma tabela com colunas esparsas são as mesmas de uma tabela típica. A exibição de catálogo
sys.columnscontém uma linha para cada coluna da tabela e inclui um conjunto de colunas, se uma estiver definida.Colunas esparsas são uma propriedade da camada de armazenamento, em vez da tabela lógica. Portanto, uma instrução
SELECT ... INTOnão copia a propriedade de coluna esparsa em uma nova tabela.A função COLUMNS_UPDATED retorna um valor varbinary para indicar todas as colunas que foram atualizadas durante uma ação DML. Os bits que são retornados pela função COLUMNS_UPDATED são os seguintes:
Quando uma coluna esparsa é explicitamente atualizada, o bit correspondente para essa 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 nessa 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 Consulte conjuntos de colunas.
Os seguintes tipos de dados não podem ser especificados como SPARSE:
geografia
geometria
imagem
ntext
texto
carimbo de data/hora
tipos de dados definidos pelo usuário
Economia de espaço estimada por tipo de dados
Colunas esparsas exigem mais espaço de armazenamento para valores não NULL do que o espaço necessário para dados idênticos que não estão marcados como SPARSE. As tabelas a seguir mostram o uso de espaço para cada tipo de dados. A coluna Porcentagem NULL indica qual porcentagem dos dados deve ser NULL para uma economia de espaço líquido de 40%.
Fixed-Length tipos de dados
| Tipo de dados | Bytes não esparsos | Bytes esparsos | Percentagem NULL |
|---|---|---|---|
| bit | 0.125 | 5 | 98% |
| tinyint | 1 | 5 | 86% |
| inteiro pequeno | 2 | 6 | 76% |
| int | 4 | 8 | 64% |
| bigint | 8 | 12 | 52% |
| real | 4 | 8 | 64% |
| flutuar | 8 | 12 | 52% |
| dinheiro pequeno | 4 | 8 | 64% |
| dinheiro | 8 | 12 | 52% |
| smalldatetime | 4 | 8 | 64% |
| data e hora | 8 | 12 | 52% |
| identificador único | 16 | 20 | 43% |
| data | 3 | 7 | 69% |
Tipos de dados de precisãoDependent-Length
| Tipo de dados | Bytes não esparsos | Bytes esparsos | Percentagem NULL |
|---|---|---|---|
| datetime2(0) | 6 | 10 | 57% |
| datetime2(7) | 8 | 12 | 52% |
| tempo(0) | 3 | 7 | 69% |
| tempo(7) | 5 | 9 | 60% |
| datetimetoffset(0) | 8 | 12 | 52% |
| datetimetoffset (7) | 10 | 14 | 49% |
| decimal/numérico(1,s) | 5 | 9 | 60% |
| decimal/numérico(38,s) | 17 | 21 | 42% |
| vardecimal(p,s) | Utilize o tipo de decimal como uma estimativa conservadora. |
Tipos de dadosDependent-Length
| Tipo de dados | Bytes não esparsos | Bytes esparsos | Percentagem NULL |
|---|---|---|---|
| sql_variant | Varia de acordo com o tipo de dados subjacente | ||
| varchar ou char | 2* | 4* | 60% |
| nvarchar ou nchar | 2* | 4*+ | 60% |
| varbinary ou binário | 2* | 4* | 60% |
| xml | 2* | 4* | 60% |
| hierarchyid | 2* | 4* | 60% |
*O comprimento é igual à média dos dados contidos no tipo, mais 2 ou 4 bytes.
In-Memory sobrecarga necessária para atualizar colunas esparsas
Ao criar tabelas com colunas esparsas, lembre-se de que são necessários 2 bytes adicionais de sobrecarga para cada coluna esparsa não nula na tabela quando uma linha está sendo atualizada. Como resultado desse requisito 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 empurrada para fora da linha.
Considere o exemplo de uma tabela que tem 600 colunas esparsas do tipo bigint. Se houver 571 colunas não nulas, o tamanho total no disco é 571 * 12 = 6852 bytes. Depois de incluir a sobrecarga de linha adicional e o cabeçalho de coluna esparso, isso aumenta para cerca de 6895 bytes. A página ainda tem cerca de 1124 bytes disponíveis no disco. Isso pode dar a impressão de que colunas adicionais podem ser atualizadas com êxito. No entanto, durante a atualização, há sobrecarga adicional na memória que é 2* (número de colunas esparsas não nulas). Neste exemplo, incluir a sobrecarga adicional - 2 * 571 = 1142 bytes - aumenta o tamanho da linha no disco para cerca de 8037 bytes. Este tamanho excede o tamanho máximo permitido de 8019 bytes. Como todas as colunas são tipos de dados de comprimento fixo, elas não podem ser empurradas para fora da linha. Como resultado, a atualização falha com o erro 576.
Restrições para o uso de colunas esparsas
Colunas esparsas podem ser de qualquer tipo de dados do SQL Server e se comportar como qualquer outra coluna com as seguintes restrições:
Uma coluna esparsa deve ser anulável e não pode ter as propriedades ROWGUIDCOL ou IDENTITY. Uma coluna esparsa não pode ser dos seguintes tipos de dados: de texto, ntext, de imagem, de carimbo de data/hora, tipo de dados definido pelo usuário, de geometria ou de geografia; ou ter o atributo FILESTREAM.
Uma coluna esparsa não pode ter um valor padrão.
Uma coluna esparsa não pode ser vinculada a uma regra.
Embora uma coluna computada possa conter uma coluna esparsa, uma coluna computada não pode ser marcada como 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.
Uma coluna esparsa não pode fazer parte de um índice clusterizado ou de um índice de chave primária exclusivo. No entanto, 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 ou heap clusterizado. No entanto, 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 são usados em variáveis de tabela e parâmetros com valor de tabela.
Colunas esparsas são incompatíveis com a compactação de dados. Portanto, colunas esparsas não podem ser adicionadas a tabelas compactadas, nem tabelas que contenham colunas esparsas podem ser compactadas.
Transformar uma coluna de esparsa para não esparsa, ou de não esparsa para esparsa, implica alterar o formato de armazenamento da coluna. O Mecanismo de Banco de Dados do SQL Server usa o seguinte procedimento para realizar essa alteração:
Adiciona uma nova coluna à tabela no novo tamanho e formato de armazenamento.
Para cada linha da tabela, atualiza e copia o valor armazenado na coluna antiga para a nova coluna.
Remove a coluna antiga do esquema da tabela.
Recria a tabela (se não houver nenhum índice clusterizado) ou recria o índice clusterizado 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 permitido da linha. Esse tamanho inclui o tamanho dos dados armazenados na coluna antiga e os dados atualizados armazenados na nova coluna. Esse limite é de 8060 bytes para tabelas que não contêm colunas esparsas ou 8018 bytes para tabelas que contêm colunas esparsas. Este erro pode ocorrer mesmo que todas as colunas elegíveis tenham sido removidas da linha.
Quando você altera uma coluna não esparsa para uma coluna esparsa, a coluna esparsa consumirá mais espaço para valores não nulos. Quando uma linha está perto do limite máximo de tamanho de linha, a operação pode falhar.
Tecnologias do SQL Server que oferecem suporte a colunas esparsas
Esta seção descreve como colunas esparsas são suportadas nas seguintes tecnologias do SQL Server:
Replicação transacional
A replicação transacional suporta colunas esparsas, mas não suporta conjuntos de colunas que podem ser utilizados com essas colunas esparsas. Para obter mais informações sobre conjuntos de colunas, consulte Utilizar 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. Versões anteriores do SQL Server não oferecem suporte a colunas esparsas. Se você precisar 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 ou alterar a propriedade esparsa de uma coluna existente. Se tal operação for necessária, elimine 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.
Acompanhamento de alterações
O controle de alterações suporta 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 para toda a linha. Nenhum controle de alterações detalhado é fornecido para obter o conjunto exato de colunas esparsas que são atualizadas por meio da operação de atualização do conjunto de colunas. Se as colunas esparsas forem atualizadas explicitamente por meio de uma instrução DML, o controle de alterações nelas funcionará normalmente e poderá identificar o conjunto exato de colunas alteradas.
Alterar a captura de dados
A captura de dados de alteração suporta colunas esparsas, mas não suporta conjuntos de colunas.
A propriedade esparsa de uma coluna não é preservada quando a tabela é copiada.
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 uma coluna ProductionLocation para todos os documentos de produção. O grupo Marketing quer uma coluna MarketingSurveyGroup para documentos de marketing. O código neste exemplo cria uma tabela que usa colunas esparsas, insere duas linhas na tabela e, em seguida, seleciona dados da tabela.
Observação
Esta tabela tem apenas cinco colunas para facilitar a sua apresentação e leitura. Declarar as colunas esparsas como anuláveis é opcional se a opção ANSI_NULL_DFLT_ON estiver definida. Quando SET ANSI_DEFAULTS está ON, SET ANSI_NULL_DFLT_ON está ativado. ANSI_DEFAULTS está ATIVADO por padrão para a maioria dos provedores de conexão. Para obter mais informações, consulte SET ANSI_DEFAULTS.
USE AdventureWorks2022;
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
Para selecionar todas as colunas da tabela retorna um conjunto de resultados ordinário.
SELECT * FROM DocumentStore ;
Aqui está o 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 retorna apenas colunas de interesse, conforme mostrado na consulta a seguir.
SELECT DocID, Title, ProductionSpecification, ProductionLocation
FROM DocumentStore
WHERE ProductionSpecification IS NOT NULL ;
Aqui está o conjunto de resultados.
DocID Title ProductionSpecification ProductionLocation
1 Tire Spec 1 AXZZ217 27