Utilizzo di colonne di tipo sparseUse Sparse Columns

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

Le colonne di tipo sparse sono colonne comuni che dispongono di archiviazione ottimizzata per i valori Null.Sparse columns are ordinary columns that have an optimized storage for null values. Tali colonne consentono di ridurre i requisiti di spazio per i valori Null aumentando tuttavia l'overhead per il recupero dei valori non Null.Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. È consigliabile utilizzare colonne di tipo sparse quando la quantità di spazio risparmiata è compresa almeno tra il 20% e il 40%.Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Le colonne di tipo sparse e i set di colonne vengono definiti utilizzando l'istruzione CREATE TABLE o ALTER TABLE .Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

Le colonne di tipo sparse possono essere utilizzate con set di colonne e indici filtrati:Sparse columns can be used with column sets and filtered indexes:

  • Set di colonneColumn sets

    Le istruzioni INSERT, UPDATE e DELETE possono fare riferimento alle colonne di tipo sparse in base al nome.INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. È tuttavia possibile visualizzare e utilizzare tutte le colonne di tipo sparse di una tabella combinate in una singola colonna XML,However, you can also view and work with all the sparse columns of a table that are combined into a single XML column. denominata set di colonne.This column is called a column set. Per altre informazioni sui set di colonne, vedere Utilizzare set di colonne.For more information about column sets, see Use Column Sets.

  • Indici filtratiFiltered indexes

    Poiché le colonne di tipo sparse contengono molte righe con valori Null, sono particolarmente adatte per l'utilizzo di indici filtrati.Because sparse columns have many null-valued rows, they are especially appropriate for filtered indexes. Un indice filtrato applicato a una colonna di tipo sparse consente di indicizzare solo le righe popolate con valori.A filtered index on a sparse column can index only the rows that have populated values. In questo modo, viene creato un indice più efficiente e di dimensioni minori.This creates a smaller and more efficient index. Per altre informazioni, vedere Create Filtered Indexes.For more information, see Create Filtered Indexes.

    Le colonne di tipo sparse e gli indici filtrati consentono alle applicazioni, ad esempio Windows SharePoint ServicesWindows SharePoint Services, di archiviare in modo efficiente un elevato numero di proprietà definite dall'utente e accedervi usando SQL Server 2017SQL Server 2017.Sparse columns and filtered indexes enable applications, such as Windows SharePoint ServicesWindows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL Server 2017SQL Server 2017.

Proprietà delle colonne di tipo sparseProperties of Sparse Columns

Le colonne di tipo sparse hanno le caratteristiche seguenti:Sparse columns have the following characteristics:

  • Il Motore di database di SQL ServerSQL Server Database Engine utilizza la parola chiave SPARSE nella definizione di una colonna per ottimizzare l'archiviazione dei valori in tale colonna.The Motore di database di SQL ServerSQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Di conseguenza, quando il valore della colonna è Null per qualsiasi riga della tabella, i valori non devono essere archiviati.Therefore, when the column value is NULL for any row in the table, the values require no storage.

  • Le viste del catalogo per una tabella contenente colonne di tipo sparse sono identiche a quelle di una tabella tipica.Catalog views for a table that has sparse columns are the same as for a typical table. La vista del catalogo sys.columns contiene una riga per ogni colonna della tabella e include un set di colonne, se definito.The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined.

  • Le colonne di tipo sparse sono una proprietà del livello dell'archiviazione, piuttosto che la tabella logica.Sparse columns are a property of the storage layer, rather than the logical table. Pertanto un'istruzione SELECT.INTO non viene copiata sopra la proprietà della colonna di tipo sparse in una nuova tabella.Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.

  • La funzione COLUMNS_UPDATED restituisce un valore varbinary per indicare tutte le colonne aggiornate durante un'azione DML.The COLUMNS_UPDATED function returns a varbinary value to indicate all the columns that were updated during a DML action. I bit restituiti dalla funzione COLUMNS_UPDATED vengono impostati come indicato di seguito:The bits that are returned by the COLUMNS_UPDATED function are as follows:

    • Quando una colonna di tipo sparse viene aggiornata in modo esplicito, il bit corrispondente per tale colonna e il bit per il set di colonne vengono impostati su 1.When a sparse column is explicitly updated, the corresponding bit for that sparse column is set to 1, and the bit for the column set is set to 1.

    • Quando un set di colonne viene aggiornato in modo esplicito, il bit per il set di colonne e i bit per tutte le colonne di tipo sparse della tabella vengono impostati su 1.When a column set is explicitly updated, the bit for the column set is set to 1, and the bits for all the sparse columns in that table are set to 1.

    • Per le operazioni di inserimento, tutti i bit vengono impostati su 1.For insert operations, all bits are set to 1.

      Per altre informazioni sui set di colonne, vedere Utilizzare set di colonne.For more information about columns sets, see Use Column Sets.

    I tipi di dati seguenti non possono essere specificati come SPARSE:The following data types cannot be specified as SPARSE:

