I Love (SQL) views

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:

Please note: table / field names are for illustration only - and not indicative of Best Practice or requirement

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 apon 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 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 practice 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: Names may have been obfuscuated to obfuscuate identities etc.
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 wass then able to update all the locations where select statements were made against the child table, and replace references to the child table, with referrences 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 not used until recently - updatable views.

Updatable Views

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.

This again has been useful in an application where we are sharing a user data across 2 instances (both with their own database)

In most cases where this data needed to be accessed, only the data which was relevant to the individual instance should be queried.

To acheive this, we added an "ApplicationID" field to the user table(s), and then created a view in each instance to access that data.

App 1:

This was an existing application - therefore minimum impact of changes was an important criteria

App 2:

A quick update of the application to reference v_users instead of tbl_users, and both instances of the application were referencing data from the correct location (and as far as the application code was concerned, did not need to known where that data was coming from)

As these views are only accessing data from 1 table, it is possible to insert into and update the view directly.

(It was of cause also necessary to ensure that inserts into the view inserted the correct applicationID - although as this was done through a stored procedure, it only needed to be updated in a single location.)

These techiques are probably only suitable for specific bespoke requirments - for this particular example an existing customer wanted to synchronise their customer data from their (in house) CRM system to control access to a suite of previously separate, but increasingly interlinked web appplications they provide for their customers and partners - and for another company they have recently purchased. Using views allowed us to integrate a disparate suite of applications, with minimum impact on existing logic.