SQL Server 2012 : SQL Server Management and Development Tools - Object Explorer (part 2)

9/28/2013 7:39:05 PM

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.

Figure 3 The AdventureWorks database relationships viewed with the Database Diagram tool.


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.

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.

A cool feature in Management Studio is the capability to create and graphically join derived tables within Query Designer's Diagram pane.

Figure 4 Object Explorer's Query Designer.


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.

Figure 5 The server or database standard reports are a great way to quickly investigate your SQL Server.


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
  •  SQL Server 2012 : SQL Server Management and Development Tools - Registered Servers
  •  SQL Server 2012 : SQL Server Management and Development Tools - Organizing the Interface
  •  SQL Server 2012 : SQL Server Private Cloud - Upgrading SQL Server
  •  SQL Server 2012 : SQL Server Private Cloud - Discovering SQL Server Sprawl
  •  SQL Server 2012 : Storage Systems (part 7) - Measuring Performance - Storage Performance Testing
  •  SQL Server 2012 : Storage Systems (part 6) - Measuring Performance - Sequential Disk Access, File Layout, Flash Storage
  •  SQL Server 2012 : Storage Systems (part 5) - Measuring Performance - Storage Performance Counters, Disk Drive Performance
  •  SQL Server 2012 : Storage Systems (part 4) - Storage Technology - Remote Data Replication, Windows Failover Clustering, SQL Server AlwaysOn Availability Groups
  •  SQL Server 2012 : Storage Systems (part 3) - Storage Technology - Storage Tiering, Data Replication
  •  SQL Server 2012 : Storage Systems (part 2) - Storage Technology - SQL Server and the Windows I/O Subsystem
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone