Dans cet article, je vais présenter des trucs et astuces SQL serveur qui peuvent nous servir dans notre programmation SQL de tous les jours.

Dans ma mission actuelle, j’ai fait à la fois du développement web et du développement pur SQL sous SQL serveur 2008. J’ai, par conséquent, passé beaucoup de temps à faire/corriger des requêtes et manipuler des bases de données ayant plus que 200 Tables.

Je vais partager avec vous quelques trucs et astuces acquis lors de mes développements et mes recherches.

Requêtes Utiles :

1. Récupération de la clé primaire d’une table :

Select column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 
AND table_name = 'REF_UTILISATEURS'

“REF_UTILISATEURS” est le nom de notre table.

2. Récupération de la liste des serveurs :

USE Master
SELECT	*
FROM dbo.sysservers

2. Création d’un profil utilisateur pour accéder à la base de données:

exec sp_addlinkedsrvlogin 'MySQLServer','false','usr_Sql','usr_Sql','password'

3. Récupération de toutes les tables contenant une colonne spécifique :

select * from sysobjects 
where id in(select id from syscolumns where name like '%UTIL_ID%') 
and xtype='u'

« UTIL_ID » est le nom de la colonne.

4. Modifier le type d’une colonne d’une table :

ALTER TABLE [dbo].[FACT_PROJET]
ALTER COLUMN [LABEL_PROJET] varchar(500)

5. Récupération de la liste des tables dans la base de données :

select * from sys.tables where is_ms_shipped='0'

6. Récupération des tables( et les colonnes) ayant une colonne avec « Set Identity » dans notre BD :

select COLUMN_NAME, TABLE_NAME  
from INFORMATION_SCHEMA.COLUMNS  
where TABLE_SCHEMA = 'dbo'  
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1  
order by TABLE_NAME

Le résultat ressemble à ceci :

7. Récupération du nombre de données dans chaque table de notre base de données :

SELECT  
    sysobjects.Name  
    , sysindexes.Rows  
FROM  
    sysobjects  
    INNER JOIN sysindexes  
    ON sysobjects.id = sysindexes.id  
WHERE  
    type = 'U'  
    AND sysindexes.IndId < 2  
ORDER BY  
    sysobjects.Name

Le résultat ressemble à ceci :

ou alors récupérer le nombre de lignes, la mémoire utilisée, les données en « kb » et le nombre des indexes de la table, les données récupérées nous permettront d’analyser notre base :

select nomTable= object_name(p.object_id),
        lignes = sum(
                CASE
                    When (p.index_id < 2) and (a.type = 1) Then p.rows
                    Else 0
                END
            ),
        'mémoire (kb)' = ltrim(str(sum(a.total_pages)* 8192 / 1024.,15,0)) ,
        'données (kb)' = ltrim(str(sum(
                CASE
                    When a.type <> 1 Then a.used_pages
                    When p.index_id < 2 Then a.data_pages
                    Else 0
                END
            ) * 8192 / 1024.,15,0)),
        'indexs (kb)' = ltrim(str((sum(a.used_pages)-sum(
                CASE
                    When a.type <> 1 Then a.used_pages
                    When p.index_id < 2 Then a.data_pages
                    Else 0
                END) )* 8192 / 1024.,15,0))
       
    from sys.partitions p, sys.allocation_units a ,sys.sysobjects s
    where p.partition_id = a.container_id
        and p.object_id = s.id and s.type = 'U'    -- tables de type Utilisateur (exclusion des tables système)
    group by p.object_id
    order by lignes desc

Le résultat de notre requête sera comme suit:

8. Trouver les relations entre deux bases de données :

SELECT f.name AS ForeignKey  
FROM sys.foreign_keys AS f where f.name not in  
(SELECT f.name AS ForeignKey  
FROM AQUAVIT.sys.foreign_keys AS f)order by f.name asc

La relation entre la base « REFERENCE » et la base « AQUAVIT » dans notre exemple donne ceci :

9. Trouver les relations entre les tables : Très pratique si on se retrouve à manipuler une énorme base de données qu’on ne connait pas le diagramme des relations entre les tables :

SELECT f.name AS ForeignKey,  
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,  
OBJECT_NAME(f.parent_object_id) AS TableName,  
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,  
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,  
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,  
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName  
FROM sys.foreign_keys AS f  
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id  
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

Le résultat de la requête ressemble à ceci :

10. Rechercher dans une procédure stockée le champ « UTIL_ID » :

SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%UTIL_ID%'

Cette requête nous retourne toutes les procédures stockées qui contiennent ce champ :

11. Transformer une chaîne de caractère séparée par un caractère spécial (virgule, point-virgule ou autre) en une table de données. Cette fonction est très utile et je l’ai utilisé dans pas mal de projets.

