CREATE TYPE (Transact-SQL)

Artikel
12/11/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Erstellt einen Aliasdatentyp oder einen benutzerdefinierten Typ in der aktuellen Datenbank in SQL Server oder Azure SQL-Datenbank. Die Implementierung eines Aliasdatentyps basiert auf einem Datenbank-Engine systemeigenen Systemtyp. Ein benutzerdefinierter Typ wird durch eine Klasse einer Assembly in der Common Language Runtime (CLR) von Microsoft. NET Framework implementiert. Um einen benutzerdefinierten Typ an seine Implementierung zu binden, muss die CLR-Assembly, die die Implementierung des Typs enthält, zuerst in der Datenbank-Engine mithilfe von CREATE ASSEMBLY registriert werden.

Die Option zum Ausführen von CLR-Code ist in SQL Server standardmäßig deaktiviert. Sie können Datenbankobjekte erstellen, ändern und ablegen, die auf verwaltete Codemodule verweisen. Diese Verweise werden jedoch nicht in SQL Server ausgeführt, es sei denn, die Clr-Option ist mithilfe von sp_configure aktiviert.

Hinweis

Die Integration der .NET Framework-CLR in SQL Server wird in diesem Thema erläutert. Die CLR-Integration gilt nicht für Azure SQL-Datenbank.

Transact-SQL-Syntaxkonventionen

Syntax

Benutzerdefinierte Datentypsyntax:

CREATE TYPE [ schema_name. ] type_name
{
      FROM base_type
      [ ( precision [ , scale ] ) ]
      [ NULL | NOT NULL ]
    | EXTERNAL NAME assembly_name [ .class_name ]
    | AS TABLE ( { <column_definition> | <computed_column_definition> [ , ...n ]
      [ <table_constraint> ] [ , ...n ]
      [ <table_index> ] [ , ...n ] } )
} [ ; ]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]
    [
        DEFAULT constant_expression ]
      | [ IDENTITY [ ( seed , increment ) ]
    ]
    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]

<data type> ::=
[ type_schema_name . ] type_name
    [ ( precision [ , scale ] | max |
                [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]

<column_constraint> ::=
{ { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [
            WITH ( <index_option> [ , ...n ] )
        ]
  | CHECK ( logical_expression )
}

<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [
            WITH ( <index_option> [ , ...n ] )
        ]
    | CHECK ( logical_expression )
]

<table_constraint> ::=
{
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
    ( column [ ASC | DESC ] [ , ...n ] )
        [
    WITH ( <index_option> [ , ...n ] )
        ]
    | CHECK ( logical_expression )
}

<index_option> ::=
{
    IGNORE_DUP_KEY = { ON | OFF }
}

< table_index > ::=
  INDEX index_name
     [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ , ...n ] )

Syntax für benutzerdefinierte Speicheroptimierte Tabellentypen:

CREATE TYPE [ schema_name. ] type_name
AS TABLE ( { <column_definition> [ , ...n ] }
    | [ <table_constraint> ] [ , ...n ]
    | [ <table_index> ] [ , ...n ] )
    [ WITH ( <table_option> [ , ...n ] ) ]
 [ ; ]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ] [ NULL | NOT NULL ]
      [ IDENTITY [ (1 , 1) ]
    ]
    [ <column_constraint> [ , ...n ] ] [ <column_index> ]

<data type> ::=
 [ type_schema_name . ] type_name [ ( precision [ , scale ] ) ]

<column_constraint> ::=
{ PRIMARY KEY { NONCLUSTERED HASH WITH ( BUCKET_COUNT = bucket_count )
                | NONCLUSTERED }
}

< table_constraint > ::=
{ PRIMARY KEY { NONCLUSTERED HASH (column [ , ...n ] )
                   WITH ( BUCKET_COUNT = bucket_count )
               | NONCLUSTERED ( column [ ASC | DESC ] [ , ...n ] )
           }
}

<column_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ , ...n ] ) WITH ( BUCKET_COUNT = bucket_count ) 
      | NONCLUSTERED ( column [ ASC | DESC ] [ , ...n ] )
}

< table_index > ::=
  INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ , ...n ] ) WITH ( BUCKET_COUNT = bucket_count )
    | [ NONCLUSTERED ] ( column [ ASC | DESC ] [ , ...n ] )
}

