SQL SERVER – Disable Clustered Index and Data Insert

Earlier today, I received following email.

“Dear Pinal,

We looked at your script and found out that in your script of disabling indexes, you have only included selected non-clustered index during the bulk insert and missed to disabled all the clustered index. Our DBA [name removed] has changed your script a bit and included all the clustered indexes. Since then our application is not working.

When DBA [name removed] tried to enable clustered indexes again he is facing error Incorrect syntax error.

We are in deep problem [word replaced]

[Removed Identity of organization and few unrelated stuff ]“

I have replied to my client and helped them fixed the problem. However, what really caught my attention was the concept of disabling clustered index. Let us try to learn a lesson from this experience.

In this case, there was no need to disable clustered index at all. I had done all the crucial work when I was called in to work on the tuning project. I removed unused indexes, created a few optimal indexes and wrote a script to disable selected high cost indexes when bulk insert operations (and the like) are performed. There was another script which rebuilds all the indexes as well. The solution worked until they included a clustered index in order to disable the script.

Clustered indexes are in fact original tables (or heap) which are physically ordered (any more things – not scope of this article) according to one or more keys (columns). When a clustered index is disabled, its data rows  cannot be accessed. This means that there will be no insertion process possible. On the other hand, when non-clustered indexes are disabled, all the data related to it are physically deleted, but the definition of the index is kept in the system.

Due to the same reason, even reorganization of the index is not possible until the clustered index (which was disabled) is rebuilt. Now, let us come to the second part of the question which is in regards to the receiving of the error when a clustered index is ‘enabled’. This is a very common question that I receive on the blog. (The following statement is written keeping the syntax of T-SQL in mind) Clustered indexes can be disabled but cannot be enabled again; they have to be rebuilt to become enabled. It is indeed a common thinking that something which we have ‘disabled’ can be ‘enabled’ but the syntax for this is ‘rebuild’. This issue has been explained here: SQL SERVER – How to Enable Index – How to Disable Index – Incorrect syntax near ‘ENABLE’.

Let us go over this example where inserting the data is not possible when a clustered index is disabled.

USE AdventureWorks
GO
-- Create Table
CREATE TABLE [dbo].[TableName](
[ID] [int] NOT NULL,
[FirstCol] [varchar](50) NULL,
CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
([ID] ASC)
)
GO
-- Create Nonclustered Index
CREATE UNIQUE NONCLUSTERED INDEX [IX_NonClustered_TableName] ON [dbo].[TableName]
([FirstCol] ASC)
GO
-- Populate Table
INSERT INTO [dbo].[TableName]
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third'
GO
-- Disable Nonclustered Index
ALTER INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] DISABLE
GO
-- Insert Data should work fine
INSERT INTO [dbo].[TableName]
SELECT 4, 'Fourth'
UNION ALL
SELECT 5, 'Fifth'
GO
-- Disable Clustered Index
ALTER INDEX [PK_TableName] ON [dbo].[TableName] DISABLE
GO
-- Insert Data will fail
INSERT INTO [dbo].[TableName]
SELECT 6, 'Sixth'
UNION ALL
SELECT 7, 'Seventh'
GO
/*
Error: Msg 8655, Level 16, State 1, Line 1
The query processor is unable to produce a plan because the index 'PK_TableName' on table or view 'TableName' is disabled.
*/
-- Reorganizing Index will also throw an error
ALTER INDEX [PK_TableName] ON [dbo].[TableName] REORGANIZE
GO
/*
Error: Msg 1973, Level 16, State 1, Line 1
Cannot perform the specified operation on disabled index 'PK_TableName' on table 'dbo.TableName'.
*/
-- Rebuliding should work fine
ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD
GO
-- Insert Data should work fine
INSERT INTO [dbo].[TableName]
SELECT 6, 'Sixth'
UNION ALL
SELECT 7, 'Seventh'
GO
-- Clean Up
DROP TABLE [dbo].[TableName]
GO

I hope this example is clear enough. There were a few additional posts I had written years ago, and they are as follows:

SQL SERVER – Enable and Disable Index Non Clustered Indexes Using T-SQL

SQL SERVER – Enabling Clustered and Non-Clustered Indexes – Interesting Fact

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – Disable Clustered Index and Data Insert

  1. Hi Pinal Dave,
    I am very happy to write you. I would like to know the Interview questions and answers. I would like to be in touch with you online, as i am new in SQL server and i have a dream to make a career in it though i am not very good programmer.
    I need your assistance in case if i required, i will write you personally when ever i required. Can u help me. please send some pdf file or other format files which will help for my prepearation of interview.

    krishna
    mumbai

  2. In whole example you did not show/explain the error message that shows ‘Incorrect syntax error…’. If you can explain that situation that will be great

  3. Pingback: SQL SERVER – Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index Journey to SQL Authority with Pinal Dave

  4. Pingback: SQL SERVER – Disabled Index and Update Statistics Journey to SQL Authority with Pinal Dave

  5. Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave

  6. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31 Journey to SQLAuthority

  7. Pingback: SQL SERVER – Difference between Create Index – Drop Index – Rebuild Index – Quiz – Puzzle – 21 of 31 « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

  9. Hi Pinal,

    I am a regular follower of your blog. Appreciate your contribution.
    I tried above example. After rebuilding the clustered index, when I query the table I do not see the rows being retrieved in sorted order, on SELECT. I believe SQL server should rearrange the sort order of those data that were inserted when clustered index was disabled.

    type_desc is_disabled
    CLUSTERED 0

    Please help me understand this scenario.

  10. Hello everyone,

    Good article.

    I have a problem and I need expert advise for.

    I have an update which take too long for updating one table has 40 million rows to update 6 million rows.

    I set all index disabled before this update. and then rebuild all indexes on this table.

    Performance enhanced for update procedure but rebuilding index become too slow after being disabled.

    I have Auto update and creation for statistics ON on database level

    Automatic update statistics: ON
    Automatic create statistics: ON

    And i have 64 statistics on this table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s