Usar conjuntos de columnasUse Column Sets

SE APLICA A: síSQL Server síAzure SQL Database síAzure Synapse Analytics (SQL DW) síAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Las tablas que utilizan columnas dispersas pueden designar un conjunto de columnas que devuelva todas las columnas dispersas de la tabla.Tables that use sparse columns can designate a column set to return all sparse columns in the table. Un conjunto de columnas es una representación XML sin tipo que combina todas las columnas dispersas de una tabla en una salida estructurada.A column set is an untyped XML representation that combines all the sparse columns of a table into a structured output. Un conjunto de columnas se asemeja a una columna calculada en que el conjunto no se almacena físicamente en la tabla.A column set is like a calculated column in that the column set is not physically stored in the table. Un conjunto de columnas difiere de una columna calculada en que el conjunto de columnas se puede actualizar directamente.A column set differs from a calculated column in that the column set is directly updatable.

Considere la posibilidad de usar los conjuntos de columnas cuando una tabla contenga un gran número de columnas y resulte complicado realizar cualquier operación con ellas.You should consider using column sets when the number of columns in a table is large, and operating on them individually is cumbersome. Las aplicaciones pueden experimentar ciertas mejoras de rendimiento si realizan la selección e inserción de datos usando los conjuntos de columnas en tablas que contienen muchas columnas.Applications might see some performance improvement when they select and insert data by using column sets on tables that have lots of columns. Sin embargo, es posible que se reduzca el rendimiento de los conjuntos de columnas si se definen muchos índices en las columnas de la tabla.However, the performance of column sets can be reduced when many indexes are defined on the columns in the table. Esto es debido a que aumenta la cantidad de memoria necesaria para un plan de ejecución.This is because the amount of memory that is required for an execution plan increases.

Para definir un conjunto de columnas, use las palabras clave <column_set_name> FOR ALL_SPARSE_COLUMN de las instrucciones CREATE TABLE o 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.

Directrices para usar conjuntos de columnasGuidelines for Using Column Sets

Cuando use conjuntos de columnas, tenga en cuenta las directrices siguientes:When you use column sets, consider the following guidelines:

  • Se pueden agregar columnas dispersas y un conjunto de columnas como parte de la misma instrucción.Sparse columns and a column set can be added as part of the same statement.

  • No se puede agregar un conjunto de columnas a una tabla si ésta ya contiene columnas dispersas.A column set cannot be added to a table if that table already contains sparse columns.

  • El conjunto de columnas no puede modificarse.The column set cannot be changed. Para modificar un conjunto de columnas, es preciso eliminar y volver a crear las columnas dispersas.To change a column set, you must delete and re-create the sparse columns and the column set.

  • Se puede agregar un conjunto de columnas a una tabla si ésta no incluye ninguna columna dispersa.A column set can be added to a table that does not include any sparse columns. Si posteriormente se agregan columnas dispersas a la tabla, dichas columnas aparecerán en el conjunto de columnas.If sparse columns are later added to the table, they will appear in the column set.

  • Solo se permite un conjunto de columnas por tabla.Only one column set is allowed per table.

  • El conjunto de columnas es opcional y no es necesario para usar columnas dispersas.A column set is optional and is not required to use sparse columns.

  • No se pueden definir restricciones ni valores predeterminados en un conjunto de columnas.Constraints or default values cannot be defined on a column set.

  • Las columnas calculadas no pueden contener columnas del conjunto de columnas.Computed columns cannot contain column set columns.

  • Las consultas distribuidas no se pueden utilizar en tablas que contienen conjuntos de columnas.Distributed queries are not supported on tables that contain column sets.

  • La replicación no admite conjuntos de columnas.Replication does not support column sets.

  • La captura de datos modificados no admite conjuntos de columnas.Change data capture does not support column sets.

  • Un conjunto de columnas no puede formar parte de ningún tipo de índice.A column set cannot be part of any kind of index. Esto incluye índices XML, índices de texto completo y vistas indizadas.This includes XML indexes, full-text indexes, and indexed views. Un conjunto de columnas no se puede agregar como columna incluida en ningún índice.A column set cannot be added as an included column in any index.

  • Un conjunto de columnas no se puede utilizar en la expresión de filtro de un índice filtrado o de estadísticas filtradas.A column set cannot be used in the filter expression of a filtered index or filtered statistics.

  • Cuando una vista incluye un conjunto de columnas, éste aparece en la vista como una columna XML.When a view includes a column set, the column set appears in the view as an XML column.

  • Un conjunto de columnas no se puede incluir en la definición de una vista indizada.A column set cannot be included in an indexed view definition.

  • Las vistas con particiones que incluyen tablas que contienen conjuntos de columnas se actualizan cuando la vista con particiones especifica las columnas dispersas por nombre.Partitioned views that include tables that contain column sets are updatable when the partitioned view specifies the sparse columns by name. Una vista con particiones no se actualiza cuando hace referencia al conjunto de columnas.A partitioned view is not updatable when it references the column set.

  • Las notificaciones de consulta que hacen referencia a conjuntos de columnas no se permiten.Query notifications that refer to column sets are not permitted.

  • Los datos XML tienen un límite de tamaño de 2 GB.XML data has a size limit of 2 GB. Si los datos combinados de todas las columnas dispersas cuyo valor de una fila no sea NULL superan este límite, la consulta o la operación DML generará un error.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 obtener más información sobre los datos devueltos por la función COLUMNS_UPDATED, vea Usar columnas dispersas.For information about the data that is returned by the COLUMNS_UPDATED function, see Use Sparse Columns.

