--- title: "Using Table and Index Partitioning | Microsoft Docs" ms.custom: "" ms.date: "08/06/2017" ms.prod: sql ms.prod_service: "database-engine" ms.reviewer: "" ms.technology: ms.topic: "reference" helpviewer_keywords: - "partitions [SMO]" - "storing data [SMO]" - "partitioned tables [SQL Server], SMO" - "partitioned indexes [SQL Server], SMO" ms.assetid: 0e682d7e-86c3-4d73-950d-aa692d46cb62 author: "markingmyname" ms.author: "maghan" monikerRange: "=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current" --- # Using Table and Index Partitioning [!INCLUDE[appliesto-ss-asdb-asdw-xxx-md](../../../includes/appliesto-ss-asdb-asdw-xxx-md.md)] Data can be stored by using the storage algorithms provided by [Partitioned Tables and Indexes](../../../relational-databases/partitions/partitioned-tables-and-indexes.md). 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 object contains a collection of objects that represent the implemented partition functions and a collection of objects that describe how data is mapped to file groups. Each and object specifies which partition scheme it uses in the property and specifies the columns in the . ## Example For the following code examples, you will have to select the programming environment, programming template and the programming language to create your application. For more information, see [Create a Visual C# SMO Project in Visual Studio .NET](../../../relational-databases/server-management-objects-smo/how-to-create-a-visual-csharp-smo-project-in-visual-studio-net.md). ## 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 [!INCLUDE[ssSampleDBnormal](../../../includes/sssampledbnormal-md.md)] sample database. The partitions are divided by date with the intention of separating out old records into the `TransactionHistoryArchive` table. ```csharp { //Connect to the local, default instance of SQL Server. Server srv; srv = new Server(); //Reference the AdventureWorks2012 database. Database db; db = srv.Databases("AdventureWorks2012"); //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 [!INCLUDE[ssSampleDBnormal](../../../includes/sssampledbnormal-md.md)] sample database. The partitions are divided by date with the intention of separating out old records into the `TransactionHistoryArchive` table. ```powershell # 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() ``` ## See Also [Partitioned Tables and Indexes](../../../relational-databases/partitions/partitioned-tables-and-indexes.md)