Artikel
03/30/2012

Verwenden besonderer Datentypen

In diesem Thema werden die speziellen Datentypen beschrieben, die in SQL Server verfügbar sind. Bei besonderen Datentypen handelt es sich um solche Datentypen, die keinen anderen Datentypkategorien zugeordnet werden können. In SQL Server gehören bit, hierarchyid, sql_variant, sysname, table, timestamp und Aliasdatentypen zu den speziellen Datentypen.

bit

Der Datentyp bit ist ein numerischer Datentyp, der entweder 0 oder 1 speichert. Die Zeichenfolgenwerte true und false können wie im folgenden Beispiel gezeigt in bit-Werte konvertiert werden.

SELECT CONVERT (bit, 'true')
SELECT CONVERT(bit, 'false')

In diesem Beispiel wird true in 1 und false in 0 konvertiert. bit-Daten müssen nicht in einzelne Anführungszeichen gesetzt werden.

hierarchyid

Der Datentyp hierarchyid wird verwendet, um hierarchische Daten und Tabellen zu verwalten, die über eine hierarchische Struktur verfügen. Um mit hierarchischen Daten in Transact-SQL-Code zu arbeiten, verwenden Sie hierarchyid-Funktionen. Weitere Informationen finden Sie unter Verwenden von hierarchyid-Datentypen (Datenbankmodul).

sql_variant

Der Datentyp sql_variant ermöglicht es einer einzelnen Spalte, einem Parameter oder einer Variablen, Datenwerte verschiedener Datentypen zu speichern. Jede Instanz einer sql_variant-Spalte zeichnet den Wert und die Metadaten auf, die den Wert beschreiben. Die folgenden Metadaten sind verfügbar:

Basisdatentyp
Maximale Größe
Skalierung
Genauigkeit
Sortierung

Zum Abrufen der Metadateninformationen für eine bestimmte sql_variant-Instanz steht Ihnen die SQL_VARIANT_PROPERTY-Funktion zur Verfügung.

Im folgenden Beispiel enthält die zweite Tabelle eine sql_variant-Spalte:

CREATE TABLE ObjectTable (
   ObjectID int CONSTRAINT PKObjectTable PRIMARY KEY,
   ObjectName nvarchar(80),
   ObjectWeight decimal(10,3),
   ObjectColor nvarchar(20))
CREATE TABLE VariablePropertyTable (
   ObjectID int REFERENCES ObjectTable(ObjectID),
   PropertyName nvarchar(100),
   PropertyValue sql_variant,
   CONSTRAINT PKVariablePropertyTable
   PRIMARY KEY(ObjectID, PropertyName))

sysname

Der Datentyp sysname wird für Tabellenspalten, Variablen und Parameter gespeicherter Prozeduren verwendet, die Objektnamen speichern. Die genaue Definition von sysname bezieht sich auf die Regeln für Bezeichner. Daher kann sie zwischen Instanzen von SQL Server variieren. sysname ist von den Funktionen her mit nvarchar(128) identisch. sysname ist jedoch standardmäßig NOT NULL. In früheren Versionen von SQL Server ist sysname als varchar(30) definiert.

Wichtig
In Datenbanken, bei denen zwischen Groß-/Kleinschreibung unterschieden wird bzw. die eine binäre Sortierung aufweisen, wird sysname nur bei Kleinschreibung als Systemdatentyp von SQL Server erkannt.

table

Der Datentyp table funktioniert wie eine temporäre Tabelle. Er wird verwendet, um ein Resultset zur späteren Verarbeitung zu speichern. Dieser Datentyp kann nur zur Definition lokaler Variablen des table-Datentyps und des Rückgabewertes einer benutzerdefinierten Funktion verwendet werden.

Die Definition einer Tabellenvariablen oder eines Rückgabewerts umfasst Definitionen der Spalten, den Datentyp, die Genauigkeit und Dezimalstellenanzahl jeder Spalte sowie optional PRIMARY KEY-, UNIQUE-, NULL- und CHECK-Einschränkungen. Eine benutzerdefinierte Tabelle kann nicht als Datentyp verwendet werden.

Das in einer table-Variablen gespeicherte oder von einer benutzerdefinierten Funktion zurückgegebene Zeilenformat muss beim Deklarieren der Variablen oder beim Erstellen der Funktion definiert werden. Die Syntax basiert auf der CREATE TABLE-Syntax. Beispiel:

DECLARE @TableVar TABLE (Cola int PRIMARY KEY, Colb char(3))
INSERT INTO @TableVar VALUES (1, 'abc')
INSERT INTO @TableVar VALUES (2, 'def')
SELECT * FROM @TableVar
GO

table-Variablen und benutzerdefinierte Funktionen, die table zurückgeben, können nur in bestimmten SELECT- und INSERT-Anweisungen verwendet werden, sofern Tabellen in den UPDATE-, DELETE- und DECLARE CURSOR-Anweisungen unterstützt werden. table-Variablen und benutzerdefinierte Funktionen, die table zurückgeben, können in keinen anderen Transact-SQL-Anweisungen verwendet werden.

Indizes oder sonstige Einschränkungen, die auf die Tabelle angewendet werden, müssen im Rahmen der DECLARE-Variablen oder der CREATE FUNCTION-Anweisung definiert werden. Sie können nicht nachträglich angewendet werden, da CREATE INDEX- oder ALTER TABLE-Anweisungen nicht auf Tabellenvariablen und benutzerdefinierte Funktionen verweisen können.

Weitere Informationen zur Syntax für die Definition der table-Variablen und benutzerdefinierten Funktionen finden Sie unter DECLARE @local_variable (Transact-SQL) und CREATE FUNCTION (Transact-SQL).

timestamp

Der Datentyp timestamp hat nichts mit Uhrzeit- oder Datumsangaben zu tun. timestamp-Werte sind binäre Zahlen, die die relative Reihenfolge anzeigen, in der Datenänderungen in einer Datenbank vorgenommen wurden.

timestamp-Spalten dürfen nie in Schlüsseln, vor allem Primärschlüsseln, verwendet werden, da der timestamp-Wert bei jedem Ändern der Zeile geändert wird.

Zum Aufzeichnen der Zeiten, zu denen Datenänderungen in einer Tabelle stattfinden, verwenden Sie entweder den datetime2- oder den smalldatetime-Datentyp, um die Ereignisse und Trigger aufzuzeichnen, die die Werte bei Änderungen automatisch aktualisieren.

Aliasdatentypen

Aliasdatentypen ermöglichen Ihnen die Erweiterung eines SQL Server-Basisdatentyps (z. B. varcha) um einen beschreibenden Namen und ein Format, das auf einen bestimmten Verwendungszweck zugeschnitten werden kann. Die folgende Anweisung implementiert z. B. einen benutzerdefinierten Datentyp birthday, der auf dem Datentyp datetime basiert und NULL-Werte zulässt (NULL):

EXEC sp_addtype birthday, datetime, 'NULL'

Gehen Sie bei der Auswahl der Basistypen zum Implementieren benutzerdefinierter Datentypen sorgfältig vor. Sozialversicherungsnummern in den USA weisen beispielsweise das Format nnn-nn-nnnn auf. Die Sozialversicherungsnummern enthalten zwar Zahlen, bilden jedoch einen Bezeichner und werden keinen mathematischen Operationen unterzogen. Als gängige Regel wird deshalb der benutzerdefinierte Datentyp varchar für Sozialversicherungsnummern und eine CHECK-Einschränkung erstellt, um das Format der in der Tabelle gespeicherten Sozialversicherungsnummern zu erzwingen. Dies ist im folgenden Beispiel gezeigt:

EXEC sp_addtype SSN, 'VARCHAR(11)', 'NOT NULL'
GO
CREATE TABLE ShowSSNUsage (EmployeeID int PRIMARY KEY, EmployeeSSN SSN, CONSTRAINT CheckSSN CHECK ( EmployeeSSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' )
)
GO

Wenn die SSN-Spalten in der Regel als Schlüsselspalten in Indizes, vor allem gruppierten Indizes, verwendet werden, kann die Größe der Schlüssel von 11 Byte auf 4 Byte reduziert werden, wenn stattdessen der benutzerdefinierte SSN-Datentyp mithilfe des int-Basisdatentyps implementiert wird. Durch diese Reduzierung der Schlüsselgröße wird der Datenabruf verbessert. Die gesteigerte Effizienz beim Datenabruf und das Wegfallen der CHECK-Einschränkung machen in der Regel den zusätzlichen Konvertierungsaufwand von int in ein Zeichenformat beim Anzeigen oder Ändern von SSN-Werten wett.