Directrices para seleccionar datos de un conjunto de columnasGuidelines for Selecting Data from a Column Set

Tenga en cuenta las directrices siguientes a la hora de seleccionar datos de un conjunto de columnas:Consider the following guidelines for selecting data from a column set:

  • Conceptualmente, un conjunto de columnas es un tipo de columna XML calculada y actualizable que agrega un conjunto de columnas relacionales subyacentes en una única representación XML.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. El conjunto de columnas solo admite la propiedad ALL_SPARSE_COLUMNS.The column set only supports the ALL_SPARSE_COLUMNS property. Esta propiedad se usa para agregar todos los valores distintos de NULL de todas las columnas dispersas para una fila determinada.This property is used to aggregate all nonnull values from all sparse columns for a particular row.

  • En el editor de tablas de SQL Server Management StudioSQL Server Management Studio , los conjuntos de columnas se muestran como un campo XML modificable.In the SQL Server Management StudioSQL Server Management Studio table editor, column sets are displayed as an editable XML field. Defina los conjuntos de columnas con el siguiente formato:Define column sets in the format:

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

    A continuación se enumeran algunos ejemplos de valores de conjuntos de columnas:Examples of column set values are as follows:

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

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

  • Las columnas dispersas que contienen valores NULL se omiten en la representación XML del conjunto de columnas.Sparse columns that contain null values are omitted from the XML representation for the column set.

Advertencia

Al agregar un conjunto de columnas, cambia el comportamiento de las consultas SELECT *.Adding a column set changes the behavior of SELECT * queries. La consulta devolverá el conjunto de columnas como una columna XML, pero no devolverá las columnas dispersas individuales.The query will return the column set as an XML column and not return the individual sparse columns. Los diseñadores de esquemas y los desarrolladores deben tener cuidado de no alterar el comportamiento de las aplicaciones existentes.Schema designers and software developers must be careful not to break existing applications.

Insertar o modificar datos en un conjunto de columnasInserting or Modifying Data in a Column Set

La manipulación de los datos de una columna dispersa se puede llevar a cabo usando el nombre de las columnas individuales, o bien haciendo referencia al nombre del conjunto de columnas y especificando sus valores usando el formato XML de dicho conjunto.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. Las columnas dispersas pueden aparecer en cualquier orden en la columna XML.Sparse columns can appear in any order in the XML column.

Cuando se insertan o actualizan valores de columnas dispersas usando el conjunto de columnas XML, los valores que se insertan en las columnas dispersas subyacentes se convierten de manera implícita desde el tipo de datos 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. En el caso de columnas numéricas, un valor en blanco en el XML para la columna numérica se convierte en una cadena vacía.In the case of numeric columns, a blank value in the XML for the numeric column converts to an empty string. Esto hace que se inserte un cero en la columna numérica, tal y como se muestra en el ejemplo siguiente.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  

En este ejemplo, no se ha especificado ningún valor para la columna i, pero se ha insertado el valor 0 .In this example, no value was specified for the column i, but the value 0 was inserted.

Usar el tipo de datos sql_variantUsing the sql_variant Data Type

