Usar conjuntos de colunasUse Column Sets

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure simSQL Data Warehouse do Azure simParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

As tabelas que usam colunas esparsas podem designar um conjunto de colunas para retornar todas as colunas esparsas na tabela.Tables that use sparse columns can designate a column set to return all sparse columns in the table. Um conjunto de colunas é uma representação em XML sem-tipo que combina todas as colunas esparsas de uma tabela em uma saída estruturada.A column set is an untyped XML representation that combines all the sparse columns of a table into a structured output. Um conjunto de colunas é como uma coluna calculada em que o conjunto de colunas não é fisicamente armazenado na tabela.A column set is like a calculated column in that the column set is not physically stored in the table. Um conjunto de colunas difere de uma coluna calculada em que o conjunto de colunas é diretamente atualizável.A column set differs from a calculated column in that the column set is directly updatable.

Considere o uso de conjuntos de colunas quando o número de colunas em uma tabela for grande e trabalhar nelas individualmente for difícil.You should consider using column sets when the number of columns in a table is large, and operating on them individually is cumbersome. Os aplicativos devem observar alguma melhoria no desempenho quando selecionam e inserem dados usando conjuntos de colunas em tabelas com muitas colunas.Applications might see some performance improvement when they select and insert data by using column sets on tables that have lots of columns. Entretanto, o desempenho de conjuntos de colunas pode ser reduzido quando muitos índices são definidos nas colunas das tabelas.However, the performance of column sets can be reduced when many indexes are defined on the columns in the table. Isso porque a quantidade de memória necessária para um plano de execução aumenta.This is because the amount of memory that is required for an execution plan increases.

Para definir um conjunto de colunas, use as palavras-chave <nome_do_conjunto_de_colunas> FOR ALL_SPARSE_COLUMNS nas instruções CREATE TABLE ou ALTER TABLE.To define a column set, use the <column_set_name> FOR ALL_SPARSE_COLUMNS keywords in the CREATE TABLE or ALTER TABLE statements.

Diretrizes para usar conjuntos de colunasGuidelines for Using Column Sets

Ao usar conjuntos de colunas, considere as seguintes diretrizes:When you use column sets, consider the following guidelines:

  • Colunas esparsas e um conjunto de colunas podem ser adicionados como parte da mesma instrução.Sparse columns and a column set can be added as part of the same statement.

  • Um conjunto de colunas não poderá ser adicionado a uma tabela se ela já contiver colunas esparsas.A column set cannot be added to a table if that table already contains sparse columns.

  • O conjunto de colunas não pode ser alterado.The column set cannot be changed. Para alterar um conjunto de colunas, as colunas esparsas e o próprio conjunto de colunas devem ser excluídos e recriados.To change a column set, you must delete and re-create the sparse columns and the column set.

  • Um conjunto de colunas pode ser adicionado a uma tabela que não inclui colunas esparsas.A column set can be added to a table that does not include any sparse columns. Se, posteriormente, forem adicionadas colunas esparsas à tabela, elas serão exibidas no conjunto de colunas.If sparse columns are later added to the table, they will appear in the column set.

  • Somente um conjunto de colunas é permitido por tabela.Only one column set is allowed per table.

  • Um conjunto de colunas é opcional e não é exigido para usar colunas esparsas.A column set is optional and is not required to use sparse columns.

  • Restrições ou valores padrão não podem ser definidos em um conjunto de colunas.Constraints or default values cannot be defined on a column set.

  • Colunas computadas não podem conter colunas de conjunto de colunas.Computed columns cannot contain column set columns.

  • Consultas distribuídas não têm suporte em tabelas que contêm conjuntos de colunas.Distributed queries are not supported on tables that contain column sets.

  • Replicação não oferece suporte a conjuntos de colunas.Replication does not support column sets.

  • Change data capture não oferece suporte a conjuntos de colunas.Change data capture does not support column sets.

  • Um conjunto de colunas não pode fazer parte de nenhum tipo de índice.A column set cannot be part of any kind of index. Incluindo índices XML, índices de texto completo e exibições indexadas.This includes XML indexes, full-text indexes, and indexed views. Um conjunto de colunas não pode ser adicionado como uma coluna incluída em qualquer índice.A column set cannot be added as an included column in any index.

  • Um conjunto de colunas não pode ser usado na expressão de filtro de um índice filtrado ou estatísticas filtradas.A column set cannot be used in the filter expression of a filtered index or filtered statistics.

  • Quando uma exibição inclui um conjunto de colunas, ele é exibido na exibição como uma coluna XML.When a view includes a column set, the column set appears in the view as an XML column.

  • Um conjunto de colunas não pode ser incluído em uma definição de exibição indexada.A column set cannot be included in an indexed view definition.

  • Exibições particionadas que incluem tabelas com conjuntos de colunas são atualizáveis quando a exibição particionada especifica as colunas esparsas por nome.Partitioned views that include tables that contain column sets are updatable when the partitioned view specifies the sparse columns by name. Uma exibição particionada não é atualizável quando referencia o conjunto de colunas.A partitioned view is not updatable when it references the column set.

  • Notificações de consulta referentes a conjuntos de colunas não são permitidas.Query notifications that refer to column sets are not permitted.

  • Dados XML têm um limite de tamanho de 2 GB.XML data has a size limit of 2 GB. Se os dados combinados de todas as colunas esparsas não nulas em uma linha excederem esse limite, a operação de consulta ou DML produzirá um erro.If the combined data of all the nonnull sparse columns in a row exceeds this limit, the query or DML operation will produce an error.

  • Para obter informações sobre os dados retornados pela função COLUMNS_UPDATED, veja Usar colunas esparsas.For information about the data that is returned by the COLUMNS_UPDATED function, see Use Sparse Columns.

