1. 针对整个SQL Server Engine,可以执行一下脚本
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'fill factor (%)', 90;
GO
RECONFIGURE;
GO
GO
RECONFIGURE;
GO
sp_configure 'fill factor (%)', 90;
GO
RECONFIGURE;
GO
然后重启SQL Server服务即可, 以后添加的索引的fill factor默认是设定值
2. 针对每个索引
在创建索引时可以为每个索引指定单独的Fill factor.
ALTER TABLE [dbo].[School] ADD CONSTRAINT [PK_School] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF) ON [PRIMARY]
(
[ID] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF) ON [PRIMARY]
PS:在执行第一种方案时, 可能会出现"Ad hoc update to system catalogs is not supported"异常错误, 这是由于SQL Server的一个Bug造成的.
相关链接:http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=324989