Archive

Posts Tagged ‘SQL Server’

Instruction MERGE

Ça fait un petit moment maintenant que je vois des instructions de ce genre, dans des scripts de données :

IF EXISTS (SELECT * FROM Client WHERE Nom = 'Legrand' AND Prenom = 'Lucas')
	BEGIN
		PRINT 'UPDATE'
		UPDATE Client
		SET Email = 'lucas.legrand@live.com'
		WHERE Nom = 'Legrand' AND Prenom = 'Lucas'
	END
ELSE
	BEGIN
		PRINT 'INSERT'
		INSERT INTO Client
		VALUES ('Legrand', 'Lucas',
				'lucas.legrand@live.com',
				'107 rue de Lille, 75007 PARIS')
	END

Le gros problème, c’est que s’il y a beaucoup de données (surtout de références), ça va multiplier les blocs comme des petits pains (ce qui peut être sympa, mais ce n’est pas le sujet).

C’est là que le sauveur arrive, sous la forme de l’instruction MERGE.

Pour obtenir l’équivalent avec MERGE, on écrira :

MERGE Client AS target
USING (VALUES 
   ('lucas.legrand@live.com', 'Legrand', 'Lucas', '107 rue de Lille, 75007 PARIS'),
   ('julienne.jeremi@hotmail.fr', 'Jeremi', 'Julienne', '146 rue de Lyon, 75012 PARIS'))
	AS source (Email, Nom, Prenom, Adresse)
ON (target.Nom = source.Nom AND target.Prenom = source.Prenom)
WHEN MATCHED
	THEN UPDATE
		SET target.Email = source.Email
WHEN NOT MATCHED BY target
	THEN INSERT (Nom, Prenom, Email, Adresse)
	VALUES (source.Nom, source.Prenom, source.Email, source.Adresse);

-- Ou, en utilisant une structure (temporaire ou non) existante
	
CREATE TABLE #temp (
   Nom varchar(50) NULL,
   Prenom varchar(50) NULL,
   Email varchar(50) NULL,
   Adresse varchar(250) NULL)

INSERT INTO #temp
VALUES 
   ('lucas.legrand@live.com', 'Legrand', 'Lucas', '107 rue de Lille, 75007 PARIS'),
   ('julienne.jeremi@hotmail.fr', 'Jeremi', 'Julienne', '146 rue de Lyon, 75012 PARIS');