Diretrizes para selecionar dados de um conjunto de colunasGuidelines for Selecting Data from a Column Set

Considere as seguintes diretrizes para selecionar dados de um conjunto de colunas:Consider the following guidelines for selecting data from a column set:

  • Conceitualmente, um conjunto de colunas é um tipo coluna XML computada, atualizável, que agrega um conjunto de colunas relacionais subjacentes em uma representação XML única.Conceptually, a column set is a type of updatable, computed XML column that aggregates a set of underlying relational columns into a single XML representation. O conjunto de colunas só oferece suporte à propriedade ALL_SPARSE_COLUMNS.The column set only supports the ALL_SPARSE_COLUMNS property. Essa propriedade é usada para agregar todos os valores não nulos de todas as colunas esparsas para uma linha específica.This property is used to aggregate all nonnull values from all sparse columns for a particular row.

  • No editor de tabela do SQL Server Management StudioSQL Server Management Studio os conjuntos de colunas são exibidos como um campo XML editável.In the SQL Server Management StudioSQL Server Management Studio table editor, column sets are displayed as an editable XML field. Defina os conjuntos de colunas no formato:Define column sets in the format:

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

    Exemplos de conjuntos de colunas:Examples of column set values are as follows:

    • <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 do conjunto de colunas.Sparse columns that contain null values are omitted from the XML representation for the column set.

Aviso

A adição de um conjunto de colunas altera o comportamento de consultas SELECT *.Adding a column set changes the behavior of SELECT * queries. A consulta retornará o conjunto de colunas como uma coluna XML e não retornará as colunas esparsas individuais.The query will return the column set as an XML column and not return the individual sparse columns. Designers de esquema e desenvolvedores de software devem ter cuidado para não violar aplicativos existentes.Schema designers and software developers must be careful not to break existing applications.

Inserindo ou modificando dados em um conjunto de colunasInserting or Modifying Data in a Column Set

A manipulação de dados de uma coluna esparsa pode ser executada usando o nome de colunas individuais ou referenciando o nome do conjunto de colunas e especificando seus valores usando o formato XML do conjunto de colunas.Data manipulation of a sparse column can be performed by using the name of the individual columns, or by referencing the name of the column set and specifying the values of the column set by using the XML format of the column set. As colunas esparsas podem ser exibidas em qualquer ordem na coluna XML.Sparse columns can appear in any order in the XML column.

Quando os valores de colunas esparsas 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 .When sparse column values are inserted or updated by using the XML column set, the values that are inserted into the underlying sparse columns are implicitly converted from the xml data type. No caso de colunas numéricas, um valor em branco no XML para a coluna numérica é convertido em uma cadeia de caracteres vazia.In the case of numeric columns, a blank value in the XML for the numeric column converts to an empty string. Isso faz com que um zero seja inserido na coluna numérica, como mostrado no exemplo a seguir.This causes a zero to be inserted into the numeric column as shown in the following example.

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  

Nesse exemplo, nenhum valor foi especificado para a coluna i, mas o valor 0 foi inserido.In this example, no value was specified for the column i, but the value 0 was inserted.

Usando o tipo de dados sql_variantUsing the sql_variant Data Type

