Freigeben über

Verwenden von sql_variant-Daten

  • Artikel

Der sql_variant-Datentyp verhält sich ähnlich wie dervariant-Datentyp in Microsoft Visual Basic. sql_variant ermöglicht einzelnen Spalten, Parametern oder Variablen das Speichern von Datenwerten mit verschiedenen Datentypen. So kann z. B. eine sql_variant-Spalte Werte der Typen int, decimal, char, binary und nchar enthalten. Jede Instanz einer sql_variant-Spalte speichert den Datenwert und die Metadateninformationen. Dazu zählen der Basisdatentyp, die maximale Größe, die Dezimalstellen, die Genauigkeit und die Sortierung.

Regeln zum Verwenden von sql_variant

Beim Verwenden des Datentyps sql_variant gelten die folgenden Regeln:

Zuweisung allgemeiner Werte

  • sql_variant-Objekte können Daten jedes in SQL Server 2005 zulässigen Datentyps enthalten, mit Ausnahme der Datentypen text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, timestamp sowie CLR-benutzerdefinierter Typen (Common Language Runtime) in Microsoft .NET Framework. Eine Instanz der sql_variant-Daten darf außerdem nicht sql_variant als zugrunde liegenden Basisdatentyp haben.
  • Konstanten aller Typen können in Prädikaten oder Zuweisungen angegeben werden, die auf sql_variant-Spalten verweisen.
  • Wenn ein sql_variant-Wert NULL ist, wird er nicht als Wert mit einem zugrunde liegenden Basisdatentyp eingestuft. Diese Regel gilt auch dann, wenn der NULL-Wert von einer Variablen oder Spalte mit einem bestimmten Datentyp stammt.
    Im folgenden Beispiel wird der Wert von VariantCol auf NULL festgelegt, ohne zugeordneten Datentyp, selbst wenn der NULL-Wert von einer int-Variablen stammt:
    DECLARE @IntVar int
    SET @IntVar = NULL
    UPDATE SomeTable SET VariantCol = @IntVar WHERE PriKey = 123
  • Bei Zuweisungen von sql_variant-Objekten zu einem Objekt mit einem beliebigen anderen Datentyp muss der sql_variant-Wert explizit in den Datentyp des Ziels umgewandelt werden. Implizite Konvertierungen werden nicht unterstützt, wenn ein sql_variant-Wert einem Objekt mit einem anderen Datentyp zugewiesen wird.
  • Aus Gründen der Kompatibilität mit anderen Datentypen melden die Katalogobjekte (wie etwa die DATALENGTH-Funktion), die die Länge von sql_variant-Objekten melden, die Länge der Daten. Die Länge der Metadaten, die in einem sql_variant-Objekt enthalten sind, wird nicht gemeldet.
  • sql_variant-Spalten setzen immer ANSI_PADDING ON ein. Wenn char-, nchar-, varchar-, nvarchar- oder varbinary-Werte von einer Quelle mit ANSI_PADDING OFF zugewiesen werden, werden die Werte nicht aufgefüllt.

sql_variant in Tabellen

  • sql_variant-Spalten können in Indizes und eindeutigen Schlüsseln verwendet werden, so lange die Länge der Daten in den Schlüsselspalten 900 Byte nicht überschreitet.
  • sql_variant-Spalten unterstützen die IDENTITY-Eigenschaft nicht, doch sind sql_variant-Spalten als Teil von Primär- oder Fremdschlüsseln zulässig.
  • sql_variant-Spalten können nicht in einer berechneten Spalte verwendet werden.
  • Verwenden Sie ALTER TABLE zum Ändern von Spalten aller Datentypen mit Ausnahme von text, ntext, image, timestamp oder sql_variant zu sql_variant. Alle vorhandenen Werte werden dabei zu sql_variant-Werten konvertiert, deren Basisdatentyp mit dem Datentyp der Spalte vor dem Ausführen der ALTER TABLE-Anweisung übereinstimmt. ALTER TABLE kann nicht zum Ändern des Datentyps einer sql_variant-Spalte zu einem anderen Datentyp verwendet werden, da keine impliziten Konvertierungen von sql_variant zu anderen Datentypen unterstützt werden.