El tipo de datos sql_variant puede almacenar varios tipos de datos distintos, como int, chary date.The sql_variant date type can store multiple different data types, such as int, char, and date. Los conjuntos de columnas generan la información sobre el tipo de datos, como la escala, la precisión y la información de configuración regional que se asocia a un valor sql_variant , como atributos en la columna XML generada.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. Si intenta proporcionar estos atributos en una instrucción XML generada de forma personalizada como una entrada para una operación de inserción o de actualización en un conjunto de columnas, algunos de dichos atributos son obligatorios y a otros se les asigna un valor predeterminado.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. En la tabla siguiente se enumeran los tipos de datos y los valores predeterminados que genera el servidor cuando no se proporciona el valor.The following table lists the data types and the default values that the server generates when the value is not provided.

Tipo de datosData type localeID*localeID* sqlCompareOptionssqlCompareOptions sqlCollationVersionsqlCollationVersion SqlSortIdSqlSortId Longitud máximaMaximum length PrecisiónPrecision EscalaScale
char, varchar, binarychar, varchar, binary -1-1 'Default''Default' 00 00 80008000 No aplicable**Not applicable** No aplicableNot applicable
nvarcharnvarchar -1-1 'Default''Default' 00 00 40004000 No aplicableNot applicable No aplicableNot applicable
decimal, float, realdecimal, float, real No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable 1818 00
integer, bigint, tinyint, smallintinteger, bigint, tinyint, smallint No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable
datetime2datetime2 No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable 77
datetime offsetdatetime offset No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable 77
datetime, date, smalldatetimedatetime, date, smalldatetime No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable
money, smallmoneymoney, smallmoney No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable
timetime No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable No aplicableNot applicable 77

* Un valor -1 en localeID significa la configuración regional predeterminada.* localeID -1 means the default locale. La configuración regional en inglés es 1033.The English locale is 1033.

No aplicable = No se genera ningún valor para estos atributos durante una operación de selección en el conjunto de columnas.** Not applicable = No values are output for these attributes during a select operation on the column set. Genera un error cuando el autor de la llamada especifica un valor para este atributo en la representación XML proporcionada para un conjunto de columnas durante una operación de inserción o actualización.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.

SeguridadSecurity

El modelo de seguridad para un conjunto de columnas funciona de forma similar al modelo de seguridad existente entre la tabla y columnas.The security model for a column set works similar to the security model that exists between table and columns. Los conjuntos de columnas se pueden visualizar en forma de minitabla, en la que una operación de selección funciona como una operación SELECT *.Column sets can be visualized as a minitable and a select operation is like a SELECT * operation on this minitable. Pero la relación entre el conjunto de columnas y las columnas dispersas es una relación de agrupación en lugar de ser estrictamente un contenedor.But, the relationship between column set to sparse columns is a grouping relationship instead of strictly a container. El modelo de seguridad comprueba la seguridad en la columna del conjunto de columnas y respeta las operaciones DENY en las columnas dispersas subyacentes.The security model checks the security on the column set column, and honors the DENY operations on the underlying sparse columns. Las características adicionales del modelo de seguridad son las siguientes:Additional characteristics of the security model are as follows:

  • Los permisos de seguridad se pueden otorgar y revocar desde la columna del conjunto de columnas, de forma similar a como se haría en cualquier otra columna de la tabla.Security permissions can be granted and revoked from the column set column, similar to any other column in the table.

  • Una instrucción GRANT o REVOKE para los permisos SELECT, INSERT, UPDATE, DELETE o REFERENCES en una columna del conjunto de columnas no se propaga a las columnas miembro subyacentes de dicho 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. Solo se aplica al uso de la columna del conjunto de columnas.It applies only to the usage of the column set column. El permiso DENY en un conjunto de columnas sí se propaga a las columnas dispersas subyacentes de la tabla.DENY permission on a column set does propagate to the underlying sparse columns of the table.

  • La ejecución de las instrucciones SELECT, INSERT, UPDATE y DELETE en la columna del conjunto de columnas requiere que el usuario disponga de los permisos correspondientes en dicha columna, así como en todas las columnas dispersas de la tabla.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. Dado que el conjunto de columnas representa todas las columnas dispersas de la tabla, debe tener el permiso correspondiente en estas columnas, y esto incluye aquellas columnas dispersas que no va a modificar.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.

  • La ejecución de una instrucción REVOKE en una columna dispersa o en un conjunto de columnas hace que éstas adopten la configuración de seguridad de su objeto primario.Executing a REVOKE statement on a sparse column or column set defaults the security to their parent object.