geographygeography texttext
geometrygeometry timestamptimestamp
imageimage tipi di dati definiti dall'utenteuser-defined data types
ntextntext

Risparmio stimato in termini di spazio in base al tipo di datiEstimated Space Savings by Data Type

Le colonne di tipo sparse richiedono una quantità maggiore di spazio di archiviazione per i valori non Null rispetto a quella necessaria per dati identici non contrassegnati come SPARSE.Sparse columns require more storage space for nonnull values than the space required for identical data that is not marked SPARSE. Nelle tabelle seguenti viene illustrato l'utilizzo dello spazio per ogni tipo di dati.The following tables show the space usage for each data type. La colonna Percentuale valori Null indica la percentuale di dati con valore Null necessaria per ottenere un risparmio netto del 40% in termini di spazio.The NULL Percentage column indicates what percent of the data must be NULL for a net space savings of 40 percent.

Tipi di dati a lunghezza fissaFixed-Length Data Types

Tipo di datiData type Byte non di tipo sparseNonsparse bytes Byte di tipo sparseSparse bytes Percentuale valori NullNULL percentage
bitbit 0,1250.125 55 98%98%
tinyinttinyint 11 55 86%86%
smallintsmallint 22 66 76%76%
intint 44 88 64%64%
bigintbigint 88 1212 52%52%
realreal 44 88 64%64%
floatfloat 88 1212 52%52%
smallmoneysmallmoney 44 88 64%64%
moneymoney 88 1212 52%52%
smalldatetimesmalldatetime 44 88 64%64%
datetimedatetime 88 1212 52%52%
uniqueidentifieruniqueidentifier 1616 2020 43%43%
datadate 33 77 69%69%

Tipi di dati con lunghezza dipendente dalla precisionePrecision-Dependent–Length Data Types

Tipo di datiData type Byte non di tipo sparseNonsparse bytes Byte di tipo sparseSparse bytes Percentuale valori NullNULL percentage
datetime2(0)datetime2(0) 66 1010 57%57%
datetime2(7)datetime2(7) 88 1212 52%52%
time(0)time(0) 33 77 69%69%
time(7)time(7) 55 99 60%60%
datetimetoffset(0)datetimetoffset(0) 88 1212 52%52%
datetimetoffset (7)datetimetoffset (7) 1010 1414 49%49%
decimal/numeric(1,s)decimal/numeric(1,s) 55 99 60%60%
decimal/numeric(38,s)decimal/numeric(38,s) 1717 2121 42%42%
vardecimal(p,s)vardecimal(p,s) Usare il tipo decimal come stima conservativa.Use the decimal type as a conservative estimate.

Tipi di dati con lunghezza dipendente dai datiData-Dependent–Length Data Types

Tipo di datiData type Byte non di tipo sparseNonsparse bytes Byte di tipo sparseSparse bytes Percentuale valori NullNULL percentage
sql_variantsql_variant Varia in base al tipo di dati sottostanteVaries with the underlying data type
varchar o charvarchar or char 22 44 60%60%
nvarchar o ncharnvarchar or nchar 22 4+4+ 60%60%
varbinary o binaryvarbinary or binary 22 44 60%60%
xmlxml 22 44 60%60%
hierarchyidhierarchyid 22 44 60%60%

La lunghezza è uguale alla media dei dati contenuti nel tipo, più 2 o 4 byte.The length is equal to the average of the data that is contained in the type, plus 2 or 4 bytes.

Overhead in memoria necessario per gli aggiornamenti alle colonne di tipo sparseIn-Memory Overhead Required for Updates to Sparse Columns

