Using view as source for application

Recently someone posed a puzzling question on the MSDN forums (How to reorder a column in a table which also has a foreign key). They needed to be able to add columns to a table in a specific order. If you know basics of relational theory, you would understand that the order of columns in a table is not guaranteed. But the problem was that the software that referenced that table expected referenced columns by the order rather than the name. Olaf Helper had a suggestion which the person asking the question totally ignored: create a view with the columns in the correct order and reference the view from the application.

I tell you that you I can get very lazy when developing applications referencing SQL Server. It is easy to simply reference the table or use “Select * from.” It works until someone makes a change (removes a column, etc.).

However, I can create a view and reference that view from my application code. I don’t have to worry about cosmetic changes (column order, column renaming) affecting my application. I make sure that the view has the columns in the correct order and have the correct data type in order to make the process work. In fact, if I completely drop a column, I can replace the column in the view with a calculated column (NULL, constants, expressions). The only problem is that the column is read-only at that point.

This is very much the principle behind using interfaces when programming. As long as my interface doesn’t change, I can change how the code is implemented without breaking the application that uses the interface.

It has an additional benefit that it is much easier to test views. You don’t have to worry about getting every detail of your query just right. All of that is handled behind the scenes with a view.

About Russ

Russ Loski is a data warehouse developer focusing on SSIS and SQL Server development. He has worked with SQL Server since 1998. He has used various technologies to move data from one source to others. He has worked for well-known companies such as the Bank of America, the Dallas Cowboys Football Club, T-Mobile and Coca Cola Enterprises. Currently he is developing a Data Warehouse for a large health care organization.
This entry was posted in ETL, Testing, TSQL. Bookmark the permalink.

Leave a Reply