Création de Tables

SQL Server utilise la syntaxe CREATE TABLE du standard ANSI SQL. SQL Server Enterprise Manager fournit un outil de conception de table, plus facile à utiliser. De toute façon, la syntaxe SQL est toujours envoyée à SQL Server pour créer la table. Vous pouvez créer la table directement avec un outil comme OSQL, ISQL ou Query Analyzer; depuis SQL Server Enterprise Manager; ou depuis n'importe quel autre outil qui transmet la syntaxe SQL.

Même si vous utilisez ces outils, il faut conserver les commandes DDL (data definition language) dans un script pour pouvoir recréer les tables. Les commandes DDL dans un script sont du coude source et doivent être considéré comme tel. Vous pouvez utilisez un outil de versionning sur ces fichiers comme Microsoft Visual Source Safe.

Simplement, la création d'une table demande un peu plus d'informations que son nom, les colonnes qu'elle contient et le domaine de valeur de chaque colonne. Voici la syntaxe basic pour créer la table client, avec trois colonnes de type caractère à longueur constante (char). (Cette définition de table n'est pas la plus efficace pour stocker les données car elle demande toujours 46 octets par enregistrement plus quelques octets supplémentaires quel que soit la longueur des données.)

 

CREATE TABLE customer ( name char(30), phone char(12), emp_id char(4) )

Voici la même définition; même elle est plus difficile à décoder:

 

CREATE TABLE customer (name char(30), phone char(12), emp_id char(4))

Ceci était un exemple très simple.

Une table est toujours créée dans une base de données et est la propriété d'un utilisateur. Normalement, le propriétaire est celui qui a créé la table, mais n'importe qui possédant le rôle sysadmin ou db_owner peut créer un table dont la propriété est un autre utilisateur. Une base peut contenir plusieurs tables avec le même nom, tant que ces tables ont des propriétaires différents. Le nom complet d'une table est composé de trois parties:

database.owner.tablename

Par exemple, l'utilisateur Bob) a créé une table client dans la base pubs. Cette table se nomme pubs.bob.client. (Si Bob est aussi le propriétaire de la base de données, pubs.dbo.client serait sont nom de table car dbo est le nom d'utilisateur utilisé pour le propriétaire de la base de données dans chaque base de données.)

Les deux premières parties du nom complet de la table ont des valeurs par défaut. La valeur par défaut pour le nom de la base est la base utilisée à cette instant (Voir use database). Le propriétaire de la table a deux valeurs par défaut possibles. Si aucun nom de propriétaire de table n'est spécifié, SQL Server assume que c'est vous ou le propriétaire de la base qui possédez la table. Par exemple, si Bob est propriétaire de la table client et que la base courante est pubs, alors il peut référencer la table en client.

Les noms des colonnes doivent être explicites car elles seront utilisées intensivement. Le nom d'une colonne (ou de n'importe quel objet dans SQL Server, comme une table ou une vue) est laissée à votre propre choix tant qu'il respecte les règles SQL Server sur les identifiants: il doit être constitué de 1 à 128 lettres, chiffres ou symboles #, $, @, or _.

Certains mots clé réservés, comme table, create, select, et update, ont un sens particulier pour SQL Server, et font référence au langage SQL. Tant que possible, vous devez éviter d'utiliser les mots-clé dans vos noms d'objets. Il en va de même pour ceux de SQL Server, du standard SQL-92, etc.

Certains mots-clé du standard SQL-92 ne sont pas encore réservé pour SQL Server. Mais cela pourrait arriver dans le future et vous seriez obliger de modifier vos schémas et vos applications pour passer au nouveau SQL Server.

Vous ne pouvez pas utiliser de mots-clé dans vos noms d'objets à moins d'utiliser un délimiteur. En fait, si vous utilisez un délimiteur, vous pouvez aussi utiliser des espaces et d'autres caractères alphanumériques normalement non autorisés. Il existe deux types de délimiteurs:

 

• Les crochets ([object name]).

 

• Les guillemets simples ("object name").

