Partitioning can be configured under the Table Settings → Performance Tab is the database process where very large tables are divided into multiple smaller individual tables. As a result, queries that access only a fraction of the data may run faster since there may be less data to scan.
Partitioning can be configured based on either fixed or dynamic date ranges.
Fixed Date Range Partitioning
Click the Template Add button to create a partition template.
Selecting the “Date - System Field” bullet point will open the date range menu below where the following options are available.
- Year (YYYY)
- Month(YYYYMM)
- Day (YYYYMMDD)
- Use NULL value Conversion
The “Use NULL value Conversion” checkbox allows developers to specify a value that will be used in lieu of any null date values, should they be present. Enter the value to be used in place of the null date value in the field below.
How the data is queried generally determines the optimal partitioning period. For example, if queries are commonly performed on monthly based data, then partitioning by Month(YYYYMM) would normally be the appropriate date range to select for partitioning.
For the scenario above, complete the configuration by setting the appropriate date field and time table, which is usually your Date table and used to compare the date data.
Custom Dynamic Range Partitioning
There may be instances where partitioning based on set time periods is not optimal, as there may be dynamic ranges that do not adhere to only one timeframe. A custom partition may be used to accomodate these types of partitioning situations.
Click the Template Add button to create a partition template.
Select the “Other - Manual Setup” radio button.
Select the data type that the range should contain, i.e. “String”.
In the New Range Value text box, enter the desired values one at a time, pressing the “+” button until of the desired values have been added to the Range Value area.
In the Advanced area change the Filegroups menu to Per partition.
Once your custom template is created, you to create the custom date ranges that correspond to the values in it that match the range values created above. These are the dynamic ranges.
Start by going to the source table of the table you want to use the partition in. Create a custom text field called, i.e. “Part”
Right-click on the field and select “Field Transformations” to add some fixed characters. For each fixed value character, add a condition that defines the dynamic ranges.
Once your custom date ranges are setup under the fixed values, you can set this field as the partitioning field and also select the appropriate date table.
