When you run the script to split a table, the secondary table is created and populated with data. The primary table, and any objects referenced by the primary table, are modified.

The following describes the actions of the split table script in more detail.

Primary keys

The primary key in the primary table is unchanged.

A primary key for the secondary table is created based on the copied columns. SQL Prompt generates a name for the primary key on the secondary table automatically.

The primary key columns cannot contain NULL values. Therefore, if any of the copied columns allow NULL values, a warning is displayed. When the script is run, these columns will be changed to NOT NULL columns. If any of the data in the copied columns contains NULL values, the script will fail when it is run.

Note that if you copy the primary key columns from the primary table to the secondary table, the primary key itself is not copied; a new primary key is always created on the secondary table. Similarly, any options set on the primary table's primary key (using the WITH clause, such as clustering) are not copied.

Foreign keys

By default, a foreign key is created on the primary table to reference the copied columns in the secondary table (which are used as the secondary table's primary key). SQL Prompt generates a name for the foreign key automatically.

If you chose to create the foreign key on the secondary table, SQL Prompt creates the foreign key to reference the primary key columns in the primary table.

Existing foreign keys that reference other tables:

• on columns in only the primary table, are preserved on the primary table
• on columns in only the secondary table, are created on the secondary table
• on columns that are copied, are preserved on the primary table and duplicated on the secondary table
• on columns in both tables that are not copied, are deleted

Permissions

Table-level permissions on the primary table are duplicated on the secondary table.

Column-level permissions:

• on columns in only the primary table, are preserved on the primary table
• on columns in only the secondary table, are created on the secondary table
• on copied columns, are preserved on the primary table and duplicated on the secondary table

Indexes

Indexes that reference:

• columns in only the primary table, are preserved on the primary table
• columns in only the secondary table, are created on the secondary table
• copied columns, are preserved on the primary table and duplicated on the secondary table
• columns from both tables that are not copied, are dropped

Note that clustered indexes are created as nonclustered indexes on the secondary table. This is because the primary key on the secondary table is clustered to improve access speed when the tables are joined in queries (for more information, see Dependencies).

Constraints

Table-level constraints:

• that reference columns in only the primary table, are preserved on the primary table
• that reference columns in only the secondary table, are created on the secondary table
• that reference copied columns, are preserved on the primary table and duplicated on the secondary table
• that reference columns from both tables that are not copied, are dropped

Column-level constraints:

• on columns in only the primary table, are preserved on the primary table
• on columns in only the secondary table, are created on the secondary table
• on copied columns, are preserved on the primary table and duplicated on the secondary table

Note that DEFAULT constraints are renamed when they are created on the secondary table; the name of the secondary table is appended to the original constraint name. For example, a DEFAULT constraint called ConstraintA that is created on a secondary table called TableB is created as ConstraintA_TableB.

Extended properties

Table-level extended properties are preserved on the primary table and duplicated on the secondary table.

Extended properties on level 2 objects (columns, constraints, triggers, and indexes):

• on objects in only the primary table, are preserved on the primary table
• on objects in only the secondary table, are created on the secondary table
• on copied objects, are preserved on the primary table and duplicated on the secondary table
• on objects from both tables that are not copied, are dropped (because the objects are dropped)

Filegroups

If the primary table was created on a filegroup, the secondary table is created on the same filegroup.

Partition schemes

If the primary table has been partitioned over a column, and that column stays in the primary table following the split, the primary table remains partitioned.

If the column over which the table is partitioned is moved to the secondary table, a warning is displayed and SQL Prompt does not allow you to generate the script.

Full-text indexes

If a full-text index exists on a column that is moved to the secondary table, the full-text index is added to the column in the secondary table.

However, note that full-text indexing cannot be added to a column from within a transaction. Therefore, if the script fails, SQL Prompt will not be able to roll back the script, and your database will be in an undetermined state. If you choose to move a column that has a full-text index, SQL Prompt displays a warning.

Dependencies

If you have only copied columns from the primary table to the secondary table, any objects referenced by the primary table are not modified.

If you have moved any columns from the primary table to the secondary table, any objects referenced by the primary table are modified as follows.

For the object types listed below, objects that referenced the primary table before it was split are modified so that they reference both tables:

• functions
• stored procedures
• views
• DML triggers
• DDL triggers

The following modifications are made to SELECT statements:

• the table reference is replaced with a JOIN subquery that joins the primary and secondary tables based on the copied columns
• an alias is created for the JOIN subquery
• the SELECT column list and the following clauses are modified to reference the appropriate columns
  • WHERE
  • GROUP BY
  • ORDER BY
  • HAVING
• SELECT * clauses are expanded
• wherever possible, fully-qualified names are used
• table hints are preserved for the primary table and duplicated for the secondary table
• table samples are removed

The following modifications are made to INSERT, UPDATE, and DELETE statements:

• INSERT statements are split into two: one for the primary table, and one for the secondary table
• the column list and WHERE clause are modified to reference the appropriate columns
• for statements that include a FROM clause, the table reference is replaced with a JOIN statement that joins the primary and secondary tables based on the copied columns

Note that the generated script for INSERT, UPDATE, and DELETE statements may not precisely reflect your intentions for the data, particularly for complex requirements. Therefore, you should review these statements in detail before you run the script. Foreign keys that reference the primary key on the primary table are not changed.

All DML triggers that accessed columns on the primary table before the split are dropped.