Disabling clustered index blocks access to underlaying table

Did you know that disabling a clustered index actually blocks the access to the underlying table? Below is a script you can use to test it, if you do not believe me :)

--First create the test table with a clustered index

USE [FTest]
GO

/****** Object:  Table [dbo].[FTable]    Script Date: 11/21/2012 2:02:56 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FTable](
	[FTableID] [int] IDENTITY(1,1) NOT NULL,
	[LargeString] [nvarchar](600) NULL,
	[LargeInt] [bigint] NULL,
 CONSTRAINT [clIX_PK_FTable_FTableID] PRIMARY KEY CLUSTERED 
	(
		[FTableID] ASC
	) ON [PRIMARY]
) ON [PRIMARY]

GO

--now test the insert of one row

INSERT INTO [dbo].[FTable] ([LargeString],[LargeInt]) VALUES('This is a test row','123')
GO

--disable the clustered index

ALTER INDEX [clIX_PK_FTable_FTableID] 
ON [dbo].[FTable] DISABLE
GO

--try inserting one more row. Note the error: "The query processor 
--is unable to produce a plan because the index 'clIX_PK_FTable_FTableID' 
--on table or view 'FTable' is disabled." 

INSERT INTO [dbo].[FTable] ([LargeString],[LargeInt]) VALUES('This is a test row','123')
GO

--rebuild the clustered index so the table to be accessible again
ALTER INDEX [clIX_PK_FTable_FTableID] ON [dbo].[FTable] REBUILD PARTITION = ALL 
GO

--insert one more row to validate the access

INSERT INTO [dbo].[FTable] ([LargeString],[LargeInt]) VALUES('This is a test row','123')
GO

If you go and try to disable the clustered index from SSMS you even got a nice warning message:

Probably you are wondering why is that – well, as you know, the clustered index is actually containing the table data in its leaf level, so if you disable it, the SQL Server Query Optimizer will not be able to use this very index for creating a plan to access the data, which ultimately prevents the access to the table itself.

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.