Speichern von Dezimaldaten als Daten variabler Länge

Implementierung des vardecimal-Speicherformats

In Abhängigkeit von der Genauigkeit der Spalte (von 1 bis 38) sind zum Speichern eines decimal-Wertes zwischen 5 und 17 Bytes erforderlich. Wenn für eine Tabelle nicht das vardecimal-Speicherformat verwendet wird, belegt jeder Tabelleneintrag für jede Spalte im Dezimalformat dieselbe Anzahl von Bytes, auch wenn eine Zeile den Wert 0, NULL, oder einen Wert aufweist, der mit einer geringeren Anzahl von Bytes ausgedrückt werden könnte, z. B. die Zahl 3. Wenn eine Tabelle im vardecimal-Speicherformat gespeichert wird, belegen die decimal-Spalten jeder Zeile nur den zur Aufnahme der angegebenen Zahl erforderlichen Speicherplatz plus 2 Bytes für den Verarbeitungsaufwand. Das Ergebnis liegt stets zwischen 5 und 20 Bytes. In diesem Wert sind jeweils die 2 Bytes Verarbeitungsaufwand zum Speichern des Offsets zum Wert enthalten. NULL-Werte und Nullen werden jedoch speziell behandelt und nehmen nur 2 Bytes ein.

Wenn die Tabelle keine Spalten variabler Länge enthält, entsteht pro Zeile ein zusätzlicher Verarbeitungsaufwand von 2 Bytes, mit dem die Anzahl der Spalten variabler Länge gespeichert wird. Wenn die Tabelle bereits mindestens eine Spalte variabler Länge besitzt, entsteht kein zusätzlicher Verarbeitungsaufwand.

In der folgenden Tabelle wird die Anzahl der Bytes angegeben, die zum Speichern von Dezimaldaten im gängigen festen Format erforderlich ist, sowie die maximale Anzahl von Bytes, die zum Speichern von Dezimaldaten im vardecimal-Speicherformat erforderlich ist. Wenn eine Tabelle im festen Format gespeichert wird, wird der aufgeführte Wert für jede Zeile verwendet. Wenn eine Tabelle im vardecimal-Speicherformat gespeichert wird, ist für viele Werte möglicherweise eine geringere Anzahl von Bytes erforderlich als angegeben.

Verwenden des vardecimal-Speicherformats

Sie können das vardecimal-Speicherformat verwenden, wenn Sie versuchen, folgende Probleme zu lösen:

• Es ist nur noch wenig Speicherplatz vorhanden.

• Datenträgerzugriffe (E/A) wirken sich als Engpass auf die Systemleistung aus.

• Für einige Daten muss ein hoher Grad an Genauigkeit vorliegen, auch wenn viele Werte klein, NULL oder 0 sind (beispielsweise eine Tabelle in einem Data Warehouse mit einer decimal-Spalte, die viele Zeilen mit dem Wert 0 oder mit einem ganzzahligen Wert enthält).

Durch das Ändern des Speicherformats einer Tabelle ist es erforderlich, dass der Speicher der Tabelle (gruppierter Index oder Heap) neu erstellt wird. Wenn der gruppierte Index einer Tabelle eine decimal-Spalte enthält, müssen auch alle nicht gruppierten Indizes neu erstellt werden, da sie die Werte des gruppierten Schlüssels enthalten. Wenn der gruppierte Index keine decimal-Spalte besitzt, werden nicht gruppierte Indizes neu erstellt, sofern sie eine decimal-Spalte enthalten. Wenn es sich bei der Tabelle um einen Heap handelt (d. h., sie besitzt keinen gruppierten Index), müssen alle nicht gruppierten Indizes neu erstellt werden, sodass sie auf die neuen Zeilenpositionen im Heap zeigen.

Für das Neuerstellen einer Tabelle zum Aktivieren bzw. Deaktivieren des vardecimal-Speicherformats kann mehr als das Doppelte des Gesamtspeicherplatzes der ursprünglichen Tabelle erforderlich sein. Wenn die Tabelle keine decimal- oder numeric-Spalten enthält, stellt das Aktivieren des vardecimal-Speicherformats lediglich einen Metadatenvorgang dar. Es ist eine hohe Protokollaktivität zu erwarten, wenn die Tabelle und die Indizes neu erstellt werden.

