CREATE TYPE (Transact-SQL)

Article
12/11/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance

Crée un type de données alias ou un type défini par l'utilisateur dans la base de données active SQL Server ou Azure SQL Database. L’implémentation d’un type de données alias est basée sur un type de système natif Moteur de base de données. Un type défini par l’utilisateur est implémenté à travers une classe d’un assembly dans le CLR Microsoft .NET Framework. Pour lier un type défini par l’utilisateur à son implémentation, l’assembly CLR qui contient l’implémentation du type doit d’abord être inscrit dans le Moteur de base de données à l’aide de CREATE ASSE Mo LY.

La possibilité d'exécuter un code CLR est désactivée par défaut dans SQL Server. Vous pouvez créer, modifier et supprimer des objets de base de données qui référencent des modules de code managé. Toutefois, ces références ne s’exécutent pas dans SQL Server, sauf si l’option clr activée est activée à l’aide de sp_configure.

Remarque

L’intégration du CLR .NET Framework à SQL Server est décrite dans cette rubrique. L’intégration clR ne s’applique pas à Azure SQL Database.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe de type de données définie par l’utilisateur :

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 ] )

Syntaxe des types de tables à mémoire optimisée définie par l’utilisateur :

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 } ]
}

Remarque

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.

Arguments

schema_name

Nom du schéma auquel appartient le type de données alias ou le type défini par l’utilisateur.

TYPE_NAME

Nom du type de données alias ou du type défini par l’utilisateur. Les noms de type doivent respecter les règles applicables aux identificateurs.

base_type

Le Moteur de base de données type de données fourni sur lequel le type de données alias est basé. base_type est de type sysname, sans valeur par défaut, et peut avoir l’une de ces valeurs :

bigint, int, smallint et tinyint
binary(n), varbinary(n), et varbinary(max)
bit
char(n), nchar(n), nvarchar(n), nvarchar(max), varchar(n)et varchar(max)
date, datetime, datetime2, datetimeoffset, smalldatetime et heure
decimal et numeric
float et real
image
money et smallmoney
sql_variant
text et ntext
uniqueidentifier

base_type peut également être n’importe quel synonyme de type de données correspondant à l’un de ces types de données système.

precision

Pour la valeur décimale ou numérique, la précision est un entier non négatif qui indique le nombre total maximal de chiffres décimaux pouvant être stockés, à gauche et à droite de la virgule décimale. Pour plus d’informations, consultez decimal et numeric (Transact-SQL).

scale

Pour la décimale ou numérique, l’échelle est un entier non négatif qui indique le nombre maximal de chiffres décimaux pouvant être stockés à droite de la virgule décimale, et il doit être inférieur ou égal à la précision. Pour plus d’informations, consultez decimal et numeric (Transact-SQL).

NULL | NOT NULL

Précise si le type accepte les valeurs NULL. S’il n’est pas spécifié, NULL est la valeur par défaut.

assembly_name

S’applique à : SQL Server

Spécifie l’assembly SQL Server qui référence l’implémentation du type défini par l’utilisateur dans le CLR. assembly_name doit correspondre à un assembly existant dans SQL Server dans la base de données actuelle.

Remarque

EXTERNAL_NAME n’est pas disponible dans une base de données autonome.

[ . class_name ]

S’applique à : SQL Server

Spécifie la classe au sein de l'assembly qui implémente le type défini par l'utilisateur. class_name doit être un identificateur valide et doit exister en tant que classe dans l’assembly avec une visibilité d’assembly. class_name respecte la casse, quel que soit le classement de base de données, et doit correspondre exactement au nom de la classe dans l’assembly correspondant. Le nom de la classe peut être un nom qualifié par un espace de noms entouré de crochets ( [ ] ) si le langage de programmation utilisé pour écrire la classe utilise le concept des espaces de noms (par exemple C#). Si class_name n’est pas spécifié, SQL Server suppose qu’il est identique à type_name.

<column_definition>

Définit les colonnes pour un type de table défini par l'utilisateur.

<type de données>

Définit le type de données dans une colonne pour un type de table défini par l'utilisateur. Pour plus d’informations sur les types de données, consultez Types de données (Transact-SQL). Pour plus d’informations sur les tables, consultez CREATE TABLE (Transact-SQL).

<column_constraint>

Définit les contraintes de colonnes pour un type de table défini par l'utilisateur. Les contraintes prises en charge incluent PRIMARY KEY, UNIQUEet CHECK. Pour plus d’informations sur les tables, consultez CREATE TABLE (Transact-SQL).

<computed_column_definition>

Définit une expression de colonne calculée en tant que colonne dans un type de table défini par l'utilisateur. Pour plus d’informations sur les tables, consultez CREATE TABLE (Transact-SQL).

<table_constraint>

Définit une contrainte de table sur un type de table défini par l'utilisateur. Les contraintes prises en charge incluent PRIMARY KEY, UNIQUEet CHECK.

<index_option>

Spécifie la réponse aux erreurs de valeurs de clés dupliquées dans une opération d'insertion de plusieurs lignes dans un index cluster unique ou un index non cluster unique. Pour plus d’informations sur les options d’index, consultez CREATE INDEX (Transact-SQL).

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

S’applique à : SQL Server 2014 (12.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance.

Indique qu’il faut créer un index sur la table. Il peut s’agir d’un index cluster ou non-cluster. L’index contient les colonnes répertoriées et trie les données dans l’ordre croissant ou décroissant.

INDEX

Vous devez spécifier des index de colonne et de table dans le cadre de l’instruction CREATE TABLE . CREATE INDEX et DROP INDEX ne sont pas pris en charge pour les tables optimisées en mémoire.

MEMORY_OPTIMIZED

S’applique à : SQL Server 2014 (12.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance. Azure SQL Managed Instance ne prend pas en charge les tables mémoire optimisées dans le niveau Usage général.

Indique si le type de table est optimisé en mémoire. Cette option est désactivée par défaut ; la table (type) n’est pas une table optimisée en mémoire (type). Les types de tables optimisées en mémoire sont des tables utilisateur optimisées en mémoire dont le schéma est rendu persistant sur disque similairement à d'autres tables utilisateur.

BUCKET_COUNT

S’applique à : SQL Server 2014 (12.x) et versions ultérieures, Azure SQL Database, Azure SQL Database et Azure SQL Managed Instance.

Indique le nombre de compartiments qui doivent être créés dans l'index de hachage. La valeur maximale des BUCKET_COUNT index de hachage est de 1 073 741 824. Pour plus d’informations sur le nombre de compartiments, consultez Index sur les tables mémoire optimisées. bucket_count est un argument obligatoire.

HASH

S’applique à : SQL Server 2014 (12.x) et versions ultérieures, Azure SQL Database, Azure SQL Database et Azure SQL Managed Instance.

Indique qu’un HASH index est créé. Les index de hachage sont pris en charge uniquement sur les tables optimisées en mémoire.

Notes

La classe de l’assembly référencé dans assembly_name, ainsi que ses méthodes, doit satisfaire tous les besoins d’implémentation d’un type défini par l’utilisateur dans SQL Server. Pour plus d’informations sur ces exigences, consultez Types CLR définis par l’utilisateur.

Les autres considérations portent sur les points suivants :

La classe peut contenir des méthodes surchargées, mais ces méthodes peuvent être appelées uniquement à partir du code managé, et non à partir de Transact-SQL.
Tous les membres statiques doivent être déclarés en tant que const ou en lecture seule si assembly_name est SAFE ou EXTERNAL_ACCESS.

Au sein d'une base de données, il ne peut y avoir qu'un seul type défini par l'utilisateur enregistré par rapport à tout type spécifié qui a été téléchargé dans SQL Server à partir du CLR. Si un type défini par l’utilisateur est créé sur un type CLR pour lequel un type défini par l’utilisateur existe déjà dans la base de données, CREATE TYPE échoue avec une erreur. Cette restriction est nécessaire pour éviter toute ambiguïté dans la résolution de type SQL si un type CLR peut être mappé à plusieurs types définis par l'utilisateur.

Si une méthode mutator dans le type ne retourne pas void, l’instruction CREATE TYPE ne s’exécute pas.

Pour modifier un type défini par l’utilisateur, vous devez supprimer le type à l’aide d’une DROP TYPE instruction, puis le recréer.

Contrairement aux types définis par l’utilisateur qui sont créés à l’aide sp_addtypede , le rôle de base de données public n’est pas automatiquement autorisé REFERENCES sur les types créés à l’aide CREATE TYPEde . Cette autorisation doit être accordée séparément.

Dans les types de tables définis par l’utilisateur, les types définis par l’utilisateur structurés utilisés dans column_name<data type> font partie de l’étendue du schéma de base de données dans lequel le type de table est défini. Pour accéder aux types structurés définis par l'utilisateur dans une étendue différente dans la base de données, utilisez des noms en deux parties.

Dans les types de tables définis par l’utilisateur, la clé primaire sur les colonnes calculées doit être PERSISTED et NOT NULL.

Types de tables à mémoire optimisée

À partir de SQL Server 2014 (12.x), le traitement des données dans un type de table peut être effectué dans la mémoire principale, et non sur disque. Pour plus d’informations, consultez la vue d’ensemble d’OLTP en mémoire et les scénarios d’utilisation. Pour obtenir des exemples de code montrant comment créer des tables à mémoire optimisée, consultez Création d’une table optimisée en mémoire et d’une procédure stockée compilée en mode natif.

Autorisations

Nécessite CREATE TYPE l’autorisation dans la base de données actuelle et ALTER l’autorisation sur schema_name. Si schema_name n’est pas spécifié, les règles de résolution de noms par défaut pour déterminer le schéma de l’utilisateur actuel s’appliquent. Si assembly_name est spécifié, un utilisateur doit posséder l’assembly ou lui avoir REFERENCES l’autorisation.

Si des colonnes dans l’instruction CREATE TABLE sont définies avec un type de données défini par l’utilisateur, une autorisation REFERENCES est nécessaire sur ce type.

Un utilisateur qui crée une table avec une colonne qui utilise un type défini par l’utilisateur a besoin de l’autorisation REFERENCES sur le type défini par l’utilisateur. Si cette table doit être créée, tempdbl’autorisation REFERENCES doit être accordée explicitement chaque fois que la table est créée, ou ce type de données et REFERENCES cette autorisation doivent être ajoutés à la model base de données. Par exemple :

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

Si c’est le cas, ce type de données et REFERENCES cette autorisation seront disponibles en tempdb permanence. Sinon, le type de données défini par l’utilisateur et les autorisations disparaîtront lors du redémarrage de SQL Server. Pour plus d’informations, consultez CREATE TABLE.

Si vous ne souhaitez pas que chaque nouvelle base de données hérite de la définition et des autorisations de ce type de données défini par l’utilisateur à partir du modèle, vous pouvez utiliser une procédure stockée de démarrage pour créer et attribuer les autorisations appropriées uniquement dans tempdb la base de données. Par exemple :

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

Sinon, au lieu d’utiliser des tables temporaires, envisagez d’utiliser des variables de table lorsque vous devez référencer des types de données définis par l’utilisateur pour les besoins de stockage temporaire. Pour que les variables de table référencent des types de données définis par l’utilisateur, vous n’avez pas besoin d’accorder explicitement des autorisations pour le type de données défini par l’utilisateur.

Exemples

R. Créer un type d’alias basé sur le type de données varchar

L'exemple suivant crée un type d'alias basé sur le type de données varchar fourni par le système.

CREATE TYPE SSN
FROM VARCHAR(11) NOT NULL;

B. Créer un type défini par l’utilisateur

S’applique à : SQL Server

L’exemple suivant crée un type Utf8String qui référence la classe utf8string dans l’assembly utf8string. Avant de créer le type, l'assembly utf8string est enregistré dans la base de données locale. Remplacez la partie binaire de l’instruction CREATE ASSEMBLY par une description valide.

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

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

C. Créer un type de table défini par l’utilisateur

L'exemple suivant crée un type de table défini par l'utilisateur qui possède deux colonnes. Pour plus d’informations sur la création et l’utilisation des paramètres table, consultez Utiliser les paramètres table (moteur de base de données).

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

D. Créer un type de table défini par l’utilisateur avec la clé primaire et l’index

L’exemple suivant crée un type de table défini par l’utilisateur qui a trois colonnes. L’une d’entre elles (Name) est la clé primaire et l’autre (Price) a un index non-cluster. Pour plus d’informations sur la création et l’utilisation des paramètres table, consultez Utiliser les paramètres table (moteur de base de données).

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)

Syntaxe

Arguments

schema_name

TYPE_NAME

base_type

precision

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<type de données>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

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

INDEX

MEMORY_OPTIMIZED

BUCKET_COUNT

HASH

Notes

Types de tables à mémoire optimisée

Autorisations

Exemples

R. Créer un type d’alias basé sur le type de données varchar

B. Créer un type défini par l’utilisateur

C. Créer un type de table défini par l’utilisateur

D. Créer un type de table défini par l’utilisateur avec la clé primaire et l’index

Commentaires

Commentaires

Ressources supplémentaires

CREATE TYPE (Transact-SQL)

Syntaxe

Arguments

schema_name

TYPE_NAME

base_type

precision

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<type de données>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

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

INDEX

MEMORY_OPTIMIZED

BUCKET_COUNT

HASH

Notes

Types de tables à mémoire optimisée

Autorisations

Exemples

R. Créer un type d’alias basé sur le type de données varchar

B. Créer un type défini par l’utilisateur

C. Créer un type de table défini par l’utilisateur

D. Créer un type de table défini par l’utilisateur avec la clé primaire et l’index

Contenu connexe

Commentaires

Commentaires

Ressources supplémentaires