Vous pouvez utiliser les crochets dans n'importe quel environnements. L'utilisation des guillemets requiert une option spéciale via SET QUOTED_IDENTIFIER ON. Une fois cette option QUOTED_IDENTIFIER activée, les guillemets doubles seront toujours interprétées comme des références à un objet. Pour utiliser des dates ou des chaînes de caractères, vous devez utiliser les guillemets simples. Par exemple, column est un mot-clé. La première commande est illégale dans tous les cas, et la seconde l'est aussi à moins que QUOTED_IDENTIFIER soit activée. Le troisième commande est correcte dans tous les cas.

 

CREATE TABLE customer (name char(30), column char(12), emp_id char(4)) CREATE TABLE customer (name char(30), "column" char(12), emp_id char(4)) CREATE TABLE customer (name char(30), [column] char(12), emp_id char(4))

Par défaut, le pilote ODBC fournit par SQL Server, positionne l'option QUOTED_IDENTIFIER à ON, mais certains outils spécifiques de SQL Server positionnent cette option à OFF. Vous déterminez l'état de cette option via:

 

DBCC useroptions

Si vous utilisez Query Analyzer, choisissez l'option Configure du menu Fichier et examinez l'onglet Connexion.

En théorie, vous pouvez utiliser les délimiteurs et ne jamais avoir à vous préoccuper des mots-clé pour les noms d'objets mais certains outils ne les gèrent pas.

Au lieu d'utiliser les délimiteurs, vous devriez utiliser une convention de nommage simple. Par exemple, les noms de colonnes peuvent être précédés par quelques lettres du nom de la table et un underscore (_).

Beaucoup de personnes adoptent une convention de nommage standard. Par exemple, cli_id représente un numéro de client dans toutes les tables de la base. Cette notation est cohérente par rapport à cli_id, cli_num, cli_numero, et cli_# dans différentes tables.

Une autre notation est la notation hongroise pour les noms de colonnes. Elle est largement utilisée en programmation C, où les noms de variables sont préfixées par leur type. Par exemple, sint_nn_cli_age indique que l'âge d'un client est de type smallint (sur 2 octets) et est NOT NULL (ne permet pas les NULLs). Bien que ce soit une bonne pratique en C, il est recommandé de ne pas la suivre avec SQL Server.

Supposons, par exemple, qu'après la création de la table et des applications, vous découvrez que la colonne clinum requiert 4 octets (int) au lieu de 2 octets (smallint). Vous pouvez facilement recréer la table en changeant le type de données pour cette colonne. (Vous pouvez aussi utiliser la command ALTER TABLE pour modifier le type de données de la table existante.) Les procédures stockées SQL Server gère le type de données différent de manière automatique. Les applications ODBC, OLE DB ou DB-Library qui assigne les colonnes à des types caractères ou entier ne seront pas affectés. Les applications ont besoin de changer car le nouveau type est plus large que l'ancien. Si le type est moins large, la conversion est automatique. Si le nom de colonne est précédé du type de données, les procédures et les objets qui l'utilise doivent être modifiés.)

SQL Server fournit plusieurs types de données, voir Table 6-1. Choisir le type de données approprié consiste à prendre le type dont la rangée de valeur convient pour les données à stocker. Le choix du type consiste aussi à limiter l'espace de stockage utilisé tout en prévoyant suffisamment de place pour données à stocker tout au long de la vie de l'application.

SQL Server accepte un certain nombre de synonymes, voir Table 6-1. , mais il n'utilise qu'un type en interne. Par exemple, vous pouvez définir une colonne en character(1), character, or char(1), et SQL Server considère cette syntaxe comme correcte. Cependant, en interne, l'expression est conservée en char(1), et les requêtes sur les catalogues système pour les types de données donneront char(1), quelle que soit la syntaxe utilisée.

Les types de données sont bien documentés dans SQL Server. Cependant, un type de données est spécial.

Utiliser un identifiant unique global (GUID)—aussi appelé identifiant unique universel (UUID)— est un moyen souvent utilisé pour identifier les données, les objets, les applications et les applets dans les systèmes distribués. Un GUID est une valeur générée de 128 bits (16 octets) qui est sensée être unique dans le monde. SQL Server 7 supporte un type de données appelé uniqueidentifier pour stocker les GUIDs. Le langage The Transact-SQL supporte la fonction système NEWID(), pour générer une valeur uniqueidentifier. Une telle valeur peut être initialisée de deux manières:

 

• En utilisant la fonction NEWID()

 