Das vardecimal-Speicherformat kann sowohl für schreibgeschützte Tabellen als auch für Tabellen mit Lese-/Schreibzugriff verwendet werden. Durch die Speicherplatzeinsparungen muss der zusätzliche CPU-Aufwand gerechtfertigt sein, der zum Konvertieren des Speicherformats der Zeilen erforderlich ist, wenn auf die Zeilen zugegriffen wird. Zudem kann die Leistung durch das Schreiben in eine Tabelle, für die das vardecimal-Speicherformat verwendet wird, aufgrund einer erhöhten Anzahl von Seitenteilungen verringert werden.

Einschränkungen des vardecimal-Speicherformats

Es gelten folgende Einschränkungen:

• SQL Server 2005 SP2 oder eine höhere Version ist erforderlich.

• Das vardecimal-Speicherformat kann nicht in den Systemdatenbanken master, model, msdb, tempdb oder distribution aktiviert werden. Wenn durch eine Abfrage Daten sortiert werden, die im vardecimal-Speicherformat gespeichert sind, werden die Daten in tempdb in einem festen Dezimalstatus sortiert. Normalerweise ist für die Daten in tempdb deutlich mehr Speicherplatz erforderlich als für die Quelltabelle mit vardecimal-Speicherformat in der Quelldatenbank.

• Das vardecimal-Speicherformat kann nicht auf Sichten, indizierte Sichten, XML-Indizes und Volltextindizes angewendet werden. Für die Tabellen, die diesen Objekten zugrunde liegen, kann jedoch das vardecimal-Speicherformat verwendet werden.

• Für interne Tabellen, z. B. Metadaten- und Benachrichtigungstabellen, kann das vardecimal-Speicherformat nicht verwendet werden.

• Für Tabellenwertfunktionen kann das vardecimal-Speicherformat nicht verwendet werden.

• Die numeric-Spalte, die in einer Tabelle mit vardecimal-Speicherformat gespeichert wird, kann nicht zusätzlich verschlüsselt werden.

• Heterogene Partitionen (d. h. Partitionen mit festem Dezimalformat und vardecimal-Speicherformat) werden nicht unterstützt.

• Neue Tabellen, die mithilfe der Syntax Transact-SQL SELECT … INTO… aus einer Tabelle mit vardecimal-Speicherformat erstellt werden, erben das vardecimal-Speicherformat nicht.

• Der Status des vardecimal-Speicherformats von Datenbanken, für die Datenbankspiegelungen aktiviert sind, kann nicht geändert werden. Sie müssen die Datenbankspiegelung entfernen, um das vardecimal-Speicherformat für die Datenbank zu aktivieren. Sie müssen die Datenbankspiegelung jedoch nicht entfernen, wenn das vardecimal-Speicherformat für einzelne Tabellen aktiviert bzw. deaktiviert ist.

• In SQL Server muss sichergestellt werden können, dass alle Aktualisierungen erfolgreich ausgeführt werden und dass für die Tabelle stets das feste Dezimalformat wiederhergestellt werden kann. Aus diesem Grund kann eine Tabelle nicht in das vardecimal-Speicherformat geändert werden, wenn der zusätzliche Verarbeitungsaufwand bewirkt, dass für eine vorhandene Zeile 8060 Bytes oder für einen vorhandenen Indexwert 900 Bytes überschritten werden.

  Hinweis

  Das vardecimal-Speicherformat unterscheidet sich von der Speicherung variablen Textes (varchar) insofern, als Sie mit SQL Server eine Zeile erstellen können, für die 8060 Bytes überschritten werden können, wenn alle variablen Spalten der maximalen Größe entsprechen. In SQL Server wird die Grenze von 8060 Bytes erzwungen, wenn Textdaten eingefügt oder aktualisiert werden. Mit SQL Server können Sie keine Spalten im Dezimalformat erstellen, mit denen die Grenze von 8060 Bytes für eine Zeile überschritten werden kann. Die Grenze von 8060 Bytes wird erzwungen, wenn das Tabellenformat zugunsten der vardecimal-Speicherung geändert wird.

• Wenn Sie eine Datenbank mithilfe der Methode zum Trennen und Anfügen des Assistenten zum Kopieren von Datenbanken übertragen, treten Fehler beim Anfügevorgang auf, wenn die Zielinstanz von Database Engine (Datenbankmodul) nicht SQL Server 2005 SP2 oder höher entspricht. Mit der SQL Server Management Objects-Methode werden die neue Datenbank und die Tabellen ohne Verwendung des vardecimal-Speicherformats erstellt. Die Datenbank und die Tabellen können nach der Übertragung zugunsten des vardecimal-Formats geändert werden, wenn Database Engine (Datenbankmodul)SQL Server 2005 SP2 oder höher entspricht.

