Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Make your SQL Server database changes backward compatible when renaming an entity


By:   |   Last Updated: 2010-07-26   |   Comments (2)   |   Related Tips: 1 | 2 | 3 | 4 | More > T-SQL

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


Last Updated: 2010-07-26


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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.



    



Monday, May 21, 2012 - 5:41:39 PM - Aaron Bertrand Back To Top
PGN, sorry for the delay in responding to the tip. The purpose for the extra column is so that if you have two apps calling the stored procedure, and they were previously referencing CarID, you don't have to change them both *at the same time* - you can change one to reference VerhicleID, and put off changing the other one until later.

Thursday, June 02, 2011 - 10:24:22 AM - PGN Back To Top

Nice article.  Can you explain why you added the second column of vehicleID to the Car_GeList procedure (snippet below).  I don't see why that's needed.  Thanks!

ALTER PROCEDURE dbo.Car_GetList
AS
BEGIN
    SELECT
        VIN,
        CarID = VehicleID,
        VehicleID

        -- , ...
    FROM
        dbo.Vehicles;
END

 


Learn more about SQL Server tools