Previous Topic: Define SQL Server Partition FunctionsNext Topic: Define SQL Server Permissions


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 2008, 2012, 2014 or 2016 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 New icon in property editors to create a new object 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 Filegroups Editor Icon in SQL Partition Scheme Editor 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 object.
  5. (Optional) Click the Where Used tab to view where the object is used within the model.
  6. (Optional) Click the UDP tab to work with user-defined properties for the object.
  7. (Optional) Click the Notes tab to view and edit user notes.
  8. (Optional) Click the Extended Notes tab to view or edit user notes.
  9. Click Close.

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

More information:

Define Permissions for an Object in a SQL Server Property Editor

Add a Comment in a Property Editor

Define SQL Server Partition Functions