• En utilisant une chaîne (32 symboles hexadécimaux séparés par des '-'): xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (Chaque x est une valeur hexadécimale comprise entre 0 et 9 ou a et f.)

Table 6-1. Les types de données SQL Server.

Table 6-1. Les types de données SQL Server.

Les seuls opérateurs qui peuvent être utilisés avec un type uniqueidentifier sont les comparaisons (=, <>, <, >, <=, >=) et la vérification du NULL.

Un avantage de ce type de données est quel les valeurs générés sont unique pour chaque machine du réseau, car les six derniers bits d'une valeur uniqueidentifier constituent le noeud de la machine. Sur une machine avec une carte réseau, le noeud est l'identifiant IEEE 802 unique de la carte. Sur une machine sans carte réseau, le noeud est une valeur 48 bits aléatoire à peu près unique.

Un autre avantage du type uniqueidentifier est que ses valeurs ne peuvent pas être reproduite. SQL Server utilise ce type de données en interne pour la réplication dans les mixes d'enregistrements. Une colonne uniqueidentifier peut avoir une propriété spéciale appelée ROWGUIDCOL; au plus, une colonne uniqueidentifier peut avoir cette propriété par table. La propriété ROWGUIDCOL peut être spécifiée dans la définition de la colonne via CREATE TABLE et ALTER TABLE ADD column ou ajoutée ou supprimée pour une colonne existante via ALTER TABLE ALTER COLUMN.

Une colonne uniqueidentifier avec la propriété ROWGUIDCOL peut être référencée dans une requête par le mot-clé ROWGUIDCOL. C'est la même chose pour le référencement d'une colonne identité via IDENTITYCOL.

Il faut décider si la taille d'une variable doit être de longueur fixe ou variable. En général, les types de données à longueur variable sont appropriés pour les colonnes dont les données ne changent pas beaucoup.

Les types de données à longueur variable peuvent libérer de l'espace disque. Les choisir induit parfois une perte des performances et parfois un gain. Un enregistrement avec des colonnes à longueur variable requiert un entrée à maintenir, qui contient la taille des données à stocker. Le calcul et le maintient de cette offset induit un surcoût par rapport au stockage d'un enregistrement à longueur fixe. Cependant, ce surcoût est minime voir nul.

D'un autre côté, les colonnes à longueur variable peuvent améliorer les performances parce qu'elles permettent de stocker plus d'enregistrements sur une page. Mais le gain n'est pas du à l'espace disque non utilisé. Une page de données SQL Server est de 8 KB (8192 octets), donc 8096 octets sont disponibles pour le stockage des données. (Le reste est utilisé en interne pour les informations de structure et les appartenances des objets qu'elle contient.) Une opération I/O opération lit la page entière. Si 80 enregistrements tiennent sur une page, une seule opération est nécessaire. Si vous pouvez stocker 160 enregistrements sur une page, une opération I/O est deux fois plus efficace. Les opérations de scan intensive retournant des enregistrements consécutifs tirent partis d'une telle amélioration en terme d'espace de stockage. Plus il y a d'enregistrements sur une page, plus les accès I/O et le cache sont efficaces.

Par exemple, considérons un simple table client, comme dans les Figure 6-1 et Figure 6-2.

Figure 6-1. Une table client avec des colonnes à longueur fixe.

Figure 6-2. Une table client avec des colonnes à longueur variable.

Les colonnes qui contiennent les adresses, les noms, ou les URLs Internet ont toutes des tailles qui varient. Quelles sont les différences entre choisir des colonnes à longueur fixe ou à longueur variable. Dans la Figure 6-1, en utilisant des colonne à longueur fixe, chaque enregistrement occupe 304 octets de données. SQL Server ajoute 10 octets en plus pour chaque enregistrement de la table donc cela fait 314 octets. En moyenne, les entrées sont seulement de la moitié de la taille maximum.

Dans la Figure 6-2, en utilisant des colonnes à longueur variable (varchar), une entrée moyenne ne fait que la moitié de taille maximum. Au lieu d'utiliser un enregistrement de 304 octets, la moyenne est de 184 octets. Cette longueur est calculée comme suit: les colonnes smallint et char(2) font un total de 4 octets. Les colonnes varchar font maximum 300 octets, donc la moitié fait 150 octets. Un surcoût de 2 octets existe pour chacune des neuf colonnes varchar, donc 18 octets. Ajoutez 2 octets pour chaque enregistrement qui a une ou plusieurs colonnes à longueur variable. Ajoutons le surcoût des même 10 octets qui précédemment, cela fait donc un total de 4 + 150 + 18 + 2 + 10, soit 184.

