CREATE TABLE (Transact-SQL)

Mis à jour : 12 décembre 2006

Crée une nouvelle table.

Conventions de la syntaxe de Transact-SQL

Syntaxe

CREATE TABLE 
    [ database_name . [ schema_name ] . | schema_name . ] table_name 
        ( { <column_definition> | <computed_column_definition> }
        [ <table_constraint> ] [ ,...n ] ) 
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup 
        | "default" } ] 
    [ { TEXTIMAGE_ON { filegroup | "default" } ] 
[ ; ]

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

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

<column_constraint> ::= 
[ CONSTRAINT constraint_name ] 
{     { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        [ 
            WITH FILLFACTOR = fillfactor  
          | WITH ( < index_option > [ , ...n ] ) 

        ]
        [ ON { partition_scheme_name ( partition_column_name ) 
            | filegroup | "default" } ]
  | [ FOREIGN KEY ] 
        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
  | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
} 

<computed_column_definition> ::=
column_name AS computed_column_expression 
[ PERSISTED [ NOT NULL ] ]
[ 
    [ CONSTRAINT constraint_name ]
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [ 
            WITH FILLFACTOR = fillfactor 
          | WITH ( <index_option> [ , ...n ] )
        ]
        [ ON { partition_scheme_name ( partition_column_name ) 
            | filegroup | "default" } ]
    | [ FOREIGN KEY ] 
        REFERENCES referenced_table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE } ] 
        [ ON UPDATE { NO ACTION } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
] 

< table_constraint > ::=
[ CONSTRAINT constraint_name ] 
{ 
    { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 

                (column [ ASC | DESC ] [ ,...n ] ) 
        [ 
            WITH FILLFACTOR = fillfactor 
           |WITH ( <index_option> [ , ...n ] ) 
        ]
        [ ON { partition_scheme_name (partition_column_name)
            | filegroup | "default" } ] 
    | FOREIGN KEY 
                ( column [ ,...n ] ) 
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
} 

<index_option> ::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF } 
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF} 
  | ALLOW_PAGE_LOCKS ={ ON | OFF} 
}

Arguments

• database_name
  Nom de la base de données dans laquelle la table est créée. L'argument database_name doit correspondre au nom d'une base de données existante. Si aucun nom n'est spécifié, la valeur par défaut de database_name est la base de données active. Le nom d'accès de la connexion courante doit être associé à un ID utilisateur existant dans la base de données spécifiée par database_name, et cet ID utilisateur doit disposer des autorisations CREATE TABLE.

• schema_name
  Nom du schéma auquel appartient la nouvelle table.

• table_name
  Nom de la nouvelle table. Les noms de tables doivent se conformer aux règles en vigueur pour les identificateurs. table_name peut comprendre un maximum de 128 caractères, à l'exception des noms de tables temporaires locales (noms précédés du signe #) qui ne peuvent dépasser 116 caractères.

• column_name
  Nom d'une colonne de la table. Les noms de colonnes doivent respecter les règles gouvernant les identificateurs et doivent être uniques dans la table. column_name peut comporter entre 1 et 128 caractères. column_name peut être omis pour les colonnes créées avec un type de données timestamp. Si column_name n'est pas spécifié, le nom par défaut d'une colonne timestamp est timestamp.

• computed_column_expression
  Expression définissant la valeur d'une colonne calculée. Une colonne calculée est une colonne virtuelle qui n'est pas stockée physiquement dans la table, à moins que la colonne ne soit indiquée comme PERSISTED. La colonne est calculée à partir d'une expression qui utilise d'autres colonnes dans la même table. Par exemple, une colonne calculée peut avoir la définition suivante : cost AS price * qty. L'expression peut être un nom de colonne non calculée, une constante, une fonction, une variable et toute combinaison de ces éléments reliés par un ou plusieurs opérateurs. L'expression ne peut pas être une sous-requête ou contenir des types de données d'alias.

  Les colonnes calculées peuvent être utilisées dans des listes de sélection, des clauses WHERE, des clauses ORDER BY ou à tout autre emplacement où il est possible d'utiliser des expressions, aux exceptions suivantes près :

  • Une colonne calculée ne peut pas être utilisée en tant que définition de contrainte DEFAULT ou FOREIGN KEY ou avec une définition de contrainte NOT NULL. Toutefois, elle peut être utilisée en tant que colonne clé dans un index ou que composante d'une contrainte PRIMARY KEY ou UNIQUE quelconque, si sa valeur est définie par une expression déterministe et que le type de données du résultat est autorisé dans les colonnes d'index.
    Par exemple, si la table possède les colonnes de type entier a et b, la colonne calculée a+b peut être indexée, contrairement à la colonne calculée a+DATEPART(dd, GETDATE()) dont la valeur est susceptible d'évoluer au fil des appels.
  • Une colonne calculée ne peut pas être la cible d'une instruction INSERT ou UPDATE.

  Remarque :
  Chaque ligne dans une table peut avoir des valeurs différentes pour les colonnes impliquées dans la colonne calculée ; par conséquent, il est possible que la colonne calculée n'ait pas la même valeur pour chaque ligne.

  En fonction des expressions utilisées, la possibilité de valeurs NULL dans les colonnes calculées est déterminée automatiquement par le moteur de base de données SQL Server 2005. Le résultat de la plupart des expressions est considéré comme pouvant avoir la valeur NULL, même si seules des colonnes n'acceptant pas cette valeur sont présentes, car des dépassements négatifs ou positifs possibles produisent également des résultats NULL. Utilisez la fonction COLUMNPROPERTY avec la propriété AllowsNull pour examiner la possibilité de valeur NULL pour chaque colonne calculée dans une table. Une expression pouvant prendre la valeur NULL peut être transformée en expression ne pouvant pas prendre cette valeur, en spécifiant ISNULL avec la constante check_expression, où la constante est une valeur non nulle substituée à n'importe quel résultat NULL. L'autorisation REFERENCES sur le type est nécessaire pour les colonnes calculées basées sur des expressions de type CLR (Common Language Runtime) défini par l'utilisateur.

• PERSISTED
  Spécifie que moteur de base de données SQL Server stockera physiquement les valeurs calculées dans la table et mettra à jour les valeurs lorsque n'importe quelle colonne dont dépend la colonne calculée est mise à jour. Notamment, la colonne calculée en tant que PERSISTED vous permet de créer un index sur une colonne calculée qui est déterministe, mais pas précise. Pour plus d'informations, consultez Création d'index sur des colonnes calculées. Toutes les colonnes calculées utilisées en tant que colonnes de partitionnement d'une table partitionnée doivent être marquées explicitement comme PERSISTED. computed_column_expression doit être déterministe lorsque PERSISTED est spécifié.

