Using Table and Index Partitioning
Data can be stored by using the storage algorithms provided by Partitioned Tables and Indexes. Partitioning can make large tables and indexes more manageable and scalable.
Index and Table Partitioning
The feature enables index and table data to be spread across multiple file groups in partitions. A partition function defines how the rows of a table or index are mapped to a set of partitions based on the values of certain columns, referred to as partitioning columns. A partition scheme maps each partition specified by the partition function to a file group. This lets you develop archiving strategies that enable tables to be scaled across file groups, and therefore physical devices.
The Database object contains a collection of PartitionFunction objects that represent the implemented partition functions and a collection of PartitionScheme objects that describe how data is mapped to file groups.
Each Table and Index object specifies which partition scheme it uses in the PartitionScheme property and specifies the columns in the PartitionSchemeParameterCollection.
Example
For the following code example, you will have to select the programming environment, programming template and the programming language to create your application. For more information, see How to: Create a Visual Basic SMO Project in Visual Studio .NET and How to: Create a Visual C# SMO Project in Visual Studio .NET.
Setting Up a Partition Scheme for a Table in Visual Basic
The code example shows how to create a partition function and a partition scheme for the TransactionHistory table in the AdventureWorks2008R2 sample database. The partitions are divided by date with the intention of separating out old records into the TransactionHistoryArchive table.
'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()
Setting Up a Partition Scheme for a Table in Visual C#
The code example shows how to create a partition function and a partition scheme for the TransactionHistory table in the AdventureWorks2008R2 sample database. The partitions are divided by date with the intention of separating out old records into the TransactionHistoryArchive table.
{
//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();
}
Setting Up a Partition Scheme for a Table in PowerShell
The code example shows how to create a partition function and a partition scheme for the TransactionHistory table in the AdventureWorks2008R2 sample database. The partitions are divided by date with the intention of separating out old records into the TransactionHistoryArchive table.
# 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()