Freigeben über

Registrieren von benutzerdefinierten Typen in SQL Server

  • Artikel

Gilt für:SQL Server

Um einen benutzerdefinierten Typ (USER-Defined Type, UDT) in 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 sind auf eine einzelne Datenbank festgelegt und können nicht in mehreren Datenbanken verwendet werden, es sei denn, die identische Assembly und UDT werden bei jeder Datenbank registriert. Nachdem die UDT-Assembly registriert und der Typ erstellt wurde, können Sie udT in Transact-SQL und im Clientcode verwenden. Weitere Informationen finden Sie unter benutzerdefinierte CLR-Typen.

Verwenden von Visual Studio zum Bereitstellen von UDTs

Die einfachste Möglichkeit zum Bereitstellen Ihres UDT ist die Verwendung von Visual Studio. Verwenden Sie für komplexere Bereitstellungsszenarien und die größte Flexibilität jedoch Transact-SQL, wie weiter unten in diesem Artikel erläutert.

Führen Sie die folgenden Schritte aus, um einen UDT mit Visual Studio zu erstellen und bereitzustellen:

  1. Erstellen Sie ein neues Datenbankprojekt in den Visual Basic - oder Visual C# -Sprachknoten.

  2. Fügen Sie einen Verweis auf die SQL Server-Datenbank hinzu, die das UDT enthält.

  3. Fügen Sie eine benutzerdefinierte Typklasse hinzu.

  4. Erstellen Sie den Code, um den UDT zu implementieren.

  5. Wählen Sie im Menü "Erstellen " die Option "Bereitstellen" aus. Dadurch wird die Assembly registriert und der Typ in der SQL Server-Datenbank erstellt.

Verwenden von Transact-SQL zum Bereitstellen von UDTs

Die Transact-SQL CREATE ASSEMBLY Syntax wird verwendet, um die Assembly in der Datenbank zu registrieren, in der Sie udT verwenden möchten. Sie wird 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. Die CREATE TYPE-Anweisung wird verwendet, um die UDT in der Datenbank zu erstellen, in der sie verwendet werden soll. Weitere Informationen finden Sie unter CREATE ASSEMBLY und CREATE TYPE.

Erstellen einer Assembly verwenden

Die CREATE ASSEMBLY Syntax registriert die Assembly in der Datenbank, in der Sie udT verwenden möchten. Sobald die Assembly registriert wurde, liegen keine Abhängigkeiten vor.

Das Erstellen mehrerer Versionen derselben Assembly in einer bestimmten Datenbank ist nicht zulässig. Es ist jedoch möglich, mehrere Versionen derselben Assembly basierend auf der Kultur in einer bestimmten Datenbank zu erstellen. SQL Server unterscheidet mehrere Kulturversionen einer Assembly durch verschiedene Namen, wie in der Instanz von SQL Server registriert. Weitere Informationen finden Sie unter Erstellen und Verwenden von assemblys mit starkem Namen.

Wenn CREATE ASSEMBLY mit den SAFE- oder EXTERNAL_ACCESS Berechtigungssätzen ausgeführt wird, wird die Assembly überprüft, um sicherzustellen, dass sie überprüfbar und typsicher ist. Wenn Sie die Angabe eines Berechtigungssatzes weglassen, wird SAFE angenommen. Code mit dem UNSAFE Berechtigungssatz ist nicht aktiviert. Weitere Informationen zu Assemblyberechtigungssätzen finden Sie unter Designassemblys.

Beispiel

Die folgende Transact-SQL-Anweisung registriert die Point-Assembly in SQL Server in der AdventureWorks2022-Datenbank mit dem SAFE Berechtigungssatz. Wenn die WITH PERMISSION_SET-Klausel nicht angegeben wird, wird die Assembly mit dem SAFE Berechtigungssatz registriert.

USE AdventureWorks2022;

CREATE ASSEMBLY Point
    FROM '\\ShareName\Projects\Point\bin\Point.dll'
    WITH PERMISSION_SET = SAFE;

Die folgende Transact-SQL-Anweisung registriert die Assembly mithilfe <assembly_bits> Arguments in der FROM-Klausel. Dieser varbinary-Wert stellt die Datei als Bytestrom dar.

USE AdventureWorks2022;
CREATE ASSEMBLY Point
FROM 0xfeac4 ... 21ac78

Verwenden des Typs "Erstellen"

Nachdem die Assembly in die Datenbank geladen wurde, können Sie den Typ mithilfe 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 das UDT bereits in der Datenbank vorhanden ist, schlägt die CREATE TYPE-Anweisung mit einem Fehler fehl.

