"Views are nothing more than named, saved SELECT statements" is always my opening line in a lecture or conversation about views. But no matter how simple views are, they still generate many questions and create a lot of confusion. As soon as I start to talk about views, people ask:
- Do views have any data stored directly with them?
- Do views slow performance?
- What permissions do I need to create views?
- What permissions do I need to use views?
- Can I update a view?
- Can I use SELECT * in a view?
- Can I index a view?
- How does a view differ from a function or a stored procedure?
- Can I export or replicate a view?
- Can I access a view if the tables on which the view is defined are dropped?
Because so many people have questions like these, I decided to write a series of articles that explain everything you need to know about using views and achieving the best query performance with them. To test the syntax for this article's examples, you can download the ViewsPartIExamples
.sql commented script file at http://www.tsqlsolutions.com, InstantDoc ID 27229.
By definition, a view is a query that you've decided to save for reuse. Instead of retyping the query every time you need the query's data set, you simply access the view as if it were a table. A query that references the view (a view query) uses the same plan and records the same performance as a query that refers directly to the base tables (a base-table query). For example, let's assume you need to see the details of an order in the Northwind sample database. You can write the five-table join that Listing 1 shows to return data about who purchased the order items, what they purchased, the quantity, and the order ID. Or you can create a view to hide the complexity of this five-table join, then access the view every time you need this data. If you want to base the view on the SELECT statement, you first need to name it according to the data that the base-table query defines. For Listing 1's data, let's call the view ProductCategoryDetailsView. Then, to create the view, you add the following code to Listing 1's query:
CREATE VIEW dbo.ProductCategoryDetailsView
AS
SELECT...
In this example, you can cut and paste the five-table join query into the SELECT section of the view. (However, in some situations, you can't place the same query directly into a view because of certain restrictions within views. I'll address that topic in a future article.)
When you need to access the data that this view references, the only thing you need to type is the following view query:
SELECT *
FROM dbo.ProductCategoryDetailsView
You can also add a WHERE clause, as the following code snippet shows:
SELECT *
FROM dbo.ProductCategoryDetailsView
WHERE CategoryName = 'Beverages'
Whether you type the base-table query with the added WHERE clause or type the view query with the WHERE clause, both queries execute at the same level of performance. Listing 2 shows the view query with the added WHERE clause and the base-table query with the added WHERE clause. Figure 1 shows the query plans that the two queries in Listing 2 produce. As you can see, the queries take the same amount of time, claim the same overhead, and generate the same plan. The only difference between the two is the amount of T-SQL code you need to type in each time.
The most fundamental benefit of using a view is simplifying access to the data. The view that you just created successfully simplifies access because
- you don't have to know the underlying schema to retrieve the data you need.
- you don't have to type in the base-table query every time you need to access this data.
- you can use the asterisk (*) shortcut to simplify accessing all the columns that the view defines.
- you can add WHERE clauses to limit the data that the query returns and customize your query against the data.
I have one warning about the * shortcut. We programmers, who are an inherently lazy lot, often forget that SELECT * simplifies the query but often complicates the query processing. When you use SELECT * against base tables, SQL Server must return all columns. However, when you reduce in the view definition the number of columns that the view returns, using SELECT * is efficient because SQL Server doesn't have to access all the columns from the base tables. In fact, using * against a view imposes no negative effect on performance.
Although you need to be judicious in your use of the * shortcut, adding WHERE clauses has the biggest potential for problems. If you use the five-table join query or view to return order data that includes customer, product name, category name, and ID, you can easily add a WHERE clause. And if you want to add a WHERE clause that limits only the product type or the customers or the categories, using the view to execute this query works well. For example, if you want to return only beverages, you can execute the following statement:
SELECT *
FROM dbo.ProductCategoryDetailsView
WHERE CategoryName = 'Beverages'
If you want to retrieve orders only for customers whose name begins with B, the following statement will efficiently return the correct results:
SELECT *
FROM dbo.ProductCategoryDetailsView
WHERE CompanyName like 'B%'
However, what happens if you refine your search further? Would you feel comfortable using this view if you wanted to return a list of customers who've placed an order and whose name begins with B? To return that specific data set, you can write a view query as above — with the SELECT statement referencing the view and with the added WHERE clause — or if you have access to the base tables, you can write the query as follows:
SELECT distinct C.CompanyName
FROM dbo.Orders AS O
INNER JOIN dbo.Customers AS C
ON C.CustomerID = O.CustomerID
WHERE CompanyName like 'B%'
Prev. page
[1]
2
next page 
