This chapter is from the book
This chapter is from the book
This chapter is from the book
Views and the Query Designer
Views and the query designer fit together naturally because you use the query designer to create views. A view is a preconfigured select query that is stored in the database and can be used just like a table. In most cases, views can be updated just like queries. One of the most common uses for views is when creating reports. You do not have to use the query designer to create views. You can also create them using a Transact-SQL script. We will look at that method later when we examine the database project. For now we will use the query designer.
To open the query designer (see Figure 3.19), first we must find the views. Using the Server Explorer, navigate to and expand the Northwind database. Expand the Views tab and select Alphabetical List of Products. To open the view in Design mode, right-click on it and select Design View from the context menu (Figure 3.20).
)
Figure 3.19. The query designer.
)
Figure 3.20. The Design View button.
If you have used the query designer in Microsoft Access this should appear familiar to you. The screen is divided into four panes: Diagram, Show Grid, SQL, and Results
The Diagram Pane
The Diagram pane is where you add the tables you want to include in your view. There are two ways to add tables:
Drag and drop your table from the Server Explorer. This is the simpler way.
Use the Add Tables dialog. Use this method if you are going to create a complex view using numerous tables. To use the Add Tables dialog, click the Toolbar button.
Next we must define the joins between tables. We do this by dragging a foreign key column to a primary table and dropping it. This is a very important step. If we do not create a join, we will get a Cartesian product when we run the query. A Cartesian product is a result that joins every single row from one table with every single row of another, where the number of rows is equal to the product of the number of rows of both tables. Suppose we have a table with 20,000 rows and another table with 500 rows. The Cartesian product of these would be 10,000,000 rows, not exactly what we desire.
There are three main types of joins:
Inner — In this type of join, both columns used to join the tables must have values that satisfy the join condition.
Left or right outer — One table will include all rows, while the other table will contain only rows that satisfy the join condition. If the table on the left side of the join condition is to return all rows, we call it a Left Outer Join. If the table on the right is to return all rows we call it a Right Outer Join. Sometimes we leave out the word outer and say only Left Join or Right Join. When a row is returned that has a missing other side of the join, the columns on the opposite side are filled with nulls.
Full outer – All rows from both side of the join are included, filling the missing halves with nulls.
If you right-click on the join line, then select Property Pages from the menu, you can see the details of the join (Figure 3.21).
)
Figure 3.21. Join property page.
Show Grid Pane
Next we have to tell SQL Server which columns we want to include in the view. You should only include columns that you will actually need in your output (screen, report, etc.) so that network traffic will be minimized. You can select columns to include two ways. You can check them off in the table diagrams or you can choose them from the drop down lists in the Show pane. The other columns allow you to set the sort order, specify whether or not to output the columns, and specify selection criteria.
SQL Pane
In this pane, the SQL statement being built by the Diagram and Show Grid is displayed. You can also type an SQL statement directly. I find that this is often easier for me. If you know SQL well and are familiar with the database schema you are working with, it is faster to type the statement directly than to click and drag with the mouse. After you've typed the statement, you can check the syntax by clicking the Check Syntax button on the toolbar.
Results Pane
The Results pane displays the data returned by the view. You can use this to test your query before using it in an actual program.
Stored Procedures and Triggers
The stored procedure editor works just like a regular code window. It supports full color-coded syntax. Stored procedures and triggers are written using the Transact-SQL language. Stored procedures are self-contained programs. They can do pretty much anything you want, but usually are used to perform operations on the database that cannot be done using a single SQL statement. A trigger is a special kind of stored procedure that executes when a row is inserted, updated, or deleted from a table.
Transact-SQL language is best left to a book of its own, and there are plenty on the market. The stored procedures we will use will be pretty simple. We will limit ourselves to procedures that fetch data or update the database using parameters.
One interesting thing about the Procedure window is that SQL queries are outlined in blue. If you right-click inside this outline you will see a Design SQL Block entry in the context menu. If you select this item, a Query Design window opens. You can now design your query graphically and when you save the query it will appear in the stored procedure.