Utilizzare set di colonneUse Column Sets

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2016)sìDatabase SQL di AzuresìAzure SQL Data Warehouse sìParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Nelle tabelle che utilizzano colonne di tipo sparse è possibile definire un set di colonne per restituire tutte le colonne di tipo sparse della tabella.Tables that use sparse columns can designate a column set to return all sparse columns in the table. Un set di colonne è una rappresentazione XML non tipizzata che combina tutte le colonne di tipo sparse di una tabella in un output strutturato.A column set is an untyped XML representation that combines all the sparse columns of a table into a structured output. Un set di colonne è analogo a una colonna calcolata poiché non è archiviato fisicamente nella tabella,A column set is like a calculated column in that the column set is not physically stored in the table. ma differisce da una colonna calcolata poiché è direttamente aggiornabile.A column set differs from a calculated column in that the column set is directly updatable.

È necessario utilizzare i set di colonne quando il numero di colonne di una tabella è elevato e l'esecuzione di operazioni sulle singole colonne risulta eccessivamente complessa.You should consider using column sets when the number of columns in a table is large, and operating on them individually is cumbersome. Potrebbe essere possibile ottenere un miglioramento delle prestazioni relative alle applicazioni in caso di selezione e inserimento dei dati utilizzando set di colonne in tabelle in cui il numero di colonne è elevato.Applications might see some performance improvement when they select and insert data by using column sets on tables that have lots of columns. Le prestazioni relative ai set di colonne, tuttavia, possono risultare ridotte quando nelle colonne della tabella sono definiti molti indici,However, the performance of column sets can be reduced when many indexes are defined on the columns in the table. poiché la quantità di memoria necessaria per un piano di esecuzione aumenta.This is because the amount of memory that is required for an execution plan increases.

Per definire un set di colonne, usare le parole chiave FOR ALL_SPARSE_COLUMNS nelle istruzioni CREATE TABLE o ALTER TABLE.To define a column set, use the FOR ALL_SPARSE_COLUMNS keywords in theCREATE TABLE or ALTER TABLE statements.

Linee guida per l'utilizzo di set di colonneGuidelines for Using Column Sets

Quando si utilizzano set di colonne, tenere presente le linee guida seguenti:When you use column sets, consider the following guidelines:

  • Le colonne di tipo sparse e un set di colonne possono essere aggiunti come parte della stessa istruzione.Sparse columns and a column set can be added as part of the same statement.

  • Un set di colonne non può essere aggiunto a una tabella in cui sono già contenute colonne di tipo sparse.A column set cannot be added to a table if that table already contains sparse columns.

  • Il set di colonne non può essere modificato.The column set cannot be changed. Per modificare un set di colonne, è necessario eliminarlo e ricreare le colonne di tipo sparse e il set di colonne.To change a column set, you must delete and re-create the sparse columns and the column set.

  • Un set di colonne può essere aggiunto a una tabella in cui non è contenuta alcuna colonna di tipo sparse.A column set can be added to a table that does not include any sparse columns. Se le colonne di tipo sparse vengono aggiunte alla tabella in un secondo momento, verranno visualizzate nel set di colonne.If sparse columns are later added to the table, they will appear in the column set.

  • Per ogni tabella è consentito un unico set di colonne.Only one column set is allowed per table.

  • Un set di colonne è facoltativo e non è necessario per utilizzare colonne di tipo sparse.A column set is optional and is not required to use sparse columns.

  • Non è possibile definire vincoli o valori predefiniti in un set di colonne.Constraints or default values cannot be defined on a column set.

  • Nelle colonne calcolate non possono essere contenute colonne di un set di colonne.Computed columns cannot contain column set columns.

  • Le query distribuite non sono supportate in tabelle che contengono set di colonne.Distributed queries are not supported on tables that contain column sets.

  • I set di colonne non sono supportati dalla replica.Replication does not support column sets.

  • I set di colonne non sono supportati da Change Data Capture.Change data capture does not support column sets.

  • Un set di colonne non può appartenere ad alcun tipo di indice,A column set cannot be part of any kind of index. ad esempio indici XML, indici full-text e viste indicizzate.This includes XML indexes, full-text indexes, and indexed views. Non è inoltre possibile aggiungere un set di colonne come colonna inclusa in un indice.A column set cannot be added as an included column in any index.

  • Un set di colonne non può essere utilizzato nell'espressione di filtro di un indice filtrato o di statistiche filtrate.A column set cannot be used in the filter expression of a filtered index or filtered statistics.

  • Quando in una vista è incluso un set di colonne, quest'ultimo viene visualizzato nella vista come una colonna XML.When a view includes a column set, the column set appears in the view as an XML column.

  • Un set di colonne non può essere incluso nella definizione di una vista indicizzata.A column set cannot be included in an indexed view definition.

  • Le viste partizionate che includono tabelle in cui sono contenuti set di colonne sono aggiornabili quando la vista partizionata specifica le colonne di tipo sparse in base al nome.Partitioned views that include tables that contain column sets are updatable when the partitioned view specifies the sparse columns by name. Una vista partizionata non è aggiornabile quando fa riferimento al set di colonne.A partitioned view is not updatable when it references the column set.

  • Le notifiche delle query che fanno riferimento a set di colonne non sono consentite.Query notifications that refer to column sets are not permitted.

  • Le dimensioni dei dati XML non possono superare i 2 GB.XML data has a size limit of 2 GB. Se i dati combinati di tutte le colonne di tipo sparse diverse da Null in una riga superano questo limite, la query o l'operazione DML genererà un errore.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.

  • Per informazioni sui dati restituiti dalla funzione COLUMNS_UPDATED, vedere Usare le colonne di tipo sparse.For information about the data that is returned by the COLUMNS_UPDATED function, see Use Sparse Columns.