Quando si progettano tabelle con colonne di tipo sparse, ricordare che sono necessari 2 byte aggiuntivi di overhead per ogni colonna di tipo sparse non Null nella tabella quando viene aggiornata una riga.When designing tables with sparse columns, keep in mind that an additional 2 bytes of overhead are required for each non-null sparse column in the table when a row is being updated. Dato questo requisito di memoria aggiuntivo, gli aggiornamenti potrebbero non riuscire in modo imprevisto con l'errore 576 quando le dimensioni totali della riga, incluso l'overhead di memoria, supera 8019 e nessuna colonna può essere spostata all'esterno della riga.As a result of this additional memory requirement, updates can fail unexpectedly with error 576 when the total row size, including this memory overhead, exceeds 8019, and no columns can be pushed off the row.

Si consideri l'esempio di una tabella contenente 600 colonne di tipo sparse di tipo bigint.Consider the example of a table that has 600 sparse columns of type bigint. Se le colonne non Null sono 571, la dimensione totale su disco è pari a 571 * 12 = 6852 byte.If there are 571 non-null columns, then the total size on disk is 571 * 12 = 6852 bytes. Dopo avere incluso l'overhead di riga aggiuntivo e l'intestazione della colonna di tipo sparse, i byte aumentano a 6895.After including additional row overhead and the sparse column header, this increases to around 6895 bytes. Per la pagina sono ancora disponibili su disco 1124 byte.The page still has around 1124 bytes available on disk. Ciò può dare l'impressione che sia possibile aggiornare correttamente le colonne aggiuntive.This can give the impression that additional columns can be updated successfully. Durante l'aggiornamento, tuttavia, si verifica un ulteriore overhead in memoria pari a 2*(numero di colonne di tipo sparse non Null).However, during the update, there is additional overhead in memory which is 2*(number of non-null sparse columns). In questo esempio, incluso l'overhead aggiuntivo – 2 * 571 = 1142 byte – le dimensioni della riga su disco aumentano a 8037 byte.In this example, including the additional overhead – 2 * 571 = 1142 bytes – increases the row size on disk to around 8037 bytes. Queste dimensioni superano le dimensioni massime consentite di 8019 byte.This size exceeds the maximum allowed size of 8019 bytes. Poiché tutte le colonne sono tipi di dati a lunghezza fissa, non è possibile spostarle all'esterno della riga.Since all the columns are fixed-length data types, they cannot be pushed off the row. Di conseguenza, durante l'aggiornamento si verificherà l'errore 576.As a result, the update fails with the 576 error.

Restrizioni relative all'utilizzo di colonne di tipo sparseRestrictions for Using Sparse Columns