CREATE FUNCTION [dbo].[FACT_GET_STRING_LISTE_INTO_TABLE]
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
@ParsedList TABLE
(
item VARCHAR(MAX)
)
AS
BEGIN
	DECLARE @item VARCHAR(MAX), @Pos INT
	SET @List = LTRIM(RTRIM(@List))+ @Delim
	SET @Pos = CHARINDEX(@Delim, @List, 1)
	WHILE @Pos > 0
	BEGIN
	SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
	IF @item <> ''
	BEGIN
	INSERT INTO @ParsedList (item)
	VALUES (CAST(@item AS VARCHAR(MAX)))
	END
	SET @List = RIGHT(@List, LEN(@List) - @Pos)
	SET @Pos = CHARINDEX(@Delim, @List, 1)
	END
	RETURN
END

Best Practices :

12. Utiliser le plan d’exécution graphique « Execution plan » qui permet d’analyser une requête et les sous requêtes et donner une information sur le temps d’exécution de chacune.
    Le plan d’exécution est la première chose à regarder lorsqu’on recherche la cause des requêtes lentes. Penser à l’utiliser si vous êtes sur une tâche d’optimisation des requêtes.

13. Commenter le code SQL : c’est très pratique pour vous et pour toute l’équipe.

a. Donner une petite description au début de la procédure stockée expliquant ce qu’elle fait.

---------------------------------------------------------------------------------------
-- Procedure  : REF_PRC_BSE_GET_USER_ENTITIES
-- Auteur     : DPA / RF
-- Date	      :10/10/2014
-- Traitement : Retourne le plus haut rattachemant d'un utilisateur selon son profil dans BSE
--              selon ses rattachements et selon qu'il appartient 
--              a une hierarchie operationnelle ou fonctionnelle ou mixte
--              Dans le cas d'une assistante DR, on retourne la DR correspondant Ë son code FR
-- Parametres : -@user_domain: Domaine NT 
--            : -@user_login: login NT 
---------------------------------------------------------------------------------------

b. Commenter les blocs de codes importants et que vous voyez critiques dans la compréhension de la logique de la T-SQL.

--1 - Declarations des variables
  set nocount on
  declare @v_info_msg varchar(300)
  declare @v_err_msg varchar(300)
  declare @v_err_cod varchar(5)
  declare @v_procedure varchar(50)
  declare @v_tache varchar(300)
  declare @v_tache_cod varchar(25)

--2 - Initialisation des variables
set @v_tache = replace(dbo.REF_FNC_Get_InfoMessage('T04'), '<flux>', @v_table_tr)
set @v_info_msg = replace(replace(replace(dbo.REF_FNC_Get_InfoMessage('L01'), '<traitement>', @v_tache), '<date>', convert(varchar, GetDate(), 103)), '<heure>', convert(varchar, GetDate(), 108))

--3 - Selection des profils

  select distinct V.Profil_cod, V.PAYS_COD,

  case V.hier_type
    -- Operationnel on genere le code selon le TYPR_COD 
    when 1 then (select case V.TYPR_COD
                   when 0 then 'PAYS'
                   when 1 then 'SEGM'
                   when 2 then 'DIVI'

14. Utiliser Temp Tables( #temp Tables) à la place des Curseurs :
    Les #temp sont des Tables SQL qui sont définies et stockées dans TempDb , la seule différence avec d’autres tables, c’est qu’elles ne sont pas permises d’avoir des clés étrangères. Quand vous manipulez des données importantes, ligne par ligne, il est conseillé d’utiliser des requêtes Select et des tables temporaires au lieu de passer par des curseurs.

15. Essayer d’utiliser les « Constraints » au lieu des « Triggers », toute fois ceci est possible, Ils sont beaucoup plus performants que les triggers.

16. Essayer de ne pas utiliser la clause « Distinct » quand c’est possible, parce qu’elle peut causer une dégradation de performance.

17. Si vous voulez avoir le nombre des données dans une table, utiliser la requête suivante mieux que d’utiliser un Select Count(*) qui scanne toute les données de la table avant de retourner le résultat.

SELECT rows 
FROM sysindexes WHERE id= OBJECT_ID('REF_UTILISATEURS') and indid < 2

18. Dans la procédure stockée, Utiliser le « SET NOCOUNT ON » pour stopper la réception des messages indiquant le nombre des lignes affectées par notre requête. Ceci peut réduire le trafic réseau.

20. Utiliser des index mais ne pas en abuser ( 4/5 index par table maximum), ça dégrade la performance d’exécution de la requête surtout pour la mise à jour, suppression et insertion.

20. Indexer plutôt les colonnes de type « int » que « varchar ».

21. Ajouter des clauses « WHERE » autant que vous pouvez, ceci permet de filtrer les données et réduire le nombre des résultats trouvés et donc augmenter les performances.