275
Chapter 11: Projecting Data Through Views
11
SQL pane. You can add columns to the view by using the Diagram pane, the Grid pane, or
the SQL pane. The Add Table feature, available in the context menu or toolbar, can add
tables, other views, synonyms, and table-valued functions.You can add tables or other views to the new view by dragging them to the Diagram pane
from the Object Explorer or using the Add Table context menu option.You can use a toolbar button and a context menu item to add a derived table to the view,
but all it does is slightly modify the FROM clause to create a placeholder for the subquery.
Then you need to manually enter the SQL for the subquery in the SQL pane.The Verify SQL Syntax button in the toolbar verifi es only the T-SQL syntax; it does not
verify the names of tables, views, or columns in the SELECT statement. As a result, the
Query Designer may report the syntax of the query as correct, but when you try to create
the view, it may fail.To test the view’s SELECT statement within Query Designer, use the Execute SQL button,
F5, or CTRL+R. This runs the SELECT statement by itself, without creating the view.The Save toolbar button actually runs the script to create the view in the database. The
view must be a valid, error-free SELECT statement to be saved.For more details on using the Query Designer, refer to Chapter 5, “SQL Server Management and
Development Tools.”After the view is created, you can perform several tasks on the view using Object Explorer’s
view context menu:■ (^) Design the view: Opens the Query Designer tool with the view’s SELECT statement.
■ (^) Select top 1000 rows: Opens the Query Editor with a SELECT statement referencing
the view. You can modify the number of selected rows in Management Studio’s options.
■ (^) Edit top 200 rows: Opens the Query Designer with a SELECT statement referencing
the view, with only the results pane visible, and executes the view.
■ (^) Script View as: Management Studio can script the DDL statements to CREATE,
ALTER, or DROP the view, as well as sample DML statements referencing the view.
■ (^) View dependencies: This option is important because views, by defi nition, refer-
ence other data sources and are often referenced themselves.
■ (^) Policies: Apply and manage policy-based management policies for the view.
■ (^) Rename/Delete the view: To rename or drop the view, select it and press Rename
or Delete, respectively.
■ (^) Properties: Opens the properties dialog with pages for security permissions and
extended properties.
Double-clicking the view opens its subnodes: columns, triggers (instead of tasks), indexes
(indexed views), and statistics.
c11.indd 275c11.indd 275 7/30/2012 4:41:09 PM7/30/2012 4:41:09 PM
http://www.it-ebooks.info