SQL Prompt enables you to split a table in your database into two tables.

Splitting a table can be useful if you want to:

• normalize a database
  For example, by moving columns to a new secondary table to avoid insert anomalies.
• improve database performance
  For example, by moving large but rarely used columns to a secondary table.
• change the database design
  For example, to enable storing multiple customer addresses.

Example: splitting a simple table

The following example demonstrates how SQL Prompt splits a table. In the example, we want to split a table with three columns, Name, ID and Category:

In the table, the value of ID _determines the value of _Category. When splitting the table, we can use SQL Prompt to:

• copy ID to a new secondary table
• move Category to a new secondary table

Preparation

If you copy any of the following to a secondary table, the split table script will fail:

• XML columns
• Columns that allow NULL values
• Timestamp columns
• Partitioned columns

For more detailed information, see Preparing the table in detail.

Splitting the table

To split a table:

1. In the Object Explorer pane, right-click the table you want to split, and then click Split Table.
   The Split Table wizard is displayed:
   
2. In Secondary table name, type a name for the new secondary table.
   You must enter a name that is not already in use.
   If required, you can also select a new owner for the table.
3. Click Next.
   
4. To assign columns from the primary to the secondary table, select the column(s) and click the Copy > or Move > buttons.
   Note that:
   
   • you must copy at least one column to the secondary table so the tables can maintain a relationship
   • you cannot move primary key columns to the secondary table
   • you can select multiple columns to move, copy, or remove using Ctrl or Shift
     To remove an assigned column from the secondary table, select the column and click < Remove.
     To remove all of the assigned columns from the secondary table, click Reset All.
5. If required, reorder the assigned columns in the secondary table using the and buttons.
   You can reorder multiple columns simultaneously.
6. Click Next.
   
   By default, a foreign key is created on the primary table, to reference the primary key on the secondary table. This is for 1:1 or m:1 relationships.
   If you have copied all the primary key columns from the primary table to the secondary table, you can choose to create the foreign key on the secondary table, to reference the primary key on the primary table. This is for 1:1 or 1:n relationships.
7. Click Next.
   SQL Prompt creates the split table script and displays summary information:
   
   
   • Actions is a summary of the actions that the script will perform, in the order in which they will occur.
   • Warnings displays information that you should consider prior to running the script, including any reasons the script might fail.
   • Dependencies lists objects that will be modified by the script because they reference, or are referenced by, the primary table.
   The actions and warnings are included in the split table script, below the header. If you do not want to include this information in the script, clear the Include summary in the script check box.

8. Click View Script.
   The wizard is closed, and the split table script is displayed in a new SQL Server Management Studio query window.

   You are recommended to back up your database before you run the script

   For detailed information describing what happens to your objects when you run the script, see What happens when I run the split table script?

9. Once you have reviewed the script, run it.

Script failure or cancellation

If a script fails, or if it is cancelled, in most cases SQL Prompt uses transactions to roll back any changes.

SQL Prompt displays a warning message if it will be unable to roll back all the changes; in these cases, your database will be in an unknown state.