Crée une nouvelle table.
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.
.gif) 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.
|
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.
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.
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() ) ;