I often receive questions about how we propagate changes to table structure in Visual Studio Team Edition for Database Professionals (VSDBPro)?
When you allow users to make arbitrary schema changes, the problem is that they expect miracles to happen when these need to be deployed. We are trying very hard to make life as easy as possible, but we are restricted in what we can do at deployment time based on the underlying support provided by the database. So lets go back to the basics for a moment. SQL Server, for the longest time, has been offering a small and very restrictive set of DDL operations when it comes to making physical table structure changes.
As you can see the number of primitives we have to work with to deploy you changes is not very extensive. This is the reason why lots of tools fall back to the CREATE-COPY-DROP-RENAME (CCDR) strategy, for example Table Designer inside SQL Server Management Studio uses this method exclusively. This means they will:
VSDBPro first tries to deploy the table changes using an ALTER TABLE statement, however when we cannot implement the changes using the 5 primitives mentioned above, we also have to fall back the CCDR strategy, because that is the only remaining option. VSDBPro does execute the CCDR (Create-Copy-Drop-Rename) inside a transaction to maintain schema consistency in case a failure would occur. But it is almost needless to say that the CCDR strategy is not the most efficient way of deploying schema changes in a production environment where you are dealing with large numbers of rows inside a table.
In case it is not obvious, here are some of the CCDR drawbacks:
So the question is how to prevent this?
The only real way to prevent this is to restrict the allowed changes on the table structure! However that does not go over very well with the consumers of the schema, most application developers want to see columns in certain order, with certain names etc. So how can we achieve this?
The best practices therefore is to always abstract your physical structures. This means that you have VIEWs abstracting all tables. This results in a minimum of one VIEW per TABLE.
At that point, you restrict yourself to only making structural changes that can be implemented using the ALTER TABLE primitives. The rest of the changes are reflected in the VIEW.
The goal is to disambiguate the VIEW as much as possible, this means you need to specify a full column list. You are not allowed to use SELECT *, because you need to guarantee the ordinal position of each column inside the result set. Each column will have an ALIAS to reflect the column name. This abstracts you from name changes in the underlying table structure. The base view only reflects a single table, you can create more views that abstract tables, but at a minimum you should start with a 1-on-1 mapping from the table to the view.
The following shows an example of how the initial version of the view will look, given a table structure. The view and the table structure will evolve over time, with the goal being to always have the most efficient (and therefore fastest) way to deploy physical structure changes and isolate the application as much as possible from breaking changes.
CREATE TABLE [Person].[Address]( [AddressID] int NOT NULL IDENTITY(1, 1) NOT FOR REPLICATION ,[AddressLine1] nvarchar(60) NOT NULL ,[AddressLine2] nvarchar(60) NULL ,[City] nvarchar(30) NOT NULL ,[StateProvinceID] int NOT NULL ,[PostalCode] nvarchar(15) NOT NULL ,[rowguid] uniqueidentifier NOT NULL ROWGUIDCOL ,[ModifiedDate] datetime NOT NULL) ON [PRIMARY];
CREATE VIEW [Person].[vAddress]AS SELECT [PA].[AddressID] AS [AddressID] ,[PA].[AddressLine1] AS [AddressLine1] ,[PA].[AddressLine2] AS [AddressLine2] ,[PA].[City] AS [City] ,[PA].[StateProvinceID] AS [StateProvinceID] ,[PA].[PostalCode] AS [PostalCode] ,[PA].[rowguid] AS [rowguid] ,[PA].[ModifiedDate] AS [ModifiedDate] FROM [Person].[Address] AS [PA];
Let walk through the various changes you want to be able to make and how to implement them:
This type of change falls into two possible categories: the first is where the data type change is supported through an automatic implicit conversion by SQL Server and the second is where you need an explicit conversion to handle the conversion of the type. The implicit conversion matrix is documented as part of the SQL Server CAST and CONVERT statement.
The next interesting question is how to name these views? This really depends on what you want and when you start using VIEWs that abstract your tables. When I have the freedom, I normally like to name the VIEWs that wrap my tables like I would normally name my tables and name the tables with some prefix. Some people choose to use different schemas to keep them apart but use the same name. Personally, I do not like this approach, but with all naming conventions, there is a lot of personal preference involved. The key is to have a standard that works for you. The method of abstracting tables using views, works regardless of the naming convention used, but you can make your life (and the lives of the people consuming the schema objects) a lot easier by having a consistent and easy to understand naming convention.
Abstracting your physical table structure using VIEWs can result in a more efficient and easier deployment of your schema changes without having to sacrifice any flexibility when it comes to making the requested and required schema changes that are based on the needs of your schema consumers.
-GertD"DataDude" Software Architect