How to refactor a SQL Server database by splitting a table

Depending on your environment, splitting a SQL table may have a positive impact on the overall database performance. For instance, in scenarios where a table contains some large but rarely used columns, moving them to a separate table will increase performance as the frequently used data will be stored in a much smaller table, and the rarely used data will be only looked up when required. The impact on performance caused by the occasional joining will be compensated just by having SQL Server look up the data that’s used more often in a table which requires less disk space leading thus to decrease in I/O and potentially increase in page cache hits.

Table splitting might also be necessary for database normalization or to accommodate changes in businesses requirements.

This time we’ll focus on the Split table option – to split a SQL database table into two tables by moving or copying columns from the original table into a new one by using ApexSQL Refactor, a free SQL Server Management Studio and a Visual Studio add-in that provides numerous features for SQL database formatting, refactoring of SQL code and uses extremely fast sql database formatter.

To perform a SQL table split, right-click the table to be split in the SQL Server Management Studio’s Object Explorer. Select ApexSQL Refactor from the menu and then the Split table sub-menu:

This will open the Split table dialog. The first thing that can be noticed is a red-blinking warning icon, which informs about the existence of a table with the same name under a specified schema. If you properly change the schema and table name, the warning will disappear

After choosing another available schema and specifying an additional (to be generated) table name, by clicking the Preview button, the following Preview tabs will be populated with information:

  • Generated script
  • Warnings
  • Sequence
  • Dependencies

The Create foreign key on and the Join type options are used to specify a foreign key type which will establish and enforce a link between the data in the primary and the new table

The Generated script tab

The preview tab displays a SQL script that will be executed in order to split the selected table. It can be opened in a new query window by selecting the Open button, ready for editing or executing via SQL Server Management Studio

The Warnings tab

Second in order, the Warnings tab will provide warnings, for example, if the original table was referenced in DML or SELECT statements contained within stored procedures, functions or triggers. If a column references in such a statement will be moved to a new table, the referring objects need to be reviewed and updated accordingly

The Sequence tab

This tab summarizes the generated SQL script in a tree-like form by showing actions in the order they will be performed. Please note that the action sequence is specifically generated in such a way as to prevent any dependency problems

The Dependencies tab

Finally, the Dependencies tab provides a list of dependent objects that refer to the table which will be split. To preserve dependencies, generated SQL script modifies dependent objects via ALTER statements accordingly

As you can see, the Split table feature provides a detailed preview in several categories of what’s affected and how a table splitting process will be executed. These can be further analyzed, or you can simply and safely split the table by selecting the Split button – it will create a secondary table and modify the primary one if any columns were marked to be moved. Note that the columns moved from the primary table cannot be moved back using ApexSQL Refactor features since the destination table cannot be an existing one.

March 17, 2015