{"id":1292,"date":"2016-04-03T11:28:53","date_gmt":"2016-04-03T09:28:53","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=1292"},"modified":"2020-09-01T10:07:52","modified_gmt":"2020-09-01T08:07:52","slug":"ajouter-les-index-manquants-pour-les-cles-etrangeres","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/ajouter-les-index-manquants-pour-les-cles-etrangeres\/","title":{"rendered":"Ajouter les index manquants pour les cl\u00e9s \u00e9trang\u00e8res"},"content":{"rendered":"<p>Dans la suite de l&rsquo;article <a href=\"https:\/\/www.sqlserver.fr\/blog\/index-manquants-pour-les-cles-etrangeres\/\" target=\"_blank\" rel=\"noopener noreferrer\">Index manqants pour les cl\u00e9s \u00e9trang\u00e8res<\/a>, il convient de prendre en main la liste (\u00e9ventuellement longue) des cl\u00e9s \u00e9trang\u00e8res list\u00e9es et de chercher \u00e0 constituer des index autour de ce besoin. Voici un petit compl\u00e9ment \u00e0 l&rsquo;article, qui pr\u00e9sente un script donnant en plus des instructions de cr\u00e9ation d&rsquo;index.<\/p>\n<p><!--more-->Je tiens \u00e0 bien pr\u00e9ciser que les instructions propos\u00e9es ne sont pas n\u00e9cessairement celles que vous devrez passer sur votre base de donn\u00e9es, notamment vis-\u00e0-vis des points suivants :<\/p>\n<ul>\n<li>L&rsquo;analyse des plans d&rsquo;ex\u00e9cution des requ\u00eates mettra peut-\u00eatre en \u00e9vidence le besoin de cr\u00e9er des index \u00e0 plusieurs colonnes (ou avec des colonnes incluses) qui contiendraient certains index minimalistes associ\u00e9s aux cl\u00e9s \u00e9trang\u00e8res<\/li>\n<li>Les instructions de cr\u00e9ation propos\u00e9es sont minimalistes. Elles ne tiennent notamment pas compte du positionnement des index sur un autre FileGroup ni d&rsquo;un \u00e9ventuel partitionnement. Elles ne pr\u00e9cisent pas non plus le taux de remplissage ou autres param\u00e8tres des index<\/li>\n<li>La redondance de cl\u00e9s \u00e9trang\u00e8res pourra amener \u00e0 la cr\u00e9ation d&rsquo;index en doublons. Pour d\u00e9tecter ce point, je vous invite \u00e0 consulter <a href=\"https:\/\/www.sqlserver.fr\/blog\/doublons-dindex\/\" target=\"_blank\" rel=\"noopener noreferrer\">cet article<\/a>.<\/li>\n<\/ul>\n<p>Voici donc le script compl\u00e9t\u00e9 (une colonne de plus dans la liste finale) :<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">-- Liste des cl\u00e9s \u00e9trang\u00e8res avec index absent\r\n-- Avec g\u00e9n\u00e9ration d'instructions de cr\u00e9ation d'index\r\n-- Auteur : Jean-Nicolas BERGER (www.sqlserver.fr)\r\n-- Declaration des variables\r\nDECLARE @FK TABLE (fk_id int,\r\n\t\t\t\t\tcolonnes varchar(MAX) DEFAULT '',\r\n                    noms_colonnes varchar(MAX) DEFAULT NULL)\r\nDECLARE @Index TABLE (object_id int, \r\n\t\t\t\t\tindex_id int,\r\n\t\t\t\t\tcolonnes varchar(MAX) DEFAULT '')\r\nDECLARE @id1 int, @id2 int, @id3 int\r\nDECLARE @nom_colonne varchar(MAX)\r\n\r\n-- Initialisation\r\nINSERT INTO @FK (fk_id)\r\nSELECT fk.object_id\r\nFROM sys.foreign_keys fk\r\n\r\nINSERT INTO @Index (object_id,index_id)\r\nSELECT i.object_id,\r\n       i.index_id\r\nFROM sys.indexes i\r\n\r\n-- Listes des colonnes des FK\r\nDECLARE fk CURSOR FOR\r\nSELECT fkc.constraint_object_id,\r\n       fkc.parent_column_id,\r\n       c.name\r\nFROM sys.foreign_key_columns fkc\r\nJOIN sys.columns c ON c.object_id=fkc.parent_object_id\r\nAND c.column_id=fkc.parent_column_id\r\nORDER BY fkc.constraint_column_id OPEN fk FETCH NEXT\r\nFROM fk INTO @id1,\r\n             @id2,\r\n             @nom_colonne\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN\r\n\r\n\tUPDATE @Fk\r\n\tSET colonnes+=convert(varchar(MAX),@id2)+',',\r\n\t\tnoms_colonnes=isnull(noms_colonnes+',','')+QUOTENAME(@nom_colonne)\r\n\tWHERE fk_id=@id1 FETCH NEXT\r\n\t  FROM fk INTO @id1,\r\n\t\t\t\t   @id2,\r\n\t\t\t\t   @nom_colonne\r\n\r\nEND\r\nCLOSE fk\r\nDEALLOCATE fk\r\n\t\t\t   \r\n-- Liste des colonnes des index\r\nDECLARE ind CURSOR FOR\r\n  SELECT ic.object_id,\r\n         ic.index_id,\r\n         ic.column_id\r\n  FROM sys.index_columns ic WHERE key_ordinal&gt;0\r\n  ORDER BY ic.key_ordinal\r\nOPEN ind\r\nFETCH NEXT FROM ind INTO @id1,@id2,@id3\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN\r\n\r\n\tUPDATE @Index\r\n\tSET colonnes+=convert(varchar(MAX),@id3)+','\r\n\tWHERE object_id=@id1 AND index_id=@id2\r\n\r\nFETCH NEXT FROM ind INTO @id1,@id2,@id3\r\nEND\r\nCLOSE ind\r\nDEALLOCATE ind\r\n\t\t\t\t\r\n-- FK sans index (avec script de cr\u00e9ation d'index)\r\nSELECT DISTINCT QUOTENAME(s.name)+'.'+QUOTENAME(t.name) AS [Table parente],\r\n                  QUOTENAME(s_r.name)+'.'+QUOTENAME(t_r.name) AS [Table de r\u00e9f\u00e9rence],\r\n                  QUOTENAME(fk.name) AS [Cl\u00e9 \u00e9trang\u00e8re],\r\n                  'CREATE INDEX ' + QUOTENAME('IX_' + fk.name) + ' ON ' + QUOTENAME(s.name)+'.'+QUOTENAME(t.name) + '(' + fk_col.noms_colonnes+');' AS [Cr\u00e9ation Index]\r\n  FROM sys.foreign_keys fk\r\n  JOIN @FK fk_col ON fk_col.fk_id=fk.object_id\r\n  JOIN sys.tables t ON t.object_id=fk.parent_object_id\r\n  JOIN sys.schemas s ON s.schema_id=t.schema_id\r\n  JOIN sys.tables t_r ON t_r.object_id=fk.referenced_object_id\r\n  JOIN sys.schemas s_r ON s_r.schema_id=t_r.schema_id\r\n  LEFT JOIN @Index ind ON ind.object_id=t.object_id\r\n  AND ind.colonnes LIKE fk_col.colonnes+'%' WHERE ind.object_id IS NULL<\/pre>\n<p>En l&rsquo;\u00e9tat, ce script peut en tout cas \u00eatre pass\u00e9 sur une base de test copie de votre base principale, ne serait-ce que pour \u00e9valuer l&rsquo;espace de stockage suppl\u00e9mentaire n\u00e9cessaire ainsi que les impacts en termes de performance &#8230;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Dans la suite de l&rsquo;article Index manqants pour les cl\u00e9s \u00e9trang\u00e8res, il convient de prendre en main la liste (\u00e9ventuellement longue) des cl\u00e9s \u00e9trang\u00e8res list\u00e9es et de chercher \u00e0 constituer des index autour de ce besoin. Voici un petit compl\u00e9ment &hellip; <a href=\"https:\/\/www.sqlserver.fr\/blog\/ajouter-les-index-manquants-pour-les-cles-etrangeres\/\">Continuer la lecture <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-1292","post","type-post","status-publish","format-standard","hentry","category-outils"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/1292","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=1292"}],"version-history":[{"count":4,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/1292\/revisions"}],"predecessor-version":[{"id":1826,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/1292\/revisions\/1826"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=1292"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=1292"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=1292"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}