Hinweis

Die CREATE TYPE syntax wird auch zum Erstellen nativer SQL Server-Alias-Datentypen verwendet und soll sp_addtype als Mittel zum Erstellen von Aliasdatentypen ersetzen. Einige der optionalen Argumente in der CREATE TYPE-Syntax beziehen sich auf das Erstellen von UDTs und gelten nicht für das Erstellen von Alias-Datentypen (z. B. Basistyp).

Weitere Informationen finden Sie unter CREATE TYPE.

Beispiel

Mit der folgenden Transact-SQL-Anweisung wird der Point Typ erstellt. Die EXTERNAL NAME wird mithilfe der zweiteiligen Benennungssyntax von <assembly_name>.<udt_name>angegeben.

CREATE TYPE dbo.Point
EXTERNAL NAME Point.[Point];

Entfernen eines UDT aus der Datenbank

Die DROP TYPE-Anweisung entfernt ein UDT aus der aktuellen Datenbank. Nachdem ein UDT abgelegt wurde, können Sie die DROP ASSEMBLY-Anweisung verwenden, um die Assembly aus der Datenbank abzulegen.

Die DROP TYPE-Anweisung wird in den folgenden Situationen nicht ausgeführt:

  • Tabellen in der Datenbank, die mit dem UDT definierte Spalten enthalten.

  • Funktionen, gespeicherte Prozeduren oder Trigger, die Variablen oder Parameter des UDT verwenden, die in der Datenbank mit der WITH SCHEMABINDING-Klausel erstellt wurden.

Beispiel

Die folgende Transact-SQL-Anweisung muss in der folgenden Reihenfolge ausgeführt werden. Zuerst muss die Tabelle, die auf die Point UDT verweist, gelöscht werden, dann den Typ und schließlich die Assembly.

DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;

Suchen von UDT-Abhängigkeiten

Wenn abhängige Objekte vorhanden sind, z. B. Tabellen mit UDT-Spaltendefinitionen, schlägt die DROP TYPE-Anweisung fehl. Es schlägt auch fehl, wenn Funktionen, gespeicherte Prozeduren oder Trigger in der Datenbank mithilfe der WITH SCHEMABINDING-Klausel erstellt wurden, wenn diese Routinen Variablen oder Parameter des benutzerdefinierten Typs verwenden. Sie müssen zuerst alle abhängigen Objekte ablegen und dann die DROP TYPE-Anweisung ausführen.

Die folgende Transact-SQL Abfrage sucht alle Spalten und Parameter, die ein UDT in der AdventureWorks2022-Datenbank verwenden.

USE AdventureWorks2022;

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
     INNER JOIN sys.objects AS o
         ON o.object_id = c.object_id
     INNER JOIN sys.assembly_types AS at
         ON at.user_type_id = c.user_type_id;

Verwalten von UDTs

Sie können ein UDT nicht ändern, nachdem es in einer SQL Server-Datenbank erstellt wurde, obwohl Sie die Assembly ändern können, auf der der Typ basiert. In den meisten Fällen müssen Sie das UDT aus der Datenbank mit der Transact-SQL DROP TYPE-Anweisung entfernen, Änderungen an der zugrunde liegenden Assembly vornehmen und mithilfe 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 verwendet, nachdem Sie Änderungen am Quellcode in Ihrer UDT-Assembly vorgenommen und 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.

Die folgende Transact-SQL ALTER ASSEMBLY-Anweisung lädt die Point.dll Assembly vom angegebenen Speicherort auf dem Datenträger neu.

ALTER ASSEMBLY Point
    FROM '\\Projects\Point\bin\Point.dll';

Verwenden der Änderungsassembly zum Hinzufügen von Quellcode

Die ADD FILE-Klausel in der ALTER ASSEMBLY-Syntax ist in CREATE ASSEMBLYnicht 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.

Die folgende Transact-SQL ALTER ASSEMBLY-Anweisung fügt den Point.cs Klassenquellcode für das Point UDT hinzu. Dadurch wird der in der datei Point.cs enthaltene Text kopiert und in der Datenbank unter dem Namen PointSourcegespeichert.

ALTER ASSEMBLY Point
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;

Assemblyinformationen werden in der sys.assembly_files Tabelle in der Datenbank gespeichert, in der die Assembly installiert wurde. Die sys.assembly_files Tabelle enthält die folgenden Spalten.

