Using Views to Hide Underlying Table Structures

Another benefit to using views to access data is that they act as an additional layer between the application and the actual tables. This allows the view to be used to hide any changes that might be made to the underlying tables. The view shown in Listing 27.1 retrieves data from several columns in the sysprotects and sysobjects tables. If an application uses queries that access these tables directly, any changes made to the tables in future releases of SQL Server could break the application. If the application accesses the data through the view, however, only the view needs to be altered to accommodate the changes in the base tables. As long as the view returns the expected data, the structure of the underlying base tables and columns is irrelevant to the application.

A common scenario that results in changes to tables in a production database is when the data grows so large that it has to be partitioned into multiple tables, usually for performance or maintenance reasons. For example, old data might need to be archived off to an archive table. In order to retrieve data from both the production table and the archive table, the application or end users need to execute multiple SELECT statements and combine the results together. Because SQL Server supports the use of the UNION statement in views, a view could be defined that makes the partitioned tables appear as a single table. These are referred to as partitioned views and are covered in more detail in a separate section later in this chapter.