Dans l'exemple à longueur fixe de la Figure 6-1, vous remplissez toujours 25 enregistrements sur une page de donnée (8096/314). Dans l'exemple à longueur variable de la Figure 6-2, vous remplissez une moyenne de 44 enregistrements par page (8096/184). Cette seconde solution est moitié moins lourde en terme de stockage sur les pages, une seule opération I/O ramène deux fois plus d'enregistrements, et une page en mémoire cache a deux fois plus de chance de contenir l'enregistrement désiré.

Dans le choix des longueur de colonnes, ne gaspillez pas, ne soyez pas radin non plus. N'hésitez pas pour deux octets, et rappelez vous l'exemple avec les types de données smallint et int sur la colonne cli_id.

Les concepteurs de la base de données peuvent regretter l'économie de 2 octets car le nombre de clients peut passer de 32,767 à 2,147,483,647. Avec une économie de 2 octets, le nombre d'enregistrements sur une page reste inchangé.

La gestion des NULLs est une source de complexité pour le moteur de stockage, car SQL Server garde un masque spécial pour chaque enregistrement pour y indiquer quelles sont les colonnes nullable (pouvant être nulles) qui sont actuellement à NULL. Si les NULLs sont autorisés, SQL Server doit décoder ce masque pour chaque enregistrement accédé. La gestion des NULLs induit aussi une complexité au niveau du code application. Une logique spéciale est à concevoir pour gérer les NULLs.

Il y a des nuances dans l'utilisation de NULL dans les opérations logique, les jointures et la recherche de valeurs. Si possible, positionnez les colonnes à NOT NULL et définissez des valeurs par défaut pour les entrées manquantes ou inconnues (et donnez à ces colonnes le type varchar).

C'est une bonne pratique de déclarer explicitement les colonnes NOT NULL ou NULL lors de la création d'une table. Si aucune déclaration n'est faite, SQL Server fixe les colonnes à NOT NULL. (Donc, les NULLs ne sont pas autorisés.) Cependant, vous pouvez mettre l'option qui par défaut autorise les NULLs en utilisant une option de base de données ou un paramétrage de session. Le standard ANSI SQL autorise les NULLs lors qu'il n'y a pas de déclaration explicite, ce qui n'est pas le cas de SQL Server.

Plusieurs options de base de données et de paramétrage de session contrôle le comportement de SQL Server par rapport aux valeurs NULLs. Vous positionnez les options de base de données via la procédure système sp_dboption. Et vous autorisez les paramétrage de session via la commande SET.

L'option de base de données ANSI null default correspond aux deux paramétrages de session ANSI_NULL_DFLT_ON ou ANSI_NULL_DFLT_OFF. Lorsque l'option de base de données ANSI null default vaut FALSE (valeur par défaut de SQL Server), les nouvelles colonnes créées avec les commandes ALTER TABLE et CREATE TABLE sont, par défaut, à NOT NULL si aucune déclaration explicite n'est faite. SET ANSI_NULL_DFLT_OFF et SET ANSI_NULL_DFLT_ON sont des options mutuellement exclusives. Lorsqu'une options est à ON, l'autre à OFF.

Utilisez la fonction GETANSINULL() pour déterminer la nullabilité par défaut de votre session. Cette fonction retourne 1 si les nouvelles colonnes permettent les valeurs NULLs et que la nullabilité colonne ou type de données n'est pas explicitement défini quand une table est créée ou modifiée. La déclaration explicite de NULL ou NOT NULL est fortement recommandée car cela évite toute ambiguïté vis à vis du comportement du moteur relationnel.

L'option de base de données concat null yields null correspond au paramétrage de session SET CONCAT_NULL_YIELDS_NULL. Si CONCAT_NULL_YIELDS_NULL est positionné à ON, la concaténation d'une valeur NULL avec une chaîne donne comme résultat une valeur NULL. Par exemple, SELECT 'abc' + NULL donne NULL. Lorsque SET CONCAT_NULL_YIELDS_NULL vaut OFF, la concaténation d'une chaîne avec une valeur NULL donne la chaîne elle-même. (La valeur NULL est considérée comme une chaîne vide.) Par exemple, SELECT 'abc' + NULL vaut abc. Si le mode de session n'est pas spécifié, la valeur de l'option de base de données concat null yields null s'applique. Si SET CONCAT_NULL_YIELDS_NULL est à OFF, SQL Server utilise le paramétrage concat null yields null de sp_dboption.