Spalte Beschreibung
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 Zahl, die jedes Objekt identifiziert, wobei das erste Objekt einem bestimmten assembly_id zugeordnet ist, der dem Wert 1zugewiesen wird. Wenn mehrere Objekte mit demselben assembly_idverknüpft sind, werden alle nachfolgenden file_id Wertschritte um 1erhöht.
content Die Hexadezimaldarstellung der Assembly oder Datei.

Sie können die funktion CAST oder CONVERT verwenden, um den Inhalt der content Spalte in lesbaren Text zu konvertieren. Die folgende Abfrage konvertiert den Inhalt der Point.cs Datei in lesbaren Text, wobei der Name in der WHERE-Klausel verwendet wird, um das Resultset auf eine einzelne Zeile zu beschränken.

SELECT CAST (content AS VARCHAR (8000))
FROM sys.assembly_files
WHERE name = 'PointSource';

Wenn Sie die Ergebnisse kopieren und in einen Text-Editor einfügen, sehen Sie, dass die Zeilenumbrüche und Leerzeichen, die im Original vorhanden sind, beibehalten werden.

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 aktualisieren, ohne dass sich dies auf Daten auswirkt, die in einer UDT-Spalte in einer Tabelle gespeichert werden können. Sie können UDT-Assemblys ändern, ohne UDT-Spalten und andere abhängige Objekte nur zu löschen, wenn die neue Definition die ehemaligen Werte lesen kann und die Signatur des Typs sich nicht ändert.

Das Trennen von prozeduralen Code, der sich möglicherweise von dem Code ändert, der zum Implementieren des UDT erforderlich ist, vereinfacht die Wartung erheblich. Wenn Sie nur Code einschließen, der für die Funktion von UDT erforderlich ist und Ihre UDT-Definitionen so einfach wie möglich beibehalten werden, verringert sich das Risiko, dass die UDT selbst möglicherweise aus der Datenbank für Coderevisionen oder Fehlerkorrekturen gelöscht werden muss.

Die Währungs-UDT- und Währungsumrechnungsfunktion

Die Currency UDT in der AdventureWorks2022 Beispieldatenbank bietet ein nützliches Beispiel für die empfohlene Methode zum Strukturieren eines UDT und der zugehörigen Funktionen. Die Currency UDT wird für die Abwicklung von Geld basierend auf dem Währungssystem einer bestimmten Kultur verwendet und ermöglicht die Speicherung verschiedener Währungstypen, wie Dollar, Euro usw. Die UDT-Klasse macht einen Kulturnamen als Zeichenfolge und einen Geldbetrag als Dezimaldatentyp verfügbar. Alle notwendigen Serialisierungsmethoden sind in der Assembly enthalten, die die Klasse definiert. Die Funktion, die die Währungsumrechnung von einer Kultur in eine andere implementiert, wird als externe Funktion namens ConvertCurrencyimplementiert, und diese Funktion befindet sich in einer separaten Assembly. Die ConvertCurrency-Funktion führt ihre Arbeit durch Abrufen der Konvertierungsrate aus einer Tabelle in der AdventureWorks2022-Datenbank aus. Wenn sich die Quelle der Konvertierungsraten jemals ändern sollte oder andere Änderungen am vorhandenen Code vorgenommen werden sollen, kann die Assembly problemlos geändert werden, ohne dass sich dies auf die Currency UDT auswirkt.

Die Codeauflistung für die funktionen Currency UDT und ConvertCurrency finden Sie, indem Sie die Beispiele für common language runtime (CLR) installieren.

Verwenden von UDTs in Datenbanken

UDTs sind definitionsgemäß auf eine einzelne Datenbank beschränkt. Daher kann ein in einer Datenbank definiertes UDT nicht in einer Spaltendefinition in einer anderen Datenbank verwendet werden. Um UDTs in mehreren Datenbanken verwenden zu können, 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 Konvertierungen nicht explizit mithilfe der funktionen Transact-SQL CAST oder CONVERT ausführen.

Sie müssen keine Aktion für die Verwendung von UDTs ergreifen, wenn das SQL Server-Datenbankmodul Arbeitstabellen in der tempdb Systemdatenbank erstellt. Dazu gehören die Behandlung von Cursorn, Tabellenvariablen und benutzerdefinierten Tabellenwertfunktionen, die UDTs enthalten und die tempdbtransparent nutzen. Wenn Sie jedoch explizit eine temporäre Tabelle in tempdb erstellen, die eine UDT-Spalte definiert, muss die UDT in tempdb auf die gleiche Weise registriert werden wie für eine Benutzerdatenbank.

Verwandte Inhalte