Trouver et corriger les index uniques (non filtrés) sans contrainte

Publié le par

Exception faite des index unique filtrées, il n’y aucune différence en termes de performances entre l’ajout d’un index unique, et l’ajout d’une contrainte d’unicité (qui est elle-même supportée par un index unique).
La contrainte d’unicité permet de signifier que l’intégrité des données est le but, alors que le but premier d’un index est l’accélération des requêtes.
La majorité des index unique étant ajoutés pour renforcer l’intégrité des données, voici donc un script pour trouver tous les index unique non filtrés qui n’ont pas de contrainte d’unicité.

Ce script donne :

– le nom de la table
– le nom de l’index actuel
– la liste des colonnes clé de l’index
– le script T-SQL de normalisation du nom de la contrainte d’unicité et de suppression de l’index actuel.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-------------------------------
-- Nicolas Souquet - 2012-08-08
-------------------------------
DECLARE @cr char(2) = CHAR(13) + CHAR(10)
 
;WITH
  CTE AS
  (
    SELECT    S.name AS schema_name
        , T.name AS table_name
        , I.name AS index_name
        , LEFT(KC.key_column_list, LEN(KC.key_column_list) - 1) AS key_column_list
    FROM    sys.indexes AS I
    INNER JOIN  sys.tables AS T
          ON I.object_id = T.object_id
    INNER JOIN  sys.schemas AS S
          ON T.schema_id = S.schema_id
    CROSS APPLY  (
          SELECT    CS.name + ', '
          FROM    sys.columns AS CS
          INNER JOIN  sys.index_columns AS ICS
                ON CS.object_id = ICS.object_id
                AND CS.column_id = ICS.column_id
          WHERE    I.index_id = ICS.index_id
          AND    I.object_id = ICS.object_id
          AND    ICS.is_included_column = 0
          ORDER BY  ICS.index_column_id
          FOR    XML PATH ('')
        ) AS KC (key_column_list)
    WHERE    I.is_unique = 1 -- l'index est unique
    AND    I.is_primary_key = 0 -- il ne s'agit pas d'un index de clé primaire
    AND    T.is_ms_shipped = 0 -- il ne s'agit pas d'une table système
    AND    I.is_unique_constraint = 0 -- l'index n'a pas de contrainte
    AND    I.has_filter = 0 -- l'index n'est pas filtré
  )
SELECT  schema_name + '.' + table_name AS qualified_table_name
  , index_name AS current_index_name
  , key_column_list
  , 'ALTER TABLE ' + schema_name + '.' + table_name + @cr
    + 'ADD CONSTRAINT UQ_' + table_name + '__' + REPLACE(key_column_list, ', ', '__') + @cr
    + '  UNIQUE (' + key_column_list + ')' + @cr + 'GO' + @cr + @cr
    + 'DROP INDEX ' + QUOTENAME(index_name) + ' ON ' + schema_name + '.' + table_name + @cr + 'GO' + @cr + @cr
FROM  CTE
ORDER  BY table_name

Bonne gestion des index !

Laisser un commentaire