SQL Prompt 10

Splitting a table

SQL Prompt can 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
DML triggers that access data in the primary table are dropped when the table is split. You are strongly recommended to back up these triggers prior to launching the split table wizard.

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.

    We recommended you 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.


Didn't find what you were looking for?