索引创建和使用

--CRMDB：数据库
--DATADICT:表
--创建索引
USE CRMDB
GO
CREATE UNIQUE INDEX INDEX_MYINDEX --UNIQUE (唯一索引),
ON DATADICT (DATAID,DATNO)
with fillfactor=50
GO

--删除索引
DROP INDEX DATADICT.INDEX_MYINDEX

--使用索引
SELECT * FROM DATADICT with(index=INDEX_MYINDEX) where DATAID =18;

 
--分析索引
USE CRMDB
GO
SET SHOWPLAN_ALL ON --SET SHOWPLAN_TEXT ON
GO
SELECT * FROM DATADICT WITH (INDEX=INDEX_MYINDEX)
GO
SET SHOWPLAN_ALL OFF --SET SHOWPLAN_TEXT OFF
GO

USE CRMDB
GO
SET SHOWPLAN_ALL ON --SET SHOWPLAN_TEXT ON
GO
SELECT * FROM DATADICT
GO
SET SHOWPLAN_ALL OFF --SET SHOWPLAN_TEXT OFF
GO

--索引分析BEGIN--
USE CRMDB
GO
SET STATISTICS IO ON
GO
SELECT * FROM DATADICT WITH (INDEX=INDEX_MYINDEX)
GO
SET STATISTICS IO OFF
GO

USE CRMDB
GO
SET STATISTICS IO ON
GO
SELECT * FROM DATADICT
GO
SET STATISTICS IO OFF
GO
--索引分析END--

--索引维护BEGIN--
--更新索引统计
USE CRMDB
GO
UPDATE STATISTICS DATADICT INDEX_MYINDEX
GO

--扫描表
USE CRMDB
GO
DBCC SHOWCONTIG (DATADICT,INDEX_MYINDEX)
GO

--碎片整理
USE CRMDB
GO
DBCC INDEXDEFRAG (CRMDB,DATADICT,INDEX_MYINDEX)
GO

--索引维护END--