Sortierung

  • Die COLLATE-Klausel kann nicht zum Zuweisen einer Spaltensortierung zu einer sql_variant-Spalte verwendet werden. Die zeichenbasierten Werte (char, nchar, varchar und nvarchar) in einer sql_variant-Spalte können eine beliebige Sortierung aufweisen, und eine einzelne sql_variant-Spalte kann zeichenbasierte Werte gemischter Sortierungen enthalten.
  • Wenn ein Wert einer sql_variant-Instanz zugewiesen wird, werden sowohl der Datenwert als auch der Basisdatentyp der Quelle zugewiesen. Wenn der Quellwert eine Sortierung aufweist, wird auch die Sortierung zugewiesen. Verfügt der Quellwert über einen benutzerdefinierten Datentyp, wird nicht der benutzerdefinierte Datentyp zugewiesen, sondern der Basisdatentyp des benutzerdefinierten Datentyps. Die sql_variant-Instanz erbt keine Regeln oder Standardeinstellungen, die an den benutzerdefinierten Datentyp gebunden sind. Wenn ein Wert einer Spalte mit einer Identitätseigenschaft einer sql_variant-Instanz zugewiesen wird, nimmt sql_variant den Basisdatentyp der Quellspalte an, erbt die IDENTITY-Eigenschaft jedoch nicht. Es ist ein Fehler, einer sql_variant-Instanz einen text-, ntext- oder image-Wert zuzuweisen. Implizite Konvertierungen werden unterstützt, wenn Werte von Objekten mit anderen Datentypen einem sql_variant-Objekt zugewiesen werden.

sql_variant-Vergleiche

sql_variant-Spalten können Werte verschiedener Basisdatentypen und Sortierungen enthalten; somit gelten spezielle Regeln beim Vergleichen von sql_variant-Operanden. Diese Regeln beziehen sich auf Operationen, die Vergleiche einschließen, wie z. B.:

  • Transact-SQL-Vergleichsoperatoren
  • ORDER BY, GROUP BY
  • Indizes
  • Die Aggregatfunktionen MAX und MIN
  • UNION (ohne ALL)
  • CASE-Ausdrücke

Für sql_variant-Vergleiche wird die Reihenfolge der Datentyphierarchie von SQL Server in Datentypfamilien unterteilt. Die sql_variant-Familie hat die höchste Familienrangfolge.

Datentyphierarchie Datentypfamilie

sql_variant

sql_variant

datetime

Datum und Zeit

smalldatetime

Datum und Zeit

float

Ungefährer numerischer Wert

real

Ungefährer numerischer Wert

decimal

Genauer numerischer Wert

money

Genauer numerischer Wert

smallmoney

Genauer numerischer Wert

bigint

Genauer numerischer Wert

int

Genauer numerischer Wert

smallint

Genauer numerischer Wert

tinyint

Genauer numerischer Wert

bit

Genauer numerischer Wert

nvarchar

Unicode

nchar

Unicode

varchar

Unicode

char

Unicode

varbinary

Binär

binary

Binär

uniqueidentifier

Uniqueidentifier

Für sql_variant-Vergleiche gelten folgende Regeln:

  • Wenn sql_variant-Werte unterschiedlicher Basisdatentypen verglichen werden und die Basisdatentypen verschiedenen Datentypfamilien angehören, wird der Wert als der größere eingestuft, dessen Datentypfamilie sich weiter oben in der Hierarchieliste befindet.
  • Wenn sql_variant-Werte unterschiedlicher Basisdatentypen verglichen werden und die Basisdatentypen derselben Datentypfamilie angehören, wird der Wert, dessen Basisdatentyp sich weiter unten in der Hierarchieliste befindet, in den anderen Datentyp implizit konvertiert, und dann wird der Vergleich durchgeführt.
  • Wenn sql_variant-Werte der Datentypen char, varchar, nchar oder varchar verglichen werden, werden diese anhand der folgenden Kriterien ausgewertet: LCID, LCID-Version, Vergleichsflags und Sortier-ID. Diese Kriterien werden als ganzzahlige Werte und in der genannten Reihenfolge verglichen.

