Working with Data Models › Working with Transformations › Using a Transformation to Vertically Partition a Table

Using a Transformation to Vertically Partition a Table

When a table is extremely large and you need to query some of the column values more often than the rest, you can apply a transformation and vertically partition a table. When you vertically partition a table, you remove columns from one table and place them into another table. Normally, you vertically partition a table to:

• Improve query performance. When an application passes a SQL SELECT statement to the DBMS, the database retrieves a specific set of records from a smaller table.
• Manage tables more easily. The partitioned data is stored in several, smaller tables. Therefore, it is easier to load and delete data in the partitioned tables than in the large table.
• Perform better backup and recovery operations. The partitioned tables are smaller than the source table. Therefore, you can have more options for backing up and recovering the partitioned tables than for a single large table.

Before you vertically partition a table, you need to identify the queries used most frequently.

Vertical Partitioning Wizard

To vertically partition a table, select a single table in a model, and click the Vertical Partition icon on the Transformations toolbar. You use the Vertical Partitioning Wizard to:

• Specify how many partitioned tables to create.
• Enter a name for the partitioned tables.
• Select the columns to copy from the current table and specify into which partitioned tables to place them.

Result of Vertically Partitioning a Table

When you click Vertical Partition to vertically partition a table, you:

• Create a new table for each partition that you specify. Each partitioned table contains all primary key and non-key columns that you specify to copy from the source table. The primary key of each partitioned table is the primary key of the source table. The partitioned tables appear in the Model Explorer under the Tables folder.
• Create all relationships associated with the source table that you vertically partition, and preserves all migrating keys.
• Preserve the properties from the source columns. The properties from the source table are not preserved.

Important! Once you apply the Vertical Partition transformation, it cannot be reversed. While in the current modeling session however, you can undo the action.

More information:

Vertically Partition a Table with a Transformation

Using a Transformation to Resolve a Many-to-Many Relationship