Registrieren benutzerdefinierter Typen in SQL Server

  • Artikel

Gilt für:SQL Server

Um einen benutzerdefinierten Typ (User-Defined Type, UDT) in Microsoft SQL Server verwenden zu können, 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 wurde, 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

Die einfachste Möglichkeit zum Bereitstellen Ihrer UDT ist die Verwendung von Microsoft Visual Studio. Für komplexere Bereitstellungsszenarien und die größte Flexibilität verwenden Sie jedoch Transact-SQL, wie weiter unten in diesem Thema 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 auf den Visual Basic- oder Visual C#- Sprachknoten.

  2. Fügen Sie einen Verweis auf die SQL Server-Datenbank hinzu, die den UDT enthalten soll.

  3. Fügen Sie eine Klasse vom Typ "Benutzerdefinierter Typ " 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 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 mehrere Kulturversionen einer Assembly durch verschiedene Namen, die im instance von SQL Server registriert sind. 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 Assemblyberechtigungen finden Sie unter Entwerfen von Assemblys.

Beispiel

Die folgende Transact-SQL-Anweisung registriert die Point-Assembly in SQL Server in der AdventureWorks-Datenbank, wobei die Berechtigung SAFE festgelegt ist. 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 mit <assembly_bits> Argument in der FROM-Klausel. Dieser varbinary-Wert stellt die Datei als Bytestrom 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 mithilfe der Create TYPE-Anweisung von Transact-SQL 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.

Hinweis

Die CREATE TYPE-Syntax wird auch zum Erstellen nativer SQL Server Aliasdatentypen verwendet und soll sp_addtype ersetzen, um 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

Die folgende Transact-SQL-Anweisung erstellt den Point-Typ . Der EXTERNAL NAME wird mithilfe der zweiteiligen Namenssyntax von AssemblyName angegeben. UDTName.

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

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

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.

Die folgende Transact-SQL-Abfrage sucht nach allen Spalten und Parametern, die einen UDT in der AdventureWorks-Datenbank verwenden.

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

Sie können einen UDT nicht ändern, nachdem er 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 den UDT mit der Drop TYPE-Anweisung von Transact-SQL aus der Datenbank entfernen, Änderungen an der zugrunde liegenden Assembly vornehmen und sie mithilfe der ALTER ASSEMBLY-Anweisung erneut 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).

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 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.

Die folgende Alter ASSEMBLY-Anweisung von Transact-SQL fügt den Quellcode der Point.cs-Klasse für point UDT hinzu. 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 sys.assembly_files Tabelle 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 Zahl, die jedes Objekt identifiziert, wobei dem ersten Objekt, das einer bestimmten assembly_id zugeordnet ist, der Wert 1 zugewiesen wird. Wenn demselben assembly_id mehrere Objekte zugeordnet sind, wird jeder nachfolgende file_id Wert um 1 erhöht.

content
Die Hexadezimaldarstellung der Assembly oder Datei.

Sie können die CAST- oder CONVERT-Funktion verwenden, um den Inhalt der Inhaltsspalte in lesbaren Text zu 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

Die Currency UDT in der AdventureWorks-Beispieldatenbank bietet ein nützliches Beispiel für die empfohlene Methode zum Strukturieren eines UDT und der zugehörigen Funktionen. Der Währungs-UDT wird für den Umgang mit Geld verwendet, das auf dem Geldsystem einer bestimmten Kultur basiert, 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 ConvertCurrency implementiert, und diese Funktion befindet sich in einer separaten Assembly. Die ConvertCurrency-Funktion übernimmt ihre Arbeit, indem sie die Konvertierungsrate aus einer Tabelle in der AdventureWorks-Datenbank abruft . Sollte sich die Quelle der Umrechnungskurse jemals ändern oder sollte es andere Änderungen am vorhandenen Code geben, kann die Assembly problemlos geändert werden, ohne dass sich dies auf die Währungs-UDT auswirkt.

Die Codeauflistung für die Funktionen Currency UDT und ConvertCurrency finden Sie, indem Sie die Beispiele der Common Language Runtime (CLR) installieren.

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 Konvertierungen nicht explizit mithilfe der Transact-SQL-Funktionen CAST oder CONVERT durchführen.

Beachten Sie, dass Sie keine Aktion für die Verwendung von UDTs ausführen müssen, wenn SQL Server-Datenbank-Engine Arbeitstabellen in der tempdb-Systemdatenbank erstellt. Dies umfasst die Behandlung von Cursorn, Tabellenvariablen und benutzerdefinierten Tabellenwertfunktionen, die UDTs enthalten und die tempdb transparent verwenden. 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.

Weitere Informationen

Benutzerdefinierte CLR-Typen