Split Table
While it is ideal if a database design is done properly in the first place, oftentimes it happens that once a database is in production, problems with the original design appear. While SQL Refactor can’t resolve all design mistakes, it can help in two areas of “after-the-fact” table design issues. These include:
- Creating missing referential integrity tables.
- Splitting a single table into two tables to better represent reality.
In the above two cases, SQL Refactor has the ability to split a table into two separate tables (schema and data) and to also rewrite the dependent objects in the database so they can work with the old and the new table. As you can quite well imagine, this is a very complex process with many restrictions, which are outlined in SQL Refactor’s Help file. Rather than focus on a lot of details, let’s look at a simple example of how this process works.
In our example, our goal is to split a table to create a new referential integrity table. What we will do is to copy the required columns to the new table, making it the referential integrity table. Our goal is to ensure that when a new record is added to dbo.en_fob_tbl it must match a pre-existing record in the newly created referential integrity table. To accomplish this, I select the currently existing table called dbo.en_fob_tbl and then start SQL Refactor. The following screen appears.
In this screen, I select the object owner (schema) of the table to be created, along with its name. Then I get this screen.
Here, I need to tell SQL Refactor what columns I want copied (or moved) from the old table to the new one. Since I am creating a referential integrity table, I will need to copy the data I want in the new table. Above, you can see that I have selected one column to move over to the new table. I am only moving one column because this is the column I want to be used for referential integrity, so when a new record is entered into dbo.en_fob_tbl it must have a matching en_fob_key in dbo.en_fob_tbl2, otherwise the record cannot be entered.
Next, as you see below, I need to tell SQL Refactor which table, the old or the new, will be used to enforce the referential integrity.
Now that SQL Refactor has all the information it needs, it is ready to analyze the objects and create a script to make all of the necessary changes, as you can see in the screen below.
Notice above that there are three tabs, similar to tabs in the Smart Rename feature. In fact, they produce the same kind of information. Warnings show you any potential problems (none in this example) and Referencing Objects lists those dependent objects that need to be modified to work with the new table.
At any time, you can view and run the script (866 lines for this example) to make the requested changes. Below is a brief look at a portion of the script that was created.
Once the script is run, the new table is created and the data moved, and all dependent objects are modified so they recognize the new table. You will also need a way to maintain the newly created table, as SQL Refactor can’t do this for you.
While this is a very simple example, SQL Refactor can do much more. Compared to other tasks that SQL Refactor can do, the Split Table feature is much more complicated and subject to many more restrictions. Before you use this option, be sure you thoroughly read the Help file on this topic.
Is It Easy to Install, Use, and Administer by the Average SQL Server DBA?
SQL Refactor is very easy to install and use. In fact, you will be able to use most of SQL Refactor’s functions without reading the Help file.
Does It Put an Unbearable Performance Drag on SQL Server Production Servers?
Because SQL Refactor is a part of Management Studio, most of the resources it uses will be on the local desktop you use to administer SQL Server. The only load put on SQL Server is in those cases where SQL Refactor needs to access SQL Server for information, or when you run the scripts created by SQL Refactor. I would recommend that you run scripts during a time of the day when the database is not too busy, as some of the scripts might lock tables as they are running.
Does It Fill a Practical Need of the Average DBA?
If you are strictly an administrative DBA who doesn’t get involved with Transact-SQL code, then SQL Refactor won’t be of any benefit to you. But if you analyze queries for performance tuning, or need to write or rewrite Transact-SQL code, SQL Refactor can be a very useful and handy tool.
Recommendation
SQL Refactor is recommended by SQL-Server-Performance.Com. The best way to find out if SQL Refactor will work for you is to download it and try it out during the 14-day free trial.
]]>