Understanding Disable Index

Understanding Disable Index

-- 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' UNION ALL SELECT 6, 'Fifth'

GO

-- Verify Data

Select * from TableName

-- Disable Clustered Index

ALTER INDEX [PK_TableName] ON [dbo].[TableName] DISABLE

GO

-- Insert Data will fail

INSERT INTO [dbo].[TableName] SELECT 7, 'Seventh' UNION ALL SELECT 8, 'Eighth'

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 7, 'Seventh' UNION ALL SELECT 8, 'Eighth'

GO

-- Clean Up

DROP TABLE [dbo].[TableName] GO

 

--DB