-- A noter : le ";" pour séparer les deux instructions
MERGE Client AS target
USING (SELECT Email, Nom, Prenom, Adresse FROM #temp) AS source
ON (target.Nom = source.Nom AND target.Prenom = source.Prenom)
WHEN MATCHED
	THEN UPDATE
		SET target.Email = source.Email
WHEN NOT MATCHED BY target
	THEN INSERT (Nom, Prenom, Email, Adresse)
	VALUES (source.Nom, source.Prenom, source.Email, source.Adresse);
	
DROP TABLE #temp

-- Fonctionne aussi avec les CTE
WITH clients AS (SELECT TOP 10 Nom, Prenom, Email, Adresse FROM Client)
MERGE Client AS target
USING (SELECT Email, Nom, Prenom, Adresse FROM clients) AS source
ON (target.Nom = source.Nom AND target.Prenom = source.Prenom)
WHEN MATCHED
	THEN UPDATE
		SET target.Email = source.Email
WHEN NOT MATCHED BY target
	THEN INSERT (Nom, Prenom, Email, Adresse)
	VALUES (source.Nom, source.Prenom, source.Email, source.Adresse);

Je ne vais pas détailler les trois blocs puisqu’ils font des choses équivalents, mais attirer l’attention sur le premier et en particulier les lignes 7, et 10.

Pour la ligne 6 : c’est une jointure tout ce qu’il y a de plus banal, comme celle que l’on peut réaliser avec l’instruction « JOIN » (ou « INNER JOIN », c’est la même chose).

Pour les lignes 7 et 10 : on peut voir qu’il est possible de réaliser des actions si les lignes correspondent à la jointure.

Ainsi, il est possible de faire des actions :

  • Si elles correspondent : « WHEN MATCHED », il est alors possible d’utiliser les instructions UPDATE ou DELETE
  • Si elles ne correspondent pas : « WHEN NOT MATCHED BY « , il est alors possible d’utiliser les instructions UPDATE et DELETE

Par défaut, l’action dans le « NOT MATCHED » s’applique à ce qui est spécifié après le « BY ».
Ainsi, il ne faut pas non plus le spécifier par la suite.

Note : dans le 2nd cas, il FAUT ajouter le point-virgule pour séparer l’instruction MERGE de la création/alimentation de la table temporaire.
Si ce n’est pas fait, vous aurez droit à une erreur :

Msg 325, Level 15, State 1, Line 6
Syntaxe incorrecte près de 'MERGE'. Vous devrez peut-être affecter au niveau de compatibilité de la base de données actuelle une valeur plus élevée pour activer cette fonctionnalité. Reportez-vous à l'aide relative à l'option SET COMPATIBILITY_LEVEL de ALTER DATABASE.
Msg 156, Level 15, State 1, Line 7
Syntaxe incorrecte vers le mot clé 'AS'.
Catégories :Développement, Sql Server

SQL-Server – RANK et ROW_NUMBER

Récemment, j’ai du faire un petit exercice relativement simple : importer des données en base depuis un fichier CSV.
Avec le bulk insert, c’est assez simple :

BULK INSERT #Bulk
FROM 'C:\Temp\FichierAImporter.csv' 
WITH 
(
	FIELDTERMINATOR = ';',
	ROWTERMINATOR = '\n',
	CODEPAGE = 'ACP',
	ROWS_PER_BATCH = 35000
)

Là où ça s’est compliqué, c’est la génération des identifiants fonctionnels avant leur insertion dans les « vraies » tables.

Pour simplifier un peu, voici le cas.
Je dois importer les nouvelles adresses de clients.
J’ai deux champs : Client et Adresse qui sont chargées depuis le fichier CSV.

Mais surtout, j’ai deux identifiants fonctionnels à créer.
Le premier est un identifiant interne, de la forme « FR00000000 » dont la partie numérique est incrémentée pour chaque entrée. Le prochain identifiant est lisible depuis une table.
Le second identifiant est externe, c’est un numérique incrémenté par client.
C’est à dire que le client #1 possèdant déjà 50 adresses, la suivants sera la 51, la prochaine pour le client #2 est la 6ème.

La bonne vieille méthode, c’est de faire des boucles pour alimenter tout le bazar.
Mais comme on est dans une base de données, c’est quand même nettement mieux de faire de l’ensembliste.
C’est là que RANK et ROW_NUMBER viennent à la rescousse !

Lire la suite…

Catégories :Développement, SGBDR, Sql Server

Copier des données d’une base à une autre

Récemment, j’ai eu besoin d’alimenter une base de données locale avec les données d’une autre base de données.
La contrainte est que la base de données source est un SQL Server 2008 R2, la base de données cible un SQL Server Express 2005.
Donc, on élimine de suite le backup/restore.

Après, il y a sans doute des outils ô combien puissant pour faire ça.
Mais…j’aime bien me faire les miens.

Du coup, j’ai réutilisé une couche d’accès aux données que j’avais déjà (que j’avais partiellement donné ici : [C# SqlServer] Moteur de requêtage) ainsi que le bulk insert (dont j’avais parlé ici [C#] Bulk Insert).

En modifiant le premier pour intégrer le deuxième, ça m’a donné le résultat escompté en un temps record.

Lire la suite…

Catégories :.Net, C#, Développement, Sql Server

SQL Server Profiler

Aujourd’hui, on va discuter d’un outil assez important que tous développeur qui peut être amené à faire du T-SQL (sous SQL Server) devrait connaître.
Bon, je dis ça, mais y a pas si longtemps, je ne le connaissais pas.
Mais c’est un outil ô combien pratique qui peut permettre de surveiller ce qui se passe sur une base de données et aider à résoudre des problèmes de performances. Et, accessoirement, il est plus « humainement » lisible qu’un plan d’exécution (enfin, ça, c’est mon avis…).

Bref, c’est outil, c’est SQL Server Profiler (oui, le titre spolait déjà le billet !).

Lire la suite…

Catégories :Développement, SGBDR, Sql Server

[C#] Bulk Insert

Voici la problématique que j’ai eu récemment : pour mes tests automatisés, j’ai besoin d’insérer des données.
Jusque là, tout va bien.
Pour des tests de charge, j’ai besoin de beaucoup de données.

Sur le ring, j’ai donc :
Côté droit, ma table, près de 70.000.000 de lignes, plus de 80 colonnes.
Côté gauche, mes données, presque 5.000 lignes à insérer.

A la base, mon moteur de gestion de base de données gère les insertions ligne par ligne (il n’a pas été fait, à l’origine, pour du test de charge).
Du coup, pour plusieurs lignes, c’est géré via une basique boucle.
Mais voilà, avec cette méthode, j’ai un rythme d’environ 500 lignes par minute.
Pas terrible, terrible…

Alors, comment faire ?
Le Bulk Insert à la rescousse !

Lire la suite…

Catégories :.Net, C#, Développement, Sql Server

[C# SqlServer] Moteur de requêtage

Dans le cadre de mes tests unitaires, je dois exploiter une base de données.
Comprendre que testant des scénarios, je dois vérifier les données qui sont insérées en base et valider leur cohérence par rapport aux données fournies en entrée.
Accessoirement, je dois aussi exporter les données afin de pouvoir les retrouver facilement et ne pas me retrouver comme un couillon devant la base de développement nouvellement rechargée depuis la production et me dire que je voudrais bien comparer avant / après.

Dans ce billet, je vais donner le code d’un que j’ai utilisé pour exécuter mes requêtes, donc ça va être un peu brut.

Lire la suite…

Catégories :.Net, SGBDR, Sql Server

[SQLServer] Quelques tables systèmes

A l’instar du billet sur les tables systèmes de Sybase, voici un billet autour des tables systèmes de SQL Server.
Vu les éléments similaires, je vais passer assez vite, ceci dit (j’avoue, ce billet va me servir pour mémoire :)).

Pour obtenir la source d’un élément (procédure stockée, par exemple), c’est la même requête :

SELECT com.text AS TEXT 
FROM dbo.sysobjects obj 
INNER JOIN dbo.syscomments com 
    ON com.id = obj.id 
INNER JOIN dbo.sysusers users ON users.uid = users.uid
WHERE obj.name LIKE '<mon élément>' 
AND obj.type = 'P'  -- facultatif
AND users.name = 'dbo' 
ORDER BY obj.name, com.colid

Idem pour rechercher une chaîne, donc :

SELECT 
    obj.name AS 'PROC',
    com.text AS TEXT 
FROM dbo.sysobjects obj 
INNER JOIN dbo.syscomments com 
    ON com.id = obj.id 
INNER JOIN dbo.sysusers users ON users.uid = users.uid
WHERE obj.type = 'P' 
AND lower(com.text) LIKE lower('%TODO%')
AND users.name = 'dbo' 
ORDER BY obj.name, com.colid

Pour avoir des informations sur une table, la requête est un peu différente et remonte plus d’informations que celle de Sybase (comprendre que j’avais besoin de plus d’infos, donc j’ai poussé un peu le sujet !) :

SELECT cols.name AS Column_name, 
    types.name AS Type, 
    CASE WHEN cols.collation_name IS NOT NULL THEN cols.max_length ELSE cols.precision END AS Prec,
    cols.scale AS Scale,
    cols.is_identity AS Is_Identity,
    cols.is_computed AS Is_Computed,
    ISNULL(IsPrimaryKey, 0) AS Is_PrimaryKey,
    cols.is_nullable AS Is_Nullable
FROM sys.columns cols
INNER JOIN sys.types types
    ON types.system_type_id = cols.system_type_id
LEFT JOIN (
    SELECT  kcu.table_name as TABLE_NAME,
            kcu.column_name as COLUMN_NAME,
            1 AS IsPrimaryKey
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS  tc
    ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
) keys
    ON keys.COLUMN_NAME = cols.name
    AND keys.TABLE_NAME = Object_Name(cols.object_id)
WHERE Object_Name(cols.object_id) = '<TABLE>'
ORDER BY column_id

Ceci dit, le champ type de la table sysobjects comprends plus de valeurs possibles (24 en tout) que pour Sybase.
Là, je renvois directement vers la site de la MSDN pour plus d’informations : table sysobjects.

Comme on peut le voir, Sybase et SQL Server sont quand même assez proches l’un de l’autre.

Mais l’avantage de SQL Server, c’est quand même aussi SQL Server Management Studio.
Par exemple, il est possible de voir la liste des vues systèmes (Bases de données\Vues\Vues Systèmes), des fonctions prédéfinies (Bases de données\Programmabilité\Fonctions) ou des types ((Bases de données\Programmabilité\Types).
Et puis, la MSDN aussi, aide beaucoup, par exemple avec la liste des tables systèmes détaillées : Compatibility Views (Transact-SQL).

Donc, au final, ma préférence va à SQL Server. D’autant plus que son intégration est bien plus poussée dans l’écosystème Microsoft !

Catégories :SGBDR, Sql Server