ParrySMS 2010-06-09
代码如下:
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) --重建表的索引 --第一个参数,可以是表名,也可以是表ID。 --第二个参数,如果是'',表示影响该表的所有索引。 --第三个参数,填充因子,即索引页的数据填充程度。如果是,表示每一个索引页都全部填满,此时select效率最高,但以后要插入索引时,就得移动后面的所有页,效率很低。如果是,表示使用先前的填充因子值。 DBCC DBREINDEX(testIndexOrder,'',)