To plan how the columns in the table are to be split and to prepare your data appropriately, consider the following points.

Copying columns

You must ensure that at least one column is copied to the secondary table so that it exists in both tables. SQL Prompt uses the copied columns to create the foreign key, and the primary key for the secondary table. Therefore, before you open the Split Table wizard, you must ensure that the primary table contains the columns that you want to copy. The values contained within the copied columns must uniquely identify each row of data in the secondary table.

Data

For columns that remain in the primary table, all data is retained.

For columns that are copied or moved to the secondary table, SQL Prompt uses the DISTINCT keyword when possible to ensure that only unique data is copied or moved.

However, for the following data types the DISTINCT keyword cannot be used:

• XML data
• SQL Server 2000 large object (LOB) data (text, ntext, image)

Therefore, if you choose to move or copy any of these data types, SQL Prompt cannot use the DISTINCT keyword when the secondary table is populated. This means that all data is moved or copied to the secondary table, including any duplicate data. If the copied columns contain duplicate data, the primary key cannot be created on these columns, and the script will fail.

Computed columns

If you move or copy a computed column, you must ensure that any columns that are referenced by the computed column are also moved or copied. If you do not do this, SQL Prompt displays a warning, and you cannot generate the script.

If you copy a computed column and all its referenced columns, and the computed column is persisted, SQL Prompt converts the computed column to a normal column in the secondary table so that data is retained. For example, a column that computes two columns that have data type char(50) is changed to a normal column of data type char(100).

If you copy a computed column and all its referenced columns, and the computed column is not persisted, SQL Prompt displays a warning; if you have chosen to create the foreign key on the secondary table, the script will fail because it is not possible to create a foreign key on a column that is not persisted.

If you move a computed column and all its referenced columns, the computed column remains a computed column in the secondary table.

You cannot move a column from the primary table if it is required by a computed column in the primary table (but you can copy it).

XML columns

XML columns cannot be copied because a primary key cannot be created on an XML column.

Large object data

Columns that contain large object (LOB) data cannot be copied because a primary key cannot be created on LOB data.

Timestamp columns

Timestamp columns cannot be copied or moved. This is because data cannot be inserted into timestamp columns and the original values cannot, therefore, be inserted into the secondary table.

Common language runtime data

If a common language runtime (CLR) data column is copied or moved to the secondary table and the CLR data is not byte ordered (IsByteOrder is false), the script will fail. This is because the DISTINCT keyword cannot be used on a CLR column that is not byte ordered.

NULL values

NULL values are not allowed in copied columns in the secondary table. This is because the copied columns are used to create the primary key on the secondary table, and primary key columns cannot contain NULL values.

If any columns that you copy allow NULL values, a warning is displayed. When the script is run, these columns will be modified to disallow NULLs (set to NOT NULL).

If any of the data in the copied columns contains NULL values, the script will fail.

Identities

If you copy or move an identity column, the identity is copied or moved, and data will be inserted using the IDENTITY_INSERT setting.

Partition schemes

You cannot move a column over which the primary table is partitioned to the secondary table. If you attempt to do this, SQL Prompt displays a warning. You can, however, copy the column if required.

DML triggers

All DML triggers that access the data in the primary table are dropped. You are recommended to save these triggers prior to running the split table script if you will want to recreate them following the split.

Triggers that do not access the primary table are preserved, but are not duplicated on the secondary table.