5. Building Database Diagrams
The Database Diagram tool takes the Table Designer up a notch by adding custom table design views (see Figure 3)
and a multitable view of the foreign-key relationships. The Database
Diagram tool has its own node under each database. Each database may
contain multiple diagrams, which makes working with large databases
easier because each module, or schema, of the database may be
represented by a diagram.
Sometimes the Database Diagram tool is useful to
visually explore the schemas of large databases. Unfortunately, the
Database Diagram tool suffers from a few clumsy issues.
- It makes sense to create a separate diagram for each section of a
large database, and databases can be organized by schemas (as
AdventureWorks is).
- Previous versions of the Database Diagram tool were not schema
aware, however, the SQL Server 2012 version is and now displays the
table schema in parentheses after the table name.
- There's no way to select all the tables of a schema and add them to
the diagram as a set from the Object Explorer tree. The Object Explorer
does not permit selecting multiple tables. The Object Explorer Details
page enables multiple table selection but does not permit dragging the
tables to the design. Even worse, the Add Table dialog box in the
Database Diagram tool does not sort by the table's schema.
- The Add Related Tables option on the table's context menu helps solve this problem.
- Relationship lines have the frustrating tendency to become pretzels when tables or lines are moved.
Best Practice
If your goal is to print the database
diagram, be sure to check the page breaks and arrange the tables first,
or you might end up wasting a lot of paper. To view the page breaks,
use the tool's context menu or the Database Diagram menu.
6.The Query Designer
The Query Designer is a popular tool
for data retrieval and modification, even though it's not the easiest
tool to find within Management Studio. You can open it three ways:
- Using the Object Explorer, select a table. Using the context menu,
choose Edit Top 200 Rows. This opens the Query Designer, showing the
return from a select top (200) query in the results pane. You can now
open the other panes using the Query Designer menu or the toolbar.
- When using the Query Editor, use the Query Designer button on the
toolbar, use the Query ? Design Query in Editor menu command, or use
the Query Editor's own context menu.
- When you open the Query Designer from the Query Editor, it's a modal dialog box, and the results pane is disabled.
Tip
If editing 200 rows, or viewing 1,000
rows, seems like too many (or not enough) for your application, you can
edit those values in the Options ? SQL Server Object Explorer ? Command
tab.
Unlike other query tools that alternate between a
graphic view, a SQL text view, and the query results, Management
Studio's Query Designer simultaneously displays multiple panes (see Figure 4), as selected with the view buttons in the toolbar:
- Diagram pane: You can add multiple tables or views to the query and join them together in this graphic representation of the SELECT statement's FROM clause.
- Grid pane: Lists the columns displayed, filtered, or sorted.
- SQL pane: You can enter or edit the raw SQL SELECT statement in this pane.
- Results pane: When the query is executed with the Run button
(!), the results are captured in the results pane. If the results are
left untouched for too long, Management Studio requests permission to
close the connection.
Tip
A cool feature in Management Studio is
the capability to create and graphically join derived tables within
Query Designer's Diagram pane.
The Query Designer can perform Data Manipulation Language (DML) queries — (SELECT, INSERT, UPDATE, DELETE) besides SELECT.
The Change Type drop-down list in the Query Designer toolbar offers to
change the query from a default select query to an Insert Results,
Insert Values, Update, Delete, or Make Table query.
However, the Query Designer is no substitute for
the Query Editor. Unlike Query Editor, it cannot perform batches or
non-DML commands. Nor can it execute SQL statements using F5. Table and
column names can't be dragged from the Object Explorer to the SQL pane.
You can use the Query Designer to edit data directly in the results pane — a quick-and-dirty way to correct or mock up data.
7. Object Explorer Reports
No section on Object Explorer would be
complete without mentioning the dozens of great reports hidden within,
one of which is shown in Figure 5.
You can find these reports in the context menus of server, database,
and security ? login nodes. While all reports aren't listed here,
they're an excellent resource and one of the most underused features of
Management Studio.
You can install custom reports in any Object Explorer node by placing the report definition file in the following directory:
...\Documents and Settings\{user}\Documents\SQL Server Management
Studio\Custom Reports
