SQL Schema Support Multi Version Tables

Background

In the event of significant modifications in a MSSQL database schema, for instance, the implementation of new data tables required by the modification. Also in order to provide support to an existing schema of tables and calls from .NET applications , I found a comprehensive solution that resolves this challenge involving a minimum of .NET application code changes.

Example

• An old Database schema contains Table_Old with fields (a int, b varchar, c decimal)
• The new Database schema contains Table_New with fields (z varchar(data of b in old), w varchar (data of a in old), t int)
• The source .NET code calls for retrieval of data from the Table_Old table via "Select * from Table_Old where .... " or alternatively by " Select a, b from Table_Old where …."
• The problem occurs in the requirement to support both database structures. Therefore, we are required to modify our query to " Select * from Table_Old where .... union select w , z, t Table_New where ...." or " Select a, b from Table_Old where .... union select w ,z from Table_Old where …… "

Solution

In order to provide an efficient migration and to support both of the systems (concurrently), we may create an INLINE database function called "Test".

For example, that will contain our mapping and union:

 "CREATE FUNCTION Test() 
RETURNS TABLE
AS RETURN
(SELECT * from Table_Old
union
SELECT w, z, t from Table_New )"

Changing query in application/stored procedures to: "Select * from Test() Table_Old where ...." or " Select a,b from Test() Table_Old where .... " .

This solution will provide you with the same names of the fields in the source .NET code and save the same ALIAS name of table and present prompt support and implementation with both structures.

*There is a limitation of MSSQL inside the functions!!! You cannot call other functions or stored procedures or dynamic SQL query.This solution does not support UPDATE/INSERT command of SQL.