復制代碼 代碼如下:

USE [Test]
GO
/****** 對象: Table [dbo].[testIndexOrder] 腳本日期: 05/27/2010 09:11:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[testIndexOrder](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[LastName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Desc] [nvarchar](400) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_testIndexOrder] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** 對象: Index [IX_testIndexOrder] 腳本日期: 05/27/2010 09:11:51 ******/
CREATE NONCLUSTERED INDEX [IX_testIndexOrder] ON [dbo].[testIndexOrder]
(
[FirstName] ASC,
[LastName] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
declare @i INT;
DECLARE @random varchar(36);
set @i = 0;
while @i < 100000
begin
set @random = newid();
INSERT INTO [testIndexOrder]
(FirstName,LastName,[Desc])
VALUES(
substring(@random,1,8),substring(@random,12,8),@random
);
set @i = @i + 1
end

set statistics time on
select * from [testIndexOrder] where lastname = '6F-4ECA-'
select * from [testIndexOrder] where firstname = 'CAABE009'
set statistics time off

復制代碼 代碼如下:

--顯示表testIndexOrder的索引碎片情況
DBCC SHOWCONTIG(testIndexOrder)

--重建表的索引
--第一個參數(shù)，可以是表名，也可以是表ID。
--第二個參數(shù)，如果是''，表示影響該表的所有索引。
--第三個參數(shù)，填充因子，即索引頁的數(shù)據(jù)填充程度。如果是，表示每一個索引頁都全部填滿，此時select效率最高，但以后要插入索引時，就得移動后面的所有頁，效率很低。如果是，表示使用先前的填充因子值。
DBCC DBREINDEX(testIndexOrder，''，)