Partilhar via

Usar conjuntos de colunas

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Azure SQL Database AzureSQL Managed InstanceSQL database in Microsoft Fabric

As tabelas que usam colunas esparsas podem designar um conjunto de colunas para retornar todas as colunas esparsas na tabela. Um conjunto de colunas é uma representação XML sem tipo que combina todas as colunas esparsas de uma tabela em uma saída estruturada. Um conjunto de colunas é como uma coluna calculada, na medida em que o conjunto de colunas não está fisicamente armazenado na tabela. Um conjunto de colunas difere de uma coluna calculada porque o conjunto de colunas é diretamente atualizável.

Você deve considerar o uso de conjuntos de colunas quando o número de colunas em uma tabela é grande e operar neles individualmente é complicado. Os aplicativos podem ver alguma melhoria de desempenho quando selecionam e inserem dados usando conjuntos de colunas em tabelas com muitas colunas. No entanto, o desempenho dos conjuntos de colunas pode ser reduzido quando muitos índices são definidos nas colunas da tabela. Isso ocorre porque a quantidade de memória necessária para um plano de execução aumenta.

Para definir um conjunto de colunas, use as palavras-chave *<column_set_name>* FOR ALL_SPARSE_COLUMNS nas instruções CREATE TABLE ou ALTER TABLE.

Diretrizes para o uso de conjuntos de colunas

Ao usar conjuntos de colunas, considere as seguintes diretrizes:

  • Colunas esparsas e um conjunto de colunas podem ser adicionados como parte da mesma instrução.

  • Um conjunto de colunas não pode ser adicionado a uma tabela se essa tabela já contiver colunas esparsas.

  • A coluna num conjunto de colunas não pode ser alterada nem renomeada. Para alterar um conjunto de colunas, você deve excluir e recriar as colunas esparsas e o conjunto de colunas. As colunas com a palavra-chave SPARSE podem ser adicionadas e retiradas da tabela.

  • Um conjunto de colunas pode ser adicionado a uma tabela que não inclui colunas esparsas. Se colunas esparsas forem adicionadas posteriormente à tabela, elas aparecerão no conjunto de colunas.

  • Só é permitido um conjunto de colunas por tabela.

  • Um conjunto de colunas é opcional e não é necessário usar colunas esparsas.

  • Restrições ou valores padrão não podem ser definidos em um conjunto de colunas.

  • As colunas computadas não podem conter colunas de conjunto de colunas.

  • Não há suporte para consultas distribuídas em tabelas que contêm conjuntos de colunas.

  • A replicação não oferece suporte a conjuntos de colunas.

  • A captura de dados de alteração não suporta conjuntos de colunas.

  • Um conjunto de colunas não pode fazer parte de nenhum tipo de índice. Isso inclui índices XML, índices de texto completo e exibições indexadas. Um conjunto de colunas não pode ser adicionado como uma coluna incluída em nenhum índice.

  • Um conjunto de colunas não pode ser usado na expressão de filtro de um índice filtrado ou estatísticas filtradas.

  • Quando um modo de exibição inclui um conjunto de colunas, o conjunto de colunas aparece no modo de exibição como uma coluna XML.

  • Um conjunto de colunas não pode ser incluído em uma definição de exibição indexada.

  • Os modos de exibição particionados que incluem tabelas que contêm conjuntos de colunas são atualizáveis quando o modo de exibição particionado especifica as colunas esparsas pelo nome. Uma vista particionada não é atualizável quando faz referência ao conjunto de colunas.

  • Não são permitidas notificações de consulta que se refiram a conjuntos de colunas.

  • Os dados XML têm um limite de tamanho de 2 GB. Se os dados combinados de todas as colunas esparsas não NULL em uma linha excederem esse limite, a consulta ou operação DML produzirá um erro.

  • Para obter informações sobre os dados retornados pela função COLUMNS_UPDATED, consulte Usar colunas esparsas.

Diretrizes para selecionar dados de um conjunto de colunas

Considere as seguintes diretrizes para selecionar dados de um conjunto de colunas:

  • Conceitualmente, um conjunto de colunas é um tipo de coluna XML atualizável e computada que agrega um conjunto de colunas relacionais subjacentes em uma única representação XML. O conjunto de colunas suporta apenas a propriedade ALL_SPARSE_COLUMNS. Esta propriedade é usada para agregar todos os valores não-NULL de todas as colunas esparsas para uma linha específica.

  • No editor de tabela do SQL Server Management Studio, os conjuntos de colunas são exibidos como um campo XML editável. Defina conjuntos de colunas no formato:

    <column_name_1>value1</column_name_1><column_name_2>value2</column_name_2>...

    Exemplos de valores de conjuntos de colunas são os seguintes:

    <sparseProp1>10</sparseProp1><sparseProp3>20</sparseProp3>  

    <DocTitle>Bicycle Parts List</DocTitle><Region>West</Region>

  • Colunas esparsas que contêm valores nulos são omitidas da representação XML para o conjunto de colunas.

