Registrieren benutzerdefinierter Typen in SQL Server
Um einen benutzerdefinierten Typ (UDT) in Microsoft SQL Server zu verwenden, müssen Sie ihn registrieren. Beim Registrieren eines UDT muss die Assembly registriert werden und der Typ in der Datenbank, in der er verwendet werden soll, erstellt werden. UDTs beschränken sich auf eine einzelne Datenbank und können nicht in mehreren Datenbanken verwendet werden, es sei denn die gleiche Assembly und der gleiche UDT wurden in jeder Datenbank registriert. Nachdem die UDT-Assembly registriert und der Typ erstellt wurden, können Sie den UDT in Transact-SQL und im Clientcode verwenden. Weitere Informationen finden Sie unter Benutzerdefinierte CLR-Typen.
Verwenden von Visual Studio zum Bereitstellen von UDTs
Am einfachsten stellen Sie den UDT mit Microsoft Visual Studio bereit. Verwenden Sie jedoch bei komplexeren Bereitstellungsszenarios und für eine größere Flexibilität Transact-SQL, wie nachfolgend in diesem Thema beschrieben.
Führen Sie die folgenden Schritte aus, um einen UDT mit Visual Studio zu erstellen und bereitzustellen:
Erstellen Sie unter den Sprachknoten Visual Basic oder Visual C# ein neues Datenbankprojekt.
Fügen Sie einen Verweis auf die SQL Server-Datenbank hinzu, die den UDT enthalten wird.
Fügen Sie eine Benutzerdefinierte Typ-Klasse hinzu.
Erstellen Sie den Code, um den UDT zu implementieren.
Wählen Sie im Menü Erstellen die Option Bereitstellen. Somit wird die Assembly registriert und der Typ in der SQL Server-Datenbank erstellt.
Verwenden von Transact-SQL zum Bereitstellen von UDTs
Mit der Transact-SQL CREATE ASSEMBLY-Syntax wird die Assembly in der Datenbank registriert, in der Sie den UDT verwenden möchten. Sie werden intern in Datenbanksystemtabellen gespeichert, nicht extern im Dateisystem. Wenn die UDTs von externen Assemblys abhängig sind, müssen sie auch in die Datenbank geladen werden. Mit der CREATE TYPE-Anweisung wird der UDT in der Datenbank erstellt, in der er verwendet werden soll. Weitere Informationen finden Sie unter CREATE ASSEMBLY (Transact-SQL) und CREATE TYPE (Transact-SQL).
Verwenden von CREATE ASSEMBLY
Mit der CREATE ASSEMBLY-Syntax wird die Assembly in der Datenbank registriert, in der Sie den UDT verwenden möchten. Sobald die Assembly registriert wurde, liegen keine Abhängigkeiten vor.
Das Erstellen mehrerer Versionen der gleichen Assembly in einer Datenbank ist nicht zulässig. Es ist jedoch möglich, mehrere Versionen der gleichen Assembly basierend auf der Kultur in einer bestimmten Datenbank zu erstellen. SQL Server unterscheidet verschiedene Kulturversionen einer Assembly anhand der Namen, die in der Instanz von SQL Server registriert wurden. Weitere Informationen finden Sie unter "Erstellen und Verwenden von Assemblys mit starkem Namen" im .NET Framework SDK.
Wenn CREATE ASSEMBLY mit dem SAFE- oder EXTERNAL_ACCESS-Berechtigungssatz ausgeführt wird, wird die Assembly überprüft, um sicherzustellen, dass sie überprüfbar und typsicher ist. Wenn Sie keinen Berechtigungssatz angeben, wird standardmäßig SAFE vorausgesetzt. Code mit dem UNSAFE-Berechtigungssatz wird nicht überprüft. Weitere Informationen zu Assemblyberechtigungssätzen finden Sie unter Entwerfen von Assemblys.
Beispiel
Mit der folgenden Transact-SQL-Anweisung wird die Point-Assembly in SQL Server in der AdventureWorks-Datenbank mit dem SAFE-Berechtigungssatz registriert. Wenn die WITH PERMISSION_SET-Klausel nicht angegeben wird, wird die Assembly mit dem SAFE-Berechtigungssatz registriert.
USE AdventureWorks;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll'
WITH PERMISSION_SET = SAFE;
Die folgende Transact-SQL-Anweisung registriert die Assembly mithilfe des <assembly_bits>-Arguments in der FROM-Klausel. Dieser varbinary-Wert stellt die Datei als Byte-Datenstrom dar.
USE AdventureWorks;
CREATE ASSEMBLY Point
FROM 0xfeac4 … 21ac78
Verwenden von CREATE TYPE
Nachdem die Assembly in die Datenbank geladen wurde, können Sie den Typ mit der Transact-SQL CREATE TYPE-Anweisung erstellen. Dadurch wird der Typ der Liste mit verfügbaren Typen für diese Datenbank hinzugefügt. Der Typ hat einen Datenbankbereich und kann nur in der Datenbank, in der er erstellt wurde, verwendet werden. Wenn der UDT bereits in der Datenbank vorhanden ist, schlägt die CREATE TYPE-Anweisung fehl.
.gif "Hinweis") Hinweis |
|---|
Die CREATE TYPE-Syntax wird auch zur Erstellung systemeigener SQL Server-Aliasdatentypen verwendet und soll sp_addtype ersetzen, um die Aliasdatentypen zu erstellen. Einige der optionalen Argumente in der CREATE TYPE-Syntax beziehen sich auf das Erstellen von UDTs, sie gelten nicht für das Erstellen von Aliasdatentypen (z. B. Basistyp). |
Weitere Informationen finden Sie unter CREATE TYPE (Transact-SQL).
Beispiel
Mit der folgenden Transact-SQL-Anweisung wird der Point-Typ erstellt. Der EXTERNAL NAME wird mit der zweiteiligen Namenssyntax AssemblyName.UDTName angegeben.
CREATE TYPE dbo.Point
EXTERNAL NAME Point.[Point];
Entfernen eines UDTs aus der Datenbank
Mit der DROP TYPE-Anweisung wird ein UDT aus der aktuellen Datenbank entfernt. Sobald der UDT entfernt wurde, können Sie die Assembly mit der DROP ASSEMBLY-Anweisung aus der Datenbank löschen.
Die DROP TYPE-Anweisung wird nicht in den folgenden Situationen ausgeführt:
Tabellen in der Datenbank, die mit dem UDT definierte Spalten enthalten.
Funktionen, gespeicherte Prozeduren oder Trigger, die Variablen und Parameter des UDT verwenden und in der Datenbank mit der WITH SCHEMABINDING-Klausel erzeugt wurden.
Beispiel
Transact-SQL muss in der folgenden Reihenfolge ausgeführt werden. Zuerst muss die Tabelle, die auf den Point-UDT verweist, anschließend der Typ und zuletzt die Assembly gelöscht werden.
DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;
Ermitteln von UDT-Abhängigkeiten
Wenn abhängige Objekte, z. B. Tabellen mit UDT-Spaltendefinitionen, vorliegen, schlägt die DROP TYPE-Anweisung fehl. Sie schlägt auch dann fehl, wenn sich in der Datenbank Funktionen, gespeicherte Prozeduren oder Trigger befinden, die mit der WITH SCHEMABINDING-Klausel erstellt wurden, wenn diese Routinen Variablen oder Parameter des benutzerdefinierten Typs verwenden. Sie müssen zuerst alle abhängigen Objekte löschen und dann die DROP TYPE-Anweisung ausführen.
Mit der folgenden Transact-SQL-Abfrage werden alle Spalten und Parameter, die einen benutzerdefinierten Typ verwenden, in der AdventureWorks-Datenbank gefunden.
USE Adventureworks;
SELECT o.name AS major_name, o.type_desc AS major_type_desc
, c.name AS minor_name, c.type_desc AS minor_type_desc
, at.assembly_class
FROM (
SELECT object_id, name, user_type_id, 'SQL_COLUMN' AS type_desc
FROM sys.columns
UNION ALL
SELECT object_id, name, user_type_id, 'SQL_PROCEDURE_PARAMETER'
FROM sys.parameters
) AS c
JOIN sys.objects AS o
ON o.object_id = c.object_id
JOIN sys.assembly_types AS at
ON at.user_type_id = c.user_type_id;
Verwalten von UDTs
Nachdem ein UDT in einer SQL Server-Datenbank erstellt wurde, können Sie ihn nicht mehr ändern; Sie können jedoch die Assembly, auf der der Typ basiert, ändern. Meistens müssen Sie den UDT aus der Datenbank mit der Transact-SQL DROP TYPE-Anweisung entfernen, Änderungen an der zugrunde liegenden Assembly vornehmen und sie dann mit der ALTER ASSEMBLY-Anweisung neu laden. Anschließend müssen der UDT und abhängige Objekte neu erstellt werden.
Beispiel
Die ALTER ASSEMBLY-Anweisung wird erst verwendet, nachdem Sie Änderungen am Quellcode in der UDT-Anweisung vorgenommen und sie neu kompiliert haben. Die DLL-Datei wird auf den Server kopiert und dort zur neuen Assembly verbunden. Die vollständige Syntax finden Sie unter ALTER ASSEMBLY (Transact-SQL).
Mit der folgenden Transact-SQL ALTER ASSEMBLY-Anweisung wird die Point.dll-Assembly erneut vom angegebenen Speicherort auf dem Datenträger geladen.
ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll'
Verwenden von ALTER ASSEMBLY zum Hinzufügen von Quellcode
Die ADD FILE-Klausel in der ALTER ASSEMBLY-Syntax ist nicht in CREATE ASSEMBLY vorhanden. Sie können sie verwenden, um Quellcode oder beliebige andere Dateien hinzuzufügen, die einer Assembly zugeordnet sind. Die Dateien werden von ihren ursprünglichen Speicherorten kopiert und in Systemtabellen in der Datenbank gespeichert. Dadurch wird sichergestellt, dass stets der Quellcode oder andere Dateien verfügbar sind, wenn Sie die aktuelle Version des UDT neu erstellen oder dokumentieren müssen.
Mit der folgenden Transact-SQL ALTER ASSEMBLY-Anweisung wird der Point.cs-Klassenquellcode für den Point-UDT hinzugefügt. Dadurch wird der in der Datei Point.cs enthaltene Text kopiert und unter dem Namen "PointSource" in der Datenbank gespeichert.
ALTER ASSEMBLY Point
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;
Assemblyinformationen werden in der Tabelle sys.assembly_files in der Datenbank gespeichert, in der die Assembly installiert wurde. Die sys.assembly_files-Tabelle enthält die folgenden Spalten.
assembly_id
Der für die Assembly definierte Bezeichner. Diese Nummer wird allen Objekten mit Bezug auf dieselbe Assembly zugewiesen.name
Der Name des Objekts.file_id
Eine Nummer, die die einzelnen Objekte identifiziert, wobei das erste Objekt, das einer angegebenen assembly_id zugeordnet ist, den Wert 1 erhält. Wenn mehrere Objekte derselben assembly_id zugeordnet werden, wird jeder nachfolgende file_id-Wert um 1 erhöht.content
Die Hexadezimaldarstellung der Assembly oder Datei.
Mit der CAST- oder CONVERT-Funktion können Sie die Inhalte der content-Spalte in lesbaren Text konvertieren. Die folgende Abfrage konvertiert die Inhalte der Point.cs-Datei in lesbaren Text, wobei der Name in der WHERE-Klausel verwendet wird, um den Ergebnissatz auf eine einzelne Zeile zu beschränken.
SELECT CAST(content AS varchar(8000))
FROM sys.assembly_files
WHERE name='PointSource';
Wenn Sie die Ergebnisse in einen Texteditor kopieren und einfügen, bleiben die Zeilenumbrüche und Leerstellen des Originals erhalten.
Verwalten von UDTs und Assemblys
Beim Planen der Implementierung von UDTs sollten Sie die Methoden berücksichtigen, die in der UDT-Assembly selbst benötigt werden und die in separaten Assemblys erstellt und als benutzerdefinierte Funktionen oder gespeicherte Prozeduren implementiert werden sollen. Durch das Trennen von Methoden in separate Assemblys können Sie Code ohne Auswirkungen auf die Daten, die in einer UDT-Spalte einer Tabelle gespeichert wurden, aktualisieren. Sie können die UDT-Assemblys nur ändern, ohne UDT-Spalten und andere abhängige Objekte zu löschen, wenn die neue Definition die vorherigen Werte lesen kann und sich die Signatur des Typs nicht ändert.
Die Trennung des Prozedurencodes, der sich vom Code zum Implementieren des UDT unterscheiden kann, vereinfacht den Verwaltungsaufwand. Wenn Sie nur Code einschließen, der für die Funktionsweise des UDT erforderlich ist, und die UDT-Definitionen so einfach wie möglich halten, können Sie das Risiko reduzieren, dass der UDT bei einer Codeüberarbeitung oder bei Fehlerbehebungen selbst aus der Datenbank gelöscht wird.
Der Currency-UDT und die Währungskonvertierungsfunktion
Der Currency-UDT in der AdventureWorks-Beispielsdatenbank ist ein gutes Beispiel für die Struktur eines UDT und seiner zugeordneten Funktionen. Der Currency-UDT wird zur Berechnung von Geldern basierend auf dem Währungssystem einer bestimmten Kultur verwendet. Mit ihm können verschiedene Währungen gespeichert werden, z. B. Dollar, Euro usw. Die UDT-Klasse stellt einen Kulturnamen als Zeichenfolge und einen Geldbetrag als decimal-Datentyp zur Verfügung. Alle notwendigen Serialisierungsmethoden sind in der Assembly enthalten, die die Klasse definiert. Die Funktion, die die Währungskonvertierung von einer Kultur in eine andere durchführt, wird als externe Funktion namens ConvertCurrency implementiert. Diese Funktion befindet sich in einer separaten Assembly. Die ConvertCurrency-Funktion ruft den Wechselkurs aus einer Tabelle in der AdventureWorks-Datenbank ab. Wenn sich die Wechselkursquelle ändern sollte oder andere Änderungen am Code auftreten sollten, kann die Assembly mühelos geändert werden, ohne den Currency-UDT zu beeinträchtigen.
Die Codeliste für den Currency-UDT und die ConvertCurrency-Funktionen werden beim Installieren der Common Language Runtime (CLR)-Beispiele installiert.
Verwenden von UDTs über mehrere Datenbanken hinweg
UDTs sind definitionsgemäß auf eine einzelne Datenbank beschränkt. Aus diesem Grund kann ein UDT, der in einer Datenbank definiert wurde, nicht in einer Spaltendefinition in einer anderen Datenbank verwendet werden. Um UDTs in mehreren Datenbanken zu verwenden, müssen Sie die CREATE ASSEMBLY- und CREATE TYPE-Anweisungen in jeder Datenbank für identische Assemblys ausführen. Assemblys gelten als identisch, wenn die folgenden Werte gleich sind: Name, starker Name, Kultur, Version, Berechtigungssatz und binäre Inhalte.
Nachdem der UDT registriert wurde und der Zugriff darauf in beiden Datenbanken erfolgen kann, können Sie einen UDT-Wert aus einer Datenbank für die Verwendung in einer anderen Datenbank konvertieren. Identische UDTs können in den folgenden Szenarios über mehrere Datenbanken hinweg verwendet werden:
Aufrufen einer gespeicherten Prozedur, die in anderen Datenbanken definiert ist.
Abfragen von in anderen Datenbanken definierten Tabellen.
Auswählen von UDT-Daten aus einer UDT-Spalte in einer Datenbanktabelle und Einfügen in eine zweite Datenbank mit einer identischen UDT-Spalte.
In diesen Szenarios findet die für den Server erforderliche Konvertierung automatisch statt. Sie können die Konvertierung nicht explizit mit den Transact-SQL CAST- oder CONVERT-Funktionen durchführen.
Es sind keine Eingaben Ihrerseits erforderlich, um UDTs zu verwenden, wenn SQL Server Database Engine (Datenbankmodul) Arbeitstabellen in der tempdb-Systemdatenbank erstellt. Dies umfasst die Handhabung von Cursorn, Tabellenvariablen und benutzerdefinierten Tabellenwertfunktionen, die UDTs enthalten und die tempdb verwenden. Wenn Sie jedoch explizit eine temporäre Tabelle in tempdb erstellen, die eine UDT-Spalte definiert, muss der UDT in tempdb auf gleiche Weise wie für eine Benutzerdatenbank registriert werden.