Diese Regeln können unterschiedliche Ergebnisse bei Vergleichen zwischen sql_variant-Werten und bei Vergleichen zwischen Werten desselben Basisdatentyps erzielen.

Operand A Operand B Ergebnis beim Vergleich zwischen Nichtvarianten Ergebnis beim Vergleich von sql_variant-Werten

'123' char

111 int

A > B

B > A

50000 int

5E1 float

A > B

B > A

Da Werte verschiedener Datentypfamilien explizit umgewandelt werden müssen, ehe in Vergleichsprädikaten auf sie verwiesen werden kann, können die Auswirkungen der Regeln nur bei der Reihenfolge der Resultsets für eine sql_variant-Spalte festgestellt werden. Die Werte in der folgenden Tabelle sind Beispiele für die Regeln bezüglich der Rangfolge von Datentypen.

PriKey VariantCol

1

50,0 (Basistyp float)

2

5000 (Basistyp int)

3

'124000' (Basistyp char(6))

Die folgende Tabelle zeigt das Ergebnis der Anweisung: SELECT * FROM VariantTest ORDER BY VariantCol ASC.

PriKey VariantCol

3

'124000' (Basistyp char(6))

2

5000 (Basistyp int)

1

50,0 (Basistyp float)

Die Werte in der folgenden Tabelle sind Beispiele für die Regeln bezüglich der Sortierungsrangfolge beim Verwenden verschiedener Sortierungen.

IntKey VariantCol

1

qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

2

abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

3

qrs (varchar SQL_Latin1_General_CP1_CS_AS)

4

17,5 (dezimal)

5

abc (varchar SQL_Latin1_General_CP1_CS_AS)

6

klm (varchar SQL_Latin1_General_CP1_CS_AS)

7

1,2 (dezimal)

Die folgende Tabelle zeigt das Ergebnis der Anweisung: SELECT * FROM CollateTest ORDER BY VariantCol. Diese Tabelle zeigt Werte aus der Gruppierung der Datentypfamilie der exakten Zahlen sowie der Gruppierung der varchar-Werte in den jeweiligen Sortierungen.

IntKey VariantCol

5

abc (varchar SQL_Latin1_General_CP1_CS_AS)

6

klm (varchar SQL_Latin1_General_CP1_CS_AS)

3

qrs (varchar SQL_Latin1_General_CP1_CS_AS)

2

abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

1

qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

7

1,2 (dezimal)

4

17,5 (dezimal)

Funktionen und sql_variant-Daten

Die folgenden Transact-SQL-Funktionen unterstützen sql_variant-Parameter und melden einen sql_variant-Wert, wenn ein sql_variant-Parameter angegeben wird:

COALESCE

MIN

MAX

NULLIF

Diese Funktionen unterstützen Verweise auf sql_variant-Spalten oder -Variablen und verwenden sql_variant nicht als Datentyp für die Rückgabewerte.

COL_LENGTH

DATALENGTH

TYPEPROPERTY

COLUMNPROPERTY

ISNULL

 

Die folgenden Transact-SQL-Funktionen unterstützen keine sql_variant-Parameter:

AVG

RADIANS

STDEV[P]

IDENTITY

ROUND

SUM

ISNUMERIC

SIGN

VAR[P]

POWER

 

 

Die Funktionen CAST und CONVERT unterstützen sql_variant.

