SQL Server: 索引禁用、启用状态对比测试

索引禁用备注：

1. 索引禁用会释放空间， 所以索引禁用只是保留了逻辑结构， 禁用再启用与新建索引都是申请新空间， 时间上差别不会太大（稍快一点）；

2. 聚集索引不能禁用， 否则select, insert 之类的操作都无法完成。提示：

查询处理器无法生成计划，因为表或视图 'xxx' 的索引 'PK_xxx' 被禁用。

脚本：

--创建新表 dbo.Person ， 不影响原来的表 Person.Person
SET NOCOUNT ON
IF OBJECT_ID('dbo.Person') IS NOT NULL
BEGIN
	DROP TABLE dbo.Person
END
GO
CREATE TABLE dbo.[Person](
	[BusinessEntityID] [int] NOT NULL,
	[PersonType] [nchar](2) NOT NULL,
	[NameStyle] [dbo].[NameStyle] NOT NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [dbo].[Name] NOT NULL,
	[MiddleName] [dbo].[Name] NULL,
	[LastName] [dbo].[Name] NOT NULL,
	[Suffix] [nvarchar](10) NULL,
	[EmailPromotion] [int] NOT NULL,
	[AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
	[Demographics] [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
	CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED 
	(
		[BusinessEntityID] ASC
	)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO dbo.[Person]
           ([BusinessEntityID]
           ,[PersonType]
           ,[NameStyle]
           ,[Title]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Suffix]
           ,[EmailPromotion]
           ,[AdditionalContactInfo]
           ,[Demographics]
           ,[rowguid]
           ,[ModifiedDate])
     SELECT [BusinessEntityID]
           ,[PersonType]
           ,[NameStyle]
           ,[Title]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Suffix]
           ,[EmailPromotion]
           ,[AdditionalContactInfo]
           ,[Demographics]
           ,[rowguid]
           ,[ModifiedDate] FROM Person.Person AS p
GO
--创建索引
CREATE NONCLUSTERED INDEX [IX_Person_LastName_FirstName_MiddleName2] ON dbo.[Person]
(
	[LastName] ASC,
	[FirstName] ASC,
	[MiddleName] ASC
)
GO

--------------------- 索引开启的情况下执行查询、删除 -----------------------
--查看索引是否禁用
SELECT OBJECT_ID,OBJECT_NAME(i.[object_id]) AS tableName, NAME, i.is_disabled 
FROM sys.indexes AS i WHERE i.[object_id]=OBJECT_ID('dbo.Person')
/*
1607676775	Person	PK_Person_BusinessEntityID_1	0
1607676775	Person	IX_Person_LastName_FirstName_MiddleName2	0
*/
--开启执行计划，查看
SELECT * FROM [dbo].[Person] p WHERE lastName='Tamburello'
GO
--开启执行计划，删除
DELETE FROM dbo.Person WHERE lastName='Tamburello' 

--------------------- 索引关闭的情况下执行查询、删除 -----------------------
--禁用索引
ALTER INDEX [IX_Person_LastName_FirstName_MiddleName2] ON [dbo].[Person] DISABLE
GO

--查看索引是否禁用
SELECT OBJECT_ID,OBJECT_NAME(i.[object_id]) AS tableName, NAME, i.is_disabled 
FROM sys.indexes AS i WHERE i.[object_id]=OBJECT_ID('dbo.Person')
/*
OBJECT_ID	tableName	NAME	is_disabled
1607676775	Person	PK_Person_BusinessEntityID_1	0
1607676775	Person	IX_Person_LastName_FirstName_MiddleName2	1
*/

--开启执行计划，查看
SELECT * FROM [dbo].[Person] p WHERE lastName='Galvin' 

--开启执行计划，删除
DELETE FROM dbo.Person WHERE lastName='Galvin' 


--重生成索引（启用索引）
ALTER INDEX [IX_Person_LastName_FirstName_MiddleName2] ON [dbo].[Person] REBUILD

--查看索引是否禁用
SELECT OBJECT_ID,OBJECT_NAME(i.[object_id]) AS tableName, NAME, i.is_disabled 
FROM sys.indexes AS i WHERE i.[object_id]=OBJECT_ID('dbo.Person')
/*
OBJECT_ID	tableName	NAME	is_disabled
1607676775	Person	PK_Person_BusinessEntityID_1	0
1607676775	Person	IX_Person_LastName_FirstName_MiddleName2	0
*/

	查询	删除
索引启用状态
索引禁用状态