By: Aaron Bertrand | Comments (4) | Related: 1 | 2 | 3 | 4 | > TSQL
Problem
As multi-tier architectures grow over time, it is often challenging to coordinate those changes across the data, logic and presentation tiers. Unless planned and implemented carefully, an act as simple as adding a column to a table can grind all of the components of your application to a halt. While some of us have comfortable 12-hour maintenance windows every weekend, many of us are bound by service level agreements that are much more strict. So we must find ways to introduce fixes and new features with zero downtime, and without requiring every single component to be refactored at the same time.
Solution
There are several methods you can use to introduce fixes and new features to an existing application without disrupting existing applications or data. Personally, I always start at the database layer, and make every effort to ensure that my changes are backward compatible. This way, I can deploy the database changes to each environment in turn (dev, QA, staging, production) independent of changes that will need to take place in API and other logic layers, as well as the presentation layers. Since it is often the case that application development can take longer than database changes (and depend on them), and because we can often have several different applications relying on the same data tier and those applications can be on different development schedules, it is almost always preferential to push database changes ahead of the curve.
The challenge, of course, is defining and properly implementing what is meant by "backward compatible." Essentially, a change is backward compatible if it can be introduced to the environment without touching any other parts of the application - and the system keeps running as if nothing happened. I will demonstrate by providing a few examples of what I do in my environments in certain scenarios, dealing mainly with simple CRUD operations (create, read, update and delete). Let's pretend we have a very simple table called Users, with very simple CRUD procedures, and we want to add a new column called "Gender" without having to make changes to the application(s) right away. Here are the existing objects (note that error handling is omitted for brevity, and I'm only listing the procedures we'll actually change due to the new column):
CREATE TABLE dbo.Users
(
UserID INT IDENTITY(1,1) PRIMARY KEY,
UserName VARCHAR(320) NOT NULL UNIQUE,
HairColor VARCHAR(10)
);
GO
CREATE PROCEDURE dbo.User_GetDetails
@UserID INT
AS
BEGIN
SELECT
UserID,
UserName,
HairColor
FROM
dbo.Users
WHERE
UserID = @UserID;
END
GO
CREATE PROCEDURE dbo.User_Create
@UserName VARCHAR(320),
@HairColor VARCHAR(32),
@UserID INT OUTPUT
AS
BEGIN
INSERT dbo.Users
(
UserName,
HairColor
)
SELECT
@UserName,
@HairColor;
SET @UserID = SCOPE_IDENTITY();
END
GO
CREATE PROCEDURE dbo.User_Update
@UserID INT,
@UserName VARCHAR(320),
@HairColor VARCHAR(32)
AS
BEGIN
UPDATE dbo.Users
SET UserName = @UserName,
HairColor = @HairColor
WHERE
UserID = @UserID;
END
GO
When adding a new column, especially one that is required (not NULLable), there are a couple of things to keep in mind:
- Unless I have a really good reason to do otherwise, I avoid the SSMS table designer, and add the column using ALTER TABLE - which puts the column at the "end" of the table.
- On larger tables, adding a NOT NULL column with a default value can lock the table for a long time. It may make sense to first add the column as NULL, populate the values in chunks, and then add a NOT NULL constraint after the fact.
Let's assume that we want to add the gender column, that we want it to be not nullable, and that the default value is 'U' (unknown/unspecified). We can do so like this, assuming the table is not huge:
ALTER TABLE dbo.Users ADD Gender CHAR(1)
NOT NULL
DEFAULT 'U';
-- may want to add CHECK Gender IN ('M', 'F', 'U')
Now the table has the added column, every user has the value set to 'U', and the application and CRUD procedures are none the wiser. Since the column has a default value, insert statements do not need to include it; and update and select statements won't need to know about it because they aren't aware of it and don't need to try and change it. We can introduce those changes to the stored procedures while still making their presence transparent to the application(s).
First, let's deal with dbo.User_GetDetails. In this case we can simply add Gender to the output list, and - assuming you aren't doing anything like "for each field in rs.fields" - the application will just ignore it until it is ready to use the column in its output:
ALTER PROCEDURE dbo.User_GetDetails
@UserID INT
AS
BEGIN
SELECT
UserID,
UserName,
HairColor,
Gender
FROM
dbo.Users
WHERE
UserID = @UserID;
END
GO
For the insert procedure, we can have the parameter default to 'U' so that the application does not have to provide a value right away (but you can test specifying this value from T-SQL or unit tests). Note that, like adding columns to a table, I always add parameters to the end of the parameter list, since application code (and other stored procedure code) might not name their parameters and simply specify the values in order.
ALTER 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
For the update procedure, we'll take a slightly different approach. Because you might have already updated some of the rows with values other than 'U', you'll only want to update the value if it is specified explicitly. So we allow the parameter to default to NULL, and use COALESCE() to update the column - which means if you updated it to 'M' previously and nothing is specified, it will stay as 'M':
ALTER 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
As you can see, it does not take a lot of effort to introduce database changes without impacting other parts of your application, allowing you to perform testing of fixes and new features without requiring simultaneous application development. Today we dealt with adding a column; in future tips, we will investigate other schema and data changes.
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