• ON { <partition_scheme> | filegroup | "default" }
  Spécifie le groupe de fichiers ou schéma de partition dans lequel la table est stockée. Si <partition_scheme> est spécifié, la table sera partitionnée avec des partitions stockées dans un ensemble d'un ou plusieurs groupes de fichiers spécifié dans <partition_scheme>. Si vous spécifiez filegroup, la table est stockée dans le groupe de fichiers nommé. Le groupe de fichiers doit exister dans la base de données. Si "default" est spécifié, ou si ON n'est pas spécifié du tout, la table est stockée dans le groupe de fichiers par défaut. Le mécanisme de stockage d'une table tel que spécifié dans CREATE TABLE ne peut plus être modifié ultérieurement.

  ON {<partition_scheme> | filegroup | "default"} peut également être spécifié dans une contrainte PRIMARY KEY ou UNIQUE. Ces contraintes créent des index. Si vous spécifiez filegroup, l'index est stocké dans le groupe de fichiers nommé. Si "default" est spécifié, ou si ON n'est pas spécifié du tout, l'index est stocké dans le même groupe de fichiers que la table. Si la contrainte PRIMARY KEY ou UNIQUE crée un index cluster, les pages de données de la table sont stockées dans le même groupe de fichiers que l'index. Si CLUSTERED est spécifié ou la contrainte crée un index cluster d'une autre manière et si une valeur <partition_scheme> est spécifiée qui diffère des valeurs <partition_scheme> ou filegroup de la définition de table, ou vice-versa, seule la définition de la contrainte sera honorée et l'autre sera ignorée.

  Remarque :
  L'élément "default" n'est pas un mot clé dans ce contexte. C'est un identifiant du groupe de fichiers par défaut et il doit être délimité, comme dans ON "default" ou ON [default]. Si "default" est spécifié, l'option QUOTED_IDENTIFIER doit être ON pour la session actuelle. Il s'agit du paramètre par défaut. Pour plus d'informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

• TEXTIMAGE_ON { filegroup| "default" }
  Mots clés indiquant que les colonnes text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) et de type CLR (Common Language Runtime) défini par l'utilisateur sont stockées dans le groupe de fichiers spécifié.

  TEXTIMAGE_ON n'est pas autorisé s'il n'y a pas de colonne de valeur élevée dans cette table. TEXTIMAGE_ON ne peut pas être spécifié si <partition_scheme> est spécifié. Si "default" est spécifié, ou si TEXTIMAGE_ON n'est pas spécifié du tout, les colonnes de valeur élevée sont stockées dans le groupe de fichiers par défaut. Le stockage de données de colonnes de valeur élevée tel que spécifié dans CREATE TABLE ne peut plus être modifié ultérieurement.

  Remarque :

  L'élément "default" n'est pas un mot clé dans ce contexte. Il représente l'identificateur du groupe de fichiers par défaut et doit être délimité, par exemple de la manière suivante : TEXTIMAGE_ON "default" ou TEXTIMAGE_ON [default]. Si "default" est spécifié, l'option QUOTED_IDENTIFIER doit être ON pour la session actuelle. Il s'agit du paramètre par défaut. Pour plus d'informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

• [ type_schema_name**.** ] type_name
  Précise le type de données de la colonne et le schéma auquel il appartient. Le type de données peut être un des suivants :

  • Type de données système SQL Server 2005.
  • Type d'alias basé sur un type de données système SQL Server. Les types de données alias sont créés par l'instruction CREATE TYPE avant de pouvoir être utilisés dans une définition de table. L'affectation NULL ou NOT NULL pour un type de données d'alias peut être ignorée au cours de l'exécution de l'instruction CREATE TABLE. Cependant, une spécification de longueur ne peut pas être modifiée, la longueur d'un type de données d'alias ne peut pas être spécifiée dans une instruction CREATE TABLE.
  • Type CLR défini par l'utilisateur. Les types de données CLR définis par l'utilisateur sont créés avec l'instruction CREATE TYPE avant de pouvoir être utilisés dans une définition de table. Pour créer une colonne sur un type de données CLR défini par l'utilisateur, une autorisation REFERENCES est nécessaire pour le type.

  Si type_schema_name n'est pas précisé, le moteur de base de données SQL Server pointe sur type_name dans l'ordre suivant :

  • Le type de données système SQL Server
  • Schéma par défaut de l'utilisateur actuel dans la base de données active.
  • Schéma dbo dans la base de données active.

• precision
  Précision du type de données spécifié. Pour plus d'informations sur les valeurs de précision valides, consultez Précision, échelle et longueur.

• scale
  Échelle du type de données spécifié. Pour plus d'informations sur les valeurs d'échelle valides, consultez Précision, échelle et longueur.

• max
  S'applique uniquement aux types de données varchar, nvarchar et varbinary pour le stockage de 2^31 octets de données de caractères et binaires et 2^30 octets de données Unicode.

• CONTENT
  Spécifie que chaque instance du type de données xml dans column_name peut contenir plusieurs éléments de niveau supérieur. CONTENT ne s'applique qu'au type de données xml et ne peut être spécifié que si xml_schema_collection est également spécifié. En l'absence de toute spécification, CONTENT est le comportement par défaut.

• DOCUMENT
  Spécifie que chaque instance du type de données xml dans column_name ne peut contenir qu'un seul élément de niveau supérieur. DOCUMENT ne s'applique qu'au type de données xml et ne peut être spécifié que si xml_schema_collection est également spécifié.

• xml_schema_collection
  S'applique uniquement au type de données xml pour l'association d'une collection de schémas XML au type. Avant d'inclure une colonne xml dans un schéma, vous devez d'abord créer celui-ci dans la base de données à l'aide de CREATE XML SCHEMA COLLECTION.

• DEFAULT
  Spécifie la valeur fournie pour la colonne lorsque vous n'avez pas spécifié explicitement de valeur lors d'une insertion. Les définitions DEFAULT peuvent être appliquées à n'importe quelle colonne exceptée celles définies en tant que timestamp ou celles dotées de la propriété IDENTITY. Si une valeur par défaut est indiquée pour une colonne de type personnalisé, ce dernier doit prendre en charge une conversion implicite de constant_expression au type de données défini par l'utilisateur. Les définitions de valeurs par défaut sont supprimées lorsque la table est supprimée. Seule une valeur constante, telle qu'une chaîne de caractères, une fonction scalaire (fonction système, définie par l'utilisateur ou CLR) ou la valeur NULL, peut être utilisée comme valeur par défaut. Pour maintenir la compatibilité avec les versions antérieures de SQL Server, un nom de contrainte peut être affecté à une définition DEFAULT.

• constant_expression
  Constante, valeur NULL ou fonction système utilisée comme valeur par défaut pour une colonne.

• IDENTITY
  Indique que la nouvelle colonne est une colonne d'identité. Lorsqu'une ligne est ajoutée à la table, le moteur de base de données affecte une valeur incrémentée unique à la colonne. Les colonnes d'identité sont normalement utilisées avec les contraintes PRIMARY KEY comme identificateur unique de ligne pour la table. La propriété IDENTITY peut être affectée aux colonnes tinyint, smallint, int, bigint, decimal(p,0) ou numeric(p,0). Une seule colonne d'identité peut être créée par table. Il n'est pas possible d'utiliser des valeurs par défaut liées et des contraintes DEFAULT avec une colonne d'identité. Vous devez spécifier à la fois la valeur de départ et l'incrément ou aucune de ces valeurs. Si vous n'en spécifiez aucune, la valeur par défaut est (1,1).

• seed
  Valeur utilisée pour la toute première ligne chargée dans la table.

• increment
  Valeur d'incrément ajoutée à la valeur d'identité de la ligne précédemment chargée.

• NOT FOR REPLICATION
  Dans l'instruction CREATE TABLE, la clause NOT FOR REPLICATION peut être spécifiée pour l'identité IDENTITY, les contraintes FOREIGN KEY et CHECK. Si la clause est spécifiée pour la propriété IDENTITY, les valeurs ne sont pas incrémentées dans les colonnes identité lorsque les agents de réplication effectuent des insertions. Si cette clause est spécifiée pour une contrainte, la contrainte n'est pas appliquée lorsque les agents de réplication effectuent des opérations d'insertion, de mise à jour ou de suppression. Pour plus d'informations, consultez Contrôle des contraintes, des identités et des déclencheurs avec l'option NOT FOR REPLICATION.

• ROWGUIDCOL
  Indique que la nouvelle colonne est une colonne d'identité ROWGUID. Une seule colonne uniqueidentifier par table peut servir de colonne ROWGUIDCOL. L'application de la propriété ROWGUIDCOL permet à la colonne d'être référencée à l'aide de $ROWGUID. La propriété ROWGUIDCOL ne peut être affectée qu'à une colonne uniqueidentifier. Le mot clé ROWGUIDCOL n'est pas valide si le niveau de compatibilité de la base de données est 65 ou inférieur. Pour plus d'informations, consultez sp_dbcmptlevel (Transact-SQL). Les colonnes avec un type de données défini par l'utilisateur ne peuvent pas être conçues avec ROWGUIDCOL.

  La propriété ROWGUIDCOL n'assure pas l'unicité des valeurs stockées dans la colonne. ROWGUIDCOL ne peut pas non plus générer automatiquement des valeurs pour les nouvelles lignes insérées dans la table. Pour générer des valeurs uniques pour chaque colonne, vous pouvez soit utiliser la fonction NEWID ou NEWSEQUENTIALID sur des instructions INSERT, soit utiliser ces fonctions comme fonctions par défaut pour la colonne.

• COLLATE collation_name
  Indique le classement de la colonne. Le nom du classement peut être un nom de classement Windows ou un nom de classement SQL. collation_name n'est applicable qu'aux colonnes avec le type de données char, varchar, text, nchar, nvarchar et ntext. Si cette valeur n'est pas spécifiée, la colonne reçoit le classement du type de données utilisateur, si son type de données est un type de données utilisateur, ou le classement par défaut de la base de données.

  Pour plus d'informations sur les noms de classement Windows et SQL, consultez Nom de classement Windows et Nom de classement SQL.

  Pour plus d'informations sur la clause COLLATE, consultez COLLATE (Transact-SQL).

• CONSTRAINT
  Mot clé facultatif qui indique le début de la définition d'une contrainte PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY ou CHECK. Pour plus d'informations, consultez Contraintes.

• constraint_name
  Nom de la contrainte. Les noms de contrainte doivent être uniques au sein du schéma auquel appartient la table.

• NULL et NOT NULL
  Détermine si les valeurs NULL sont autorisées dans la colonne. NULL n'est pas strictement une contrainte, mais peut être spécifié de la même manière que NOT NULL. NOT NULL peut être spécifié pour les colonnes calculées uniquement si PERSISTED est également spécifié.

• PRIMARY KEY
  Contrainte assurant l'intégrité d'entité d'une ou plusieurs colonnes spécifiées au moyen d'un seul index. Une seule contrainte PRIMARY KEY peut être créée par table.

• UNIQUE
  Contrainte assurant l'intégrité d'entité d'une ou plusieurs colonnes spécifiées au moyen d'un seul index. Une table peut comprendre plusieurs contraintes UNIQUE.

• CLUSTERED et NONCLUSTERED
  Indique la création d'un index cluster ou non cluster pour la contrainte PRIMARY KEY ou UNIQUE. Les contraintes PRIMARY KEY ont la valeur par défaut CLUSTERED et les contraintes UNIQUE la valeur par défaut NONCLUSTERED.

  CLUSTERED peut être spécifié pour une seule contrainte dans une instruction CREATE TABLE. Si CLUSTERED est spécifié pour une contrainte UNIQUE et une contrainte PRIMARY KEY est également spécifiée, la contrainte PRIMARY KEY a la valeur par défaut NONCLUSTERED.

• FOREIGN KEY REFERENCES
  Contrainte qui assure l'intégrité référentielle des données des colonnes. Avec les contraintes FOREIGN KEY, il faut que chaque valeur de la colonne existe dans la ou les colonnes référencées correspondantes de la table référencée. Les contraintes FOREIGN KEY ne peuvent référencer que les colonnes qui sont des contraintes PRIMARY KEY ou UNIQUE dans la table référencée ou les colonnes référencées dans un index UNIQUE INDEX de la table référencée. Les clés étrangères sur les colonnes calculées doivent également être marquées comme PERSISTED.

• [ schema_name**.****] referenced_table_name]
  Nom de la table référencée par la contrainte FOREIGN KEY, et le schéma à laquelle elle appartient.

• **(**ref_column [ ,... n ] )
  Colonne, ou liste de colonnes, provenant de la table référencée par la contrainte FOREIGN KEY.

• ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
  Spécifie l'action qui se produit dans les lignes de la table créée, si ces lignes comportent une relation référentielle et que la ligne référencée est supprimée de la table parente. La valeur par défaut est NO ACTION.

  • NO ACTION
    Le moteur de base de données déclenche une erreur et la suppression de la ligne dans la table parente est restaurée.
  • CASCADE
    Les lignes correspondantes sont supprimées de la table de référence si cette ligne est supprimée de la table parente.
  • SET NULL
    Toutes les valeurs qui composent la clé étrangère sont NULL si la ligne correspondante dans la table parente est supprimée. Cette contrainte ne peut être exécutée que si les colonnes de la clé étrangère acceptent les valeurs NULL.
  • SET DEFAULT
    Toutes les valeurs qui composent la clé étrangère sont celles par défaut si la ligne correspondante dans la table parente est supprimée. Cette contrainte ne peut être exécutée que si toutes les colonnes de la clé étrangère possèdent des définitions par défaut. Si une colonne peut avoir une valeur NULL et qu'il n'existe aucun ensemble de valeurs par défaut explicite, NULL devient la valeur par défaut implicite de la colonne.

  Ne spécifiez pas CASCADE si la table est incluse dans une publication de fusion qui utilise des enregistrements logiques. Pour plus d'informations sur les enregistrements logiques, consultez Regroupements des modifications apportées à des lignes connexes à l'aide d'enregistrements logiques.

  ON DELETE CASCADE ne peut pas être défini si un déclencheur ON DELETE de INSTEAD OF existe déjà pour la table.

  Par exemple, dans la base de données AdventureWorks, la table ProductVendor possède une relation référentielle avec la table Vendor. La clé étrangère ProductVendor.VendorID référence la clé primaire Vendor.VendorID.

  Si une instruction DELETE est exécutée sur une ligne dans la table Vendor et qu'une action ON DELETE CASCADE est spécifiée pour ProductVendor.VendorID, le moteur de base de données recherche l'existence éventuelle de lignes dépendantes dans la table ProductVendor. Le cas échéant, les lignes dépendantes détectées dans la table ProductVendor sont supprimées, ainsi que la ligne référencée dans la table Vendor.

  Par contre, si la valeur NO ACTION est spécifiée, le moteur de base de données déclenche une erreur et annule la suppression de la ligne dans la table Vendor si au moins une ligne y fait référence dans la table ProductVendor.

• ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
  Spécifie l'action qui se produit dans les lignes de la table modifiée lorsque ces lignes comportent une relation référentielle et que la ligne référencée est mise à jour dans la table parente. La valeur par défaut est NO ACTION.

  • NO ACTION
    Le moteur de base de données déclenche une erreur et la mise à jour de la ligne dans la table parente est restaurée.
  • CASCADE
    Les lignes correspondantes sont mises à jour dans la table de référence si cette ligne est mise à jour dans la table parente.
  • SET NULL
    Toutes les valeurs qui composent la clé étrangère sont NULL si la ligne correspondante dans la table parente est mise à jour. Pour que cette contrainte soit exécutée, les colonnes de clé étrangère doivent accepter les valeurs NULL.
  • SET DEFAULT
    Toutes les valeurs qui composent la clé étrangère sont celles par défaut si la ligne correspondante dans la table parente est mise à jour. Cette contrainte ne peut être exécutée que si toutes les colonnes de la clé étrangère possèdent des définitions par défaut. Si une colonne peut avoir une valeur NULL et qu'il n'existe aucun ensemble de valeurs par défaut explicite, NULL devient la valeur par défaut implicite de la colonne.

  Ne spécifiez pas CASCADE si la table est incluse dans une publication de fusion qui utilise des enregistrements logiques. Pour plus d'informations sur les enregistrements logiques, consultez Regroupements des modifications apportées à des lignes connexes à l'aide d'enregistrements logiques.

  ON UPDATE CASCADE ne peut pas être défini si un déclencheur ON UPDATE de INSTEAD OF existe déjà pour la table qui est modifiée.

  Par exemple, dans la base de données AdventureWorks, la table ProductVendor possède une relation référentielle avec la table Vendor : La clé étrangère ProductVendor.VendorID fait référence à la clé primaire Vendor.VendorID.

  Si une instruction UPDATE est exécutée sur une ligne dans la table Vendor, et qu'une action ON UPDATE CASCADE est spécifiée pour ProductVendor.VendorID, le moteur de base de données vérifie la présence d'une ou plusieurs lignes dépendantes dans la table ProductVendor. Le cas échéant, les lignes dépendantes détectées dans la table ProductVendor sont mises à jour, ainsi que la ligne référencée dans la table Vendor.

  Par contre, si la valeur NO ACTION est spécifiée, le moteur de base de données déclenche une erreur et annule la mise à jour de la ligne dans la table Vendor si au moins une ligne y fait référence dans la table ProductVendor.

• CHECK
  Contrainte qui assure l'intégrité de domaine en limitant les valeurs qui peuvent être entrées dans une ou plusieurs colonnes. Les contraintes CHECK sur les colonnes calculées doivent également être marquées comme PERSISTED.

• logical_expression
  Expression logique qui renvoie TRUE ou FALSE. Les types de données alias ne peuvent pas faire partie de l'expression.

• column
  Colonne, ou liste de colonnes, entre parenthèses, utilisée dans des contraintes de table pour indiquer les colonnes utilisées dans la définition de la contrainte.

• [ ASC | DESC ]
  Indique l'ordre de tri de la ou des colonnes impliquées dans les contraintes de table. La valeur par défaut est ASC.

• partition_scheme_name
  Nom du schéma de partition qui définit les groupes de fichiers vers lesquels les partitions d'une table partitionnée seront mappées. Le schéma de partition doit exister dans la base de données.

• [ partition_column_name**.** ]
  Désigne la colonne selon laquelle une table partitionnée sera partitionnée. La colonne doit correspondre à celle spécifiée dans la fonction de partition que partition_scheme_name utilise en termes de type de données, de longueur et de précision. Une colonne calculée qui participe à une fonction de partition doit être explicitement marquée comme PERSISTED.

  Important :

  Nous vous conseillons de spécifier NOT NULL sur la colonne de partitionnement des tables partitionnées et également des tables non partitionnées qui sont sources ou cibles des opérations ALTER TABLE...SWITCH. Ainsi, vous êtes certain que les contraintes CHECK sur les colonnes de partitionnement ne doivent pas rechercher la présence de valeurs nulles. Pour plus d'informations, consultez Transfert efficace de données à l'aide du commutateur de partitionnement.

• WITH FILLFACTOR **=**fillfactor
  Spécifie le remplissage par le moteur de base de données des pages d'index utilisées pour stocker les données d'index. Les valeurs personnalisées du paramètre fillfactor peuvent être comprises entre 1 et 100. Si vous ne spécifiez pas de valeur, la valeur par défaut est 0. Les valeurs 0 et 100 de l'option fillfactor sont identiques en tous points.

  Important :
  Dans la documentation, l'indication que WITH FILLFACTOR = fillfactor constitue l'unique option d'indexation s'appliquant aux contraintes PRIMARY KEY ou UNIQUE est maintenue dans un but de compatibilité avec les versions précédentes, mais elle ne sera plus indiquée ainsi dans les versions à venir.

• <index_option> ::=
  Spécifie une ou plusieurs options d'index. Pour une description complète de ces options, consultez CREATE INDEX (Transact-SQL).

• PAD_INDEX = { ON | OFF }
  Lorsque ON est spécifié, le pourcentage d'espace disponible spécifié par FILLFACTOR est appliqué aux pages de niveau intermédiaire de l'index. Lorsque OFF ou une valeur FILLFACTOR n'est pas spécifié, les pages de niveau intermédiaire de l'index sont presque entièrement remplies, ce qui laisse un espace libre suffisant pour prendre en charge au moins une ligne de la taille maximale permise par l'index, en prenant en compte l'ensemble de clés sur les pages intermédiaires. La valeur par défaut est OFF.

• FILLFACTOR **=**fillfactor
  Spécifie un pourcentage qui indique le taux de remplissage de chaque niveau de feuille de chaque page d'index par le moteur de base de données pendant la création ou l'altération de l'index. fillfactor doit être une valeur entière de 1 à 100, la valeur par défaut étant 0. Les valeurs 0 et 100 de l'option fillfactor sont identiques en tous points.

• IGNORE_DUP_KEY = { ON | OFF }
  Spécifie la réponse d'erreur aux valeurs de clé dupliquées dans une transaction INSERT de plusieurs lignes sur un index unique cluster ou non cluster. Lorsque ON et une ligne violent l'index unique, un message d'avertissement est émis et seules les lignes violant l'index UNIQUE échouent. Lorsque OFF et une ligne violent l'index unique, un message d'erreur est émis et toute la transaction INSERT est annulée. Lors du traitement d'une instruction UPDATE, l'option IGNORE_DUP_KEY n'a aucun effet. La valeur par défaut est OFF.

• STATISTICS_NORECOMPUTE = { ON | OFF }
  Lorsque la valeur spécifiée est ON, les statistiques d'index périmées ne sont pas recalculées automatiquement. Lorsque la valeur spécifiée est OFF, la mise à jour automatique des statistiques est activée. La valeur par défaut est OFF.

• ALLOW_ROW_LOCKS = { ON | OFF }
  Si la valeur est ON, les verrous de ligne sont autorisés lorsque vous accédez à l'index. Le moteur de base de données détermine le moment où les verrous de ligne sont utilisés. Si la valeur est OFF, les verrous de ligne ne sont pas utilisés. La valeur par défaut est ON.

• ALLOW_PAGE_LOCKS = { ON | OFF }
  Si la valeur est ON, les verrous de page sont autorisés lorsque vous accédez à l'index. Le moteur de base de données détermine le moment où les verrous de page sont utilisés. Si la valeur est OFF, les verrous de page ne sont pas utilisés. La valeur par défaut est ON.

Notes

SQL Server 2005 peut comprendre jusqu'à deux milliards de tables par base de données et 1 024 colonnes par table. Le nombre de lignes et la taille totale de la table sont limités par l'espace de stockage disponible. Le nombre maximal d'octets par ligne est de 8 060. Cette restriction est assouplie pour des tables avec des colonnes de type varchar, nvarchar, varbinary ou sql_variant qui font que la largeur totale définie de la table dépasse 8 060 octets. La longueur de chacune de ces colonnes ne doit cependant pas dépasser les 8 000 octets, mais leurs largeurs cumulées peuvent dépasser la limite de 8 060 octets dans une table. Pour plus d'informations, consultez Données de dépassement de ligne de plus de 8 Ko.

Chaque table peut contenir jusqu'à 249 index non-clusters et un index cluster. Ces chiffres comprennent les index générés pour prendre en charge toutes les contraintes PRIMARY KEY et UNIQUE définies pour la table.

L'espace est généralement alloué aux tables et aux index par incréments d'une valeur d'extension à la fois. Lors de la création d'une table ou d'un index, les pages sont allouées à partir de valeurs d'extension mixtes jusqu'à ce qu'il y ait suffisamment de pages pour remplir une extension uniforme. Quand il y a assez de pages pour remplir une extension uniforme, une autre extension est allouée chaque fois que l'extension en cours est pleine. Pour obtenir des informations sur la quantité d'espace allouée et utilisée par une table, exécutez sp_spaceused.

Le moteur de base de données ne garantit pas l'application de l'ordre dans lequel des contraintes DEFAULT, IDENTITY, ROWGUIDCOL, ou des contraintes de colonne, sont spécifiées dans une définition de colonne.

Lors de la création d'une table, l'option QUOTED IDENTIFIER est toujours stockée avec la valeur ON dans les métadonnées de la table, même si elle a la valeur OFF au moment de sa création.

Tables temporaires

Vous pouvez créer des tables temporaires locales et globales. Les tables temporaires locales ne peuvent être vues que dans la session en cours ; les tables temporaires globales sont accessibles dans toutes les sessions. Les tables temporaires ne peuvent pas être partitionnées.

Faites précéder les noms de tables temporaires locales d'un signe dièse (#table_name), et les noms de tables temporaires globales de deux signes dièse (##table_name).

Les instructions SQL font référence à une table temporaire à l'aide de la valeur spécifiée pour table_name dans l'instruction CREATE TABLE, par exemple :

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY);
INSERT INTO #MyTempTable VALUES (1);

Si plusieurs tables temporaires sont créées dans un lot ou une seule procédure stockée, elles doivent porter des noms différents.

Si vous créez une table temporaire locale dans une procédure stockée ou dans une application qui peut être exécutée en même temps par plusieurs utilisateurs, le moteur de base de données doit être capable de distinguer les tables créées par les différents utilisateurs. Cela est effectué en interne par le moteur de base de données en ajoutant de manière interne un suffixe numérique à chaque nom de table temporaire locale. Le nom complet d'une table temporaire, tel qu'il est stocké dans la table sysobjects de tempdb, est constitué du nom de table spécifié dans l'instruction CREATE TABLE plus le suffixe numérique généré par le système. Pour laisser assez de place au suffixe, le table_name spécifié pour un nom de table temporaire locale ne doit pas dépasser 116 caractères.

Les tables temporaires sont automatiquement supprimées lorsqu'elles passent hors de portée, sauf si elles sont supprimées explicitement à l'aide de DROP TABLE :

• Une table temporaire locale créée dans une procédure stockée est supprimée automatiquement lorsque la procédure stockée est terminée. La table peut être référencée par des procédures stockées imbriquées exécutées par la procédure stockée qui a créé la table. La table ne peut pas être référencée par le processus qui a appelé la procédure stockée ayant créé la table.
• Toutes les autres tables temporaires locales sont supprimées automatiquement à la fin de la session en cours.
• Les tables temporaires globales sont supprimées automatiquement lorsque la session qui a créé la table se termine, et que toutes les autres tâches n'y font plus référence. L'association entre une tâche et une table n'est assurée que pendant la durée d'une seule instruction Transact-SQL. Cela signifie qu'une table temporaire globale est supprimée à la fin de la dernière instruction Transact-SQL qui faisait activement référence à la table lorsque la session de création s'est terminée.

Une table temporaire locale créée au sein d'une procédure stockée ou d'un déclencheur peut avoir le même nom qu'une table temporaire créée avant l'appel de la procédure stockée ou du déclencheur. Cependant, si une requête fait référence à une table temporaire et que deux tables temporaires portent ce nom, la table par rapport à laquelle la requête est résolue n'est pas définie. Les procédures stockées imbriquées peuvent également créer des tables temporaires portant le même nom qu'une table temporaire créée par la procédure stockée qui l'a appelée. Cependant, pour que les modifications résolvent la table créée par la procédure imbriquée, la table doit avoir la même structure, avec les mêmes noms de colonnes, que la table créée dans la procédure d'appel. C'est ce qu'illustre l'exemple suivant.

CREATE PROCEDURE dbo.Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (2);
SELECT Test2Col = x FROM #t;
GO
CREATE PROCEDURE dbo.Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (1);
SELECT Test1Col = x FROM #t;
EXEC Test2;
GO
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (99);
GO
EXEC Test1;
GO

Voici l'ensemble des résultats.

(1 row(s) affected)

Test1Col    
----------- 
1           

(1 row(s) affected)

Test2Col    
----------- 
2           

Lorsque vous créez des tables temporaires locales ou globales, la syntaxe CREATE TABLE prend en charge les définitions de contraintes à l'exception des contraintes FOREIGN KEY. Si vous spécifiez une contrainte FOREIGN KEY dans une table temporaire, l'instruction renvoie un message d'avertissement précisant que la contrainte a été ignorée. La table est toujours créée mais sans les contraintes FOREIGN KEY. Les tables temporaires ne peuvent pas être référencées dans des contraintes FOREIGN KEY.

Nombre de tables temporaires/variables de table en cours d'utilisation. Les tables temporaires sont utiles lorsque les index doivent être créés explicitement d'après elles, ou lorsque des valeurs de table doivent être visibles à travers plusieurs procédures stockées ou fonctions. Généralement, les variables de table contribuent à un traitement des requêtes plus efficace. Pour plus d'informations, consultez table (Transact-SQL).

Tables partitionnées

Avant de créer une table partitionnée à l'aide de CREATE TABLE, vous devez d'abord créer une fonction de partition pour spécifier la manière dont la table est partitionnée. Une fonction de partition est créée à l'aide de CREATE PARTITION FUNCTION (Transact-SQL). Ensuite, vous devez créer un schéma de partition pour spécifier les groupes de fichiers qui contiendront les partitions indiquées par la fonction de partition. Un schéma de partition est créé à l'aide de CREATE PARTITION SCHEME (Transact-SQL). Le placement des contraintes PRIMARY KEY ou UNIQUE pour séparer les groupes de fichiers ne peut pas être spécifié pour les tables partitionnées. Pour plus d'informations, consultez Tables et index partitionnés.

Contraintes PRIMARY KEY

• Une table ne peut contenir qu'une seule contrainte PRIMARY KEY.
• L'index généré par une contrainte PRIMARY KEY ne peut avoir pour conséquence une augmentation du nombre d'index dans la table à plus de 249 index non-clusters et un index cluster.
• Si vous ne spécifiez pas CLUSTERED ou NONCLUSTERED pour une contrainte PRIMARY KEY, CLUSTERED est utilisé s'il n'y a pas d'index clusters spécifiés pour les contraintes UNIQUE.
• Toutes les colonnes définies dans une contrainte PRIMARY KEY doivent avoir la valeur NOT NULL. Si vous ne spécifiez pas la possibilité ou non de valeurs NULL, toutes les colonnes participant à une contrainte PRIMARY KEY sont définies à NOT NULL.
• Si une clé primaire est définie sur une colonne avec le type de données CLR défini par l'utilisateur, l'implémentation du type doit prendre en charge le tri binaire. Pour plus d'informations, consultez CLR User-Defined Types.

Contraintes UNIQUE

• Si vous ne spécifiez pas CLUSTERED ou NONCLUSTERED pour une contrainte UNIQUE, NONCLUSTERED est utilisé par défaut.
• Chaque contrainte UNIQUE génère un index. Le nombre de contraintes UNIQUE ne peut avoir pour conséquence une augmentation du nombre d'index dans la table à plus de 249 index non-clusters et un index cluster.
• Si une contrainte unique est définie sur une colonne avec le type de données CLR défini par l'utilisateur, l'implémentation du type doit prendre en charge le tri binaire ou basé sur l'opérateur. Pour plus d'informations, consultez CLR User-Defined Types.

Contraintes FOREIGN KEY

• Lorsqu'une valeur différente de NULL est entrée dans la colonne d'une contrainte FOREIGN KEY, la valeur doit exister dans la colonne référencée. Dans le cas contraire, le système renvoie un message d'erreur signalant une violation de clé étrangère.
• Les contraintes FOREIGN KEY sont appliquées à la colonne précédente, à moins que des colonnes sources ne soient spécifiées.
• Les contraintes FOREIGN KEY ne peuvent faire référence qu'à des tables au sein de la même base de données sur le même serveur. L'intégrité référentielle inter-base de données doit être implémentée via les déclencheurs. Pour plus d'informations, consultez CREATE TRIGGER (Transact-SQL).
• Les contraintes FOREIGN KEY peuvent faire référence à une autre colonne dans la même table. On appelle habituellement ce mécanisme « auto-référence ».
• La clause REFERENCES d'une contrainte FOREIGN KEY au niveau des colonnes, ne peut lister qu'une colonne de référence. Cette colonne doit avoir le même type de données que la colonne pour laquelle la contrainte est définie.
• La clause REFERENCES d'une contrainte FOREIGN KEY de niveau table doit avoir le même nombre de colonnes de référence que le nombre de colonnes de la liste des colonnes de la contrainte. Le type de données de chaque colonne de référence doit également être identique à la colonne de référence correspondante dans la liste des colonnes.
• La valeur CASCADE, SET NULL ou SET DEFAULT ne peut pas être spécifiée si une colonne de type timestamp fait partie de la clé étrangère ou de la clé référencée.
• Il est possible de combiner CASCADE, SET NULL, SET DEFAULT et NO ACTION pour des tables liées par des relations référentielles. Si le moteur de base de données rencontre NO ACTION, il s'interrompt et annule les actions CASCADE, SET NULL et SET DEFAULT. Lorsqu'une instruction DELETE génère une combinaison d'actions CASCADE, SET NULL, SET DEFAULT et NO ACTION, les actions CASCADE, SET NULL et SET DEFAULT sont appliquées par le moteur de base de données avant toute recherche de NO ACTION.
• Le moteur de base de données n'a pas de limite prédéfinie du nombre de contraintes FOREIGN KEY qu'une table peut contenir et qui référencent d'autres tables ou du nombre de contraintes FOREIGN KEY possédées par d'autres tables qui font référence à une table spécifique.
  Cependant, le nombre réel de contraintes FOREIGN KEY qui peuvent être utilisées est limité par la configuration matérielle et par la conception de la base de données et de l'application. Nous vous recommandons de ne pas insérer plus de 253 contraintes FOREIGN KEY dans une table et qu'une même table ne soit pas référencée par plus de 253 contraintes FOREIGN KEY. La limite effective pour vous peut varier en fonction de l'application et du matériel. Prenez en compte le coût d'application des contraintes FOREIGN KEY avant de concevoir vos bases de données et applications.
• Les contraintes FOREIGN KEY ne sont pas appliquées dans les tables temporaires.
• Les contraintes FOREIGN KEY ne peuvent référencer que les colonnes dans des contraintes PRIMARY KEY ou UNIQUE dans la table référencée ou dans un index UNIQUE INDEX de la table référencée.
• Si une clé étrangère est définie sur une colonne avec le type de données CLR défini par l'utilisateur, l'implémentation du type doit prendre en charge le tri binaire. Pour plus d'informations, consultez CLR User-Defined Types.
• Une colonne de type varchar(max) ne peut participer à une contrainte FOREIGN KEY que si la clé primaire qu'elle référence est également définie comme étant de type varchar(max).

Définitions DEFAULT

• Une colonne ne peut avoir qu'une seule définition DEFAULT (valeur par défaut).

• Une définition DEFAULT peut contenir des valeurs constantes, des fonctions, des fonctions niladic SQL-92 ou des valeurs NULL. Le tableau suivant montre les fonctions niladiques et les valeurs qu'elles renvoient pour la valeur par défaut, lors d'une instruction INSERT.

  Fonction niladique SQL-92	Valeur renvoyée
  CURRENT_TIMESTAMP	Date et heure actuelles.
  CURRENT_USER	Nom de l'utilisateur effectuant une insertion.
  SESSION_USER	Nom de l'utilisateur effectuant une insertion.
  SYSTEM_USER	Nom de l'utilisateur effectuant une insertion.
  USER	Nom de l'utilisateur effectuant une insertion.

• constant_expression dans une définition DEFAULT ne peut faire référence à une autre colonne de la table, ou à d'autres tables, vues ou procédures stockées.

• Les définitions DEFAULT ne peuvent être créées dans des colonnes ayant un type de données timestamp ou une propriété IDENTITY.

• Les définitions DEFAULT ne peuvent pas être créées pour des colonnes qui possèdent des types de données d'alias, si ces types de données sont liés à un objet par défaut.

Contraintes CHECK

• Une colonne peut posséder un nombre illimité de contraintes CHECK et la condition peut inclure plusieurs expressions logiques combinées par AND et OR. S'il existe plusieurs contraintes CHECK pour une même colonne, elles sont validées dans l'ordre de leur création.
• La condition de recherche doit correspondre à une expression booléenne et ne peut pas faire référence à une autre table.
• Une contrainte CHECK de niveau colonne ne peut faire référence qu'à la colonne contenant la contrainte, et une contrainte CHECK de niveau table ne peut faire référence qu'aux colonnes d'une même table.
  Les contraintes CHECK et les règles servent toutes les deux à valider les données lors des instructions INSERT et UPDATE.
• Quand il existe une règle et une ou plusieurs contraintes CHECK pour une colonne, toutes les restrictions sont évaluées.
• Les contraintes CHECK ne peuvent pas être définies sur les colonnes text, ntext ou image.

Informations supplémentaires sur les contraintes

• Un index créé pour une contrainte ne peut pas être supprimé en utilisant DROP INDEX ; la contrainte doit être supprimée à l'aide de ALTER TABLE. Un index créé pour une contrainte et utilisé par elle peut être recréé en utilisant DBCC DBREINDEX.
• Les noms de contrainte doivent suivre les règles des identificateurs, excepté le fait que le nom ne peut pas commencer par un signe dièse (#). En l'absence de constraint_name, un nom généré par le système est affecté à la contrainte. Le nom de la contrainte apparaît dans tous les messages d'erreur relatifs aux violations de contraintes.
• Lorsqu'une contrainte est violée dans une instruction INSERT, UPDATE ou DELETE, l'instruction est terminée. Cependant, lorsque SET XACT_ABORT a la valeur OFF, la transaction, si l'instruction fait partie d'une transaction explicite, continue à être traitée. Lorsque SET XACT_ABORT a pour valeur ON, toute la transaction est annulée. Vous pouvez utiliser l'instruction ROLLBACK TRANSACTION avec la définition de la transaction en vérifiant la fonction système **@@**ERROR.
• Si ALLOW_ROW_LOCKS = ON et ALLOW_PAGE_LOCK = ON, les verrous de ligne, de page et de table sont autorisés lorsque vous accédez à l'index. Le moteur de base de données choisit le verrou approprié et peut convertir un verrou de ligne ou de page en verrou de table. Pour plus d'informations, consultez Promotion de verrous (moteur de base de données). Si ALLOW_ROW_LOCKS = OFF et ALLOW_PAGE_LOCK = OFF, seuls les verrous de table sont autorisés lorsque vous accédez à l'index. Pour plus d'informations sur la définition de la granularité des verrous d'un index, consultez Personnalisation du verrouillage pour un index.
• Si une table contient des contraintes FOREIGN KEY ou CHECK, et des déclencheurs, les conditions de la contrainte sont évaluées avant l'exécution du déclencheur.

Pour obtenir des informations sur une table et ses colonnes, utilisez sp_help ou sp_helpconstraint. Pour renommer une table, utilisez sp_rename. Pour obtenir des informations sur les vues et les procédures stockées qui dépendent d'une table, utilisez sp_depends.

Règles des possibilités de valeurs Null dans une définition de table

La possibilité de valeurs Null pour une colonne détermine si cette colonne peut accepter une valeur nulle (NULL) comme données dans la colonne. La valeur NULL n'est pas équivalente à la valeur zéro ou à un blanc : cela signifie qu'il n'y a pas eu d'entrée dans la colonne ou que la valeur NULL explicite a été spécifiée. Cela implique généralement que la valeur est, soit inconnue, soit non applicable.

Lorsque vous créez ou modifiez une table à l'aide des instructions CREATE TABLE ou ALTER TABLE, les paramètres de la base de données et de la session influencent et éventuellement modifient la possibilité de valeur Null pour le type de données utilisé dans une définition de colonne. Il est recommandé de toujours définir explicitement une colonne comme NULL ou NOT NULL ou, si vous utilisez un type de données défini par l'utilisateur, d'autoriser la colonne à utiliser la possibilité de valeur NULL par défaut pour ce type de données.

Lorsque vous ne l'avez pas spécifiée explicitement, la possibilité de valeurs Null pour les colonnes respecte les règles récapitulées dans le tableau suivant :

Type de données de la colonne	Règle
Type de données d'alias	Le moteur de base de données utilise la possibilité de valeurs Null spécifiée lors de la création du type de données. Utilisez sp_help pour obtenir la possibilité de valeurs Null par défaut du type de données.
Type CLR défini par l'utilisateur	La possibilité de valeur NULL est déterminée en fonction de la définition de la colonne.
Type de données fourni par le système	Si le type de données fourni par le système ne possède qu'une option, il a priorité. Les types de données timestamp doivent être NOT NULL. Si le niveau de compatibilité est de 65 ou inférieur, les types de données bit ont par défaut la valeur NOT NULL si la colonne n'est pas définie explicitement comme NULL ou NOT NULL. Pour plus d'informations, consultez sp_dbcmptlevel (Transact-SQL). Lorsque les paramètres de session ont pour valeur ON en utilisant SET : ANSI_NULL_DFLT_ON = ON, NULL est affecté. ANSI_NULL_DFLT_OFF = ON, NOT NULL est affecté. Lorsque les paramètres de base de données sont configurés en utilisant ALTER DATABASE : ANSI_NULL_DEFAULT_ON = ON, NULL est affecté. ANSI_NULL_DEFAULT_OFF = ON, NOT NULL est affecté. Pour voir le paramètre de la base de données pour ANSI_NULL_DEFAULT, utilisez l'affichage catalogue sys.databases.

Si aucune des options ANSI_NULL_DFLT n'est définie pour la session et que la base de données est définie avec les valeurs par défaut (ANSI_NULL_DEFAULT étant OFF), la valeur NOT NULL par défaut de SQL Server est affectée.

La possibilité de valeurs NULL dans les colonnes calculées est déterminée automatiquement par le moteur de base de données. Pour rechercher si les valeurs NULL sont acceptées ou non par ce type de colonne, utilisez la fonction COLUMNPROPERTY avec la propriété AllowsNull.

Remarque :
Que ce soit pour le pilote ODBC de SQL Server ou pour le fournisseur Microsoft OLE DB de SQL Server, ANSI_NULL_DFLT_ON a par défaut la valeur ON. Les utilisateurs ODBC et OLE DB peuvent réaliser cette configuration dans les sources de données ODBC ou à l'aide d'attributs ou de propriétés de connexion définies par l'application.

Autorisations

Nécessite une autorisation CREATE TABLE dans la base de données et une autorisation ALTER pour le schéma dans lequel la table a été créée.

Si des colonnes dans l'instruction CREATE TABLE sont définies avec le type de données CLR défini par l'utilisateur, la propriété du type ou une autorisation REFERENCES est nécessaire.

Si des colonnes dans l'instruction CREATE TABLE sont associées à une collection de schémas XML, la propriété de la collection de schémas XML ou une autorisation REFERENCES pour le type est nécessaire.

Exemples

A. Utilisation des contraintes PRIMARY KEY

Cet exemple montre la définition de colonne pour une contrainte PRIMARY KEY avec un index cluster sur la colonne EmployeeID de la table Employee (autorisant le système à fournir le nom de contrainte) dans la base de données exemple AdventureWorks.

EmployeeID int
PRIMARY KEY CLUSTERED

B. Utilisation des contraintes FOREIGN KEY

Une contrainte FOREIGN KEY sert à référencer une autre table. Les clés étrangères peuvent être des clés à colonne unique ou sur plusieurs colonnes. Cet exemple montre une contrainte FOREIGN KEY à colonne unique dans la table SalesOrderHeader qui fait référence à la table SalesPerson. Seule la clause REFERENCES est obligatoire pour une contrainte FOREIGN KEY à colonne unique.

SalesPersonID int NULL
REFERENCES SalesPerson(SalesPersonID)

Vous pouvez également utiliser de manière explicite la clause FOREIGN KEY et redéterminer l'attribut de la colonne. Notez que le nom de la colonne ne doit pas nécessairement être le même dans les deux tables.

FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(SalesPersonID)

Les contraintes de clés sur plusieurs colonnes sont créées comme des contraintes de table. Dans la base de données AdventureWorks, la table SpecialOfferProduct inclut une clause PRIMARY KEY sur plusieurs colonnes. L'exemple suivant montre comment faire référence à cette clé à partir d'une autre table ; un nom de contrainte explicite n'est pas obligatoire.

CONSTRAINT FK_SpecialOfferProduct_SalesOrderDetail FOREIGN KEY
 (ProductID, SpecialOfferID)
REFERENCES SpecialOfferProduct (ProductID, SpecialOfferID)

C. Utilisation des contraintes UNIQUE

Les contraintes UNIQUE servent à garantir l'unicité dans les colonnes qui n'ont pas de clés primaires. L'exemple suivant applique la restriction suivant laquelle la colonne Name de la table Product doit être unique.

Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED

D. Utilisation des définitions DEFAULT

Les valeurs par défaut fournissent une valeur (avec les instructions INSERT et UPDATE) lorsqu'aucune valeur n'est fournie. Par exemple, la base de données AdventureWorks peut inclure une table de correspondance répertoriant les différents emplois que les employés peuvent occuper dans la société. Sous une colonne décrivant chaque emploi, une chaîne de caractères par défaut peut fournir une description lorsqu'aucune description réelle n'est entrée explicitement.

DEFAULT 'New Position - title not formalized yet'

Outre des constantes, les définitions de valeurs par défaut peuvent inclure des fonctions. Utilisez l'exemple suivant pour obtenir la date actuelle d'une entrée.

DEFAULT (getdate())

Une fonction niladique peut également améliorer l'intégrité des données. Afin de garder une trace de l'utilisateur qui a inséré une ligne, utilisez la fonction niladique pour USER. N'entourez pas les fonctions niladiques de parenthèses.

DEFAULT USER

E. Utilisation des contraintes CHECK

L'exemple suivant affiche une restriction appliquée aux valeurs entrées dans la colonne CreditRating de la table Vendor. La contrainte n'a pas de nom.

CHECK (CreditRating >= 1 and CreditRating <= 5)

Cet exemple montre une contrainte nommée avec un modèle de restriction pour les données de caractère entrées dans une colonne d'une table.

CONSTRAINT CK_emp_id CHECK (emp_id LIKE 
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' 
OR emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')

Cet exemple spécifie que les valeurs doivent figurer dans une liste spécifique ou suivre un modèle donné.

CHECK (emp_id IN ('1389', '0736', '0877', '1622', '1756')
OR emp_id LIKE '99[0-9][0-9]')

F. Affichage des définitions de tables complètes

Cet exemple montre des définitions complètes de tables avec toutes les définitions de contraintes pour la table PurchaseOrderDetail créée dans la base de données AdventureWorks. Notez que pour exécuter l'exemple, le schéma de table est modifié en dbo.

CREATE TABLE [dbo].[PurchaseOrderDetail]
(
    [PurchaseOrderID] [int] NOT NULL
        REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID),
    [LineNumber] [smallint] NOT NULL,
    [ProductID] [int] NULL 
        REFERENCES Production.Product(ProductID),
    [UnitPrice] [money] NULL,
    [OrderQty] [smallint] NULL,
    [ReceivedQty] [float] NULL,
    [RejectedQty] [float] NULL,
    [DueDate] [datetime] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL
        CONSTRAINT [DF_PurchaseOrderDetail_rowguid] DEFAULT (newid()),
    [ModifiedDate] [datetime] NOT NULL 
        CONSTRAINT [DF_PurchaseOrderDetail_ModifiedDate] DEFAULT (getdate()),
    [LineTotal]  AS (([UnitPrice]*[OrderQty])),
    [StockedQty]  AS (([ReceivedQty]-[RejectedQty])),
CONSTRAINT [PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber]
    PRIMARY KEY CLUSTERED ([PurchaseOrderID], [LineNumber])
    WITH (IGNORE_DUP_KEY = OFF)
) 
ON [PRIMARY];

G. Création d'une table avec une colonne XML de type collection de schémas XML

L'exemple suivant crée une table avec une colonne xml de type collection de schémas XML HRResumeSchemaCollection. Le mot clé DOCUMENT spécifie que chaque instance du type de données xml dans column_name ne peut contenir qu'un seul élément de niveau supérieur.

USE AdventureWorks;
GO
CREATE TABLE HumanResources.EmployeeResumes 
   (LName nvarchar(25), FName nvarchar(25), 
    Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) );

H. Création d'une table partitionnée

L'exemple suivant crée une fonction de partition pour partitionner une table ou un index en quatre partitions. L'exemple crée ensuite un schéma de partition pour spécifier les groupes de fichiers qui contiendront chacune des quatre partitions. Enfin, l'exemple crée une table utilisant le schéma de partition. Cet exemple suppose que les groupes de fichiers existent déjà dans la base de données.

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO
CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO

En fonction des valeurs de la colonne col1 de PartitionTable, les partitions sont affectées en suivant les méthodes ci-dessous.

Groupe de fichiers	test1fg	test2fg	test3fg	test4fg
Partition	1	2	3	4
Valeurs	col 1 <= 1	col1 > 1 AND col1 <= 100	col1 > 100 AND col1 <= 1 000	col1 > 1 000

I. Utilisation du type de données uniqueidentifier dans une colonne

L'exemple suivant crée une table avec une colonne uniqueidentifier. Il utilise une contrainte PRIMARY KEY pour empêcher les utilisateurs de la table d'insérer des doublons, et la fonction NEWSEQUENTIALID() dans la contrainte DEFAULT pour fournir des valeurs aux nouvelles lignes. La propriété ROWGUIDCOL est appliquée à la colonne uniqueidentifier pour que cette dernière puisse être référencée à l'aide du mot clé $ROWGUID.

CREATE TABLE dbo.Globally_Unique_Data
(guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
    Employee_Name varchar(60)
CONSTRAINT Guid_PK PRIMARY KEY (guid) );

J. Utilisation d'une expression pour une colonne calculée

L'exemple suivant illustre l'utilisation d'une expression ((low + high)/2) pour le calcul de la colonne calculée myavg.

CREATE TABLE dbo.mytable 
( low int, high int, myavg AS (low + high)/2 ) ;

K. Création d'une colonne calculée basée sur une colonne de type défini par l'utilisateur

L'exemple suivant crée une table, avec une colonne définie comme utf8string dont le type de données est défini par l'utilisateur, en supposant que l'assembly du type et le type lui-même ont déjà été créés dans la base de données active. Une seconde colonne est définie d'après utf8string et utilise la méthode ToString() de type(class)utf8string pour calculer une valeur pour la colonne.

CREATE TABLE UDTypeTable 
( u utf8string, ustr AS u.ToString() PERSISTED ) ;

L. Utilisation de la fonction USER_NAME pour une colonne calculée

L'exemple suivant utilise la fonction USER_NAME() dans la colonne myuser_name.

CREATE TABLE dbo.mylogintable
( date_in datetime, user_id int, myuser_name AS USER_NAME() ) ;

Voir aussi

Référence

ALTER TABLE (Transact-SQL)
COLUMNPROPERTY (Transact-SQL)
CREATE INDEX (Transact-SQL)
CREATE VIEW (Transact-SQL)
CREATE VIEW (Transact-SQL)
Types de données (Transact-SQL)
DROP INDEX (Transact-SQL)
DROP RULE (Transact-SQL)
DROP TABLE (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
CREATE TYPE (Transact-SQL)
EVENTDATA (Transact-SQL)
sp_depends (Transact-SQL)
sp_help (Transact-SQL)
sp_helpconstraint (Transact-SQL)
sp_rename (Transact-SQL)
sp_spaceused (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version	Historique
12 décembre 2006	Contenu modifié : Clarification de la position de la clause ON dans la section <computed_column_definition> du diagramme de la syntaxe. Nouveau contenu : Ajout d'une remarque concernant l'argument partition_column_name qui recommande de spécifier NOT NULL sur la colonne de partitionnement des tables partitionnées et également des tables non partitionnées qui sont sources ou cibles des opérations ALTER TABLE...SWITCH.
14 avril 2006	Contenu modifié : Mise à jour de l'exemple I pour utiliser la fonction NEWSEQUENTIALID et la propriété ROWGUIDCOL. Clarification du rôle de la propriété ROWGUIDCOL et ajout d'une information sur l'utilisation de NEWSEQUENTIALID en plus de NEWID. Suppression de la restriction documentée relative à l'utilisation de CASCADE avec des partitions précalculées pour la réplication de fusion.