WEB开发网
开发学院数据库MSSQL Server SqlServer性能优化:Partition(创建分区) 阅读

SqlServer性能优化:Partition(创建分区)

 2010-03-10 00:00:00 来源:WEB开发网   
核心提示: 1.在表没有索引的情况下:BEGINTRANSACTIONCREATEPARTITIONFUNCTION[TestFunction](datetime)ASRANGELEFTFORVALUES(N'2010-01-01T00:00:00',N'2010-02-01T00:

1.在表没有索引的情况下:

BEGIN TRANSACTION 
CREATE PARTITION FUNCTION [TestFunction](datetime) AS RANGE LEFT FOR VALUES (N'2010-01-01T00:00:00', N'2010-02-01T00:00:00', 
N'2010-03-01T00:00:00', N'2010-04-01T00:00:00', N'2010-05-01T00:00:00', N'2010-06-01T00:00:00') 
 
CREATE PARTITION SCHEME [TestScheme] AS PARTITION [TestFunction] TO ([PRIMARY], [PRIMARY], [PRIMARY], 
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]) 
 
CREATE CLUSTERED INDEX [ClusteredIndex_on_TestScheme_634025264502439124] ON [dbo].[Account] 
( 
  [birthday] 
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [TestScheme]([birthday]) 
 
DROP INDEX [ClusteredIndex_on_TestScheme_634025264502439124] ON [dbo].[Account] WITH ( ONLINE = OFF ) 
COMMIT TRANSACTION

这里先创建Partition Function以及Partition Scheme,之后在分区列上创建聚集索引并按照分区方案分区,最后删除了这一索引。</>

2.在表有索引的情况下:

如果原先没有聚集索引:

CREATE CLUSTERED INDEX [ClusteredIndex_on_TestScheme_634025229911990663] ON [dbo].[Account] 
( 
  [birthday] 
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [TestScheme]([birthday]) 
 
DROP INDEX [ClusteredIndex_on_TestScheme_634025229911990663] ON [dbo].[Account] WITH ( ONLINE = OFF ) 

上一页  1 2 3 4 5  下一页

Tags:SqlServer 性能 优化

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接