[转载]可恶SQL SERVER的索引Disable后不能直接ENABLE，只有rebuild后才OK!SQL SERVER – Disable Clustered Index and Data Insert

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

-- Reorganizing Index will also throw an error
ALTER INDEX [PK_TableName] ON [dbo].[TableName] REORGANIZE
GO

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