<table_option> ::=
{
    [ MEMORY_OPTIMIZED = { ON | OFF } ]
}

Hinweis

Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 (12.x) und früher finden Sie unter Dokumentation zu früheren Versionen.

Argumente

schema_name

Der Name des Schemas, zu dem der Aliasdatentyp oder der benutzerdefinierte Typ gehört.

type_name

Der Name des Alias-Datentyps oder benutzerdefinierten Typs. Typnamen müssen den Regeln für Bezeichner entsprechen.

base_type

Die Datenbank-Engine bereitgestellten Datentyp, auf dem der Aliasdatentyp basiert. base_type ist vom Datentyp sysname und hat keinen Standardwert. Folgende Werte sind möglich:

bigint, int, smallint, and tinyint
binary(n), varbinary(n), and varbinary(max)
bit
char(n), nchar(n), nvarchar(n), nvarchar(max), varchar(n), and varchar(max)
Date, datetime, datetime2, datetimeoffset, smalldatetime und time
decimal und numeric
float und real
Abbildung
Geld und Kleingeld
sql_variant
text und ntext
uniqueidentifier

base_type kann außerdem jedes Synonym für Datentypen sein, das einem dieser Systemdatentypen zugeordnet wird.

precision

Bei dezimaler oder numerischer Genauigkeit handelt es sich um eine nicht negative ganze Zahl, die die maximale Gesamtanzahl von Dezimalstellen angibt, die gespeichert werden können, sowohl links als auch rechts vom Dezimalkomma. Weitere Informationen finden Sie unter decimal und numeric (Transact-SQL).

scale

Bei dezimalen oder numerischen Zahlen ist die Skalierung eine nicht negative ganze Zahl, die die maximale Anzahl von Dezimalstellen angibt, die rechts vom Dezimalkomma gespeichert werden können, und sie muss kleiner oder gleich der Genauigkeit sein. Weitere Informationen finden Sie unter decimal und numeric (Transact-SQL).

NULL | NOT NULL

Gibt an, ob für den Typ NULL-Werte zulässig sind. Wenn nichts anderes angegeben wird, wird als Standard NULL verwendet.

assembly_name

Gilt für: SQL Server

Gibt die SQL Server-Assembly an, die auf die Implementierung des benutzerdefinierten Typs in der Common Language Runtime (CLR) verweist. assembly_name sollte einer vorhandenen Assembly in SQL Server in der aktuellen Datenbank entsprechen.

Hinweis

EXTERNAL_NAME ist in einer enthaltenen Datenbank nicht verfügbar.

[ . class_name ]

Gilt für: SQL Server

Gibt die Klasse innerhalb der Assembly an, die den benutzerdefinierten Typ implementiert. class_name muss ein gültiger Bezeichner sein und als Klasse mit Assemblysichtbarkeit in der Assembly vorhanden sein. Bei class_name muss unabhängig von der Datenbanksortierung die Groß-/Kleinschreibung beachtet werden, und der Wert muss genau dem Klassennamen in der entsprechenden Assembly entsprechen. Der Klassenname kann ein mit einem Namespace qualifizierter Name sein, der in eckigen Klammern ( [ ] ) steht, wenn die Programmiersprache, die zum Schreiben der Klasse verwendet wird, das Konzept von Namespaces verwendet, wie z.B. C#. Wenn class_name nicht angegeben ist, geht SQL Server davon aus, dass sie mit type_name identisch ist.

<column_definition>

Definiert die Spalten für einen benutzerdefinierten Tabellentyp.

<Datentyp>

Definiert die Datentypen in einer Spalten für einen benutzerdefinierten Tabellentyp. Weitere Informationen zu Datentypen finden Sie unter Datentypen (Transact-SQL). Weitere Informationen zu Tabellen finden Sie unter CREATE TABLE (Transact-SQL).

<column_constraint>

Definiert die Spalteneinschränkungen für einen benutzerdefinierten Tabellentyp. Zu den unterstützten Einschränkungen gehören PRIMARY KEY, UNIQUEund CHECK. Weitere Informationen zu Tabellen finden Sie unter CREATE TABLE (Transact-SQL).

<computed_column_definition>

