By: Aaron Bertrand | Updated: 2010-07-26 | Comments (2) | Related: 1 | 2 | 3 | 4 | > TSQL
Problem
Every once in a while an entity can change. For example, marketing may decide that a feature called "Cars" should be more generic and called "Vehicles." Now, you may have tables and other objects with "Cars" in the name. You're going to re-label everything that is externally facing to say "Vehicles," of course, but what you do in the code and in the data model involves more of a choice.
You can leave the data model as is, and concede to dealing with the inconsistency between the exposed labels and what ultimately represents them, or rename all of your entities, refactor the code, and perform a full regression test. The former is the easy way out, and can lead to much confusion -- especially when you have new hires or, at least, folks that are new to this area of the code. But the latter can be excruciatingly painful if you have multiple applications, spread across multiple tiers, and that are developed and maintained on different schedules. In order to avoid a major and simultaneous re-write and deployment of all of the applications, you need to find a way to introduce the change gradually and ensure that each application can be updated when it is appropriate.
Solution
AAn entity name change can be tricky, but it can be handled in such a way that it doesn't break anything and there is no downtime or complicated change synchronization. As I've outlined in previous tips about backward compatibility (see Tip #2035 and Tip #2050), I am a big proponent of making database changes in such a way that they can be pushed to each environment before applications are deployed -- and even before application changes are made. Let's take the example above, where you have an entity called Cars. We probably have something like the following tables and procedures (I'll just show the table and relevant columns/parameters):
CREATE TABLE dbo.Cars(
CarID INT IDENTITY(1,1) PRIMARY KEY,
VIN VARCHAR(24) NOT NULL
--, ...
);
GO
CREATE PROCEDURE dbo.Car_Create
@VIN VARCHAR(24),
-- ... ,
@CarID INT OUTPUT
AS
BEGIN
INSERT dbo.Cars
(
VIN
-- , ...
)
SELECT
@VIN
-- , ...
;
SELECT @CarID = SCOPE_IDENTITY();
END
GO
CREATE PROCEDURE dbo.Car_GetList
AS
BEGIN
SELECT
VIN,
CarID
-- , ...
FROM
dbo.Cars;
END
GO
CREATE PROCEDURE dbo.Car_GetDetails
@CarID INT
AS
BEGIN
SELECT
VIN
-- , ...
FROM
dbo.Cars
WHERE
CarID = @CarID;
END
GO
For the application or middle tier, the important thing is that, at least in the short term, the interface (meaning the set of stored procedures and their input/output) stays the same. So even if you rename the table and column to reflect Vehicles, the interface is still expected to see the old column and parameter names. This means the following code should work both before and after the change:
DECLARE @CarID INT;
EXEC dbo.Car_Create
@VIN = 'car 1',
@CarID = @CarID OUTPUT;
EXEC dbo.Car_GetList;
EXEC dbo.Car_GetDetails @CarID = @CarID;
(As before, I am trying to keep to simple concepts here, so I assume that all data access is controlled via stored procedures. I am omitting proper error handling and other defensive coding techniques, and also pretending that you don't have more complex scenarios such as ad hoc SQL statements, ordinal column references, BULK INSERT techniques or replication. These situations will require a more thorough treatment, but the overarching concepts remain the same.)
I would approach this situation with the following steps:
- Rename the table from dbo.Cars to dbo.Vehicles
- Rename the column from CarID to VehicleID
- Add the new column to the GetList stored procedure, and use an alias to provide the old column name
- Add new, optional parameters to the Create and GetDetails stored procedures, making the old parameters optional as well
So here is the code I would use to accomplish these goals (again, error handling omitted):
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
EXEC sp_rename 'dbo.Cars', 'Vehicles', 'OBJECT';
EXEC sp_rename 'dbo.Vehicles.CarID', 'VehicleID', 'COLUMN';
GO
ALTER PROCEDURE dbo.Car_Create
@VIN VARCHAR(24),
-- ... ,
@CarID INT = NULL OUTPUT,
@VehicleID INT = NULL OUTPUT
AS
BEGIN
INSERT dbo.Vehicles
(
VIN
-- , ...
)
SELECT
@VIN
-- , ...
;
SELECT
@CarID = SCOPE_IDENTITY(),
@VehicleID = @CarID;
END
GO
ALTER PROCEDURE dbo.Car_GetList
AS
BEGIN
SELECT
VIN,
CarID = VehicleID,
VehicleID
-- , ...
FROM
dbo.Vehicles;
END
GO
ALTER PROCEDURE dbo.Car_GetDetails
@CarID INT = NULL,
@VehicleID INT = NULL
AS
BEGIN
SET @VehicleID = COALESCE(@VehicleID, @CarID);
SELECT
VIN
-- , ...
FROM
dbo.Vehicles
WHERE
VehicleID = @VehicleID;
END
GO
COMMIT TRANSACTION;
Now the apps can continue to call the Car_ stored procedures, and use the @CarID parameters, changing over to use the @VehicleID parameters in their own good time, and eventually replacing the Car_ procedures with equivalent Vehicle_ procedures.
That said, another step you may want to do to keep the schema even more current during the transition is to use synonyms. A synonym is like a pointer or alias, and allows you to reference an object under a simpler or different name. Typically I use synonyms to simplify references to objects in other databases or in linked servers, but I have also used it to ease the transition of an entity name. Basically you can rename the Car_ stored procedures as Vehicle_ stored procedures, then create synonyms that map the old names to the new objects:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION; EXEC sp_rename 'dbo.Car_Create', 'Vehicle_Create', 'OBJECT'; EXEC sp_rename 'dbo.Car_GetList', 'Vehicle_GetList', 'OBJECT'; EXEC sp_rename 'dbo.Car_GetDetails', 'Vehicle_GetDetails', 'OBJECT'; CREATE SYNONYM dbo.Car_Create FOR dbo.Vehicle_Create; CREATE SYNONYM dbo.Car_GetList FOR dbo.Vehicle_GetList; CREATE SYNONYM dbo.Car_GetDetails FOR dbo.Vehicle_GetDetails; COMMIT TRANSACTION;
This way, there are no Car_ procedures cluttering your object list, however calling the stored procedures can still work the way they used to, since the Car_ synonyms still point to code that is essentially the same. This way when your apps have all converted to using the Vehicle_ stored procedures, all that is left to handle is dropping these synonyms, and then cleaning the references to Car columns / parameters. Until then, both of these code blocks will continue to work:
-- old naming:
DECLARE @CarID INT;
EXEC dbo.Car_Create @VIN = 'car 2',
@CarID = @CarID OUTPUT;
EXEC dbo.Car_GetList;
EXEC dbo.Car_GetDetails @CarID = @CarID;
-- new naming:
DECLARE @VehicleID INT;
EXEC dbo.Vehicle_Create
@VIN = 'vehicle 1',
@VehicleID = @VehicleID OUTPUT;
EXEC dbo.Vehicle_GetList;
EXEC dbo.Vehicle_GetDetails @VehicleID = @VehicleID;
In my experience, a little planning can really help you prepare the database for changes long before they will ever make it to the applications. In fact, I've found it a much bigger challenge to keep track of which apps have changed, which lets me know when I can drop the deprecated items from the database. (Though no matter what the developers tell me, I don't have much trust in anything except what a server-side trace will tell me -- perhaps ammo for a future tip.)
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-07-26
