Create Sliding Window Scenarios
The sliding window scenario is a SQL Server 2005 table partitioning feature that enables you to handle data aging effectively. Using this scenario, you can automatically add new partitions to a large table without having to manually move data, archive aged data, and delete partitions. Sliding window scenarios are useful for tables where large loads occur periodically, such as billing or payroll systems.
Because active data is queried much more often than aged data, the sliding window scenario rolls new data into the production table and switches aged partitions out. The main benefit is that queries can be performed on the new data while the aged data is being archived. SQL Server 2005 supports the sliding window scenario with its T-SQL statements.
Notes:
- Sliding window scenarios should only be used on larger, unpartitioned tables.
- This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To create a sliding window scenario
-
Select Tables from the Databases node in the Object Explorer.
- Right-click an unpartitioned table and select Storage | Sliding Window.
- Complete the wizard.
Switch Partitions
Switching a partition from one table to another eliminates the time consuming process of running a delete operation and eliminates blocking access to the partition during the process. When you switch a table partition to a non-partitioned table in the same filegroup, SQL Server accomplishes this task with a metadata change.
Because this is a special operation, it is only successful under certain conditions. The source table and the target table must meet the following conditions for each object:
| Object | Source and Target Table Criteria |
|---|---|
|
Boundary values |
Must have the same boundary values. The source partition must fit within the boundary values of the target partition. If not, the source table must have a constraint defined to ensure that all the data fits into the target partition. The same criteria applies when you add a nonpartitioned table as a partition to a partitioned table. |
|
Columns |
Must be partitioned on the same column and have columns in the same order and with the same names, data type, length, collation, precision, scale, and nullability. |
|
In-row |
Must have the same settings for text, ntext, or image columns. |
|
Partition Keys |
Must be defined the same. When using computed columns, the expressions defining the computed columns must be the same and both must be persisted. |
|
ROWGUID column |
Must have a corresponding column in the other table with a ROWGUID property. |
|
XML column |
Must have the same XML schema. |
|
Constraints |
Must be the same for both tables. |
|
CHECK |
Must have identical structure on both tables or the source table's CHECK constraint can be a subset of the target table's CHECK constraint. |
|
FOREIGN KEY |
Must be identical in both tables and must reference the same PRIMARY keys. No active PRIMARY key or FOREIGN key relationship is allowed between the source table and the target table if one of the tables contains a FOREIGN key. A FOREIGN key in another table cannot reference the source table. |
|
PRIMARY KEY |
Must be the same for both tables. |
|
Filegroup |
Must be located in the same filegroup and be partitioned on the same column if both are partitioned. |
|
Indexes |
Must be aligned with the table whether one of the tables is partitioned or whether both are partitioned. No full-text indexes are permitted on either table and no XML indexes are permitted on the target table. |
|
Clustered |
Must have the same clustered indexes and both must be enabled. |
|
Non-clustered |
Must be structured the same and have the same uniqueness, subkeys, and ascending or descending sorting direction for each index key column. Note: Disabled nonclustered indexes are exempt from this requirement. |
|
Permissions |
Can be different for both tables. |
|
Replication |
No replication using the source or the target table as a source. |
|
Rules |
No rules can be defined on either the source or the target table . |
|
Settings |
Must have the same ANSI_NULLS and QUATED IDENTIFIER setttings. |
|
Structure |
Must have same structure (BLOBS, indexes, index partitions, etc.) |
|
Target Table/ Partition |
Target table or partition must be empty. |
|
View |
No indexed views can be on the source or the target table. You cannot use a view with schema binding. |
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To switch a partition to another table
-
Select Tables from the Databases node in the Object Explorer.
- Right-click a partitioned table and select Storage | Switch Partition.
-
Complete the wizard.
Tip: You can right-click a table and select Storage | Partition Cleanup to remove unused partition schemes and functions.
Bind and Unbind Columns
Bind and Unbind Columns to Rules
You can bind an existing rule to a column from this window, which is useful for specifying valid values for the column.
Consider the following guidelines for binding rules to columns:
-
If a rule is currently bound to a column, selecting a new rule for that column automatically replaces the original rule.
-
If a column has a rule and a default bound to it, the default cannot conflict with the rule. If there is a conflict, the rule is not inserted.
To bind or unbind a column to a rule
- Select Tables from the Databases node in the Object Explorer.
-
Right-click a table and select View Details.
-
Select the Columns tab in the Viewer pane.
-
Select the column and do one of the following:
- Click
to bind a column to rule.
- Click
to unbind a column from rule.
- Click
- Select a rule from the list.