SQL Server – Disable Clustered Index and Data Insert

Send Us a Sign! (Contact Us!)

--> (Word)	--> (PDF)	--> (Epub)	--> (Text)
--> (XML)	--> (OpenOffice)		--> (XPS)

Show More Info

This article has been read [post_view] times. This article has been read [post_view time="day"] times today. This article has been read [post_view time="month"] times this month. This article has been read [post_view time="year"] times this year. This article has 517 total words. (Including header) This article has been updated on [rup_display_update_date] [tweet]

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’.

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’.

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.

SOURCE	LINK (Sqlauthority.com)	LANGUAGE	ENGLISH