Archiviazione di dati decimal come lunghezza variabile
I tipi di dati decimal e numeric sono in genere archiviati sul disco come dati a lunghezza fissa. Il tipo di dati numeric dal punto di vista funzionale è equivalente al tipo di dati decimal. In SQL Server 2005 Service Pack 2 (SP2) e versioni successive i tipi di dati decimal e numeric possono essere archiviati come una colonna a lunghezza variabile utilizzando il formato di archiviazione vardecimal, disponibile solo in SQL Server Enterprise, Developer ed Evaluation Edition.
Nota
Questa caratteristica verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Evitare di utilizzare questa caratteristica in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Utilizzare preferibilmente la compressione di riga e di pagina. Per ulteriori informazioni, vedere Creazione di tabelle e di indici compressi.
Nota
Vardecimal è un formato di archiviazione e non un tipo di dati.
Il formato di archiviazione vardecimal potrebbe ridurre in modo significativo le dimensioni di archiviazione dei dati, causando tuttavia un lieve overhead del processore. Viene applicato a livello di tabella, pertanto non è possibile archiviare nel formato di archiviazione vardecimal solo alcune colonne decimal di una tabella e tralasciarne altre. I dati decimal rimangono un tipo di dati esatto.
Se per una tabella viene abilitato il formato di archiviazione vardecimal, i dati decimal verranno archiviati per le pagine di dati, di indice e di log nel formato di archiviazione vardecimal. La modifica del formato di archiviazione viene eseguita in modalità offline. La tabella in corso di modifica è bloccata in modo esclusivo per tutta la durata dell'operazione e non è disponibile per l'accesso simultaneo in lettura o scrittura.
Implementazione del formato di archiviazione vardecimal
In base alla precisione della colonna (da 1 a 38), l'archiviazione di un valore decimal richiede da 5 a 17 byte. Se per una tabella non viene utilizzato il formato di archiviazione vardecimal, ogni voce della tabella richiederà lo stesso numero di byte per ogni colonna decimal definita, anche se il valore di una riga è 0, NULL o un valore diverso esprimibile con un numero di byte inferiore, ad esempio il numero 3. Se una tabella viene archiviata nel formato di archiviazione vardecimal, le colonne decimal di ogni riga richiederanno solo lo spazio necessario per contenere il numero specificato, più 2 byte di overhead. Il risultato è sempre compreso tra 5 e 20 byte, inclusi i 2 byte di overhead necessari per archiviare l'offset al valore. I valori Null e gli zero vengono invece trattati in modo speciale e richiedono solo 2 byte.
Se la tabella non dispone di colonne a lunghezza variabile, sarà necessario un overhead aggiuntivo di 2 byte per riga per archiviare il numero di colonne a lunghezza variabile. Se invece la tabella dispone già di almeno una colonna a lunghezza variabile, non sarà necessario alcun overhead.
Nella tabella seguente è illustrato il numero di byte necessari per archiviare dati decimal nel formato fisso standard e il numero massimo di byte necessari per archiviare dati decimal nel formato di archiviazione vardecimal. Quando una tabella è archiviata nel formato fisso, il valore elencato verrà utilizzato per ogni riga. Quando una tabella è archiviata nel formato di archiviazione vardecimal, molti valori richiederanno un numero di byte minore di quello elencato.
Precisione della colonna |
Dimensioni nel formato decimal fisso originale (byte) |
Area massima dati vardecimal (byte) |
Overhead per l'archiviazione dell'offset (byte) |
Massimo per l'archiviazione vardecimal (byte) |
|---|---|---|---|---|
1-3 |
5 |
3 |
2 |
5 |
4-6 |
5 |
4 |
2 |
6 |
7-9 |
5 |
5 |
2 |
7 |
10-12 |
9 |
6 |
2 |
8 |
13-15 |
9 |
8 |
2 |
10 |
16-18 |
9 |
9 |
2 |
11 |
19 |
9 |
10 |
2 |
12 |
20-21 |
13 |
10 |
2 |
12 |
22-24 |
13 |
11 |
2 |
13 |
25-27 |
13 |
13 |
2 |
15 |
28 |
13 |
14 |
2 |
16 |
29-30 |
17 |
14 |
2 |
16 |
31-33 |
17 |
15 |
2 |
17 |
34-36 |
17 |
16 |
2 |
18 |
37-38 |
17 |
18 |
2 |
20 |
Utilizzo del formato di archiviazione vardecimal
Il formato di archiviazione vardecimal può essere utilizzato per risolvere i problemi seguenti:
Lo spazio su disco è insufficiente.
L'accesso al disco (I/O) rappresenta un collo di bottiglia per le prestazioni del sistema.
È necessario disporre di un livello di precisione elevato per alcuni dati, sebbene molti valori siano bassi, Null o 0, ad esempio nel caso di una tabella di una data warehouse con una colonna decimal contenente molte righe con valori 0 o interi.
La modifica del formato di archiviazione di una tabella richiede la ricompilazione dell'archiviazione della tabella (indice cluster o heap). Se l'indice cluster di una tabella contiene una colonna decimal, anche tutti gli indici non cluster dovranno essere ricompilati perché contengono i valori della chiave cluster. Se l'indice cluster non è associato a una colonna decimal, gli indici non cluster verranno ricompilati se contengono una colonna decimal. Se la tabella è un heap, ovvero non dispone di un indice cluster, tutti gli indici non cluster dovranno essere ricompilati affinché puntino alle nuove posizioni delle righe nell'heap.
La ricompilazione di una tabella per abilitare o disabilitare il formato di archiviazione vardecimal può richiedere oltre il doppio dello spazio totale di archiviazione occupato dalla tabella originale. Se la tabella non contiene colonne decimal o numeric, l'abilitazione del formato di archiviazione vardecimal è una semplice operazione su metadati. In seguito alla ricompilazione della tabella e degli indici verranno registrate molte azioni.
Il formato di archiviazione vardecimal può essere utilizzato per tabelle sia in sola lettura che in lettura/scrittura. Lo spazio risparmiato in termini di archiviazione deve essere considerato a fronte dell'utilizzo aggiuntivo della CPU necessario per convertire il formato di archiviazione delle righe a ogni accesso a esse. La scrittura in una tabella che utilizza il formato di archiviazione vardecimal, inoltre, può rallentare le prestazioni a causa delle maggiori suddivisioni di pagine.
Limitazioni del formato di archiviazione vardecimal
Il formato presenta le limitazioni seguenti:
Richiede SQL Server 2005 SP2 o versione successiva.
Il formato di archiviazione vardecimal non può essere abilitato nel database di sistema master, model, msdb, tempdb o nel database di distribuzione. In seguito a una query di ordinamento eseguita su dati nel formato di archiviazione vardecimal, i dati verranno ordinati in tempdb in uno stato decimal fisso. In genere i dati richiedono molto più spazio in tempdb rispetto a quanto ne richiedono nel database di origine, nella tabella di origine nel formato di archiviazione vardecimal.
Il formato di archiviazione vardecimal non può essere applicato a viste, viste indicizzate, indici XML e indici full-text. Nelle tabelle sottostanti, tuttavia, è possibile utilizzare il formato di archiviazione vardecimal.
Nelle tabelle interne, ad esempio tabelle dei metadati e delle notifiche, non è possibile utilizzare il formato di archiviazione vardecimal.
Il formato di archiviazione vardecimal non può essere utilizzato per le funzioni con valori di tabella.
La colonna numeric archiviata in una tabella in formato di archiviazione vardecimal non può essere crittografata.
Le partizioni eterogenee, ovvero partizioni in formato decimal fisso e nel formato di archiviazione vardecimal, non sono supportate.
Le nuove tabelle create da un formato di archiviazione vardecimal utilizzando la sintassi Transact-SQL SELECT … INTO… non ereditano il formato di archiviazione vardecimal.
Per i database per cui è abilitato il mirroring del database non è possibile modificare gli stati del formato di archiviazione vardecimal. Per abilitare il formato di archiviazione vardecimal sul database, è necessario rimuovere il mirroring del database. Non è invece necessario rimuovere il mirroring del database se il formato di archiviazione vardecimal viene abilitato o disabilitato per tabelle singole.
SQL Server deve essere in grado di garantire che tutti gli aggiornamenti vengano eseguiti correttamente e che la tabella possa essere sempre reimpostata al formato decimal fisso. Per questo motivo non è possibile attivare il formato di archiviazione vardecimal per una tabella se, in seguito all'overhead aggiuntivo, una riga esistente supera 8060 byte o un valore di indice esistente supera 900 byte.
Nota
Il formato di archiviazione vardecimal è diverso dall'archiviazione in testo variabile (varchar) in quanto SQL Server consente di creare una riga che può superare 8060 byte se tutte le colonne variabili sono alla dimensione massima. SQL Server applica il limite degli 8060 byte quando i dati in formato testo vengono immessi o aggiornati. SQL Server non consente la creazione di un set di colonne decimal che possano superare il limite degli 8060 byte per una riga. Il limite degli 8060 byte viene applicato quando il formato della tabella viene convertito all'archiviazione vardecimal.
Quando si trasferisce un database tramite il metodo di collegamento e scollegamento di Copia guidata database, l'operazione di collegamento non verrà completata se il Motore di database di destinazione non è SQL Server 2005 SP2 o versione successiva. Il metodo SMO (SQL Server Management Objects) creerà il nuovo database e le nuove tabelle senza utilizzare il formato di archiviazione vardecimal. Il database e le tabelle possono essere modificati al formato di archiviazione vardecimal dopo il trasferimento se il Motore di database è SQL Server 2005 SP2 o versione successiva.
Backup e recupero, mirroring del database, sp_attach_db e log shipping
Backup e recupero, mirroring del database, sp_attach_db e log shipping funzionano correttamente con il formato di archiviazione vardecimal, ma, per includere un database che utilizza il formato di archiviazione vardecimal, ogni istanza di SQL Server deve essere aggiornata ad almeno SQL Server 2005 SP2. Non è possibile, ad esempio, recuperare il backup di un log di un database per cui sia stato abilitato il formato di archiviazione vardecimal in un database per cui tale formato non sia stato abilitato, oppure eseguire il mirroring da un database per cui tale formato è stato abilitato in un database per cui il formato non è stato abilitato, oppure ancora collegare un database per cui è stato abilitato tale formato da SQL Server 2005 SP2 a una versione precedente di SQL Server. Se si recupera un backup completo di un database per cui è stato abilitato il formato di archiviazione vardecimal in un database per cui tale formato non è abilitato, per il database verrà abilitato il formato di archiviazione vardecimal.
Quando per una tabella viene attivato il formato di archiviazione vardecimal, la catena dei backup dei log rimane valida e il database potrà essere ripristinato applicando l'ultimo backup completo più una catena di log valida. Per impedire la creazione di backup non validi, è necessario modificare il database al modello di recupero con registrazione minima prima di modificare qualsiasi tabella per rimuovere il formato di archiviazione vardecimal. Dopo aver rimosso il formato di archiviazione vardecimal di una o più tabelle, sarà necessario creare un backup completo del database.
Utilizzo del formato di archiviazione vardecimal con il mirroring del database
Le procedure seguenti illustrano i passaggi per l'utilizzo del formato di archiviazione vardecimal con il mirroring del database.
Per utilizzare il formato di archiviazione vardecimal con il mirroring del database
Aggiornare sia l'istanza principale che l'istanza del partner per il mirroring ad almeno SQL Server 2005 SP2.
Se si utilizza il mirroring del database, rimuoverlo ed eliminare il partner per il mirroring. Per ulteriori informazioni, vedere Procedura: Rimozione del mirroring del database (Transact-SQL).
Abilitare il formato di archiviazione vardecimal sul database principale (se è un database di SQL Server 2005) e verificare che tale database sia nel modello di recupero con registrazione completa.
Attivare il mirroring del database utilizzando il backup completo e del log del database principale. Per ulteriori informazioni, vedere Procedura: Stabilire una sessione di mirroring del database tramite autenticazione di Windows (Transact-SQL).
Modificare le singole tabelle affinché utilizzino il formato di archiviazione vardecimal.
Nota
Per modificare il formato di archiviazione di tabelle singole non è necessario rimuovere il mirroring del database.
Per rimuovere il formato di archiviazione vardecimal
Modificare le tabelle del database principale per rimuovere il formato di archiviazione vardecimal.
Rimuovere il mirroring del database.
Impostare il database principale in modalità di recupero con registrazione minima. In questo modo viene interrotta la catena di log.
Disabilitare il formato di archiviazione principale vardecimal sul database principale se è un database di SQL Server 2005.
Eliminare il database del partner di mirroring.
Modificare il database principale riportandolo in modalità di recupero con registrazione completa.
Eseguire il backup del database principale e riattivare il mirroring del database.
Effetti del formato di archiviazione vardecimal sulle operazioni di replica
La replica funziona in modo standard sulle tabelle che utilizzano il formato di archiviazione vardecimal. È tuttavia necessario tenere presente quanto segue:
I tipi di dati decimal archiviati nel formato di archiviazione vardecimal vengono convertiti in formato decimal fisso per il trasferimento durante la replica. Sul database di distribuzione non può essere abilitato il formato di archiviazione vardecimal. I dati pertanto non verranno archiviati nel formato di archiviazione vardecimal quando verranno archiviati nelle tabelle di replica del database di distribuzione. Nel Sottoscrittore i record del log vengono applicati in modo standard.
Una tabella in formato di archiviazione vardecimal può essere replicata in una tabella in formato decimal fisso e una tabella in formato decimal fisso può essere replicata in una tabella in formato di archiviazione vardecimal.
Il processo di creazione di una tabella relativo a una nuova sottoscrizione non consente di creare tabelle nel formato di archiviazione vardecimal per garantire il completamento corretto della replica, indipendentemente dal livello del service pack del Motore di database o dallo stato di abilitazione del formato di archiviazione vardecimal del database di sottoscrizione. Sulla tabella di sottoscrizione può essere abilitato il formato di archiviazione vardecimal nel Sottoscrittore dopo la creazione della tabella o tramite modifica degli script di creazione prima che essi vengano applicati.
Nella tabella seguente sono descritti i requisiti di script per i diversi Sottoscrittori.
Sottoscrittore |
Script |
|---|---|
SQL Server 2000 o SQL Server versione 7.0 |
Gli script per la creazione di tabelle possono essere utilizzati senza apportare modifiche. |
SQL Server 2005, database non contrassegnato per il formato di archiviazione vardecimal. |
Gli script per la creazione di tabelle possono essere utilizzati senza apportare modifiche. |
SQL Server 2005, database contrassegnato per il formato di archiviazione vardecimal. Non si desidera che sulla tabella del Sottoscrittore sia abilitato il formato di archiviazione vardecimal. |
Gli script per la creazione di tabelle possono essere utilizzati senza apportare modifiche. |
SQL Server 2005, database contrassegnato per il formato di archiviazione vardecimal. Si desidera che sulla tabella del Sottoscrittore SQL Server 2005 sia abilitato il formato di archiviazione vardecimal. |
Gli script per la creazione di tabelle possono essere modificati affinché nelle tabelle del database venga abilitato il formato di archiviazione vardecimal. In alternativa, sul database e sulle tabelle del Sottoscrittore può essere abilitato il formato di archiviazione vardecimal utilizzando le stored procedure descritte nella sezione "Abilitazione del formato di archiviazione vardecimal" riportata di seguito. |
Considerazioni aggiuntive
Di seguito è riportato un elenco di considerazioni aggiuntive da tener presente quando si utilizza il formato di archiviazione vardecimal:
Il formato di archiviazione vardecimal non ha effetto sulle operazioni di importazione ed esportazione bulk (bcp).
La funzione DATALENGTH non rileva il formato di archiviazione vardecimal e restituisce il numero di byte che verrebbero archiviati nel formato decimal fisso.
In alcune occasioni il formato di archiviazione vardecimal potrebbe impedire l'utilizzo in SQL Server di un piano di query ottimale per i dati decimal fissi.
Il formato di archiviazione vardecimal può essere utilizzato con qualsiasi livello di compatibilità del database.
Se quando viene eseguita la stored procedure sp_tableoption una tabella non contiene colonne del tipo di dati decimal o numeric, i metadati della tabella verranno modificati per indicare che la tabella utilizza il formato di archiviazione vardecimal. Quando in seguito verranno aggiunte nuove colonne decimal, queste verranno archiviate nel formato di archiviazione vardecimal. Per aggiungere o rimuovere colonne da una tabella per cui viene utilizzato il formato di archiviazione vardecimal non sono necessarie tecniche particolari.
Abilitazione del formato di archiviazione vardecimal
Per abilitare o modificare il formato di archiviazione vardecimal sono necessarie le autorizzazioni seguenti:
Per abilitare il formato di archiviazione vardecimal in un database è necessario ALTER DATABASE sul server.
Per modificare una tabella nel formato di archiviazione vardecimal è necessaria l'autorizzazione ALTER sulla tabella.
Prima di abilitare il formato di archiviazione vardecimal, è consigliabile verificare che l'abilitazione del formato garantisca una riduzione delle dimensioni della tabella. Se la precisione della colonna definita è necessaria per la maggior parte delle righe, l'overhead associato al formato di archiviazione vardecimal potrebbe essere superiore al guadagno di spazio e quindi determinare una tabella di dimensioni maggiori. Per stimare la riduzione delle dimensioni delle righe prima di modificare la tabella, utilizzare la stored procedure sp_estimated_rowsize_reduction_for_vardecimal. Se si decide di modificare il formato di archiviazione della tabella, abilitare tale formato per il database e quindi abilitarlo per le singole tabelle. In SQL Server 2008 non è necessario abilitare il formato di archiviazione vardecimal per i database.
In un database SQL Server 2008 è possibile abilitare il formato di archiviazione vardecimal per i tipi di dati decimal utilizzando stored procedure o SQL Server Management Studio:
Eseguire sp_db_vardecimal_storage_format per abilitare il formato di archiviazione vardecimal nel database (se l'istanza di SQL Server è SQL Server 2005 SP 2) e quindi eseguire sp_tableoption per abilitare il formato nelle tabelle appropriate.
In Management Studio utilizzare la pagina delle opzioni Proprietà database per abilitare il formato di archiviazione vardecimal nel database. Per modificare il formato di una tabella nel formato di archiviazione vardecimal, è necessario utilizzare sp_tableoption.
Nota
A partire da SQL Server 2008, tutti i database vengono abilitati per il formato di archiviazione vardecimal.
Identificazione delle tabelle con formato di archiviazione vardecimal
Per determinare quali tabelle del database utilizzano il formato di archiviazione vardecimal, utilizzare la funzione OBJECTPROPERTY e cercare la proprietà TableHasVarDecimalStorageFormat.
Nell'esempio seguente viene restituito 1 se la tabella Production.WorkOrderRouting utilizza il formato di archiviazione vardecimal. In caso contrario viene restituito 0.
USE AdventureWorks2008R2 ;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('Production.WorkOrderRouting'),
'TableHasVarDecimalStorageFormat') ;
GO
Nell'esempio seguente nel database AdventureWorks2008R2 vengono cercate tutte le tabelle che utilizzano il formato di archiviazione vardecimal.
USE AdventureWorks2008R2 ;
GO
SELECT name, object_id, type_desc
FROM sys.objects
WHERE OBJECTPROPERTY(object_id,
N'TableHasVarDecimalStorageFormat') = 1 ;
GO
Problemi relativi alla rimozione del formato di archiviazione vardecimal
Per rimuovere il formato di archiviazione vardecimal da una tabella è necessario ricompilare la tabella nel formato decimal fisso. A seguito di tale operazione, lo spazio su disco occupato dalla tabella potrebbe aumentare considerevolmente. Se non è disponibile spazio sufficiente sul disco, l'operazione non verrà completata. In questo caso, per disabilitare il formato di archiviazione vardecimal, è necessario garantire che SQL Server disponga di spazio sufficiente sul disco. Anche l'operazione di espansione richiede spazio temporaneo per archiviare dati sia nel formato vardecimal che nel formato standard. Se per le dimensioni della tabella espansa dopo l'operazione di espansione lo spazio su disco sarebbe sufficiente, ma l'operazione di espansione non viene completata solo per mancanza di spazio temporaneo su disco, è possibile espandere i dati in modo incrementale, copiando le righe della tabella in una nuova tabella non espansa.
Per rimuovere il formato di archiviazione vardecimal da un database immediatamente dopo la modifica, eliminare il database e quindi recuperarlo da un backup eseguito prima dell'abilitazione del formato vardecimal.
Quando si sposta un database da un'edizione Enterprise Edition, Developer Edition o Evaluation Edition di SQL Server 2005 a un'altra edizione o a una versione precedente di SQL Server, è innanzitutto necessario aprire il database utilizzando una delle edizioni richieste, quindi rimuovere il formato di archiviazione vardecimal e infine eseguire la migrazione del database. Il collegamento di un database contenente il formato di archiviazione vardecimal a un server non idoneo non verrà completato.