Views and the smart Query Optimizer

In general I don't really like to use views. Although they have their advantages I often see it turn out bad in projects. The main reason is that people start to use complex views that were not written for their specific functionality and many tables get touched that have nothing to do with the result they are trying to retrieve.

The query optimizer is quite intelligent when it comes to resolve the query plan for a view. It is smart enough to ignore the tables you do not need to resolve your query.

CREATE VIEW SmartView AS
SELECT i.ItemTitle, i.ItemLink, u.UserName
FROM tbl_item i
JOIN tbl_user u ON i.OwnerID = u.UserID
GO

SELECT ItemTitle, UserName FROM SmartView

SELECT ItemTitle FROM SmartView

You can see SQL Server is quite intelligent when it comes to optimizing views. Unfortunately it still cannot prevent every situation of misuse. Some people have no problem of using SELECT * FROM SmartView and use only the ItemTitle field for example. When your view uses many (large) tables this might cause a lot of grieve on your database. One of the most extreme examples I have seen in real life is a SELECT from the biggest view in the project, causing millions of reads, where the result that was being used was the rowcount - which could actually be retrieved by counting the rows in one table. The key is obviously having a good DBA to review every query ;-)