使用表和索引分区
可以使用已分区表和已分区索引所提供的存储算法来存储数据。分区可以使大型表和索引更易于管理并且更灵活。
索引和表分区
通过该功能可以将索引和表数据分散到各个分区中的多个文件组。分区函数定义如何根据某些列（称为分区依据列）中的值将表或索引的行映射到一组分区。分区方案将分区函数指定的每个分区映射到一个文件组。这样，您便可以制定相应存档策略，用以将表扩展到多个文件组，因而也可扩展到多个物理设备。
Database 对象包含表示已实现的分区函数的 PartitionFunction 对象的集合，以及描述如何将数据映射到文件组的 PartitionScheme 对象的集合。
每个 Table 和 Index 对象在 PartitionScheme 属性中指定其使用的分区方案，并在 PartitionSchemeParameterCollection 中指定列。
示例
对于下面的代码示例，您必须选择编程环境、编程模板和编程语言才能创建应用程序。有关详细信息，请参阅如何在 Visual Studio .NET 中创建 Visual Basic SMO 项目和如何在 Visual Studio .NET 中创建 Visual C# SMO 项目。
在 Visual Basic 中为表设置分区方案
此代码示例说明如何为 AdventureWorks2008R2 示例数据库中的 TransactionHistory 表创建分区函数和分区方案。这些分区是按日期划分的，这样做的目的是将以前的记录分离出来，放入 TransactionHistoryArchive 表中。
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server()
'Reference the AdventureWorks2008R2database.
Dim db As Database
db = srv.Databases("AdventureWorks2008R2")
'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# 中为表设置分区方案
此代码示例说明如何为 AdventureWorks2008R2 示例数据库中的 TransactionHistory 表创建分区函数和分区方案。这些分区是按日期划分的，这样做的目的是将以前的记录分离出来，放入 TransactionHistoryArchive 表中。
{
//Connect to the local, default instance of SQL Server.
Server srv;
srv = new Server();
//Reference the AdventureWorks2008R2 database.
Database db;
db = srv.Databases("AdventureWorks2008R2");
//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();
}
在 PowerShell 中为表设置分区方案
此代码示例说明如何为 AdventureWorks2008R2 示例数据库中的 TransactionHistory 表创建分区函数和分区方案。这些分区是按日期划分的，这样做的目的是将以前的记录分离出来，放入 TransactionHistoryArchive 表中。
# Set the path context to the local, default instance of SQL Server.
CD \sql\localhost\default
#Get a server object which corresponds to the default instance
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server
$db = $srv.Databases["AdventureWorks"]
#Create four filegroups
$fg1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Filegroup -argumentlist $db, "First"
$fg2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Filegroup -argumentlist $db, "Second"
$fg3 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Filegroup -argumentlist $db, "Third"
$fg4 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Filegroup -argumentlist $db, "Fourth"
#Define a partition function by supplying the parent database and name arguments in the constructor.
$pf = New-Object -TypeName Microsoft.SqlServer.Management.SMO.PartitionFunction -argumentlist $db, "TransHistPF"
$T = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime
$T
$T.GetType()
#Add a partition function parameter that specifies the function uses a DateTime range type.
$pfp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.PartitionFunctionParameter -argumentlist $pf, $T
#Specify the three dates that divide the data into four partitions.
#Create an array of type object to hold the partition data
$val = "1/1/2003"."1/1/2004","1/1/2005"
$pf.RangeValues = $val
$pf
#Create the partition function
$pf.Create()
#Create partition scheme
$ps = New-Object -TypeName Microsoft.SqlServer.Management.SMO.PartitionScheme -argumentlist $db, "TransHistPS"
$ps.PartitionFunction = "TransHistPF"
#add the filegroups to the scheme
$ps.FileGroups.Add("PRIMARY")
$ps.FileGroups.Add("Second")
$ps.FileGroups.Add("Third")
$ps.FileGroups.Add("Fourth")
#Create it at the server
$ps.Create()