Love the View!

I've always been a fan of Views within SQL Server - I've found them especially useful when creating applications which need to be able to access data from external sources, without necessarily being aware of where that data came from - or even of its precise format.

One feature which I have found especially useful, is the ability to select and join to data from within a different database, from within a view.

This can be done as follows:

So specifying an explicit database name in front of the schema name causes the data to be retrieved from the named DB, on the same server, rather than from the db in which the view is placed. I believe it is also possible to specify a database which is on a different server, using Linked Servers - but I've never had a requirement to check this out so far.

What's great about this, is that if I reference this view rather than the tables directly within my application / stored procedures, I can change where the data is coming from without impact on the application. This has been particularly useful in the past when a system is reliant upon external data, but needs to be developed in isolation. I can create a table in my local DB which mirrors the structure of the remote data source, and point my view at that - and then when the application is ready for integration, I can update the view to point to the remote DB.

Another cool thing with views, is that you can put conditional logic within them, and use them to perform real time data merge operations.

For instance, yesterday I had a requirement to link 2 applications which had previously been completely separate, but which contained some shared information. The point of the process was 2 have one master source for the information, which fed all the applications. Now in theory the data within the applications had been cleansed - so there should have been a matching record in the child application, for each record in the parent application. In practise I knew that this was not the case, and that there were some records in the child DB, which did not have a corresponding record in the Parent DB. What I needed to do, was to display the data from the Parent DB if it existed, otherwise show the data from the child DB - but to highlight this information so it was clear that it was an orphan. I did this using a combination of an outer join, together with the isnull() function and case statements:

Note how that all field aliases remain the same as field names in the child table, so selections can be made against this view in the same way as they could against the original table.

Having made these changes, I was then able to update all the locations where select statements were made against the child table, and replace references to the child table, with references to this view.

Due to the complexities of this view, it is only usable as a Read Only data source - but this was not too much of an issue in this case, as all inserts / updates to the data were in a single procedure - and hence it was relatively simple to modify.

This brings me on to another feature of views - one which I had been aware of a while, but had never used before today - updatable views.

It is possible to insert / update data within a view directly, with the following restrictions:

Extract from SQL Server BOL http://msdn2.microsoft.com/en-us/library/ms187956.aspx

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
• The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
  • An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
  • A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
• The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
• TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

Effictively, if the view is a select statement from a single table, you can access it as if it were the base table itself.

I had to make it possible for 2 copies of one of our applications (identical application code, separate databases) to share a couple of the tables - i.e. data was only to be written to or read from one of the database for both applications.

All the data access in this application was through the use of stored procedures - and in total there were 41 procedures that accessed one or both of these tables. By creating views in each of the databases, which referenced the original tables, and were identical in structure, I was able to replace all references to these tables, with references to the views. Any selects, inserts, updates or deletes could be done against the views, in the same way as they could be done against the original tables. By simply adding the name of database1 into the select statements within the views in database2, I was able to re-route all access to the tables in DB 1.

Replacing all the references to the original tables within the procedures with the views was not completely simple - but I'll write about the solution for that another day - I think this post is long enough already!

Please note that the applications described here are large existing legacy applications - and hence the requirements to introduce functionality and override aspects of the original intended functionality. There are undoubtedly much better ways of achieving the same end result if the applications had been designed with this kind of functionality in mind.

I am aware also that this is placing a strong dependency between the databases of the different applications, which will need to be taken into consideration if either of the applications or the hosting environment is changed.

All of the above is specific to SQL server - I'm sure that much of it does apply to other DBMS's - but I've no idea what!