Les tables SQL Server

Les tutoriels de cette section sur les tables SQL Server expliquent les syntaxes par l’exemple. Ces requêtes permettent de gérer les tables SQL Server, qui sont l’élément central des bases de données relationnelles de Microsoft.

Autrement dit, comment créer, modifier, copier vider et supprimer une ou plusieurs tables. C’est à dire comment manipuler ces objets avec des commandes T-SQL.

Exemples basiques pour gérer les tables SQL Server

Voici donc des tutoriels informatiques en Français pour gérer les tables stockées sous SQL Server, c’est à dire dans le SGBD de Microsoft. La première partie expose les requêtes de bases pour la gestion.

La deuxième partie des tutoriels explique des concepts avancés comme le partitionnement des tables par exemple.

Cet exemple de script utilise la commande CREATE TABLE pour créer une table de ventes. Le script utilise des colonnes pour stocker le temps au niveau mois, le numéro d’employé et les montants des ventes.

Exemple T-SQL pour créer une table MS SQL

Cette table utilise plusieurs types de données pour montrer une palette des formats disponible avec SQL Server. Toutefois il est aussi judicieux de calculer les montants à la volée avec la valeur de la TVA stockée une seule fois dans une autre table, avec par exemple l’année.

Tout d’abord, comment créer une table SQL Server avec la commande CREATE TABLE?

-- Créer la table des ventes
CREATE TABLE [dbo].[VENTES] 
(
	[MoisNom] NVARCHAR(50), -- alphanumérique
	[MoisCourant]   BIT, -- booléen, 0 ou 1 , false / true
	[NumeroMois] TINYINT, -- très petit entier, de 0 à 255
	[NumeroEmploye] SMALLINT, -- petit entier, minimum -2^15, maximum 2^15
	[NombreClients] INTEGER, -- entier, minimum -2^31, maximum 2^31
	[NombreVentes] BIGINT, -- grand entier, minimum: -2^63, maximum 2^63
	[Montant_HT] NUMERIC(15,5), -- numérique, 15 chiffres, dont 5 après la virgule
	[Montant_TTC] DECIMAL(15,5) -- décimal, 15 chiffres, dont 5 après la virgule
);

-- Insérer des données de Ventes pour notre exemple
INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES  ( N'Janvier', 1000);
INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES  ( N'Février', 2000);
INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES  ( N'Mars', 3000);
GO
Script pour gérer les tables SQL Server (CREATE TABLE et INSERT INTO)
Script pour gérer les tables SQL Server (CREATE TABLE et INSERT INTO)

Script pour modifier une table (ALTER TABLE)

Ensuite, comment modifier une table SQL Server avec ALTER TABLE pour ajouter ou supprimer une colonne par exemple. Ou encore changer la taille d’un champs texte ou le type d’une colonne.

Premièrement, pour modifier une table et ajouter des contraintes pour contrôler l’intégrité des données saisies, utiliser les commandes ALTER TABLE et ADD CONSTRAINT.

Ajouter une contrainte sur la table des ventes pour vérifier que la colonne nom du mois n’est pas nulle.

ALTER TABLE [dbo].[VENTES] WITH CHECK
   ADD CONSTRAINT Mois_check CHECK (MoisNom IS NOT NULL);
GO

Il est maintenant possible d’insérer dans la table des ventes uniquement des valeurs non nulles.

-- Insérer trois lignes de plus dans la table des ventes
INSERT INTO [DBO].[VENTES] VALUES ('Mars', 0,1,10,20,12,1000,1200);
INSERT INTO [DBO].[VENTES] VALUES ('Avril', 0,1,10,20,12,1000,1200); 
INSERT INTO [DBO].[VENTES] VALUES ('Mai', 0,1,10,20,12,1000,1200);

-- Cette ligne ne sera pas insérée car il y a une valeur nulle pour le mois 
INSERT INTO [DBO].[VENTES] VALUES (null, 0,1,10,20,12,1000,1200);

Si le mois est nul alors cette erreur SQL Server apparaît :

The INSERT statement conflicted with the CHECK constraint “Mois_check”.
The conflict occurred in database “Expert-Only”, table “dbo.VENTES”, column ‘MoisNom’.

Requête INSERT INTO depuis un SELECT avec SQL Server

Pour aller plus loin et insérer des lignes depuis une requête de sélection, utiliser la requête de type Insert into directement depuis un SELECT.

INSERT INTO [dbo].[VENTES]  ([MoisNom], [NumeroMois], [Montant_HT])
SELECT N'Janvier', '1', 1000 UNION ALL
SELECT N'Février', '2', 2000 UNION ALL
SELECT N'Mars', '3', 3000;

Ajouter une colonne à une table SQL (ALTER COLUMN)

Ensuite, pour faire évoluer une table, adapter cet exemple pour modifier la longueur des colonnes avec un script de type ALTER COLUMN.

 -- Passer la longueur nom du mois de 20 à 100 caractères
ALTER TABLE [dbo].[VENTES]
   ALTER COLUMN [MoisNom] NVARCHAR(100);
GO

Supprimer des données depuis une table SQL avec Microsoft (DELETE FROM)

