What aren't you seeing?

Problem
When writing queries I either need to reference an existing data model or visualize the tables in my head in order to complete my query.  Does a visual query tool exist in the SQL Server 2005 tool set?  If so, how can I access it and use it to streamline my development process?

Solution
The SQL Server 2005 Management Studio ships with Query Designer which in a nutshell is a visual querying tool to build SELECT statements.  (*** NOTE *** - This tool was available in SQL Server 2000 from Enterprise Manager.) This is an add-in to the Query Window offering a means to add specific tables from a specific database to the designer.  You can see a visual representation of the tables with the referential integrity all in one screen, then build the needed query with JOINs, WHERE and\or ORDER BY clauses.

How do I access the SQL Server 2005 Management Studio Query Designer?

1. Open SQL Server 2005 Management Studio
2. Click the 'New Query' button from the standard tool bar or press Ctrl+Shift+Q
3. On the SQL Editor tool bar, select the needed database from the database drop down list in our case 'BaltSSUG'
4. On the SQL Editor tool bar, click on the 'Design Query in Editor' button
5. Follow the steps in next section to select the needed tables, then build and test the query

What are the capabilities with the SQL Server 2005 Management Studio Query Designer?

1. Select the user defined tables needed for the query.  *** NOTE *** - System tables are not available as options in this tool.

2. Once the tables load in the top of the interface, arrange the tables so they make sense.  Next select the columns that you want to use in the query as well as the alias, sort type, sort order, etc.  Notice how the T-SQL for the SELECT statement is being created and updated as columns are chosen.  Finally, press the 'OK' button and the query will then load into the original query window as show in step 3.

3. The query as setup in step 2 will load into the original Query Editor window where the query can be executed, tested and modified to meet your needs.

Can this tool help me with INSERT, UPDATE or DELETE statements?

Yes - as a matter of fact.  Just right click in the query pane and select Change Type and then the type of query desired.

Next Steps

• The next time you are having a hard time pulling your query together, consider the Query Designer.  Many developers and DBAs are visual and this tool serves them well to get started and stay productive.
• As you begin to work with developers and DBAs new to SQL Server, let them know about the Query Designer and show them how it can help them get off the ground.
• Consider the Query Designer in addition to the visual data modeling tools in SQL Server 2005 Management Studio when you are starting to work with a new system or just can not remember the data model on a large system.
• Based on initial research, the Query Designer is available from the SQL Server 2005 Express to Enterprise editions.
• Check out these related tips on MSSQLTips.com:
  • SQL Server 2005 Exposed = Data Modeling Tools
  • Simple way to create table structures using an Excel spreadsheet
  • Cross Walk - SQL Server Query Plans
  • Specifying Max Degree of Parallelism for a Query
  • SQL Server Data Modeling Tools

• Using the Services applet to Administer SQL Server Services Remotely
• Insert Extended Characters into a SQL Server Table Using OSQL and T-SQL
• Read the end of a large SQL Server Error Log
• Free Microsoft Tools to Help Setup and Maintain PerfMon for SQL Server
• Free Performance Profiler Tool for SQL Server 2005 Express
• More...

• Building a Data Mart with Integration Services

• Discuss this tip, post comments and feedback