How to disable an index in SQL Server? To disable an index for performance reasons, use this code and adjust it to your index and table names. <\/p>\n\n\n\n
For instance, let’s consider a large sales table, with million lines. Let’s consider that the table has a non-unique and non-clustered index on the month.<\/p>\n\n\n\n
First, why it is better to disable an index on a given table? <\/p>\n\n\n\n
In other words, to insert data faster in the table, it’s much better to disable this index. For instance, let’s use the same index from this database tutorial on how to create a SQL Server index<\/a><\/strong>, and then disable it.<\/p>\n\n\n\n The goal is to explicitly disable the index usage on the table by the Relational Database Management System (RDMS).<\/p>\n\n\n\n After been disabled, the index is not used anymore, and it need to be rebuilt to be enabled again<\/strong>. <\/p>\n\n\n\n To sum up, more insights on disabling the indexes and SQL constraints<\/a> in the official MS database design documentation. <\/p>\n\n\n\n In order to check all indexes on a database, it possible to use the SQL query from this IT tutorial on how list all SQL Server indexes from a database<\/a><\/strong>.<\/p>\n\n\n\nALTER INDEX indexYearMonth \n\tON [dbo].[SALES] DISABLE;<\/pre>\n\n\n\n