常見索引操作:
--显示指定表的数据和索引的碎片信息
--察看索引統計信息
--察看對應表存在那些索引及相關信息
--創建有名主鍵索引非聚集
--創建表,同時創建主鍵索引
--删除主键
--創建索引
--删除索引
--重建索引
--重建指定数据库中表的一个或多个索引
--一个删除指定表的所有索引和统计的过程(周老大)
-----------------------------------------------------------------------
-- DBCC showcontig
--显示指定表的数据和索引的碎片信息
--察看對應表存在那些統計及相關信息
SELECT name,rowmodctr,* --+ ('DROP STATISTICS DGMOCPU.'+name)
FROM sysindexes
WHERE id=OBJECT_ID('DGMOCPU') AND indid BETWEEN 1 AND 254
AND status IN (96,10485856,8388704)
---察看索引統計信息
DBCC SHOW_STATISTICS (DGMOCPB, PK_DGMOCPB)
--察看對應表存在那些索引及相關信息
select name,object_name(id)as tableName,* from sysindexes where
id=OBJECT_ID('DGMOCPU') and --(通過表名稱查詢)
--name=object_name('DGMOCPU_PK') and --(通過索引名稱查詢)
indid BETWEEN 1 AND 254
AND status NOT IN (96,10485856,8388704)
-- AND OBJECTPROPERTY (OBJECT_ID(name),'IsConstraint') IS NULL --过程不顯示
CONSTRAINTS
-----------------------------------------------------------------------------
--創建有名主鍵索引非聚集
alter table dgmocpu add constraint PK_DGMOCPU_PR primary key nonCLUSTERED
(PU001,PU002,PU003,PU005)
--删除主键
alter table dgmocpU drop constraint PK_DGMOCPU_PR
--創建索引(非聚集)
create nonclustered index IX_DGMOCPU on dgmocpU(PU001 ASC,PU002 ASC)
--創建索引(聚集)
create clustered index IC_DGMOCPU on dgmocpU(PUDATE desc)
--删除索引
drop INDEX dgmocpU.IX_DGMOCPU
--禁用约束
alter table tablename
nocheck constraint constraintname
--啓用约束
alter table tablename
check constraint constraintname
--有名稱的主鍵+聚集索引
ALTER TABLE [dbo].[agent_post_reg] WITH NOCHECK ADD
CONSTRAINT [PK_agent_post_reg] PRIMARY KEY CLUSTERED
(
[agnet_post_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
--創建表,同時創建主鍵索引
CREATE TABLE CM_TEMP (
PART_ID VARCHAR2(20),
COST_CODE VARCHAR2(20),
QTY NUMBER(12)
PRIMARY KEY (PART_ID,COST_CODE)
)
CREATE INDEX IDX_CM_TEMP ON CM_TEMP(PART_ID,COST_CODE)
--重組索引 (碎片率30%内)
ALTER INDEX PK_DGMOCPU_PR ON DGMOCPU
REORGANIZE
--重建索引 (大约30%)
ALTER INDEX PK_DGMOCPU_PR ON DGMOCPU
REBUILD
--重建指定数据库中表的一个或多个索引。
dbcc dbreindex (tablename)
--一个删除指定表的所有索引和统计的过程
------------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date : 2009-09-05 00:57:10
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
------------------------------------------------------------------------
IF EXISTS (SELECT name FROM sysobjects WHERE id = OBJECT_ID('sp_DropAllIndex')
AND OBJECTPROPERTY(OBJECT_ID('sp_DropAllIndex'),'IsProcedure')=1)
DROP PROCEDURE sp_DropAllIndex
GO
CREATE PROCEDURE sp_DropAllIndex
@tabname nvarchar(150) -- 需要删除统计或索引的表
AS
BEGIN
DECLARE @drop_idx_string nvarchar(4000) -- 存放动态组织而成的DROPS
index/stats 语法
SET NOCOUNT ON
-- check table
IF NOT EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table' AND table_name = @tabname)
BEGIN
RAISERROR(N'------当前表:''%s'' 不存在!',16, 1, @tabname)
RETURN (1)
END
SET @tabname = OBJECT_ID(@tabname)
IF EXISTS (SELECT 1
FROM sysindexes
WHERE id=@tabname AND indid BETWEEN 1 AND 254
AND status IN (96,10485856,8388704))
BEGIN
SELECT @drop_idx_string = isnull(@drop_idx_string+';','')
+ ('DROP STATISTICS '+OBJECT_NAME(@tabname)+'.'+name)
FROM sysindexes
WHERE id=@tabname AND indid BETWEEN 1 AND 254
AND status IN (96,10485856,8388704)
END
IF Len(@drop_idx_string) > 0
BEGIN
PRINT N'------统计删除列表------'
PRINT @drop_idx_string+';'
EXECUTE(@drop_idx_string+';')
PRINT N'------统计删除结束------'
END
IF EXISTS (SELECT 1 FROM sysindexes
WHERE id=@tabname AND indid BETWEEN 1 AND 254
AND status NOT IN (96,10485856,8388704))
BEGIN
SET @drop_idx_string = NULL
select @drop_idx_string = isnull(@drop_idx_string+';'+CHAR(13)+CHAR
(10),'')
+ ('DROP INDEX '+OBJECT_NAME(@tabname)+'.'+name)
FROM sysindexes
WHERE id=@tabname AND indid BETWEEN 1 AND 254
AND status NOT IN (96,10485856,8388704)
AND OBJECTPROPERTY (OBJECT_ID(name),'IsConstraint') IS NULL--过程不处
理CONSTRAINTS
END
PRINT N'------索引删除列表------'
PRINT (@drop_idx_string+';')
EXEC( @drop_idx_string+';')
PRINT ('......'+CHAR(13)+CHAR(10)+'......')
PRINT N'------索引删除结束------'
END
GO
create clustered index idx_id on ta(id)
create index idx_col on ta(col)
go
sp_DropAllIndex 'ta'
/*
------索引删除列表------
DROP INDEX ta.idx_id;
DROP INDEX ta.idx_col;
......
......
------索引删除结束------
*/