For the older generation of SQL developers, discouraging the use of views is tantamount to accusing a respectable aging dowager of immorality. No way! They’ve always been such benign and conventional database objects.

Views are virtual tables, constructed from base tables and other views. They behave just like tables, and are a very simple means to “pre-assemble” data into an easily-queried format for the end user, thus protecting them from the underlying complexity of the SQL, and providing a layer of protection to your base tables.

Views are doubtless an occasionally-useful tool for the database developer, when designed wisely. Unfortunately, abuse of views has increased proportionately with the widening mismatch between the relational model and the object-oriented world of C# development. The result has been the increasing use of “Godzilla Views” that attempt to deliver entire business objects to LINQ or EF users, hiding dozens of tables, hundreds of columns, and almost always returning far more data than is really necessary. Like Godzilla, such views will inevitably turn on their creators and wreak awful havoc on the databases they infest, through the performance and maintenance problems they cause.

So when should you use views? Under what circumstances is it a bad idea? Much of the SQL literature is curiously silent on the issue. Surely, in the light of advice discouraging their use, it is time to take a reasoned stand and start a debate about what guidance IT departments should give over the use of views. I’d like to start by suggesting a few ‘best practices’.

• DO use views as the interface to grid controls, and so on, in applications that are designed to only work against table structures. You should never expose base tables to data controls
• DON’T use views as a general-purpose means to deal with ad-hoc, or parameterized queries from applications. Use a properly-designed interface that is based on stored procedures, wherever possible.
• DO use views to promote flexibility and portability – for example, isolating all query cross-references to a remote database in a single view.
• DO consider the careful use of Table-Valued UDFs as an alternative to views, where you also need to perform processing, pass parameters and so on.

Help us finish the list, and correct the advice we’ve given. Better still, if you have view code on which you’d like a second opinion, send it to us and we’ll get our authors to review it and potentially offer alternative solutions. We’d also like to hear of real instances of Godzilla views of frightening complexity which have caused difficulties with production systems. The best example will receive a prize.

Cheers,

Tony.