Warning

Adicionar um conjunto de colunas altera o comportamento das consultas de SELECT *. A consulta retornará o conjunto de colunas como uma coluna XML e não retornará as colunas esparsas individuais. Designers de esquema e desenvolvedores de software devem ter cuidado para não quebrar aplicativos existentes. Colunas individuais dispersas ainda podem ser consultadas pelo nome numa instrução SELECT.

Inserir ou modificar dados em um conjunto de colunas

A manipulação de dados de uma coluna esparsa pode ser realizada usando o nome das colunas individuais ou fazendo referência ao nome do conjunto de colunas e especificando os valores do conjunto de colunas usando o formato XML do conjunto de colunas. Colunas esparsas podem aparecer em qualquer ordem na coluna XML.

Quando valores de coluna esparsa são inseridos ou atualizados usando o conjunto de colunas XML, os valores inseridos nas colunas esparsas subjacentes são implicitamente convertidos do tipo de dados xml. No caso da maioria dos tipos de dados numéricos, incluindo bigint, int, smallint, tinyint, bit, floate real , um valor em branco no XML para a coluna é convertido em uma cadeia de caracteres vazia. Isso faz com que um zero seja inserido na coluna, conforme mostrado no exemplo a seguir. No entanto, a substituição para 0 não se aplica ao numérico e tipos de dados decimais, esses valores devem ser especificados ou causarão um erro de conversão.

CREATE TABLE t (i int SPARSE, cs xml column_set FOR ALL_SPARSE_COLUMNS);  
GO  
INSERT t(cs) VALUES ('<i/>');  
GO  
SELECT i FROM t;  
GO

Neste exemplo, nenhum valor foi especificado para a coluna i, mas o valor 0 foi inserido.

Utilizar o tipo de dados sql_variant

O tipo de data sql_variant pode armazenar vários tipos de dados diferentes, como int, chare data. Os conjuntos de colunas produzem as informações de tipo de dados, como escala, precisão e informações de localidade, associadas a um valor sql_variant como atributos na coluna XML gerada. Se você tentar fornecer esses atributos em uma instrução XML personalizada como uma entrada para uma operação de inserção ou atualização em um conjunto de colunas, alguns desses atributos serão necessários e alguns deles receberão um valor padrão. A tabela a seguir lista os tipos de dados e os valores padrão que o servidor gera quando o valor não é fornecido.

Tipo de dados localeID* sqlCompareOptions sqlCollationVersion SqlSortId Comprimento máximo Precision Scale
char, varchar, binário -1 'Default' 0 0 8000 Não aplicável** Não aplicável
nvarchar -1 'Default' 0 0 4000 Não aplicável Não aplicável
decimal, flutuante, real Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável 18 0
inteiro, bigint, tinyint, smallint Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável
datetime2 Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável 7
Deslocamento de data e hora Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável 7
data e hora, data, data e hora reduzida Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável
dinheiro, dinheiro pequeno Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável
time Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável Não aplicável 7

* localeID -1 significa a localidade padrão. A localidade em inglês é 1033.

** Não aplicável = Nenhum valor é gerado para esses atributos durante uma operação de seleção no conjunto de colunas. Gera um erro quando um valor é especificado para esse atributo pelo chamador na representação XML fornecida para um conjunto de colunas em uma operação de inserção ou atualização.

Segurança

O modelo de segurança para um conjunto de colunas funciona de forma semelhante ao modelo de segurança que existe entre a tabela e as colunas. Os conjuntos de colunas podem ser visualizados como uma mini-tabela e uma operação de seleção é como uma operação de SELECT * nesta mini-tabela. Mas, a relação entre colunas definidas para colunas esparsas é uma relação de agrupamento em vez de estritamente um container. O modelo de segurança verifica a segurança no conjunto de colunas e respeita as operações de recusa nas colunas esparsas subjacentes. As características adicionais do modelo de segurança são as seguintes:

  • As permissões de segurança podem ser concedidas e revogadas no conjunto de colunas, tal como em qualquer outra coluna da tabela.

  • Uma permissão GRANT ou REVOKE de SELECT, INSERT, UPDATE, DELETE e REFERENCES num conjunto de colunas não se propaga para as colunas subjacentes desse conjunto. Aplica-se apenas ao uso da coluna do conjunto de colunas. A permissão DENY em um conjunto de colunas se propaga para as colunas esparsas subjacentes da tabela.

  • A execução de instruções SELECT, INSERT, UPDATE e DELETE na coluna de conjunto de colunas requer que o utilizador tenha as permissões correspondentes nessa coluna, bem como nas colunas esparsas da tabela. Como o conjunto de colunas representa todas as colunas esparsas na tabela, deve ter permissão para todas as colunas esparsas, incluindo aquelas que poderá não estar a alterar.

  • A execução de uma instrução REVOKE em uma coluna ou conjunto de colunas esparsas faz com que a segurança seja redefinida para o padrão do objeto pai.

Examples

Nos exemplos a seguir, uma tabela de documento contém o conjunto comum de 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 de Marketing quer uma coluna MarketingSurveyGroup para documentos de marketing.

A. Criar uma tabela que tenha um conjunto de colunas

O exemplo a seguir cria a tabela que usa colunas esparsas e inclui o conjunto de colunas SpecialPurposeColumns. O exemplo insere duas linhas na tabela e, em seguida, seleciona dados da tabela.

Note

Esta tabela tem apenas cinco colunas para facilitar a sua apresentação e leitura.

USE AdventureWorks2022;  
GO  
  
CREATE TABLE DocumentStoreWithColumnSet  
    (DocID int PRIMARY KEY,  
     Title varchar(200) NOT NULL,  
     ProductionSpecification varchar(20) SPARSE NULL,  
     ProductionLocation smallint SPARSE NULL,  
     MarketingSurveyGroup varchar(20) SPARSE NULL,  
     MarketingProgramID int SPARSE NULL,  
     SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);  
GO

B. Inserir dados em uma tabela usando os nomes das colunas esparsas

Os exemplos a seguir inserem duas linhas na tabela criada no Exemplo A. Os exemplos usam os nomes das colunas esparsas e não fazem referência ao conjunto de colunas.

INSERT DocumentStoreWithColumnSet (DocID, Title, ProductionSpecification, ProductionLocation)  
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);  
GO  
  
INSERT DocumentStoreWithColumnSet (DocID, Title, MarketingSurveyGroup)  
VALUES (2, 'Survey 2142', 'Men 25 - 35');  
GO

C. Inserir dados em uma tabela usando o nome do conjunto de colunas

O exemplo a seguir insere uma terceira linha na tabela criada no exemplo A. Desta vez, os nomes das colunas esparsas não são usados. Em vez disso, o nome do conjunto de colunas é usado e a inserção fornece os valores para duas das quatro colunas esparsas no formato XML.

INSERT DocumentStoreWithColumnSet (DocID, Title, SpecialPurposeColumns)  
VALUES (3, 'Tire Spec 2', '<ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>');  
GO

D. Observe os resultados de um conjunto de colunas quando SELECT * é usado

O exemplo a seguir seleciona todas as colunas da tabela que contém um conjunto de colunas. Ele retorna uma coluna XML com os valores combinados das colunas esparsas. Ele não retorna as colunas esparsas individualmente.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;

Aqui está o conjunto de resultados.

DocID  Title        SpecialPurposeColumns  
1      Tire Spec 1  <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>  
2      Survey 2142  <MarketingSurveyGroup>Men 25 - 35</MarketingSurveyGroup>  
3      Tire Spec 2  <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>

E. Observe os resultados da seleção do conjunto de colunas por nome

Como o departamento de produção não está interessado nos dados de marketing, este exemplo adiciona uma cláusula WHERE para restringir a saída. O exemplo usa o nome do conjunto de colunas.

SELECT DocID, Title, SpecialPurposeColumns  
FROM DocumentStoreWithColumnSet  
WHERE ProductionSpecification IS NOT NULL ;

Aqui está o conjunto de resultados.

DocID  Title        SpecialPurposeColumns  
1      Tire Spec 1  <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>  
3      Tire Spec 2  <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>

F. Observe os resultados da seleção de colunas esparsas por nome

Quando uma tabela contém um conjunto de colunas, você ainda pode consultar a tabela usando os nomes de colunas individuais, conforme mostrado no exemplo a seguir.

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

Aqui está o conjunto de resultados.

DocID  Title        ProductionSpecification ProductionLocation`  
1      Tire Spec 1  AXZZ217                 27`  
3      Tire Spec 2  AXW9R411                38`

G. Atualizar uma tabela usando um conjunto de colunas

O exemplo a seguir atualiza o terceiro registro com novos valores para ambas as colunas esparsas usadas por essa linha.

UPDATE DocumentStoreWithColumnSet  
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification><ProductionLocation>38</ProductionLocation>'  
WHERE DocID = 3 ;  
GO

Important

Uma instrução UPDATE que usa um conjunto de colunas atualiza todas as colunas esparsas na tabela. As colunas esparsas que não são referenciadas são atualizadas para NULL.

O exemplo a seguir atualiza o terceiro registro, mas especifica apenas o valor de uma das duas colunas preenchidas. A segunda coluna ProductionLocation não está incluída na instrução UPDATE e é atualizada para NULL.

UPDATE DocumentStoreWithColumnSet  
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification>'  
WHERE DocID = 3 ;  
GO

Próximos passos

  • Last updated on