EjemplosExamples

En los ejemplos siguientes, una tabla de documentos contiene el conjunto de columnas común DocID y Title.In the following examples, a document table contains the common set of columns DocID and Title. El grupo de producción desea tener una columna ProductionSpecification y una columna ProductionLocation para todos los documentos de producción.The Production group wants a ProductionSpecification and ProductionLocation column for all production documents. El grupo de marketing desea tener una columna MarketingSurveyGroup para los documentos de marketing.The Marketing group wants a MarketingSurveyGroup column for marketing documents.

A.A. Crear una tabla que tenga un conjunto de columnasCreating a table that has a column set

En el ejemplo siguiente se crea la tabla que utiliza columnas dispersas y se incluye el conjunto de columnas SpecialPurposeColumns.The following example creates the table that uses sparse columns and includes the column set SpecialPurposeColumns. El ejemplo inserta dos filas en la tabla y, a continuación, selecciona datos de dicha tabla.The example inserts two rows into the table, and then selects data from the table.

Nota

Esta tabla solo tiene cinco columnas para facilitar su visualización y lectura.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. Insertar datos en una tabla usando los nombres de las columnas dispersasInserting data to a table by using the names of the sparse columns

En los ejemplos siguientes se insertan dos filas en la tabla creada en el ejemplo A. Estos ejemplos utilizan los nombres de las columnas dispersas y no hacen referencia al conjunto de columnas.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. Insertar datos en una tabla usando el nombre del conjunto de columnasInserting data to a table by using the name of the column set

En el ejemplo siguiente se inserta una tercera fila en la tabla creada en el ejemplo A. Esta vez no se utilizan los nombres de las columnas dispersas.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. En su lugar, se utiliza el nombre del conjunto de columnas y la operación de inserción proporciona los valores para dos de las cuatro columnas dispersas en 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. Observar los resultados de un conjunto de columnas al usar SELECT *Observing the results of a column set when SELECT * is used

En el ejemplo siguiente se seleccionan todas las columnas de la tabla que contiene un conjunto de columnas.The following example selects all the columns from the table that contains a column set. Devuelve una columna XML con los valores combinados de las columnas dispersas.It returns an XML column with the combined values of the sparse columns. No devuelve las columnas dispersas de forma individual.It does not return the sparse columns individually.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;  

El conjunto de resultados es el siguiente.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. Observar los resultados de seleccionar el conjunto de columnas por nombreObserving the results of selecting the column set by name

Dado que el departamento de producción no está interesado en los datos de marketing, en este ejemplo se agrega una cláusula WHERE para restringir la salida.Because the Production department is not interested in the marketing data, this example adds a WHERE clause to restrict the output. El ejemplo usa el nombre del conjunto de columnas.The example uses the name of the column set.

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

El conjunto de resultados es el siguiente.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. Observar los resultados de seleccionar columnas dispersas por nombreObserving the results of selecting sparse columns by name

Si una tabla contiene un conjunto de columnas, también se puede consultar dicha tabla usando los nombres de columna individuales, tal y como se muestra en el ejemplo siguiente.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 ;  

El conjunto de resultados es el siguiente.Here is the result set.

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

G.G. Actualizar una tabla usando un conjunto de columnasUpdating a table by using a column set

En el ejemplo siguiente se actualiza el tercer registro con nuevos valores para las dos columnas dispersas que usa la fila.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

Una instrucción UPDATE que usa un conjunto de columnas actualiza todas las columnas dispersas de la tabla.An UPDATE statement that uses a column set updates all the sparse columns in the table. Los valores de las columnas dispersas a las que no se hace referencia se actualizan a NULL.Sparse columns that are not referenced are updated to NULL.

En el ejemplo siguiente se actualiza el tercer registro, pero solo se especifica el valor de una de las dos columnas rellenadas.The following example updates the third record, but only specifies the value of one of the two populated columns. La segunda columna, ProductionLocation , no está incluida en la instrucción UPDATE y se actualiza a 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 tambiénSee Also

Usar columnas dispersasUse Sparse Columns