Sicherung und Wiederherstellung, Datenbankspiegelung, sp_attach_db und Protokollversand

Sicherung und Wiederherstellung, Datenbankspiegelung, sp_attach_db und Protokollversand können ordnungsgemäß mit dem vardecimal-Speicherformat verwendet werden. Beim Einschließen einer Datenbank, für die das vardecimal-Speicherformat verwendet wird, muss jedoch jede Instanz von SQL Server mindestens auf SQL Server 2005 SP2 aktualisiert werden. Sie können beispielsweise eine Protokollsicherung einer Datenbank, für die das vardecimal-Speicherformat aktiviert ist, nicht als Datenbank wiederherstellen, für die das Format nicht aktiviert ist. Sie können auch keine Spiegelung zwischen einer Datenbank, für die das vardecimal-Speicherformat aktiviert ist, und einer Datenbank, für die das Format nicht aktiviert ist, ausführen. Ein Anfügen einer Datenbank, für die das vardecimal-Speicherformat aktiviert ist, wenn die Aktivierung in SQL Server 2005 SP2 erfolgt ist, ist in einer früheren Version von SQL Server nicht möglich. Wenn Sie eine vollständige Sicherung einer Datenbank, für die das vardecimal-Speicherformat aktiviert ist, als Datenbank wiederherstellen, für die das vardecimal-Speicherformat nicht aktiviert ist, wird das vardecimal-Speicherformat für die Datenbank aktiviert.

Wenn eine Tabelle zugunsten des vardecimal-Speicherformats geändert wird, bleibt die Protokollsicherungskette gültig, und die Datenbank kann wiederhergestellt werden, indem die letzte vollständige Sicherung und zusätzlich eine gültige Protokollkette übernommen werden. Wenn Sie verhindern möchten, dass ungültige Sicherungen erstellt werden, müssen Sie die Datenbank zugunsten des einfachen Wiederherstellungsmodells ändern, bevor Sie Tabellen dahingehend ändern, dass das vardecimal-Speicherformat entfernt wird. Nachdem Sie das vardecimal-Speicherformat einer Tabelle entfernt haben, sollten Sie eine vollständige Datenbanksicherung durchführen.

Verwenden des vardecimal-Speicherformats für die Datenbankspiegelung

Im Folgenden werden die Schritte zum Verwenden des vardecimal-Speicherformats für die Datenbankspiegelung aufgezeigt.

So verwenden Sie das vardecimal-Speicherformat für die Datenbankspiegelung

1. Aktualisieren Sie die Instanzen des Prinzipalservers und des Spiegelungspartners auf mindestens SQL Server 2005 SP2.

2. Wenn Sie gegenwärtig eine Datenbankspiegelung verwenden, entfernen Sie die Datenbankspiegelung, und löschen Sie den Spiegelungspartner. Weitere Informationen finden Sie unter Vorgehensweise: Entfernen der Datenbankspiegelung (Transact-SQL).

3. Aktivieren Sie das vardecimal-Speicherformat für die Prinzipaldatenbank (wenn es sich um eine Datenbank in SQL Server 2005 handelt), und stellen Sie sicher, dass für die Prinzipaldatenbank das vollständige Wiederherstellungsmodell verwendet wird.

4. Richten Sie die Datenbankspiegelung mithilfe von vollständigen Sicherungen und Protokollsicherungen aus der Prinzipaldatenbank ein. Weitere Informationen finden Sie unter Vorgehensweise: Einrichten einer Datenbank-Spiegelungssitzung mithilfe der Windows-Authentifizierung (Transact-SQL).

5. Ändern Sie einzelne Tabellen, sodass das vardecimal-Speicherformat verwendet wird.

Hinweis
Sie müssen die Datenbankspiegelung nicht entfernen, um das Speicherformat einzelner Tabellen zu ändern.

So entfernen Sie das vardecimal-Speicherformat

1. Ändern Sie die Tabellen in der Prinzipaldatenbank dahingehend, dass Sie das vardecimal-Speicherformat entfernen.

2. Entfernen Sie die Datenbankspiegelung.

3. Legen Sie für die Prinzipaldatenbank den einfachen Wiederherstellungsmodus fest. Dadurch wird die Protokollkette unterbrochen.

4. Wenn es sich um eine Datenbank in SQL Server 2005 handelt, deaktivieren Sie das vardecimal-Speicherformat für die Prinzipaldatenbank.

5. Löschen Sie die Spiegelungspartnerdatenbank.

