Exploring SQL Server Views

Like tables, views open in conventional Access datasheets. Figure 20.12 shows the Order Details Extended view, which is almost identical to the Jet version. The Expr1 column duplicates the ProductID column, a minor fault in the NorthwindCS.sql script. Although the new project designer has an extended Format property for columns, the script doesn't set this property value, nor does it set the Caption property. Access 2003 continues to display the Discount field as a decimal fraction, instead of a percentage. The Datasheet View toolbar for views is identical to that for Jet queries. Like tables, the Sort Ascending, Sort Descending, Filter by Form, Filter by Selection, and Find buttons of the toolbar operate on the locally cached copy (snapshot) of the view's Recordset, not the view itself.

Figure 20.12. NorthwindCS's Order Details Extended view has a spurious Expr1 column, doesn't format the Discount column as percent, and doesn't emulate the Jet version by setting the Caption property for the columns.

Note

The Data properties page of ADP forms and reports has two properties Server Filter and Server Filter by Form added by Access 2002. These properties let you apply a server-side filter to a table, view, or function instead of the cached Recordset for the form or report. Using server-side filters minimizes the amount of data sent to the client, which reduces network traffic and improves performance.

Changing to Design view opens the graphical view designer, the structure of which resembles the Access query designer. The primary difference between the two designers is a 90-degree rotation (transposition) of the axes of the design grid. Click the SQL button of the toolbar to display the SQL statement that creates the view (see Figure 20.13). The upper diagram pane displays field lists for each table with a symbolic join. The key symbol indicates the primary-key field(s) and the infinity symbol ([if]) specifies the foreign-key field. Primary-key field(s) of the tables appear in bold type.

Figure 20.13. The project designer's equivalent of Jet's Query Design view adds a convenient SQL pane.

The view designer adds the following five design-related buttons to the toolbar:

• Diagram toggles the display of the diagram in the upper pane. You specify the type of join in a properties sheet.

• Grid toggles the display of the column information, which is where you alias columns, select the source table for the column, specify whether column data appears in the view, and add a TOP 100 PERCENT modifier, ORDER BY clause, or WHERE criteria.

• SQL toggles the lower text box that displays the Transact-SQL statement that generates the view.

• Verify SQL Syntax runs a grammar check on the SQL statement but doesn't execute the query to create the view.

• Group By adds a GROUP BY expression that includes every member of the SELECT statement's field list. Group By properties let you add a ROLLUP, CUBE, or ALL modifier for complex aggregation.

The SQL statement for the Order Details Extended view illustrates substitution of the SQL CONVERT function for VBA's CCur function to change the data type of the calculated ExtendedPrice column to money. Access 2002 added the dbo schema prefix to provide three of the four elements of SQL Server's four-part naming convention. Linked databases require use of three-part names to resolve duplicate table and field names in local and linked servers.

The complete SQL SELECT statement without the extra Expr1 field for the view is as follows:

 SELECT dbo.[Order Details].OrderID, dbo.[Order Details].ProductID,    dbo.Products.ProductName, dbo.[Order Details].UnitPrice,    dbo.[Order Details].Quantity, dbo.[Order Details].Discount, CONVERT(money,    (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) *    (1 - dbo.[Order Details].Discount) / 100) * 100 AS ExtendedPrice FROM dbo.Products INNER JOIN dbo.[Order Details]    ON dbo.Products.ProductID = dbo.[Order Details].ProductID 

To explore adding new tables and setting JOIN properties, do the following:

1. Move the Products and Order Details tables to the right to make room for the addition of the field list of the Suppliers table.

2. 

   Click the Add Table button to open the Add Table dialog, which has Tables, Views, and Functions pages. On the Tables page, select Suppliers and click Add to add an INNER JOIN between the SupplierID fields (see Figure 20.14).

   Figure 20.14. The process of adding a new table and its columns to a view is similar to that for Access QueryDefs.

   

3. Close the Add Table dialog, and click the SupplierID and CompanyName check boxes of the Suppliers table to add the columns to the view.

4. Right-click the join line between the Suppliers and Products tables, and choose Properties to open the Join Properties dialog. You select the type of join from a drop-down list of the available operators. Mark the All Rows from Suppliers check box to create a RIGHT OUTER JOIN, which squares the left side of the diamond join symbol (see Figure 20.15). (Marking both check boxes creates a FULL OUTER JOIN; the join symbol becomes a square. Scroll the SQL pane to read the JOIN changes to the view's SQL statement).

   Figure 20.15. The Properties dialog for JOINs in a view offers the same functions as Jet's Join Properties dialog in a different format. SQL Server adds the capability to specify the JOIN operator.

   

5. Clear the All Rows from Suppliers and All Rows from Products check boxes if you marked them, and close the Join Properties dialog. Click the Group By button to add a GROUP BY clause containing all columns, which adds Group By symbols to fields selected in the field lists. Like Jet Totals queries, tabbing or selecting the Group By cell for each column lets you choose Group By, Sum, Avg, Min, Max, Count, Expression, Where, and many additional SQL Server-specific options from a drop-down list (see Figure 20.16).

   Figure 20.16. Clicking the toolbar's Group By button adds a Group By column to the design grid and a GROUP BY clause to the SELECT statement. The effect is similar to specifying a Jet Totals query.

   

6. 

   Click the toolbar's Properties button or right click the window and choose Properties to open the Properties dialog for views, which differs from the original Access 2000 version (see Figure 20.17). On the View page, you can add the DISTINCT qualifier to eliminate duplicate rows; specify CUBE, ROLLUP, or ALL extensions to the GROUP BY clause; and specify a TOP n [PERCENT] view by marking the TOP check box, typing the n value in the text box, and marking the PERCENT, WITH TIES, or both check boxes. You also can add text to the extended Description and SQL Comment properties.

   Figure 20.17. Access 2003's View Properties dialog adds many more properties to the simple, single-page version of Access 2000's project designer.

   

   Note

   Many of the properties on the View page have counterparts in Jet's Query Properties dialog. The other property values of the View page such as Bind to Schema and Update Using View Rules require familiarity with T-SQL, the subject of the next chapter.

7. 

   Click the Columns tab, and select the Discount column in the Column Name text box. The enabled labels indicate extended properties whose values you can set for the specific field data type, such as Description, Format, Decimal Places, and Caption for most numeric data types (see Figure 20.18). Close the Properties dialog.

   Figure 20.18. The Columns page lets you set additional extended property values.

   

   Note

   The Lookup and Data pages of a view have the same set of properties as those for tables.

8. Click the Verify SQL Syntax button on the toolbar to check the changes you made in the preceding steps. You receive a message box confirming the statement's validity. Deliberately introducing an error results in a message providing the approximate location of the mistake. Figure 20.19 illustrates the message that occurs if you delete the left bracket from the second instance of [Order Details] in the query's SQL pane and click Verify SQL Syntax.

   Figure 20.19. You receive an error message if the SQL Server query parser detects incorrect syntax. In this case, the parser interprets 'Order' as the SQL keyword ORDER because of a missing left bracket.

   

9. Close the view designer and don't save the changes you made. Modifications to the database are temporary until you close the designer or run the query and elect to save changes.