Depending on the 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 a decrease in I/O and potentially an increase in page cache hits.
Table splitting might also be necessary for database normalization or to accommodate changes in businesses requirements.
This time the focus will be 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 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, select the table in the SQL Server Management Studio’s Object Explorer, and from the ApexSQL Refactor main menu under the Other refactors sub-menu, click the Split table command:
This will open the Split table window. The first thing that can be noticed is a yellow-blinking warning icon, which informs about the existence of a table with the same name under a specified schema. If the schema and table name are properly changed, the warning will disappear:
After choosing another available schema and specifying an additional table name(to be generated), with a click on 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 Create script 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:
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 simply and safely split the table with a click on the Create script button, which will open script in the Query editor. After the Execute button is clicked, secondary table will be created and the primary table will be modified 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
