Physical Modeling › SQL Server Common Functionality › SQL Server Partitions › Define SQL Server Partition Schemes

Define SQL Server Partition Schemes

A partition scheme maps the partitions created by a partition function to a set of filegroups that you define. Use the SQL Server Partition Scheme Editor in a SQL Server 2005 or 2008 physical model to define the filegroups and the partition function that the partition scheme must reference.

Note: A partition scheme must reference one partition function, however, a partition function can reference more than one partition scheme.

To define SQL Server partition schemes

1. Click Target - SQL Server, Partition, Schemes on the Model menu.

   The SQL Server Partition Scheme Editor opens.

2. Select the partition scheme in the Navigation Grid that you want to define and work with the following options:

   Note: Click New on the toolbar to create a new partition scheme. Use the Enter filter text box to filter a very large list of partition schemes to quickly locate the one that you want to define.

   Name

   Displays the partition scheme name. You can change the partition scheme name in this field.

   Partition Function

   Specifies the partition function to which the partition scheme is mapped.

   Physical Data Type

   Specifies the data type of the column used for the partition. Select a data type from the drop-down list.

   Database

   Specifies the database that contains the filegroup to which to map the partition. Select a database from the drop-down list.

   Generate

   Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.

3. Click the General tab and work with the following options:

   Map all partitions to single filegroup

   Specifies to have the partition scheme map all the partitions to the same filegroup. Clear the check box to assign different filegroups to each partition boundary.

   Note: Click the Filegroups Editor icon to open the SQL Server Database File Group Editor to add more filegroups.

4. (Optional) Click the Comment tab and enter any comments that you want to associate with the partition scheme.
5. (Optional) Click the Where Used tab to view where the partition scheme is used in the model.
6. (Optional) Click the UDP tab to work with user-defined properties for the partition scheme.
7. (Optional) Click the Notes tab to view history information and view or edit user notes.
8. Click Close.

   The partition scheme is defined and the SQL Server Partition Scheme Editor closes.

More information:

SQL Server Property Editors

Define Permissions for an Object in a SQL Server Property Editor

Add a Comment in a Property Editor

Define a Database Filegroup in SQL Server 2005

Define SQL Server Partition Functions