Linee guida per la selezione di dati da un set di colonneGuidelines for Selecting Data from a Column Set

Per selezionare dati da un set di colonne, tenere presente le linee guida seguenti:Consider the following guidelines for selecting data from a column set:

  • Concettualmente, un set di colonne è un tipo di colonna XML calcolata e aggiornabile, che aggrega un set di colonne relazionali sottostanti in un'unica rappresentazione 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. Il set di colonne supporta solo la proprietà ALL_SPARSE_COLUMNS,The column set only supports the ALL_SPARSE_COLUMNS property. utilizzata per aggregare tutti i valori diversi da Null di tutte le colonne di tipo sparse per una riga specifica.This property is used to aggregate all nonnull values from all sparse columns for a particular row.

  • Nell'editor di tabella di SQL Server Management StudioSQL Server Management Studio i set di colonne vengono visualizzati come un campo XML modificabile.In the SQL Server Management StudioSQL Server Management Studio table editor, column sets are displayed as an editable XML field. Definire i set di colonne nel formato seguente:Define column sets in the format:

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

    Di seguito vengono riportati alcuni esempi di valori di set di colonne:Examples of column set values are as follows:

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

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

  • Le colonne di tipo sparse in cui sono contenuti valori Null vengono omesse dalla rappresentazione XML del set di colonne.Sparse columns that contain null values are omitted from the XML representation for the column set.

Avviso

L'aggiunta di un set di colonne modifica il comportamento delle query SELECT *.Adding a column set changes the behavior of SELECT * queries. La query restituirà il set di colonne come una colonna XML e non restituirà le singole colonne di tipo sparse.The query will return the column set as an XML column and not return the individual sparse columns. È necessario che i progettisti degli schemi e gli sviluppatori del software prestino particolare attenzione a non causare l'interruzione delle applicazioni esistenti.Schema designers and software developers must be careful not to break existing applications.

Inserimento o modifica di dati in un set di colonneInserting or Modifying Data in a Column Set

Per manipolare i dati di una colonna di tipo sparse, è possibile utilizzare il nome delle colonne singole o fare riferimento al nome del set di colonne e specificarne i valori utilizzando il formato XML del set di colonne stesso.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. Nella colonna XML le colonne di tipo sparse possono essere visualizzate in qualsiasi ordine.Sparse columns can appear in any order in the XML column.

Quando i valori di una colonna di tipo sparse vengono inseriti o aggiornati usando il set di colonne XML, i valori inseriti nelle colonne di tipo sparse sottostanti vengono convertiti implicitamente dal tipo di dati 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. Nel caso di colonne numeriche, un valore vuoto nel codice XML per la colonna numerica viene convertito in una stringa vuota,In the case of numeric columns, a blank value in the XML for the numeric column converts to an empty string. provocando l'inserimento di uno zero nella colonna numerica come illustrato nell'esempio seguente.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  

In questo esempio per la colonna inon è stato specificato alcun valore, ma è stato inserito il valore 0 .In this example, no value was specified for the column i, but the value 0 was inserted.

Utilizzo del tipo di dati sql_variantUsing the sql_variant Data Type