O tipo de dados sql_variant pode armazenar vários tipos de dados diferentes, como int, chare data.The sql_variant date type can store multiple different data types, such as int, char, and date. Os conjuntos de colunas geram informações de tipo de dados como escala, precisão e informações de localidade que são associadas a um valor sql_variant como atributo na coluna XML gerada.Column sets output the data type information such as scale, precision, and locale information that is associated with a sql_variant value as attributes in the generated XML column. 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 exigidos e a outros será atribuído um valor padrão.If you try to provide these attributes in a custom-generated XML statement as an input for an insert or update operation on a column set, some of these attributes are required and some of them are assigned a default value. A tabela a seguir lista os tipos de dados e os valores padrão que o servidor gera quando o valor não é fornecido.The following table lists the data types and the default values that the server generates when the value is not provided.

Tipo de dadosData type localeID*localeID* sqlCompareOptionssqlCompareOptions sqlCollationVersionsqlCollationVersion SqlSortIdSqlSortId Comprimento máximoMaximum length PrecisãoPrecision EscalaScale
char, varchar, binarychar, varchar, binary -1-1 'Padrão''Default' 00 00 80008000 Não aplicável**Not applicable** Não aplicávelNot applicable
nvarcharnvarchar -1-1 'Padrão''Default' 00 00 40004000 Não aplicávelNot applicable Não aplicávelNot applicable
decimal, float, realdecimal, float, real Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable 1818 00
integer, bigint, tinyint, smallintinteger, bigint, tinyint, smallint Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable
datetime2datetime2 Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable 77
datetime offsetdatetime offset Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable 77
datetime, date, smalldatetimedatetime, date, smalldatetime Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable
money, smallmoneymoney, smallmoney Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable
timetime Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable Não aplicávelNot applicable 77

* localeID -1 significa a localidade padrão.* localeID -1 means the default locale. A localidade Inglês é 1033.The English locale is 1033.

** Não aplicável = nenhum valor é produzido para esses atributos durante uma operação de seleção no conjunto de colunas.** Not applicable = No values are output for these attributes during a select operation on the column set. Gera um erro quando um valor é especificado para esse atributo pelo chamador na representação XML fornecida por um conjunto de colunas, em uma operação de inserção ou atualização.Generates an error when a value is specified for this attribute by the caller in the XML representation provided for a column set in an insert or update operation.

SegurançaSecurity

O modelo de segurança para um conjunto de colunas funciona de modo semelhante ao modelo de segurança que existe entre tabela e colunas.The security model for a column set works similar to the security model that exists between table and columns. Os conjuntos de colunas podem ser visualizados como uma minitabela e uma operação de seleção é como uma operação SELECT * nessa minitabela.Column sets can be visualized as a minitable and a select operation is like a SELECT * operation on this minitable. Mas a relação entre o conjunto de colunas e as colunas esparsas é uma relação de agrupamento, em vez de estritamente um contêiner.But, the relationship between column set to sparse columns is a grouping relationship instead of strictly a container. O modelo de segurança verifica a segurança na coluna do conjunto de colunas e respeita as operações DENY nas colunas esparsas subjacentes.The security model checks the security on the column set column, and honors the DENY operations on the underlying sparse columns. Características adicionais do modelo de segurança:Additional characteristics of the security model are as follows:

  • Permissões de segurança podem ser concedidas e revogadas da coluna do conjunto de colunas, semelhante a qualquer outra coluna na tabela.Security permissions can be granted and revoked from the column set column, similar to any other column in the table.

  • Um GRANT ou REVOKE de permissões SELECT, INSERT, UPDATE, DELETE e REFERENCES em uma coluna de conjunto de colunas não se propaga às colunas membros subjacentes daquele conjunto.A GRANT or REVOKE of SELECT, INSERT, UPDATE, DELETE, and REFERENCES permission on a column set column does not propagate to the underlying member columns of that set. Só se aplica ao uso da coluna do conjunto de colunas.It applies only to the usage of the column set column. Permissão DENY em um conjunto de colunas se propaga às colunas esparsas subjacentes da tabela.DENY permission on a column set does propagate to the underlying sparse columns of the table.

  • A execução das instruções SELECT, INSERT, UPDATE e DELETE na coluna do conjunto de colunas exige que o usuário tenha permissões correspondentes na coluna do conjunto de colunas e, também, a permissão correspondente em todas as colunas esparsas da tabela.Executing SELECT, INSERT, UPDATE, and DELETE statements on the column set column require that the user has corresponding permissions on the column set column, and also the corresponding permission on all the sparse columns in the table. Como o conjunto de colunas representa todas as colunas esparsas na tabela, é necessário ter permissão para todas as colunas esparsas, incluindo as colunas esparsas que podem não estar sendo alteradas.Because the column set represents all the sparse columns in the table, you must have permission on all the sparse columns, and this includes sparse columns that you might not be changing.

  • A execução de uma instrução REVOKE em uma coluna esparsa ou conjunto de colunas faz com que a segurança assuma como padrão seu objeto pai.Executing a REVOKE statement on a sparse column or column set defaults the security to their parent object.