Definiert einen berechneten Spaltenausdruck in einem benutzerdefinierten Tabellentyp als Spalte. Weitere Informationen zu Tabellen finden Sie unter CREATE TABLE (Transact-SQL).

<table_constraint>

Definiert eine Spalteneinschränkung für einen benutzerdefinierten Tabellentyp. Zu den unterstützten Einschränkungen gehören PRIMARY KEY, UNIQUEund CHECK.

<index_option>

Gibt die Fehlerantwort auf doppelte Schlüsselwerte beim Einfügen mehrerer Zeilen für einen eindeutigen gruppierten oder einen eindeutigen nicht gruppierten Index an. Weitere Informationen zu Indexoptionen finden Sie unter CREATE INDEX (Transact-SQL).

INDEX index_name [ GRUPPIERT | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ , ... n ] )

Gilt für: SQL Server 2014 (12.x) und höhere Versionen, Azure SQL-Datenbank und Azure SQL verwaltete Instanz.

Gibt an, dass ein Index in der Tabelle erstellt werden soll. Dies kann ein gruppierter oder ein nicht gruppierter Index sein. Der Index enthält die aufgelisteten Spalten und sortiert die Daten in aufsteigender oder absteigender Reihenfolge.

INDEX

Sie müssen Spalten- und Tabellenindizes als Teil der CREATE TABLE Anweisung angeben. CREATE INDEX und DROP INDEX werden für speicheroptimierte Tabellen nicht unterstützt.

MEMORY_OPTIMIZED

Gilt für: SQL Server 2014 (12.x) und höhere Versionen, Azure SQL-Datenbank und Azure SQL verwaltete Instanz. Azure SQL verwaltete Instanz unterstützt keine speicheroptimierten Tabellen auf der Ebene "Allgemeiner Zweck".

Gibt an, ob der Tabellentyp speicheroptimiert ist. Diese Option ist standardmäßig deaktiviert; Die Tabelle (Typ) ist keine speicheroptimierte Tabelle (Typ). Speicheroptimierte Tabellentypen sind speicheroptimierte Benutzertabellen, deren Schema auf dem Datenträger ähnlich anderen Benutzertabellen beibehalten wird.

BUCKET_COUNT

Gilt für: SQL Server 2014 (12.x) und höhere Versionen, Azure SQL-Datenbank, Azure SQL-Datenbank und Azure SQL verwaltete Instanz.

Gibt die Anzahl der Buckets an, die im Hashindex erstellt werden sollen. Der Maximalwert für BUCKET_COUNT Hashindizes beträgt 1.073.741.824. Weitere Informationen zu Bucketanzahlen finden Sie unter "Indizes für speicheroptimierte Tabellen". bucket_count ist ein erforderliches Argument.

HASH

Gilt für: SQL Server 2014 (12.x) und höhere Versionen, Azure SQL-Datenbank, Azure SQL-Datenbank und Azure SQL verwaltete Instanz.

Gibt an, dass ein HASH Index erstellt wird. Hashindizes werden nur für speicheroptimierte Tabellen unterstützt.

Bemerkungen

Die Klasse der Assembly, auf die in assembly_name verwiesen wird, und ihre Methoden sollten alle Anforderungen für die Implementierung eines benutzerdefinierten Typs in SQL Server erfüllen. Weitere Informationen zu diesen Anforderungen finden Sie unter CLR User-Defined Types (Benutzerdefinierte CLR-Typen).

Noch einige zusätzliche Überlegungen:

Die Klasse kann überladene Methoden enthalten, aber diese Methoden können nur innerhalb von verwaltetem Code aufgerufen werden, nicht aus Transact-SQL.
Alle statischen Elemente müssen als Const oder readonly deklariert werden, wenn assembly_name ist SAFE oder EXTERNAL_ACCESS.

Innerhalb einer Datenbank kann nur ein benutzerdefinierter Typ für einen angegebenen Typ registriert werden, der von der CLR in SQL Server hochgeladen wurde. Wenn ein benutzerdefinierter Typ auf einem CLR-Typ erstellt wird, für den ein benutzerdefinierter Typ bereits in der Datenbank vorhanden ist, CREATE TYPE tritt ein Fehler auf. Diese Einschränkung ist erforderlich, um eine Mehrdeutigkeit bei der Zuordnung des SQL-Typs zu vermeiden, wenn ein CLR-Typ mehr als einem benutzerdefinierten Typ zugeordnet werden kann.

Wenn eine Mutatormethode im Typ keine Ungültige zurückgibt, wird die CREATE TYPE Anweisung nicht ausgeführt.

Um einen benutzerdefinierten Typ zu ändern, müssen Sie den Typ mithilfe einer DROP TYPE Anweisung ablegen und ihn dann erneut erstellen.

Im Gegensatz zu benutzerdefinierten Typen, die mit der Verwendung sp_addtypeerstellt werden, erhält die Rolle der öffentlichen Datenbank nicht automatisch REFERENCES Berechtigungen für Typen, die mit der Verwendung CREATE TYPEerstellt werden. Diese Berechtigung muss separat erteilt werden.

Bei benutzerdefinierten Tabellentypen gehören strukturierte benutzerdefinierte Typen, die in column_name<data type> verwendet werden, zum Bereich des Datenbankschemas, in dem der Tabellentyp definiert ist. Um auf strukturierte benutzerdefinierte Typen in einem anderen Bereich innerhalb der Datenbank zuzugreifen, verwenden Sie zweiteilige Namen.

In benutzerdefinierten Tabellentypen muss der Primärschlüssel für berechnete Spalten sein PERSISTED und NOT NULL.

Speicheroptimierte Tabellentypen

Ab SQL Server 2014 (12.x) kann die Verarbeitung von Daten in einem Tabellentyp im Primärspeicher und nicht auf einem Datenträger erfolgen. Weitere Informationen finden Sie in den Übersichts- und Verwendungsszenarien von In-Memory OLTP. Codebeispiele, die das Erstellen speicheroptimierter Tabellentypen veranschaulichen, finden Sie unter Erstellen einer speicheroptimierten Tabelle und einer systemintern kompilierten gespeicherten Prozedur.

Berechtigungen

Erfordert CREATE TYPE die Berechtigung in der aktuellen Datenbank und ALTER die Berechtigung für schema_name. Wenn schema_name nicht angegeben ist, gelten die Standardregeln für die Namensauflösung zum Bestimmen des Schemas für den aktuellen Benutzer. Wenn assembly_name angegeben ist, muss ein Benutzer entweder die Assembly besitzen oder REFERENCES über entsprechende Berechtigungen verfügen.

Wenn in der CREATE TABLE-Anweisung eine Spalte als Spalte eines benutzerdefinierten Typs definiert wird, ist die REFERENCES-Berechtigung für den benutzerdefinierten Typ erforderlich.

Ein Benutzer, der eine Tabelle mit einer Spalte erstellt, die einen benutzerdefinierten Typ verwendet, benötigt die REFERENCES Berechtigung für den benutzerdefinierten Typ. Wenn diese Tabelle erstellt tempdbwerden muss, muss entweder REFERENCES die Berechtigung jedes Mal explizit erteilt werden, bevor die Tabelle erstellt wird, oder dieser Datentyp und REFERENCES diese Berechtigung müssen der model Datenbank hinzugefügt werden. Beispiel:

CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;
GO
GRANT REFERENCES ON TYPE::dbo.udt_money TO public

Wenn dies geschieht, ist dieser Datentyp und REFERENCES diese Berechtigung dauerhaft tempdb verfügbar. Andernfalls verschwinden der benutzerdefinierte Datentyp und die Berechtigungen bei einem Neustart von SQL Server. Weitere Informationen finden Sie unter CREATE TABLE.

Wenn sie nicht möchten, dass jede neue Datenbank die Definition und Berechtigungen für diesen benutzerdefinierten Datentyp vom Modell erbt, können Sie eine gespeicherte Startprozedur verwenden, um die entsprechenden Berechtigungen nur in tempdb der Datenbank zu erstellen und zuzuweisen. Beispiel:

