Make your SQL Server database changes backward compatible when dropping a column
By: Aaron Bertrand | Comments | Related: 1 | 2 | 3 | 4 | More > TSQL
In a previous tip (entitled "Making your database changes backward compatible: Adding a new column"), I introduced a topic that I have to deal with quite often: how do you make database changes without having to synchronize changes to the surrounding applications? Adding a column is relatively easy, since the applications can just ignore it in most cases. But taking a column away can be a little bit more work, as you may have to trick the applications into believing the column is still there. I often see systems where deprecated columns have stayed around "forever" -- because people are too afraid to remove them, or because they don't have the access or capability to modify the source code of the affected applications.
As with adding a column, there are certainly ways you can remove a column from the database without having to immediately change any of the applications that expect that column to exist. Going back to the previous example, where we have a users table and some CRUD-type stored procedures:
CREATE TABLE dbo.Users ( UserID INT IDENTITY(1,1) PRIMARY KEY, UserName VARCHAR(320) NOT NULL UNIQUE, HairColor VARCHAR(10), Gender CHAR(1) NOT NULL DEFAULT 'U' ); GO CREATE PROCEDURE dbo.User_GetDetails @UserID INT AS BEGIN SELECT UserID, UserName, HairColor, Gender FROM dbo.Users WHERE UserID = @UserID; END GO CREATE PROCEDURE dbo.User_Create @UserName VARCHAR(320), @HairColor VARCHAR(32), @UserID INT OUTPUT, @Gender CHAR(1) = 'U' AS BEGIN INSERT dbo.Users ( UserName, HairColor, Gender ) SELECT @UserName, @HairColor, @Gender; SET @UserID = SCOPE_IDENTITY(); END GO CREATE PROCEDURE dbo.User_Update @UserID INT, @UserName VARCHAR(320), @HairColor VARCHAR(32), @Gender CHAR(1) = NULL AS BEGIN UPDATE dbo.Users SET UserName = @UserName, HairColor = @HairColor, Gender = COALESCE(@Gender, Gender) WHERE UserID = @UserID; END GO
Let's assume for a moment that we want to stop caring about our users' HairColor (I'll leave the reasons to your imagination; pretend it is just an edict from upper management). Since the app will continue to pass the value in from the edit screens, and will continue to display it on the details screens, we need a placeholder value - so let's assume we want to show N/A in its place until all references to the data can be removed from the application.
In order to take this column away, we need to change the stored procedures to assign a fixed value to the data, and then drop the column. For the GetDetails procedure, it is simply changing the line that returns HairColor:
ALTER PROCEDURE dbo.User_GetDetails @UserID INT AS BEGIN SELECT UserID, UserName, HairColor = 'N/A', Gender FROM dbo.Users WHERE UserID = @UserID; END GO
For the Create procedure, we can set a default value to the incoming parameter (so that when the application can be updated, it can stop sending the parameter), and simply ignore the parameter altogether, by dropping the HairColor column and parameter references from the INSERT statement:
ALTER PROCEDURE dbo.User_Create @UserName VARCHAR(320), @HairColor VARCHAR(32) = NULL, @UserID INT OUTPUT AS BEGIN INSERT dbo.Users ( UserName, -- commented out for demonstration, but this should be deleted: -- HairColor, Gender ) SELECT @UserName, -- as above: -- @HairColor, @Gender; SET @UserID = SCOPE_IDENTITY(); END GO
With the Update procedure, the changes are almost identical. We set the incoming parameter to be optional, and don't touch that column when performing the update:
CREATE PROCEDURE dbo.User_Update @UserID INT, @UserName VARCHAR(320), @HairColor VARCHAR(32) = NULL, @Gender CHAR(1) = NULL AS BEGIN UPDATE dbo.Users SET UserName = @UserName, -- HairColor = @HairColor, Gender = @Gender WHERE UserID = @UserID; END GO
When all of the applications have removed their references to the HairColor column and parameters, then you can come back in and remove the portions that allowed the applications to continue operating without changes.
Before committing these changes, you will first want to identify any other views, procedures, triggers or functions that reference the HairColor column. You can rely on dependencies alone, but I don't really trust that mechanism because it can be impacted by order of object modification. I use a combination of the dependencies views in SQL Server 2008 (sys.sql_expression_dependencies, sys.dm_sql_referenced_entities, and sys.dm_sql_referencing_entities), and a brute force search against sys.sql_modules:
SELECT [object name] = QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) FROM sys.sql_modules WHERE definition LIKE '%HairColor%';
You may come up with some false positives here; you'll just have to look at the code for each hit to see if the reference is legitimate or benign... but it's a better alternative to relying on the dependencies views alone. You may run into difficulties determining column usage if any of your modules are encrypted (in this case, you will need source control access in order to identify references). Or if any database modules (never mind external applications) use dynamic SQL, SELECT * or BCP/BULK INSERT and rely on implicit column mapping or order.
And there are more complicated cases of course; for example, if the column you want to drop participates in an index or constraint, you will need to drop or disable those in order to drop the column. You can also run into trouble if the table is involved with replication or change data capture. We may investigate those cases more closely in a future tip; for now I just wanted to focus on making sure, as much as possible, that the surrounding code continues to work *as if* the column were no longer there.
- Examine planned database changes and determine if they could be introduced with minimal intrusion.
- If possible, split up your planned changes so that database modifications do not depend on application modifications.
- Plan to push database changes ahead of application changes, if necessary.
- Review the following tips and other resources:
About the author
View all my tips