6. Legen Sie für die Prinzipaldatenbank wieder den vollständigen Wiederherstellungsmodus fest.

7. Sichern Sie die Prinzipaldatenbank, und stellen Sie die Datenbankspiegelung wieder her.

Auswirkungen des vardecimal-Speicherformats auf Replikationsvorgänge

Die Replikation kann für Tabellen mit dem vardecimal-Speicherformat wie gewohnt verwendet werden. Es ist dabei jedoch Folgendes zu beachten:

• decimal-Datentypen, die im vardecimal-Speicherformat gespeichert sind, werden für die Übertragung während der Replikation in das feste Dezimalformat umgewandelt. Für die Verteilungsdatenbank kann das vardecimal-Speicherformat nicht aktiviert werden. Deshalb werden Daten nicht im vardecimal-Speicherformat gespeichert, wenn sie in den Replikationstabellen in der Verteilungsdatenbank gespeichert werden. Auf dem Abonnenten werden die Protokolldatensätze wie gewohnt angewendet.

• Eine Tabelle im vardecimal-Speicherformat kann zu einer Tabelle im festen Dezimalformat repliziert werden, und eine Tabelle im festen Dezimalformat kann zu einer Tabelle im vardecimal-Speicherformat repliziert werden.

• Durch den Tabellenerstellungsvorgang im Zusammenhang mit einem neuen Abonnement werden keine Tabellen mit dem vardecimal-Speicherformat erstellt. Somit kann die Replikation erfolgreich ausgeführt werden, wobei weder die Service Pack-Ebene von Database Engine (Datenbankmodul) noch der Aktivierungsstatus für das vardecimal-Speicherformat der Abonnementdatenbank eine Rolle spielt. Für die Abonnementtabelle kann das vardecimal-Speicherformat auf dem Abonnenten aktiviert werden, nachdem die Tabelle erstellt wurde oder indem die Erstellungsskripts vor deren Anwendung geändert werden.

In der folgenden Tabelle werden die Skriptanforderungen für verschiedene Abonnenten beschrieben.

Weitere Überlegungen

Im Folgenden sind zusätzliche Überlegungen aufgelistet, die bei der Verwendung des vardecimal-Speicherformats berücksichtigt werden müssen:

• Das vardecimal-Speicherformat wirkt sich nicht auf Massenimport- und Massenexportvorgänge (bcp) aus.

• Mit der DATALENGTH-Funktion wird das vardecimal-Speicherformat nicht erkannt; es wird die Anzahl von Bytes zurückgegeben, die im festen Dezimalformat gespeichert werden würden.

• In seltenen Fällen wird durch das vardecimal-Speicherformat verhindert, dass in SQL Server ein Abfrageplan verwendet wird, der für feste Dezimaldaten optimal war.

• Das vardecimal-Speicherformat kann für jeden Datenbankkompatibilitätsgrad verwendet werden.

• Wenn eine Tabelle beim Ausführen von sp_tableoption keine Spalten des Datentyps decimal oder numeric aufweist, werden die Tabellenmetadaten so geändert, dass sie die Verwendung des vardecimal-Speicherformats anzeigen. Wenn zu einem späteren Zeitpunkt neue decimal-Spalten hinzugefügt werden, werden sie im vardecimal-Speicherformat gespeichert. Zum Hinzufügen bzw. Entfernen von Spalten einer Tabelle, für die das vardecimal-Speicherformat verwendet wird, sind keine speziellen Verfahren erforderlich.

Aktivieren des vardecimal-Speicherformats

Zum Aktivieren bzw. Ändern des vardecimal-Speicherformats sind folgende Berechtigungen erforderlich:

• Zum Aktivieren des vardecimal-Speicherformats in einer Datenbank ist ALTER DATABASE auf dem Server erforderlich.

• Zum Ändern einer Tabelle in das vardecimal-Speicherformat ist eine ALTER-Berechtigung für die Tabelle erforderlich.

Bevor Sie das vardecimal-Speicherformat aktivieren, müssen Sie zunächst sicherstellen, dass die Tabelle kleiner wird, wenn das vardecimal-Speicherformat aktiviert wird. Wenn die definierte Spaltengenauigkeit für die meisten Zeilen erforderlich ist, ist der zusätzliche Aufwand im Zusammenhang mit dem vardecimal-Speicherformat möglicherweise höher als die Einsparungen. Dadurch erhöht sich eventuell die Größe der Tabelle. Mithilfe der gespeicherten Prozedur sp_estimated_rowsize_reduction_for_vardecimal können Sie vor dem Ändern der Tabelle abschätzen, um wie viel sich die Zeilengröße verringern wird. Wenn Sie sich entscheiden, das Speicherformat der Tabelle zu ändern, aktivieren Sie das vardecimal-Speicherformat für die Datenbank, und aktivieren Sie das vardecimal-Speicherformat anschließend für einzelne Tabellen. SQL Server 2008-Datenbanken müssen für das vardecimal-Speicherformat nicht aktiviert werden.

