{"id":7343,"date":"2022-04-18T07:35:00","date_gmt":"2022-04-18T05:35:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=7343"},"modified":"2022-08-09T16:51:58","modified_gmt":"2022-08-09T14:51:58","slug":"desactiver-un-index-sql-server","status":"publish","type":"post","link":"https:\/\/expert-only.com\/fr\/t-sql\/desactiver-un-index-sql-server\/","title":{"rendered":"D\u00e9sactiver un index SQL server"},"content":{"rendered":"\n<p>Comment d\u00e9sactiver un index SQL Server ? Pour d\u00e9sactiver un index pour des raisons de performance, utiliser ce code et adaptez-le \u00e0 vos noms d&rsquo;index et de table. <\/p>\n\n\n\n<p>Par exemple, consid\u00e9rons une grande table de ventes, avec des millions de lignes. Consid\u00e9rons que la table a un index non-unique et non-cluster sur le mois.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-desactiver-un-index-sql-server-avec-un-script-t-sql\">D\u00e9sactiver un index SQL Server avec un script T-SQL<\/h2>\n\n\n\n<p>Tout d&rsquo;abord, dans quel cas est-il pr\u00e9f\u00e9rable de d\u00e9sactiver un index sur une table ?<\/p>\n\n\n\n<p>Il est conseill\u00e9 de d\u00e9sactiver un index SQL pour ins\u00e9rer plus rapidement les donn\u00e9es dans une table index\u00e9e.<\/p>\n\n\n\n<p>C&rsquo;est \u00e0 dire que l&rsquo;op\u00e9ration se fait en trois \u00e9tapes: <\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>D\u00e9sactiver l&rsquo;index<\/li><li>Ins\u00e9rer les nouvelles donn\u00e9es<\/li><li>Activer \u00e0 nouveau l&rsquo;index avec un script<\/li><\/ol>\n\n\n\n<p>Par exemple, voici un exemple de script T-SQL pour d\u00e9sactiver un index donn\u00e9. C&rsquo;est le m\u00eame index que dans <strong><a href=\"https:\/\/expert-only.com\/transact-sql\/creer-un-index-sql-server\/\">ce tutoriel pour cr\u00e9er un index SQL Server<\/a><\/strong> pas \u00e0 pas.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ALTER INDEX indexYearMonth \n\tON [dbo].[SALES] DISABLE ;\n<\/pre>\n\n\n\n<p>Ex\u00e9cuter cette commande MS SQL d\u00e9sactive donc l&rsquo;index sur la table des ventes. L&rsquo;index n&rsquo;est plus utilis\u00e9 et <strong>il doit \u00eatre reconstruit pour \u00eatre r\u00e9activ\u00e9<\/strong>, et non <strong><a href=\"https:\/\/expert-only.com\/transact-sql\/reorganiser-un-index-sql-server\/\">simplement r\u00e9organiser, comme expliqu\u00e9 pas \u00e0 pas dans ce tutoriel<\/a><\/strong>.<\/p>\n\n\n\n<p>En r\u00e9sum\u00e9, plus d&rsquo;informations sur la d\u00e9sactivation des index et des <a href=\"https:\/\/docs.microsoft.com\/fr-fr\/sql\/relational-databases\/indexes\/disable-indexes-and-constraints?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener\">contraintes MS SQL<\/a> dans la documentation officielle du SGBD de Microsoft.<\/p>\n\n\n\n<p>Voici enfin un <strong><a href=\"https:\/\/expert-only.com\/transact-sql\/creer-un-index-sql-server\/\">court tutoriel pour cr\u00e9er un index et l&rsquo;ajouter \u00e0 une table SQL<\/a><\/strong>. <\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-tutoriels-sql-et-it wp-block-embed-tutoriels-sql-et-it\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"XqqHItvM0q\"><a href=\"https:\/\/expert-only.com\/fr\/t-sql\/creer-un-index-sql-server\/\">Cr\u00e9er un index SQL Server avec un script<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"\u00ab\u00a0Cr\u00e9er un index SQL Server avec un script\u00a0\u00bb &#8212; Tutoriels SQL et IT\" src=\"https:\/\/expert-only.com\/fr\/t-sql\/creer-un-index-sql-server\/embed\/#?secret=zCKcxoBr3i#?secret=XqqHItvM0q\" data-secret=\"XqqHItvM0q\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>Comment d\u00e9sactiver un index SQL Server ? Pour d\u00e9sactiver un index pour des raisons de performance, utiliser ce code et adaptez-le \u00e0 vos noms d&rsquo;index et de table. Par exemple, consid\u00e9rons une grande table de ventes, avec des millions <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/fr\/t-sql\/desactiver-un-index-sql-server\/\" title=\"D\u00e9sactiver un index SQL server\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":{"0":"post-7343","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-t-sql"},"_links":{"self":[{"href":"https:\/\/expert-only.com\/fr\/wp-json\/wp\/v2\/posts\/7343","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/fr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/fr\/wp-json\/wp\/v2\/comments?post=7343"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/fr\/wp-json\/wp\/v2\/posts\/7343\/revisions"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/fr\/wp-json\/wp\/v2\/media?parent=7343"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/fr\/wp-json\/wp\/v2\/categories?post=7343"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/fr\/wp-json\/wp\/v2\/tags?post=7343"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}