Il tipo di dati sql_variant consente di archiviare più tipi di dati diversi, ad esempio int, chare date.The sql_variant date type can store multiple different data types, such as int, char, and date. I set di colonne restituiscono informazioni sul tipo di dati, ad esempio scala, precisione e informazioni sulle impostazioni locali, associate a un valore sql_variant come attributi nella colonna XML generata.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 si tenta di specificare questi attributi un'istruzione XML generata dall'utente come input per un'operazione di inserimento o di aggiornamento su un set di colonne, alcuni di questi attributi risultano obbligatori e ad alcuni di essi viene assegnato un valore predefinito.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. Nella tabella seguente vengono elencati i tipi di dati e i valori predefiniti generati dal server quando il valore non viene specificato.The following table lists the data types and the default values that the server generates when the value is not provided.

Tipo di datiData type localeIDlocaleID sqlCompareOptionssqlCompareOptions sqlCollationVersionsqlCollationVersion SqlSortIdSqlSortId Lunghezza massimaMaximum length PrecisionePrecision ScalaScale
char, varchar, binarychar, varchar, binary -1-1 Impostazione predefinita'Default' 00 00 80008000 Non applicabileNot applicable Non applicabileNot applicable
nvarcharnvarchar -1-1 Impostazione predefinita'Default' 00 00 4.0004000 Non applicabileNot applicable Non applicabileNot applicable
decimal, float, realdecimal, float, real Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable 1818 00
integer, bigint, tinyint, smallintinteger, bigint, tinyint, smallint Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable
datetime2datetime2 Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable 77
datetime offsetdatetime offset Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable 77
datetime, date, smalldatetimedatetime, date, smalldatetime Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable
money, smallmoneymoney, smallmoney Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable
timetime Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable Non applicabileNot applicable 77

* localeID -1 indica le impostazioni locali predefinite.* localeID -1 means the default locale. Le impostazioni locali per l'inglese sono rappresentate dal valore 1033.The English locale is 1033.

** Non applicabile = Non viene restituito alcun valore per questi attributi durante un'operazione di selezione sul set di colonne.** Not applicable = No values are output for these attributes during a select operation on the column set. Quando per questo attributo viene specificato un valore dal chiamante nella rappresentazione XML fornita per un set di colonne in un'operazione di inserimento o di aggiornamento, viene generato un errore.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.

SicurezzaSecurity

Il funzionamento del modello di sicurezza per un set di colonne è analogo a quello del modello di sicurezza che esiste tra una tabella e le relative colonne.The security model for a column set works similar to the security model that exists between table and columns. I set di colonne possono essere visualizzati come una tabella di dimensioni ridotte e un'operazione di selezione è analoga a un'operazione SELECT * eseguita su tale tabella.Column sets can be visualized as a minitable and a select operation is like a SELECT * operation on this minitable. La relazione tra il set di colonne e le colonne di tipo sparse, tuttavia, rappresenta un raggruppamento anziché rappresentare esclusivamente un contenitore.But, the relationship between column set to sparse columns is a grouping relationship instead of strictly a container. Il modello controlla la sicurezza sulla colonna del set di colonne e applica le operazioni DENY sulle colonne di tipo sparse sottostanti.The security model checks the security on the column set column, and honors the DENY operations on the underlying sparse columns. Di seguito sono riportate le caratteristiche aggiuntive del modello di sicurezza.Additional characteristics of the security model are as follows:

  • È possibile concedere e revocare le autorizzazioni di sicurezza alla colonna del set di colonne, analogamente a qualsiasi altra colonna della tabella.Security permissions can be granted and revoked from the column set column, similar to any other column in the table.

  • Un'istruzione GRANT o REVOKE dell'autorizzazione SELECT, INSERT, UPDATE, DELETE e REFERENCES su una colonna del set di colonne non si propaga alle colonne membro sottostanti di tale set.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. Questa condizione si applica solo all'utilizzo della colonna del set di colonne.It applies only to the usage of the column set column. L'autorizzazione DENY su un set di colonne si propaga alle colonne di tipo sparse sottostanti della tabella.DENY permission on a column set does propagate to the underlying sparse columns of the table.

  • Per eseguire le istruzioni SELECT, INSERT, UPDATE e DELETE nella colonna del set di colonne, è necessario che l'utente disponga delle autorizzazioni corrispondenti sulla colonna stessa, nonché dell'autorizzazione corrispondente su tutte le colonne di tipo sparse della tabella.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. Poiché il set di colonne rappresenta tutte le colonne di tipo sparse della tabella, è necessario disporre dell'autorizzazione su tutte le colonne di tipo sparse, incluse quelle che potrebbero anche non subire modifiche.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.

  • L'esecuzione di un'istruzione REVOKE su una colonna di tipo sparse o su un set di colonne imposta automaticamente la sicurezza sul relativo oggetto padre.Executing a REVOKE statement on a sparse column or column set defaults the security to their parent object.