USE master
GO
CREATE PROCEDURE setup_udt_in_tempdb
AS
EXEC ( 'USE tempdb;
CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;
GRANT REFERENCES ON TYPE::dbo.udt_money TO public;')
GO
EXEC sp_procoption 'setup_udt_in_tempdb' , 'startup' , 'on'
GO

Alternativ können Sie anstelle temporärer Tabellen tabellenvariablen verwenden, wenn Sie auf benutzerdefinierte Datentypen für temporäre Speicheranforderungen verweisen müssen. Damit Tabellenvariablen auf benutzerdefinierte Datentypen verweisen können, müssen Sie nicht explizit Berechtigungen für den benutzerdefinierten Datentyp erteilen.

Beispiele

.A Erstellen eines Aliastyps basierend auf dem Varchar-Datentyp

Im folgenden Beispiel wird ein Aliastyp erstellt, der auf dem vom System bereitgestellten Datentyp varchar basiert.

CREATE TYPE SSN
FROM VARCHAR(11) NOT NULL;

B. Erstellen eines benutzerdefinierten Typs

Gilt für: SQL Server

Im folgenden Beispiel wird der Typ Utf8String erstellt, der auf die Klasse utf8string in der Assembly utf8string verweist. Vor dem Erstellen des Typs wird die Assembly utf8string in der lokalen Datenbank registriert. Ersetzen Sie den binären Teil der CREATE ASSEMBLY Anweisung durch eine gültige Beschreibung.

CREATE ASSEMBLY utf8string
AUTHORIZATION [dbi]
FROM 0x4D... ;
GO

CREATE TYPE Utf8String
EXTERNAL NAME utf8string.[Microsoft.Samples.SqlServer.utf8string];
GO

C. Erstellen eines benutzerdefinierten Tabellentyps

Das folgende Beispiel zeigt, wie ein benutzerdefinierter Tabellentyp mit zwei Spalten erstellt wird: Weitere Informationen zum Erstellen und Verwenden von Tabellenwertparametern finden Sie unter Verwenden von Tabellenwertparametern (Datenbank-Engine).

CREATE TYPE LocationTableType AS TABLE (
    LocationName VARCHAR(50),
    CostRate INT
);
GO

D: Erstellen eines benutzerdefinierten Tabellentyps mit Primärschlüssel und Index

Im folgenden Beispiel wird ein benutzerdefinierter Tabellentyp mit drei Spalten erstellt, von denen eine (Name) der Primärschlüssel und eine andere (Price) ein nicht gruppierter Index ist. Weitere Informationen zum Erstellen und Verwenden von Tabellenwertparametern finden Sie unter Verwenden von Tabellenwertparametern (Datenbank-Engine).

CREATE TYPE InventoryItem AS TABLE (
    [Name] NVARCHAR(50) NOT NULL,
    SupplierId BIGINT NOT NULL,
    Price DECIMAL(18, 4) NULL,
    PRIMARY KEY (Name),
    INDEX IX_InventoryItem_Price(Price)
);
GO

CREATE TYPE (Transact-SQL)

Syntax

Argumente

schema_name

type_name

base_type

precision

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<Datentyp>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

INDEX index_name [ GRUPPIERT | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ , ... n ] )

INDEX

MEMORY_OPTIMIZED

BUCKET_COUNT

HASH

Bemerkungen

Speicheroptimierte Tabellentypen

Berechtigungen

Beispiele

.A Erstellen eines Aliastyps basierend auf dem Varchar-Datentyp

B. Erstellen eines benutzerdefinierten Typs

C. Erstellen eines benutzerdefinierten Tabellentyps

D: Erstellen eines benutzerdefinierten Tabellentyps mit Primärschlüssel und Index

Feedback

Feedback

Zusätzliche Ressourcen

CREATE TYPE (Transact-SQL)

Syntax

Argumente

schema_name

type_name

base_type

precision

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<Datentyp>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

INDEX index_name [ GRUPPIERT | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ , ... n ] )

INDEX

MEMORY_OPTIMIZED

BUCKET_COUNT

HASH

Bemerkungen

Speicheroptimierte Tabellentypen

Berechtigungen

Beispiele

.A Erstellen eines Aliastyps basierend auf dem Varchar-Datentyp

B. Erstellen eines benutzerdefinierten Typs

C. Erstellen eines benutzerdefinierten Tabellentyps

D: Erstellen eines benutzerdefinierten Tabellentyps mit Primärschlüssel und Index

Zugehöriger Inhalt

Feedback

Feedback

Zusätzliche Ressourcen