L'option de base de données ANSI nulls correspond au paramétrage de session SET ANSI_NULLS. Positionné à TRUE, une comparaison à une valeur NULL vaut NULL (inconnue). Positionné à FALSE, la comparaison de valeurs non-Unicode à une valeur NULL donne TRUE si les deux valeurs sont à NULL. De même, positionné à TRUE, votre code doit utiliser la condition IS NULL pour déterminer si une colonne vaut NULL. Positionné à FALSE, SQL Server interprète "= NULL" comme "IS NULL" et "<> NULL" comme "IS NOT NULL". Observez ce comportement avec la table titles dans la base pubs. La table titles a deux enregistrements avec un prix à NULL. Le premier batch, exécuté depuis le Query Analyzer, retourne deux enregistrements, et le second ne retourne aucun enregistrement:

 

-- Retourne 2 enregistrements use pubs set ansi_nulls off go select * from titles where price = null go -- Retourne 0 enregistrement use pubs set ansi_nulls on go select * from titles where price = null go

Une quatrième option de session est ANSI_DEFAULTS. Positionnée à ON, cette option autorise ANSI_NULLS et ANSI_NULL_DEFAULT_ON. Le pilote ODBC de SQL Server et le provider OLEDB de SQL Server positionnent automatiquement ANSI_DEFAULTS à ON. Vous pouvez changer le paramétrage ANSI_NULLS dans votre source de données (DSN). Les outils Query Analyzer et osql utilisent ODBC en interne mais positionnent à FALSE certaines options. Pour voir les options activées, faites:

 

DBCC USEROPTIONS

Voici le résultat:

 

Set Option Value -------------------------- ------------------------- textsize 64512 language us english dateformat mdy datefirst 7 ansi_null_dflt_on SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET

Le niveau de compatibilité a un impact dans la gestion du NULL par SQL Server, et il est déterminé via la procédure système sp_dbcmptlevel. Si le niveau de compatibilité est 70, le bit de nullabilité des colonnes sans nullabilité explicite est déterminé soit par le paramétrage de session de SET ANSI_NULL_DFLT_ON ou SET ANSI_NULL_DFLT_OFF ou l'option de base de données ANSI null default. Dans le mode 60 ou 65, le bit de nullabilité pour les colonnes sans NOT ou NOT NULL explicite via les commandes CREATE TABLE ou ALTER TABLE est considéré comme NOT NULL.

Le niveau de compatibilité de base de données contrôle aussi la manière avec laquelle SQL Server gère une chaîne vide (deux guillemets simples sans rien entre) comme un espace simple ou une vraie chaîne vide. Avec le niveau de compatibilité 60 ou 65, SQL Server interprète les chaînes vide comme un seul espace. Si le niveau de compatibilité est 70, SQL Server interprète les chaînes vide comme vide, c'est à dire une chaîne avec 0 caractère. Des fois, cet espace vide vaut NULL, mais SQL Server ne le considère pas comme un NULL. SQL Server marque les NULLs en interne comme des NULLs, mais une chaîne vide est stockée comme une chaîne de caractère à longueur variable de taille 0.

Dans le mode 60 ou 65, la chaîne vide ('') est interprétée comme un seul espace (' ') dans une commande INSERT ou sur une affectation à une donnée varchar. Dans une concaténation avec un varchar, char, ou text, la chaîne vide est interprétée comme un seul espace. Cela veut dire qu'il n'y a jamais de vraie chaîne vide. La seul manière en mode 60 ou 65 est d'autoriser les champs à NULL et d'utiliser NULL à la place d'une chaîne vide.

Vous voyez, ce n'est pas simple... Suivez ces quelques recommandations:

 

• N'autorisez pas les valeurs NULLs dans vos tables.

 

• Déclarer l'attribut NOT NULL dans la définition des tables.

 

• Faites fonctionner vos bases de données en mode de compatibilité 70.