EsempiExamples

Negli esempi seguenti una tabella in cui sono contenuti i documenti è presente il set di colonne comune DocID e Title.In the following examples, a document table contains the common set of columns DocID and Title. Il gruppo Production richiede una colonna ProductionSpecification e una colonna ProductionLocation per tutti i documenti relativi alla produzione,The Production group wants a ProductionSpecification and ProductionLocation column for all production documents. mentre il gruppo Marketing richiede una colonna MarketingSurveyGroup per i documenti relativi al marketing.The Marketing group wants a MarketingSurveyGroup column for marketing documents.

A.A. Creazione di una tabella in cui è presente un set di colonneCreating a table that has a column set

Nell'esempio seguente viene creata la tabella che utilizza colonne di tipo sparse e include il set di colonne SpecialPurposeColumns.The following example creates the table that uses sparse columns and includes the column set SpecialPurposeColumns. Vengono inserite due righe nella tabella, quindi vengono selezionati dati dalla tabella.The example inserts two rows into the table, and then selects data from the table.

Nota

Questa tabella è costituita solo da cinque colonne per semplificare la visualizzazione e la lettura.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. Inserimento di dati in una tabella utilizzando i nomi delle colonne di tipo sparseInserting data to a table by using the names of the sparse columns

Negli esempi seguenti vengono inserite due righe nella tabella creata nell'esempio A. Negli esempi vengono utilizzati i nomi delle colonne di tipo sparse e non si fa riferimento al set di colonne.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. Inserimento di dati in una tabella utilizzando il nome del set di colonneInserting data to a table by using the name of the column set

Nell'esempio seguente viene inserita una terza riga nella tabella creata nell'esempio A. In questo caso i nomi delle colonne di tipo sparse non vengono utilizzati,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. ma viene utilizzato il nome del set di colonne. L'inserimento fornisce i valori per due delle quattro colonne di tipo sparse in 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. Analisi dei risultati di un set di colonne quando viene utilizzata l'istruzione SELECT *Observing the results of a column set when SELECT * is used

Nell'esempio seguente vengono selezionate tutte le colonne dalla tabella che contiene un set di colonne.The following example selects all the columns from the table that contains a column set. Viene restituita una colonna XML con i valori combinati delle colonne di tipo sparse,It returns an XML column with the combined values of the sparse columns. ma non vengono restituite le singole colonne di tipo sparse.It does not return the sparse columns individually.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;  

Set di risultati: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. Analisi dei risultati della selezione del set di colonne in base al nomeObserving the results of selecting the column set by name

Poiché i dati di marketing non interessano il reparto Production, in questo esempio viene aggiunta una clausola WHERE per limitare l'output.Because the Production department is not interested in the marketing data, this example adds a WHERE clause to restrict the output. Nell'esempio viene utilizzato il nome del set di colonne.The example uses the name of the column set.

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

Set di risultati: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. Analisi dei risultati della selezione di colonne di tipo sparse in base al nomeObserving the results of selecting sparse columns by name

Quando una tabella contiene un set di colonne, è ancora possibile eseguire una query sulla tabella utilizzando i nomi delle singole colonne come illustrato nell'esempio seguente.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 ;  

Set di risultati:Here is the result set.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

3 Tire Spec 2 AXW9R411 38

G.G. Aggiornamento di una tabella tramite un set di colonneUpdating a table by using a column set

Nell'esempio seguente viene aggiornato il terzo record con i nuovi valori per entrambe le colonne di tipo sparse utilizzate da tale riga.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

Un'istruzione UPDATE che utilizza un set di colonne aggiorna tutte le colonne di tipo sparse della tabella.An UPDATE statement that uses a column set updates all the sparse columns in the table. Le colonne di tipo sparse alle quali non viene fatto riferimento vengono impostate su NULL.Sparse columns that are not referenced are updated to NULL.

Nell'esempio seguente viene aggiornato il terzo record, ma viene specificato solo il valore di una delle due colonne popolate.The following example updates the third record, but only specifies the value of one of the two populated columns. La seconda colonna ProductionLocation non è inclusa nell'istruzione UPDATE e viene impostata su 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  

Vedere ancheSee Also

Usare le colonne di tipo sparseUse Sparse Columns