In einer SQL Server 2008-Datenbank können Sie das vardecimal-Speicherformat für decimal-Datentypen mithilfe von gespeicherten Prozeduren oder SQL Server Management Studio aktivieren:

• Führen Sie sp_db_vardecimal_storage_format aus, um das vardecimal-Speicherformat in der Datenbank zu aktivieren (wenn es sich bei der Instanz von SQL Server um SQL Server 2005 SP 2 handelt), und führen Sie anschließend sp_tableoption aus, um das vardecimal-Speicherformat in den entsprechenden Tabellen zu aktivieren.

• Verwenden Sie in Management Studio die Seite Optionen unter Datenbankeigenschaften, um das vardecimal-Speicherformat in der Datenbank zu aktivieren. Sie müssen sp_tableoption verwenden, um das vardecimal-Speicherformat auf eine Tabelle anzuwenden.

Hinweis
Ab SQL Server 2008 ist für alle Datenbanken das vardecimal-Speicherformat aktiviert.

Identifizieren der Tabellen im vardecimal-Speicherformat

Wenn Sie bestimmen möchten, für welche Tabellen in einer Datenbank das vardecimal-Speicherformat verwendet wird, verwenden Sie die OBJECTPROPERTY-Funktion, und suchen Sie nach der TableHasVarDecimalStorageFormat-Eigenschaft.

Im folgenden Beispiel wird 1 zurückgegeben, wenn für die Production.WorkOrderRouting-Tabelle das vardecimal-Speicherformat verwendet wird, und 0, wenn dieses Format nicht verwendet wird.

USE AdventureWorks ;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('Production.WorkOrderRouting'), 
   'TableHasVarDecimalStorageFormat') ;
GO

Im folgenden Beispiel wird die AdventureWorks-Datenbank nach allen Tabellen durchsucht, für die das vardecimal-Speicherformat verwendet wird.

USE AdventureWorks ;
GO
SELECT name, object_id, type_desc
FROM sys.objects 
 WHERE OBJECTPROPERTY(object_id, 
   N'TableHasVarDecimalStorageFormat') = 1 ;
GO

Probleme beim Entfernen des vardecimal-Speicherformats

Wenn das vardecimal-Speicherformat für eine Tabelle entfernt werden soll, muss die Tabelle im festen Dezimalformat neu erstellt werden. Dadurch erhöht sich der für die Tabelle verwendete Speicherplatz möglicherweise erheblich. Wenn nicht genügend Speicherplatz zur Verfügung steht, treten bei dem Vorgang Fehler auf. In diesem Fall müssen Sie zum Deaktivieren des vardecimal-Speicherformats sicherstellen, dass genügend Speicherplatz für SQL Server zur Verfügung steht. Für den Erweiterungsvorgang ist es zudem erforderlich, dass die Daten sowohl im vardecimal-Format als auch im regulären Format gespeichert werden. Wenn der verfügbare Speicherplatz nach der Erweiterung für die erweiterte Tabelle ausreichen würde, diese jedoch aufgrund des Mangels an temporärem Speicherplatz nicht erweitert werden kann, können Sie die Daten inkrementell erweitern, indem Sie Zeilen der Tabelle in eine neue, nicht erweiterte Tabelle kopieren.

Wenn Sie das vardecimal-Speicherformat für eine Datenbank unmittelbar nach deren Änderung entfernen möchten, löschen Sie die Datenbank, und stellen Sie die Datenbank anschließend aus einer Sicherung wieder her, die vor der Aktivierung des vardecimal-Speicherformats für die Datenbank vorgenommen wurde.

Wenn Sie eine Datenbank aus einer SQL Server 2005 Enterprise, Developer oder Evaluation Edition in eine andere Edition oder in eine frühere Version von SQL Server verschieben, müssen Sie die Datenbank zunächst mithilfe einer der erforderlichen Editionen öffnen, das vardecimal-Speicherformat entfernen und die Datenbank anschließend migrieren. Beim Anfügen einer Datenbank mit dem vardecimal-Speicherformat an einen nicht geeigneten Server treten Fehler auf.

Siehe auch

Konzepte