Browse DevX
Sign up for e-mail newsletters from DevX


Satisfy Your Users with Views-2 : Page 2




Building the Right Environment to Support AI, Machine Learning and Deep Learning

What Are Views?
A SQL Server view is simply a query that has been predefined, named, and stored in SQL Server. Unlike a stored procedure or function, however, a view can be treated as a table. This allows you to simplify your data model for the user.

This is best illustrated with a simple example. Suppose you wanted to display the author's name and the book's title in the pubs database. This requires a three-table join between the authors, titleauthor, and title tables in the pubs database. Instead of joining the tables each time you want this information, you could create this view:

create view vw_authorbooks as select au_fname as 'Fname', au_lname as 'Lname',title from authors a inner join titleauthor ta on(a.au_id = ta.au_id) inner join titles t on (ta.title_id = t.title_id)

Now anyone needing the author and book names can simply query the view:

select * from vw_authorbooks this

Simplifying Access to Your Data
The example above illustrates the most common use of views: to simplify the query necessary to retrieve information. Does this mean you should replace all queries that need joins with views?

Definitely not! Programmers should be able to handle multi-join queries. With few exceptions, you, as the database designer, will probably not find it necessary to use a view to simplify queries for programmers building applications that retrieve information from a database. Views are most useful whenever you have users querying the database directly. Even though there are all sorts of point-and-click reporting tools meant to be used by "power users," a non-programmer can still find it difficult to build the correct query for the problem he or she has.

Consider a user of the pubs database who is trying to assess the importance of various authors to the business. He's decided that it would be useful to see the author's name, book's name, number of books published by that author, and the total sales for the particular book. The query needs several different joins and two subqueries as well.

Rather than give a reason for a frustrated user to call and berate you for the complexity of your database (or to point out a "bug" in your system because his poorly designed query returns clearly incorrect numbers) simply create a view instead, using the code in Listing 1. Then the user can simply select the necessary result with:

select * from vw_authsales

Another benefit to providing a prebuilt view to the user is that you can tune the view and ensure that it performs well before the user executes it. This prevents the possibility of a user's badly written, but functional, query from dragging down the performance of the system.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date