使用表和索引分区

索引和表分区

通过该功能可以将索引和表数据分散到各个分区中的多个文件组。分区函数定义如何根据某些列（称为分区依据列）中的值将表或索引的行映射到一组分区。分区方案将分区函数指定的每个分区映射到一个文件组。这样，您便可以制定相应存档策略，用以将表扩展到多个文件组，因而也可扩展到多个物理设备。

Database 对象包含表示已实现的分区函数的 PartitionFunction 对象的集合，以及描述如何将数据映射到文件组的 PartitionScheme 对象的集合。

每个 Table 和 Index 对象在 PartitionScheme 属性中指定其使用的分区方案，并在 PartitionSchemeParameterCollection 中指定列。

示例

对于下面的代码示例，您必须选择编程环境、编程模板和编程语言才能创建应用程序。有关详细信息，请参阅如何在 Visual Studio .NET 中创建 Visual Basic SMO 项目和如何在 Visual Studio .NET 中创建 Visual C# SMO 项目。

在 Visual Basic 中为表设置分区方案

此代码示例说明如何为 AdventureWorks 示例数据库中的 TransactionHistory 表创建分区函数和分区方案。这些分区是按日期划分的，这样做的目的是将以前的记录分离出来，放入 TransactionHistoryArchive 表中。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server()
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define and create three new file groups on the database.
Dim fg2 As FileGroup
fg2 = New FileGroup(db, "Second")
fg2.Create()
Dim fg3 As FileGroup
fg3 = New FileGroup(db, "Third")
fg3.Create()
Dim fg4 As FileGroup
fg4 = New FileGroup(db, "Fourth")
fg4.Create()
'Define a partition function by supplying the parent database and name arguments in the constructor.
Dim pf As PartitionFunction
pf = New PartitionFunction(db, "TransHistPF")
'Add a partition function parameter that specifies the function uses a DateTime range type.
Dim pfp As PartitionFunctionParameter
pfp = New PartitionFunctionParameter(pf, DataType.DateTime)
pf.PartitionFunctionParameters.Add(pfp)
'Specify the three dates that divide the data into four partitions.
Dim val() As Object
val = New Object() {"1/1/2003", "1/1/2004", "1/1/2005"}
pf.RangeValues = val
'Create the partition function.
pf.Create()
'Define a partition scheme by supplying the parent database and name arguments in the constructor.
Dim ps As PartitionScheme
ps = New PartitionScheme(db, "TransHistPS")
'Specify the partition function and the filegroups required by the partition scheme.
ps.PartitionFunction = "TransHistPF"
ps.FileGroups.Add("PRIMARY")
ps.FileGroups.Add("second")
ps.FileGroups.Add("Third")
ps.FileGroups.Add("Fourth")
'Create the partition scheme.
ps.Create()

在 Visual C# 中为表设置分区方案

此代码示例说明如何为 AdventureWorks 示例数据库中的 TransactionHistory 表创建分区函数和分区方案。这些分区是按日期划分的，这样做的目的是将以前的记录分离出来，放入 TransactionHistoryArchive 表中。

{ 
//Connect to the local, default instance of SQL Server. 
Server srv; 
srv = new Server(); 
//Reference the AdventureWorks database. 
Database db; 
db = srv.Databases("AdventureWorks"); 
//Define and create three new file groups on the database. 
FileGroup fg2; 
fg2 = new FileGroup(db, "Second"); 
fg2.Create(); 
FileGroup fg3; 
fg3 = new FileGroup(db, "Third"); 
fg3.Create(); 
FileGroup fg4; 
fg4 = new FileGroup(db, "Fourth"); 
fg4.Create(); 
//Define a partition function by supplying the parent database and name arguments in the constructor. 
PartitionFunction pf; 
pf = new PartitionFunction(db, "TransHistPF"); 
//Add a partition function parameter that specifies the function uses a DateTime range type. 
PartitionFunctionParameter pfp; 
pfp = new PartitionFunctionParameter(pf, DataType.DateTime); 
pf.PartitionFunctionParameters.Add(pfp); 
//Specify the three dates that divide the data into four partitions. 
object[] val; 
val = new object[] {"1/1/2003", "1/1/2004", "1/1/2005"}; 
pf.RangeValues = val; 
//Create the partition function. 
pf.Create(); 
//Define a partition scheme by supplying the parent database and name arguments in the constructor. 
PartitionScheme ps; 
ps = new PartitionScheme(db, "TransHistPS"); 
//Specify the partition function and the filegroups required by the partition scheme. 
ps.PartitionFunction = "TransHistPF"; 
ps.FileGroups.Add("PRIMARY"); 
ps.FileGroups.Add("second"); 
ps.FileGroups.Add("Third"); 
ps.FileGroups.Add("Fourth"); 
//Create the partition scheme. 
ps.Create(); 
} 

请参阅

概念