Views

This is one of the simplest database objects (at least views can be simple). On the surface, a view is nothing more than a SELECT query that is saved with a name in a database. Ask any modern-day programmer what they believe to be the most important and fundamental concept of programming. They will likely tell you that it is code reuse. Writing every line of code, every object, every script, and every query represents a cost or risk. One risk is that there could be a mistake (a bug) in the code. The cost of a bug is that it must be fixed (debugged) and tested. Buggy applications must be redeployed, shipped, installed, and supported. Undiscovered bugs pose a risk to productivity, business viability, and perhaps even legal exposure. One of the few constants in the software universe is change. Business rules will change, program logic will change, and the structure of your databases will also change. For all of these and other reasons, it just makes sense to reduce the number of objects that you create and use in your solutions. If you can create one object and reuse it in several places rather than duplicating the same effort, this limits your exposure to risk. Views promote this concept of code reuse by allowing you to save common queries into a uniform object. Rather than rewriting queries, complex queries can be created and tested and then reused without the added risk of starting over the next time you need to add functionality to an application.

Virtual Tables

One of the great challenges facing users is dealing with the complexity of large business databases. Many tools are available for use by casual database consumers for browsing data and building reports. Applications such as Microsoft Excel and Access are often used by information workers, rather than programmers, to obtain critical business management and operational information. A typical mid-scale database can contain scores of tables that contain supporting or special-purpose data. To reassemble the information stored in a large database, several tables must be joined in queries that take even skilled database professionals time and effort to create effectively. As you've seen in many examples, this is often not a trivial task. From the user's perspective, views are tables. They show up in most applications connecting to a SQL Server, along with the tables. A view is addressed in a SELECT statement and exposed columns, just like a table.

From the developer or database designer's perspective, a view can be a complex query that is exposed as if it were a simple table. This gives you an enormous amount of flexibility and the ability to hide all of the query logic, exposing a simple object. Users simply see a table-like object from which they can select data.

Creating a View

Defining a view is quite simple. First of all, a database user must be granted permission to create database objects. This is a task that you may want to have performed only by a database administrator or a select number of trusted users. Because creating most views isn't particularly complicated, you may want certain users to be granted this ability.

Several simplified tools are available that you can use to create views. Microsoft Access, Enterprise Manager, and Visual Studio all leverage the Transact-SQL Query Designer interface to create and manage views. The process is just about the same in all of these tools because they all actually expose the same components. The following section steps through creating a view using Microsoft Access. I will not demonstrate each tool because the process is nearly identical.

Creating a View in Microsoft Access

Microsoft Access is a popular tool that can be used to manage and query a SQL Server database. I don't intend for this example to serve as a full-blown Access tutorial so it's just going to demonstrate some of the basics. The following example is an Access Data Project (ADP) connected to the AdventureWorks2000 database.

In the database window shown in Figure 13-1, you can see that the database contains a stored procedure and two views, all listed on the Queries tab.

Figure 13-1:

Click the New button on the toolbar to create a new query. The New Query window opens, allowing you to create a few different types of objects. Choose Design View, as shown in Figure 13-2, to create a new view and then click OK.

Figure 13-2:

The next window should look familiar. Access uses a version of the Transact-SQL query designer window. In the default view, the table diagramming pane and the columns grid are displayed. The Access product designers made this tool appear as much as they could like the original Access SQL query designer by hiding the actual SQL script. The Add Table window, shown in Figure 13-3, is automatically opened. Use this to select and add three tables: Product, ProductCategory, and ProductSubCategory. Because of the relationships that exist between these tables, inner joins are automatically defined in the query.

Figure 13-3:

Select the Name column from the ProductCategory table (using the checkboxes in the table windows), the Name column from the ProductSubCategory table, and the columns you see in Figure 13-4, from the Product table. Using the Alias column in the columns grid, define aliases for the following three columns:

Figure 13-4:

Table.Column	Alias
ProductCategory.Name	CategoryName
ProductSubCategory.Name	SubCategoryName
Product.Name	ProductName

Also, designate these three columns for sorting in the order listed by dropping down and selecting the word Ascending in the Sort Type column. Check your results against Figure 13-4 and make any adjustments necessary.

If you close the window, using the Close button in the top-right corner, Access will prompt you to save the view. Enter a name for the new view in the Save As dialog, as demonstrated in Figure 13-5. I've always made it a point to prefix view names with v, vw, or vw_ and to use Pascal-case (no spaces, with the first letter of each word capitalized).

Figure 13-5: