By: Aaron Bertrand | Updated: 2010-06-29 | Comments | Related: 1 | 2 | 3 | 4 | > TSQL
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
- 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
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 also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-06-29