Troisièmement, pour supprimer des données avec un filtre, utiliser ce script et adapter le aux besoins du projet. Ce script supprimer les données des trois premiers mois, soit Janvier, Février et Mars.

DELETE FROM [DBO].[VENTES]
   WHERE MoisNom in ('Janvier', 'Février', 'Mars');

Vider toutes les données d’une table MSSQL (TRUNCATE TABLE)

De plus, avant de supprimer complètement une table et donc le contenu et la structure, il est possible de vider le contenu de la table MS SQL avec la commande TRUNCATE TABLE.

Pour vider toutes les données d’une table SQL Server sans aucun filtre, utiliser le script TRUNCATE TABLE.

TRUNCATE TABLE [dbo].[VENTES];

Vérifier si une table existe et la supprimer (IF EXISTS et DROP TABLE)

Il est possible de tester si une table SQL Server existe avant de la supprimer pour éviter les erreurs du type « Impossible de trouver l’objet car il n’existe pas ou vous n’avez pas les autorisations nécessaires »?

IF EXISTS (
	SELECT 1 FROM sys.objects
	WHERE  object_id = object_id(N'[dbo].[VENTES]')
	AND type in (N'U') )
BEGIN 
  DROP TABLE [dbo].[VENTES]
END;

Supprimer une table SQL Server (DROP TABLE)

Pour supprimer complètement une table SQL Server, utiliser la commande T-SQL DROP TABLE. C’est à dire que la structure et le contenu sont supprimer. Noter que cette action est irréversible et qu’il faut donc sauvegarder le code de la table au préalable.

Utiliser cette commande T-SQL pour supprimer une table SQL Server, c’est à dire le contenu de la table et sa structure.

DROP TABLE [dbo].[VENTES];
Supprimer une table SQL Server

Optimiser les performances et administrer les tables SQL Server

Cette deuxième partie sur les tables SQL porte sur des sujets avancés. Comme par exemple lister certains objets et leur caractéristiques ou optimiser les performances des requêtes.

Créer une table SQL avec une clef primaire

Une clef primaire permet d’identifier de façon unique une ligne dans une table. Voici un script pour créer une table et définir une colonne comme une clef primaire. 

CREATE TABLE [dbo].[Customers](
	[CustomerID] [int] NOT NULL,
	[FirstName] [nvarchar](20) NULL,
	[LastName] [nvarchar](20) NULL,
	[City] [nvarchar](20) NULL,
	[Country] [nvarchar](50) NULL,
	CONSTRAINT [CustomersPrimaryKeyCustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO

Créer une table SQL Serveur avec des partitions

Pour améliorer les performances il est possible de créer une table SQL Server avec des partitions. En effet, le partitionnement SQL permet de séparer les données d’une même table sur différents groupes de fichiers indépendants. Les données sont découpées d’après la valeur d’une colonne.

Ce découpage intelligent permet d’obtenir des performances optimisées à chaque fois que la colonne de partitionnement est utilisée dans une requête.

Afficher une liste avec la taille des tables MSSQL et l’espace disque utilisé

Voici donc un court tutoriel pour lister les tables avec leur taille et l’espace disque utilisé par chaque objet.

SELECT
     sch.name as SchemaName,
     tab.name as TableName,
     par.rows as RowCounts, 
     sum(alc.total_pages) * 8 as TotalSpace,
     sum(alc.used_pages) * 8 as UsedSpace,
     (sum(alc.total_pages) - sum(alc.used_pages)) * 8 as UnusedSpace
FROM sys.tables tab 
INNER JOIN sys.indexes ind 
     ON tab.object_id = ind.object_id 
INNER JOIN sys.partitions par 
     ON ind.object_id = par.object_id 
     and ind.index_id = par.index_id 
INNER JOIN sys.allocation_units alc 
     ON par.partition_id = alc.container_id 
LEFT OUTER JOIN sys.schemas sch 
     ON tab.schema_id = sch.schema_id 
GROUP BY 
     tab.name, 
     sch.name, 
     par.rows 
ORDER BY 1,2;

Requête SQL pour lister les types de tables définies par l’utilisateur

Ce tutoriel SQL explique comment lister des types de tables définies par l’utilisateur avec un script en T-SQL. Ces type des tables ou User Defined Tables (UDT) en Anglais sont des objets de type table. Ou pour être plus précis des variables de type table.

SELECT
     name, 
     system_type_id, 
     user_type_id, 
     schema_id, 
     principal_id, 
     max_length, 
     precision, 
     scale, 
     collation_name, 
     is_nullable, 
     is_user_defined, 
     is_assembly_type, 
     default_object_id, 
     rule_object_id, 
     is_table_type
FROM      SYS.TABLE_TYPES
WHERE      IS_USER_DEFINED = 1;

Enfin, ces exemples de requêtes simples montrent comment créer, supprimer, modifier, vider, supprimer ou optimiser des tables SQL.

Cet article que je recommande de garder en favori et de partager, rappelle les syntaxes de bases sur les tables SQL Server. 

De plus, le détail de chaque opération n’est pas listé ici, car l’objectif est d’avoir sous la main la syntaxe le plus rapidement possible.

Liste des types de tables définies par l’utilisateur