Le colonne di tipo sparse possono essere di qualsiasi tipo di dati di SQL ServerSQL Server e presentano un comportamento analogo a qualsiasi altra colonna, ma con le restrizioni seguenti:Sparse columns can be of any SQL ServerSQL Server data type and behave like any other column with the following restrictions:

  • Una colonna di tipo sparse deve ammettere i valori Null e non può includere proprietà ROWGUIDCOL o IDENTITY.A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties. Una colonna di tipo sparse non può essere costituita dai tipi di dati text, ntext, image, timestamp, tipi di dati definiti dall'utente, geometryo geography, né disporre dell'attributo FILESTREAM.A sparse column cannot be of the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography; or have the FILESTREAM attribute.

  • Una colonna di tipo sparse non può avere un valore predefinito.A sparse column cannot have a default value.

  • Una colonna di tipo sparse non può essere associata a una regola.A sparse column cannot be bound to a rule.

  • Sebbene possa contenere una colonna di tipo sparse, una colonna calcolata non può essere contrassegnata come SPARSE.Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE.

  • È possibile definire una maschera dati in una colonna di tipo sparse, ma non in una colonna di tipo sparse che fa parte di un set di colonne.A data mask can be defined on a sparse column, but not on a sparse column that is part of a column set.

  • Una colonna di tipo sparse non può far parte di un indice cluster o di un indice di chiave primaria univoco.A sparse column cannot be part of a clustered index or a unique primary key index. Sia le colonne calcolate persistenti sia quelle non persistenti definite in colonne di tipo sparse, tuttavia, possono far parte di una chiave cluster.However, both persisted and nonpersisted computed columns that are defined on sparse columns can be part of a clustered key.

  • Una colonna di tipo sparse non può essere utilizzata come chiave di partizione di un indice cluster o di un heap,A sparse column cannot be used as a partition key of a clustered index or heap. ma può essere utilizzata come chiave di partizione di un indice non cluster.However, a sparse column can be used as the partition key of a nonclustered index.

  • Una colonna di tipo sparse non può far parte di un tipo di tabella definito dall'utente, utilizzato in variabili di tabella e in parametri con valori di tabella.A sparse column cannot be part of a user-defined table type, which are used in table variables and table-valued parameters.

  • Le colonne di tipo sparse sono incompatibili con la compressione dei dati.Sparse columns are incompatible with data compression. Non è pertanto possibile aggiungere colonne di tipo sparse alle tabelle compresse, né comprimere tabelle contenenti colonne di tipo sparse.Therefore sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.

  • Per modificare una colonna di tipo sparse in non sparse o viceversa, è necessario modificare il formato di archiviazione della colonna.Changing a column from sparse to nonsparse or nonsparse to sparse requires changing the storage format of the column. Per effettuare questa modifica, nel Motore di database di SQL Server viene utilizzata la procedura seguente:The SQL Server Database Engine uses the following procedure to accomplish this change:

    1. Viene aggiunta una nuova colonna alla tabella con le nuove dimensioni e nel nuovo formato di archiviazione.Adds a new column to the table in the new storage size and format.

    2. Per ogni riga della tabella, il valore archiviato nella colonna precedente viene aggiornato e copiato nella nuova colonna.For each row in the table, updates and copies the value stored in the old column to the new column.

    3. La colonna precedente viene rimossa dallo schema della tabella.Removes the old column from the table schema.

    4. Viene ricompilata la tabella, se non include un indice cluster, oppure viene ricompilato l'indice cluster per recuperare lo spazio utilizzato dalla colonna precedente.Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column.

    Nota

    È possibile che il passaggio 2 non venga completato correttamente se i dati della riga superano le dimensioni di riga massime consentite.Step 2 can fail when the size of the data in the row exceeds the maximum allowable row size. Tali dimensioni includono quelle dei dati archiviati nella colonna precedente e quelle dei dati aggiornati archiviati nella nuova colonna.This size includes the size of the data stored in the old column and the updated data stored in the new column. Il limite è di 8060 byte per le tabelle che non contengono colonne di tipo sparse o di 8018 byte per le tabelle che contengono colonne di tipo sparse.This limit is 8060 bytes for tables that do not contain any sparse columns or 8018 bytes for tables that contain sparse columns. Questo errore può verificarsi anche se tutte le colonne idonee sono state spostate all'esterno delle righe.This error can occur even if all eligible columns have been pushed off-row.

  • Quando una colonna non di tipo sparse viene modificata in una di tipo sparse, quest'ultima utilizzerà una quantità di spazio maggiore per i valori non Null.When you change a non-sparse column to a sparse column, the sparse column will consume more space for non-null values. Quando le dimensioni di una riga si avvicinano al limite massimo consentito, potrebbe non essere possibile completare l'operazione.When a row is close to the maximum row size limit, the operation can fail.

Tecnologie SQL Server che supportano le colonne di tipo sparseSQL Server Technologies That Support Sparse Columns

