Premiers pas avec Microsoft SQL Server

INSERT / SELECT / UPDATE / DELETE : les bases du Data Manipulation Language #

Data Manipulation Langage (DML en abrégé) comprend des opérations telles que INSERT, UPDATE et DELETE :

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld

Dans ce script, nous créons une table pour illustrer certaines requêtes de base.

Les exemples suivants montrent comment interroger des tables :

USE Northwind;
GO
SELECT TOP 10 * FROM Customers 
ORDER BY CompanyName

sélectionnera les 10 premiers enregistrements de la table Customer, triés par la colonne CompanyName de la base de données Northwind (qui est l’une des bases de données d’exemple de Microsoft, elle peut être téléchargée à partir de [ici](https:// technet.microsoft.com/en-us/library/ms143221(v=sql.105).aspx)) :

[![Requête de base de données Northwind][1]][1]

Notez que Use Northwind; modifie la base de données par défaut pour toutes les requêtes suivantes. Vous pouvez toujours référencer la base de données en utilisant la syntaxe complète sous la forme [Database].[Schema].[Table] :

SELECT TOP 10 * FROM Northwind.dbo.Customers 
ORDER BY CompanyName

SELECT TOP 10 * FROM Pubs.dbo.Authors
ORDER BY City

Ceci est utile si vous interrogez des données provenant de différentes bases de données. Notez que dbo, spécifié “entre” est appelé un schéma et doit être spécifié lors de l’utilisation de la syntaxe complète. Vous pouvez le considérer comme un dossier dans votre base de données. dbo est le schéma par défaut. Le schéma par défaut peut être omis. Tous les autres schémas définis par l’utilisateur doivent être spécifiés.

Si la table de la base de données contient des colonnes nommées comme des mots réservés, par ex. Date, vous devez mettre le nom de la colonne entre parenthèses, comme ceci :

-- descending order
SELECT TOP 10 [Date] FROM dbo.MyLogTable
ORDER BY [Date] DESC

Il en va de même si le nom de la colonne contient des espaces dans son nom (ce qui n’est pas recommandé). Une syntaxe alternative consiste à utiliser des guillemets doubles au lieu de crochets, par exemple :

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
order by "Date" desc 

est équivalent mais pas si couramment utilisé. Notez la différence entre les guillemets doubles et les guillemets simples : les guillemets simples sont utilisés pour les chaînes, c’est-à-dire

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
where UserId='johndoe'
order by "Date" desc 

est une syntaxe valide. Notez que T-SQL a un préfixe “N” pour les types de données NChar et NVarchar, par ex.

SELECT TOP 10 * FROM Northwind.dbo.Customers 
WHERE CompanyName LIKE N'AL%'
ORDER BY CompanyName

renvoie toutes les sociétés dont le nom commence par AL (% est un caractère générique, utilisez-le comme vous utiliseriez l’astérisque dans une ligne de commande DOS, par exemple DIR AL*). Pour LIKE, quelques caractères génériques sont disponibles, regardez ici pour en savoir plus.

Joints #

Les jointures sont utiles si vous souhaitez interroger des champs qui n’existent pas dans une seule table, mais dans plusieurs tables. Par exemple : vous souhaitez interroger toutes les colonnes de la table “Region” dans la base de données “Northwind”. Mais vous remarquez que vous avez également besoin de la RegionDescription, qui est stockée dans une table différente, Region. Cependant, il existe une clé commune, RgionID que vous pouvez utiliser pour combiner ces informations dans une seule requête comme suit (Top 5 ne renvoie que les 5 premières lignes, omettez-la pour obtenir toutes les lignes):

SELECT TOP 5 Territories.*, 
    Regions.RegionDescription 
FROM Territories 
INNER JOIN Region 
    ON Territories.RegionID=Region.RegionID
ORDER BY TerritoryDescription

affichera toutes les colonnes de Territories plus la colonne RegionDescription de Region. Le résultat est trié par TerritoryDescription.

Alias ​​de table #

Lorsque votre requête nécessite une référence à deux tables ou plus, vous pouvez trouver utile d’utiliser un alias de table. Les alias de table sont des références abrégées aux tables qui peuvent être utilisées à la place d’un nom de table complet et peuvent réduire la saisie et l’édition. La syntaxe d’utilisation d’un alias est :

<TableName> [as] <alias>

Où as est un mot-clé facultatif. Par exemple, la requête précédente peut être réécrite comme suit :

SELECT TOP 5 t.*, 
    r.RegionDescription 
FROM Territories t
INNER JOIN Region r 
    ON t.RegionID = r.RegionID
ORDER BY TerritoryDescription

Les alias doivent être uniques pour toutes les tables d’une requête, même si vous utilisez deux fois la même table. Par exemple, si votre table Employee incluait un champ SupervisorId, vous pouvez utiliser cette requête pour renvoyer un employé et le nom de son superviseur :

SELECT e.*, 
    s.Name as SupervisorName -- Rename the field for output
FROM Employee e
INNER JOIN Employee s
    ON e.SupervisorId = s.EmployeeId
WHERE e.EmployeeId = 111

Syndicats #

Comme nous l’avons vu précédemment, une jointure ajoute des colonnes provenant de différentes sources de table. Mais que se passe-t-il si vous souhaitez combiner des lignes provenant de différentes sources ? Dans ce cas, vous pouvez utiliser un UNION. Supposons que vous organisiez une fête et que vous souhaitiez inviter non seulement des employés, mais également des clients. Ensuite, vous pouvez exécuter cette requête pour le faire :

SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees
UNION
SELECT ContactName, Address, City FROM Customers

Il renverra les noms, adresses et villes des employés et des clients dans une seule table. Notez que les lignes en double (s’il y en a) sont automatiquement éliminées (si vous ne le souhaitez pas, utilisez plutôt UNION ALL). Le numéro de colonne, les noms de colonne, l’ordre et le type de données doivent correspondre dans toutes les instructions de sélection qui font partie de l’union - c’est pourquoi le premier SELECT combine FirstName et LastName de Employee à ContactName.

Variables de tableau #

Il peut être utile, si vous avez besoin de traiter des données temporaires (en particulier dans une procédure stockée), d’utiliser des variables de table : la différence entre une “vraie” table et une variable de table est qu’elle existe juste en mémoire pour un traitement temporaire.

Exemple:

DECLARE @Region TABLE
(
  RegionID int, 
  RegionDescription NChar(50)
)

crée une table en mémoire. Dans ce cas, le préfixe @ est obligatoire car il s’agit d’une variable. Vous pouvez effectuer toutes les opérations DML mentionnées ci-dessus pour insérer, supprimer et sélectionner des lignes, par ex.

INSERT INTO @Region values(3,'Northern')
INSERT INTO @Region values(4,'Southern')

Mais normalement, vous le rempliriez en fonction d’une vraie table comme

INSERT INTO @Region
SELECT * FROM dbo.Region WHERE RegionID>2;

qui lira les valeurs filtrées de la table réelle dbo.Region et l’insérera dans la table mémoire @Region - où elle pourra être utilisée pour un traitement ultérieur. Par exemple, vous pouvez l’utiliser dans une jointure comme

SELECT * FROM Territories t
JOIN @Region r on t.RegionID=r.RegionID

qui renverrait dans ce cas tous les territoires “Nord” et “Sud”. Des informations plus détaillées peuvent être trouvées ici. Les tables temporaires sont discutées ici, si vous souhaitez en savoir plus sur ce sujet .

REMARQUE : Microsoft ne recommande l’utilisation de variables de table que si le nombre de lignes de données dans la variable de table est inférieur à 100. Si vous travaillez avec de plus grandes quantités de données, utilisez une table temporaire, ou table temporaire, à la place.

[1] : http://i.stack.imgur.com/6xeX4.jpg

SELECT toutes les lignes et colonnes d’une table #

Syntaxe:

SELECT *
FROM table_name

L’opérateur astérisque * sert de raccourci pour sélectionner toutes les colonnes du tableau. Toutes les lignes seront également sélectionnées car cette instruction SELECT n’a pas de clause WHERE, pour spécifier des critères de filtrage.

Cela fonctionnerait également de la même manière si vous ajoutiez un alias à la table, par exemple e dans ce cas :

SELECT *
FROM Employees AS e

Ou si vous vouliez tout sélectionner à partir d’une table spécifique, vous pouvez utiliser l’alias + " .* " :

SELECT e.*, d.DepartmentName
FROM Employees AS e
    INNER JOIN Department AS d 
        ON e.DepartmentID = d.DepartmentID

Les objets de la base de données sont également accessibles à l’aide de noms complets :

SELECT * FROM [server_name].[database_name].[schema_name].[table_name]

Cela n’est pas nécessairement recommandé, car la modification des noms de serveur et/ou de base de données entraînerait l’arrêt des requêtes utilisant des noms complets en raison de noms d’objet non valides.

Notez que les champs avant table_name peuvent être omis dans de nombreux cas si les requêtes sont exécutées sur un seul serveur, base de données et schéma, respectivement. Cependant, il est courant qu’une base de données ait plusieurs schémas et, dans ces cas, le nom du schéma ne doit pas être omis lorsque cela est possible.

Avertissement : L’utilisation de SELECT * dans le code de production ou les procédures stockées peut entraîner des problèmes ultérieurement (lorsque de nouvelles colonnes sont ajoutées au tableau ou si les colonnes sont réorganisées dans le tableau), en particulier si votre code fait des hypothèses simples sur l’ordre des colonnes ou le nombre de colonnes renvoyées. Il est donc plus sûr de toujours spécifier explicitement les noms de colonne dans les instructions SELECT pour le code de production.

SELECT col1, col2, col3
FROM table_name

UPDATE ligne spécifique #

UPDATE HelloWorlds
SET HelloWorld = 'HELLO WORLD!!!'
WHERE Id = 5

Le code ci-dessus met à jour la valeur du champ “HelloWorld” avec “HELLO WORLD !!!” pour l’enregistrement où “Id = 5” dans la table HelloWorlds.

Remarque : Dans une instruction de mise à jour, il est conseillé d’utiliser une clause “où” pour éviter de mettre à jour l’intégralité de la table à moins que et jusqu’à ce que vos besoins soient différents.

Commentaires dans le code #

Transact-SQL prend en charge deux formes d’écriture de commentaires. Les commentaires sont ignorés par le moteur de base de données et sont destinés à être lus.

Les commentaires sont précédés de -- et sont ignorés jusqu’à ce qu’une nouvelle ligne soit rencontrée :

-- This is a comment
SELECT *
FROM MyTable -- This is another comment
WHERE Id = 1;

**Les commentaires en forme d’étoile ** commencent par /* et se terminent par */. Tout le texte entre ces délimiteurs est considéré comme un bloc de commentaires.

/* This is
a multi-line
comment block. */
SELECT Id = 1, [Message] = 'First row'
UNION ALL
SELECT 2, 'Second row'
/* This is a one liner */
SELECT 'More';

Les commentaires slash astérisque ont l’avantage de garder le commentaire utilisable si l’instruction SQL perd de nouveaux caractères de ligne. Cela peut se produire lorsque SQL est capturé lors du dépannage.

Les commentaires en étoile oblique peuvent être imbriqués et un /* commençant à l’intérieur d’un commentaire en étoile oblique doit se terminer par un */ pour être valide. Le code suivant entraînera une erreur

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/

L’étoile slash même si à l’intérieur de la citation est considérée comme le début d’un commentaire. Par conséquent, il doit être terminé par une autre barre oblique fermante. La bonne façon serait

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/  */

SUPPRIMER toutes les lignes #

DELETE
FROM Helloworlds

Cela supprimera toutes les données de la table. La table ne contiendra aucune ligne après l’exécution de ce code. Contrairement à DROP TABLE, cela préserve la table elle-même et sa structure et vous pouvez continuer à insérer de nouvelles lignes dans cette table.

Une autre façon de supprimer toutes les lignes du tableau est de le tronquer, comme suit :

TRUNCATE TABLE HelloWords

La différence avec l’opération DELETE est multiple :

1. L’opération de troncation n’est pas stockée dans le fichier journal des transactions
2. S’il existe un champ “IDENTITY”, celui-ci sera réinitialisé
3. TRUNCATE peut être appliqué sur toute la table et non sur une partie de celle-ci (à la place de la commande DELETE, vous pouvez associer une clause WHERE)

Restrictions de TRUNCATE

1. Impossible de TRUNCATE une table s’il y a une référence “FOREIGN KEY”
2. Si la table fait partie d’une VUE INDEXÉE
3. Si la table est publiée en utilisant TRANSACTIONAL REPLICATION ou MERGE REPLICATION
4. Il ne déclenchera aucun TRIGGER défini dans le tableau

[[sic]][1]

[1] : https://msdn.microsoft.com/en-us/library/ms177570.aspx

IMPRIMER #

Affichez un message sur la console de sortie. En utilisant SQL Server Management Studio, cela sera affiché dans l’onglet messages, plutôt que dans l’onglet résultats :

PRINT 'Hello World!';

Sélectionnez les lignes qui correspondent à une condition #

Généralement, la syntaxe est :

SELECT <column names>
FROM <table name>
WHERE <condition>

Par exemple:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith'

Les conditions peuvent être complexes :

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith' AND (City = 'New York' OR City = 'Los Angeles')

MISE À JOUR de toutes les lignes #

Une forme simple de mise à jour consiste à incrémenter toutes les valeurs d’un champ donné de la table. Pour ce faire, nous devons définir le champ et la valeur d’incrément

Voici un exemple qui incrémente le champ “Score” de 1 (dans toutes les lignes) :

UPDATE Scores
SET score = score + 1  

Cela peut être dangereux car vous pouvez corrompre vos données si vous effectuez accidentellement une MISE À JOUR pour une ligne spécifique avec une MISE À JOUR pour Toutes les lignes du tableau.

Récupérer les informations de base du serveur #

SELECT @@VERSION

Renvoie la version de MS SQL Server en cours d’exécution sur l’instance.

SELECT @@SERVERNAME

Renvoie le nom de l’instance MS SQL Server.

SELECT @@SERVICENAME

Renvoie le nom du service Windows sous lequel MS SQL Server s’exécute.

SELECT serverproperty('ComputerNamePhysicalNetBIOS');

Renvoie le nom physique de la machine sur laquelle SQL Server s’exécute. Utile pour identifier le nœud dans un cluster de basculement.

SELECT * FROM fn_virtualservernodes();

Dans un cluster de basculement, renvoie chaque nœud sur lequel SQL Server peut s’exécuter. Il ne renvoie rien sinon un cluster.

TRUNCATE TABLE #

TRUNCATE TABLE Helloworlds 

Ce code supprimera toutes les données de la table Helloworlds. Tronquer la table est presque similaire au code “Supprimer de la table”. La différence est que vous ne pouvez pas utiliser les clauses where avec Truncate. La table tronquée est considérée comme meilleure que la suppression car elle utilise moins d’espaces dans le journal des transactions.

Notez que si une colonne d’identité existe, elle est réinitialisée à la valeur de départ initiale (par exemple, l’ID auto-incrémenté redémarrera à partir de 1). Cela peut entraîner des incohérences si les colonnes d’identité sont utilisées comme clé étrangère dans une autre table.

Créer une nouvelle table et insérer des enregistrements de l’ancienne table #

SELECT * INTO NewTable FROM OldTable

Crée une nouvelle table avec la structure de l’ancienne table et insère toutes les lignes dans la nouvelle table.

Quelques restrictions

1. Vous ne pouvez pas spécifier une variable de table ou un paramètre table comme nouvelle table.
2. Vous ne pouvez pas utiliser SELECT…INTO pour créer une table partitionnée, même lorsque la table source est partitionnée. SELECT…INTO n’utilise pas le schéma de partition de la table source ; à la place, la nouvelle table est créé dans le groupe de fichiers par défaut. Pour insérer des lignes dans un partitionné table, vous devez d’abord créer la table partitionnée puis utiliser le Instruction INSERT INTO…SELECT FROM.
3. Les index, contraintes et déclencheurs définis dans la table source ne sont pas transférés dans la nouvelle table, ni spécifiés dans la Instruction SELECT…INTO. Si ces objets sont nécessaires, vous pouvez créer après l’exécution de l’instruction SELECT…INTO.
4. La spécification d’une clause ORDER BY ne garantit pas que les lignes sont insérées dans l’ordre spécifié. Lorsqu’une colonne clairsemée est incluse dans la liste de sélection, la propriété de colonne clairsemée n’est pas transférée à la colonne dans le nouveau tableau. Si cette propriété est requise dans le nouveau table, modifiez la définition de la colonne après avoir exécuté SELECT…INTO déclaration pour inclure cette propriété.
5. Lorsqu’une colonne calculée est incluse dans la liste de sélection, la colonne correspondante dans la nouvelle table n’est pas une colonne calculée. La les valeurs dans la nouvelle colonne sont les valeurs qui ont été calculées à l’époque SELECT…INTO a été exécuté.

[[sic][1]]

[1] : https://msdn.microsoft.com/en-us/library/ms188029.aspx

Utiliser les transactions pour modifier les données en toute sécurité #

Chaque fois que vous modifiez des données, dans une commande DML (Data Manipulation Language), vous pouvez encapsuler vos modifications dans une transaction. DML inclut UPDATE, TRUNCATE, INSERT et DELETE. L’un des moyens de vous assurer que vous modifiez les bonnes données consiste à utiliser une transaction.

Les modifications DML verrouillent les lignes concernées. Lorsque vous commencez une transaction, vous devez terminer la transaction ou tous les objets modifiés dans le DML resteront verrouillés par celui qui a commencé la transaction. Vous pouvez terminer votre transaction avec ROLLBACK ou COMMIT. ROLLBACK renvoie tout dans la transaction à son état d’origine. COMMIT place les données dans un état final où vous ne pouvez pas annuler vos modifications sans une autre instruction DML.

Exemple:

--Create a test table

USE [your database]
GO
CREATE TABLE test_transaction (column_1 varchar(10))
GO

INSERT INTO 
 dbo.test_transaction
        ( column_1 )
VALUES
        ( 'a' )

BEGIN TRANSACTION --This is the beginning of your transaction

UPDATE dbo.test_transaction
SET column_1 = 'B'
OUTPUT INSERTED.*
WHERE column_1 = 'A'
  

ROLLBACK TRANSACTION  --Rollback will undo your changes
           --Alternatively, use COMMIT to save your results

SELECT * FROM dbo.test_transaction   --View the table after your changes have been run

DROP TABLE dbo.test_transaction

Remarques:

• Ceci est un exemple simplifié qui n’inclut pas la gestion des erreurs. Mais toute opération de base de données peut échouer et donc lever une exception. Voici un exemple à quoi pourrait ressembler une telle gestion d’erreur requise. Vous ne devez jamais utiliser de transactions sans gestionnaire d’erreurs, sinon vous risquez de laisser la transaction dans un état inconnu.
• En fonction du niveau d’isolation, les transactions verrouillent les données interrogées ou changé. Vous devez vous assurer que les transactions ne sont pas en cours d’exécution pendant une longue période, car elles verrouillent les enregistrements dans une base de données et peuvent entraîner des [deadlocks](https://www.simple-talk.com/sql/performance/sql- server-deadlocks-by-example/) avec d’autres transactions en cours d’exécution parallèles. Gardez les opérations encapsulées dans des transactions aussi courtes que possible et minimisez l’impact avec la quantité de données que vous verrouillez.

Obtenir le nombre de lignes du tableau #

L’exemple suivant peut être utilisé pour trouver le nombre total de lignes pour une table spécifique dans une base de données si table_name est remplacé par la table que vous souhaitez interroger :

SELECT COUNT(*) AS [TotalRowCount] FROM table_name;

Il est également possible d’obtenir le nombre de lignes pour toutes les tables en rejoignant la partition de la table en fonction du HEAP des tables (index_id = 0) ou de l’index cluster cluster (index_id = 1) à l’aide du script suivant :

SELECT  [Tables].name                AS [TableName],
        SUM( [Partitions].[rows] )    AS [TotalRowCount]
FROM    sys.tables AS [Tables]
JOIN    sys.partitions AS [Partitions]
    ON  [Tables].[object_id]    =    [Partitions].[object_id]
    AND [Partitions].index_id IN ( 0, 1 )
--WHERE    [Tables].name = N'table name' /* uncomment to look for a specific table */
GROUP BY    [Tables].name;

Cela est possible car chaque table est essentiellement une table de partition unique, à moins que des partitions supplémentaires ne lui soient ajoutées. Ce script a également l’avantage de ne pas interférer avec les opérations de lecture/écriture sur les lignes des tables.