Tips for Implementing Views

To keep your views working properly, you need to pay attention to their structure and performance. Here are some tips for keeping your views tip-top:

* Don't create views based on other views. Though it's tempting, layering views can hide a myriad of problems. If you're the slightest bit tempted to create a "view-on-view," take a closer look at your needs; perhaps your initial view should be changed or altered. * Be very careful when dropping objects from your database. Dropping a referenced object can cause your view to become essentially useless. Always execute the sp_depends system stored procedure first to make sure your views don't depend on the object you're getting ready to drop. * Develop a naming convention for your views that helps you distinguish them from tables. * The syscomments system table contains information for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure in your database. This is where you can find the text of the SQL definition statement. Do not delete comments from this table. If an entry is manually deleted, the view will no longer function properly. If you want to protect the view definition, use the WITH ENCRYPTION option with either the CREATE VIEW or ALTER VIEW statement. (Remember to save your script definition before you encrypt the script.) * When you create a view, specify the owner of the view as dbo. In addition, make dbo own all of the objects referenced by the view. If you don't, you'll have to specify the owner name when you query the view.