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

来源：互联网发布：升调降调软件编辑：程序博客网时间：2024/06/02 23:35

索引禁用备注：

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

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

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

脚本：

--创建新表 dbo.Person ， 不影响原来的表 Person.PersonSET NOCOUNT ONIF OBJECT_ID('dbo.Person') IS NOT NULLBEGINDROP TABLE dbo.PersonENDGOCREATE 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]GOINSERT 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 pGO--创建索引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')/*1607676775PersonPK_Person_BusinessEntityID_101607676775PersonIX_Person_LastName_FirstName_MiddleName20*/--开启执行计划，查看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] DISABLEGO--查看索引是否禁用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_IDtableNameNAMEis_disabled1607676775PersonPK_Person_BusinessEntityID_101607676775PersonIX_Person_LastName_FirstName_MiddleName21*/--开启执行计划，查看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_IDtableNameNAMEis_disabled1607676775PersonPK_Person_BusinessEntityID_101607676775PersonIX_Person_LastName_FirstName_MiddleName20*/

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

1 0