In questa sezione viene descritto il supporto delle colonne di tipo sparse nelle tecnologie SQL ServerSQL Server seguenti:This section describes how sparse columns are supported in the following SQL ServerSQL Server technologies:

  • Replica transazionaleTransactional replication

    La replica transazionale supporta le colonne di tipo sparse, ma non i set di colonne che possono essere utilizzati con le colonne di tipo sparse.Transactional replication supports sparse columns, but it does not support column sets, which can be used with sparse columns. Per altre informazioni sui set di colonne, vedere Utilizzare set di colonne.For more information about column sets, see Use Column Sets.

    La replica dell'attributo SPARSE è determinata da un'opzione di schema specificata utilizzando sp_addarticle o la finestra di dialogo Article Properties in SQL Server Management StudioSQL Server Management Studio.The replication of the SPARSE attribute is determined by a schema option that is specified by using sp_addarticle or by using the Article Properties dialog box in SQL Server Management StudioSQL Server Management Studio. Le versioni precedenti di SQL ServerSQL Server non supportano le colonne di tipo sparse.Earlier versions of SQL ServerSQL Server do not support sparse columns. Se è necessario replicare dati in una versione precedente, specificare che l'attributo SPARSE non deve essere replicato.If you must replicate data to an earlier version, specify that the SPARSE attribute should not be replicated.

    Per le tabelle pubblicate, non è possibile aggiungere nuove colonne di tipo sparse a una tabella né modificare la proprietà sparse di una colonna esistente.For tables that are published, you cannot add any new sparse columns to a table or change the sparse property of an existing column. Se questa operazione è necessaria, eliminare e ricreare la pubblicazione.If such an operation is required, drop and re-create the publication.

  • Replica di tipo mergeMerge replication

    La replica di tipo merge non supporta le colonne di tipo sparse né i set di colonne.Merge replication does not support sparse columns or column sets.

  • Rilevamento modificheChange tracking

    Il rilevamento delle modifiche supporta le colonne di tipo sparse e i set di colonne.Change tracking supports sparse columns and column sets. Quando un set di colonne viene aggiornato in una tabella, il rilevamento delle modifiche considera questa operazione un aggiornamento all'intera riga.When a column set is updated in a table, change tracking treats this as an update to the whole row. Non è disponibile alcun rilevamento delle modifiche dettagliato per ottenere il set esatto di colonne di tipo sparse aggiornate mediante l'aggiornamento del set di colonne.No detailed change tracking is provided to obtain the exact set of sparse columns that are updated through the column set update operation. Se le colonne di tipo sparse vengono aggiornate in modo esplicito mediante un'istruzione DML, il rilevamento delle modifiche su tali colonne funzionerà nel modo usuale e verrà identificato il set esatto delle colonne modificate.If the sparse columns are updated explicitly through a DML statement, change tracking on them will work ordinarily and can identify the exact set of changed columns.

  • Change Data CaptureChange data capture

    La funzionalità Change Data Capture supporta le colonne di tipo sparse, ma non i set di colonne.Change data capture supports sparse columns, but it does not support column sets.

  • La proprietà di tipo sparse di una colonna non viene mantenuta in caso di copia della tabella.The sparse property of a column is not preserved when the table is copied.

EsempiExamples

In questo esempio viene illustrata una tabella Document che contiene un set comune in cui sono presenti le colonne DocID e Title.In this example, a document table contains a common set that has the 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. Tramite il codice incluso nell'esempio viene creata una tabella che utilizza colonne di tipo sparse, vengono inserite due righe nella tabella, quindi vengono selezionati dati nella tabella.The code in this example creates a table that uses sparse columns, 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. Se è impostata l'opzione ANSI_NULL_DFLT_ON, è facoltativo dichiarare che le colonne di tipo sparse devono ammettere i valori Null.Declaring the sparse columns to be nullable is optional if the ANSI_NULL_DFLT_ON option is set.

USE AdventureWorks2012;  
GO  

CREATE TABLE DocumentStore  
    (DocID int PRIMARY KEY,  
     Title varchar(200) NOT NULL,  
     ProductionSpecification varchar(20) SPARSE NULL,  
     ProductionLocation smallint SPARSE NULL,  
     MarketingSurveyGroup varchar(20) SPARSE NULL ) ;  
GO  

INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)  
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);  
GO  

INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)  
VALUES (2, 'Survey 2142', 'Men 25 - 35');  
GO  

Selezionando tutte le colonne della tabella viene restituito un set di risultati comune.To select all the columns from the table returns an ordinary result set.

SELECT * FROM DocumentStore ;  

Set di risultati:Here is the result set.

DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup

1 Tire Spec 1 AXZZ217 27 NULL

2 Survey 2142 NULL NULL Men 25-35

Poiché i dati di marketing non interessano il reparto Production, questo desidera utilizzare un elenco di colonne che restituisca solo colonne di interesse specifico, come illustrato nella query seguente.Because the Production department is not interested in the marketing data, they want to use a column list that returns only columns of interest, as shown in the following query.

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

Set di risultati:Here is the result set.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

Vedere ancheSee Also

Utilizzare set di colonne Use Column Sets
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
sys.columns (Transact-SQL) sys.columns (Transact-SQL)