ExemplosExamples

Nos exemplos a seguir, uma tabela de documento contém o conjunto comum de colunas DocID e Title.In the following examples, a document table contains the common set of 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.

A.A. Criando uma tabela que possui um conjunto de colunasCreating a table that has a column set

O exemplo a seguir cria a tabela que usa colunas esparsas e inclui o conjunto de colunas SpecialPurposeColumns.The following example creates the table that uses sparse columns and includes the column set SpecialPurposeColumns. O exemplo insere duas linhas na tabela e, depois, seleciona dados da tabela.The example 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.

USE AdventureWorks2012;  
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.B. Inserindo dados em uma tabela usando os nomes das colunas esparsasInserting data to a table by using the names of the sparse columns

Os exemplos a seguir inserem duas linhas na tabela criada no exemplo A. Os exemplos usam os nomes das colunas esparsas e não referenciam o conjunto de colunas.The following examples insert two rows into the table that is created in example A. The examples use the names of the sparse columns and do not reference the column set.

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.C. Inserindo dados em uma tabela usando o nome do conjunto de colunasInserting data to a table by using the name of the column set

O exemplo a seguir insere uma terceira linha na tabela criada no exemplo A. Dessa vez, os nomes das colunas esparsas não são usados.The following example inserts a third row into the table that is created in example A. This time the names of the sparse columns are not used. Em vez disso, o nome do conjunto de colunas é usado e a inserção fornece os valores para duas das colunas esparsas no formato XML.Instead, the name of the column set is used, and the insert provides the values for two of the four sparse columns in XML format.

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

D.D. Observando os resultados de um conjunto de colunas quando SELECT * é usadoObserving the results of a column set when SELECT * is used

O exemplo a seguir seleciona todas as colunas da tabela que contém um conjunto de colunas.The following example selects all the columns from the table that contains a column set. Retorna uma coluna XML com os valores combinados das colunas esparsas.It returns an XML column with the combined values of the sparse columns. Não retorna as colunas esparsas individualmente.It does not return the sparse columns individually.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;  

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

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.E. Observando os resultados da seleção do conjunto de colunas por nomeObserving the results of selecting the column set by name

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.Because the Production department is not interested in the marketing data, this example adds a WHERE clause to restrict the output. O exemplo usa o nome do conjunto de colunas.The example uses the name of the column set.

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

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

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

F.F. Observando os resultados da seleção de colunas esparsas por nomeObserving the results of selecting sparse columns by name

Quando uma tabela contém um conjunto de colunas, você ainda pode consultar a tabela usando nomes de colunas individuais, como mostrado no exemplo a seguir.When a table contains a column set, you can still query the table by using the individual column names as shown in the following example.

SELECT DocID, Title, ProductionSpecification, ProductionLocation   
FROM DocumentStoreWithColumnSet  
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`  
3      Tire Spec 2  AXW9R411                38`  

G.G. Atualizando uma tabela usando um conjunto de colunasUpdating a table by using a column set

O exemplo a seguir atualiza o terceiro registro com os novos valores para as colunas esparsas usadas por aquela linha.The following example updates the third record with new values for both sparse columns that are used by that row.

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

Importante

Uma instrução UPDATE que usa um conjunto de colunas atualiza todas as colunas esparsas na tabela.An UPDATE statement that uses a column set updates all the sparse columns in the table. As colunas esparsas que não são referenciadas são atualizadas como NULL.Sparse columns that are not referenced are updated to NULL.

O exemplo a seguir atualiza o terceiro registro, mas só especifica o valor de uma das duas colunas populadas.The following example updates the third record, but only specifies the value of one of the two populated columns. A segunda coluna ProductionLocation não é incluída na instrução UPDATE e é atualizada como NULL.The second column ProductionLocation is not included in the UPDATE statement and is updated to NULL.

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

Consulte TambémSee Also

Usar colunas esparsasUse Sparse Columns