Make your SQL Server database changes backward compatible when dropping a column


By:   |   Updated: 2010-06-29   |   Comments   |   Related: 1 | 2 | 3 | 4 | More > T-SQL

Problem

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.

Solution

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.

Next Steps


Last Updated: 2010-06-29


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools