This article is about altering a table and adding NOT NULL columns into it. The table being altered contains MILLIONS of records. One straight forward approach could be:
Step 1: Alter Table — Add Nullable Column.
Step 2: Update Newly Added Column.
Step 3: Alter Table — Modify New Column to Not Null.
I tried a new approach for this by which I created a new table using the SELECT INTO command and within this command included new columns as well by joining with the old table. This took less than 1/10th of the time compared to the standard approach.
However, I had to overcome the problem of creating CONSTRAINTS, INDEXES, foreign keys, etc. afresh on the new table as the SELECT INTO command does not transfer these. Also, there were other issues I discovered during quality checks. Most of them were resolved later on.
For the purpose of explaining the exact problem and the solution, let us consider an example:
We have two tables BUYER_ADDRESSES and ADDRESSES. ADDRESSES contains Buyer, Seller and Contact addresses, while BUYER_ADDRESSES contains buyer specific details only.
|
BUYER_ADDRESSES |
ADDRESSES |
|
================= |
========== |
|
ADR_ID |
ADR_ID |
|
B_REF_NO |
B_ADR |
|
| |
| |
|
| |
| |
|
| |
| |
There is a one-to-one relation between the two tables. Each table has around two million records. We need to move all the columns from the BUYER_ADDRESSES table to the ADDRESSES table. For simplicity, we’ll move one NOT NULL column, B_REF_NO, from BUYER_ADDRESSES to ADDRESSES.
Standard Approach and Analysis
Using the standard approach, I implemented the move as follows:
Step 1: Alter Table ADDRESSES Add B_REF_NO Varchar(10) NULL
Step 2:
Update ADDR
Set ADDR.B_REF_NO = B_ADDR.B_REF_NO
From ADDRESSES ADDR
Join BUYER_ADDRESSES B_ADDR ON (B_ADDR.ADR_ID = ADDR.ADR_ID)
Step 3: Alter Table ADDRESSES Alter Column B_REF_NO Varchar(10) NOT NULL
Steps 1 and 3 were not difficult to implement. However, Step 2 took a substantial amount of time to update.
Optimization
In order to optimize the change, I considered copying the join output to a temp table and using it in place of the original table after copying all constraints, indexes, foreign keys, etc. into it. Hence, the optimized solution to replace Step 2:
— Clone the ADDRESSES table and create a new table that is a superset of ADDRESSES and also includes the new column from the BUYER_ADDRESSES table, i.e., B_REF_NO.
Select ADR_SRC.*, B_ADDR.B_REF_NO
Into ADDRESSES_TARGET
From ADDRESSES ADR_SRC
Join BUYER_ADDRESSES B_ADDR ON (B_ADDR.ADR_ID = ADR_SRC.ADR_ID)
— Extract the schema definitions for Dropping/Creating all related objects for the ADDRESSES table and store them in a table. (The script for the stored procedure usp_ExtractSchema is given at the end of this article.)
EXEC usp_ExtractSchema ‘ADDRESSES’, “ADDRESSES_TARGET”
— Execute all “Drop” followed by “Create” statements from stored schema definitions. This would drop all the objects like Indexes, Constraints, foreign keys, etc. based on the original table as well as create the same on the new table. (The script for the stored procedure usp_ExecuteScripts is given at the end of this article.)
EXEC usp_ExecuteScripts
— Drop original table.
DROP TABLE ADDRESSES
— Rename cloned table.
EXEC sp_Rename ‘ADDRESSES_TARGET’, ‘ADDRESSES’
Side Effects and Solution
There were a few issues that came up during testing of the modified schema:
- Some of the constraints and foreign keys disabled in the database were accidentally enabled by the DROP-CREATE process.
- Computed columns’ formulas were missing in the new table.
- Triggers were still associated with the old table only.
The solution was to generate NOCHECK syntax for disabled objects. Computed columns were to be re-created with the original formula extracted from syscomments.
As for the triggers, I tried to extract the code from syscomments and re-create them for the new table. However, this did not work out as the code obtained from the Text column of syscomments was not properly organized and when I tried to create a trigger in dynamic SQL, it gave me a syntax error. So, I did that step manually.
Analysis
Using the optimized approach, I was able to complete the implementation in less than 1/10th of the time of the standard approach.
Future Scope
I could not successfully re-create triggers. This one thing, if added to the process, would make it fully automated.
Download the Script
sm_instance_ss_2000_tuning.zip (1.98 KB)
]]>