Die neue SQL_VARIANT_PROPERTY()-Funktion wird verwendet, um Eigenschaftsinformationen zu sql_variant-Werten, wie z. B. Datentyp, Genauigkeit oder Dezimalstellen, zu erhalten.

Andere Transact-SQL-Elemente und sql_variant-Daten

sql_variant-Spalten werden im LIKE-Prädikat nicht unterstützt.

sql_variant-Spalten werden in Volltextindizes nicht unterstützt. sql_variant-Spalten können in Volltextfunktionen, wie CONTAINSTABLE und FREETEXTTABLE, nicht angegeben werden.

Diese Transact-SQL-Anweisungen unterstützen die Angabe von sql_variant in den gleichen Syntaxspeicherorten wie auch andere ganzzahlige Datentypen:

  • ALTER PROCEDURE
  • ALTER TABLE
  • CREATE PROCEDURE
  • CREATE TABLE
  • DECLARE variable

Die SQL Server 2005-Katalogkomponenten melden Informationen zu sql_variant-Spalten.

Das Ergebnis des CASE-Ausdrucks ist sql_variant, wenn einer der Eingabe- oder Ergebnisausdrücke zu sql_variant ausgewertet wird. Der zugrunde liegende Basistyp des Ergebnisses ist der zur Laufzeit als Ergebnis ausgewertete Ausdruck.

Operanden numerischer Operatoren oder von Operatoren für das Verketten von Zeichenfolgen können nicht sql_variant sein: So erzeugt z. B. der folgende Code einen Fehler:

SELECT VariantCol + @CharacterVar

FROM MyTable

Sie können jedoch durch Konvertieren des sql_variant-Operanden den folgenden Vorgang ausführen:

SELECT CAST(VariantCol AS varchar(25)) + @CharacterVar

FROM MyTable

Anwendungen und sql_variant-Daten

Wenn eine Anwendung ein Resultset anfordert, in dem eine bestimmte Spalte sql_variant-Daten eines einzelnen zugrunde liegenden Basisdatentyps zurückgibt, kann die Anwendung die Funktionen CAST oder CONVERT in den Transact-SQL-Anweisungen zur Rückgabe der sql_variant-Daten mithilfe des zugrunde liegenden Basisdatentyps verwenden. In diesem Fall behandelt die Anwendung die Daten wie eine Resultsetspalte des zugrunde liegenden Basisdatentyps.

Der SQL Native Client-OLE DB-Anbieter für SQL Server führt einen anbieterspezifischen OLE DB-Typ DBTYPE_SQLVARIANT zum Verwenden mit sql_variant-Spalten und -Parametern ein.

Der SQL Native Client-ODBC-Treiber von SQL Server führt einen anbieterspezifischen ODBC-Datenbank-Datentyp SQL_SS_VARIANT zum Verwenden mit sql_variant-Spalten und -Parametern ein.

SQL Server konvertiert sql_variant-Werte zu nvarchar(4000), wenn Sie mit Anwendungen arbeiten, die über die folgenden Schnittstellen verbunden wurden:

  • OLE DB Provider für SQL Server, Version 7.0
  • SQL Server ODBC-Treiber von SQL Server 7.0

Wenn die sich ergebende Zeichenfolge 4.000 Zeichen überschreitet, gibt SQL Server die ersten 4.000 Zeichen zurück.

SQL Server konvertiert sql_variant-Werte zu varchar(255), wenn es mit Anwendungen arbeitet, die über die folgenden Schnittstellen verbunden wurden:

  • SQL Server ODBC-Treiber aus SQL Server, Version 6.5 oder früher

Wenn die sich ergebende Zeichenfolge 255 Zeichen überschreitet, gibt SQL Server die ersten 255 Zeichen zurück.

Siehe auch

Andere Ressourcen

CAST und CONVERT (Transact-SQL)
sql_variant (Transact-SQL)
SQL_VARIANT_PROPERTY (Transact-SQL)
Datentypen (Transact-SQL)

Hilfe und Informationen

Informationsquellen für SQL Server 2005