SQL Server 2012 : SQL Server Management and Development Tools - Using the Query Editor

10/1/2013 3:09:57 AM

With SQL Server 2005, the Query Editor carried on the legacy of SQL Server's Query Analyzer as the primary UI for database developers. In SQL Server 2008 and 2008R2, that experienced was enhanced and evolved. Now, with SQL Server 2012 it's an absolutely awesome product to work with!

Opening a Query Connecting to a Server

The Query Editor can maintain multiple open query documents and connections within the tabbed document area. Different queries may be connected as different users, which is useful for testing security. And the Query Editor can open and work with a .sql file even when not connected to a server.

When Query Editor first opens, it prompts for an initial login. To make further connections, use the File ? New Connection menu command.

The New Query toolbar button opens a new Query Editor document. There's some intelligence in how it selects the current database for the new query. If the Object Explorer has focus before the New Query button is pressed, the new query connects to Object Explorer's currently selected database. If the Query Editor has focus, the new query opens to the same database as the Query Editor's current query.

You can also switch a query's connection to another server using the Query ? Connection menu, the Change Connection toolbar button, or the Query Editor's context menu.

The query tab displays the most it can of the current SQL Server and database merged with the filename.

In some extreme cases, if SQL Server appears to not accept new connections, SQL Server listens on a dedicated port for a special diagnostic connection and tries to make a connection. A Dedicated Administrator Connection (DAC) is only possible if you are a member of the server's sysadmin role. To attempt a DAC connection using Query Editor, connect to the server with a prefix of admin: before the server name. For example, my servers' name is Torchwood, so connecting to it as admin:torchwood opens a DAC connection. DAC connections are also possible using the SQLCMD utility.

You can set Query Editor's connection bar's (at the bottom of the Query Editor) display color per connected server. This is a great visual cue. You can set the development server to green and the production server to red. When connecting to a server, open the connection dialog's options, and select Use Custom Color to set the color for that server.

Opening a .sql File

You can open a saved query batch file in multiple ways, and a huge trap you want to avoid follows:

  • If Management Studio is not open, double-clicking a .sql file in Windows File Explorer launches Management Studio, prompts you for a connection, and opens the file. Here's the gotcha: If you select multiple .sql files in Windows File Explorer and open them as a group, Windows launches a separate instance of Management Studio for each file — not a good thing. You end up running several copies of Management Studio.
  • If Management Studio is already open, double-clicking opens the file or selected files into a Query Editor document. Each file prompts you for a connection.
  • Multiple .sql files may be dragged from Widows File Explorer and dropped on Management Studio. Each file opens a Query Editor after prompting for a connection.
  • The most recently viewed files are listed in the Files ? Recent Files menu. Selecting a file opens it in the Query Editor.
  • The File ? File Open menu or toolbar command opens a dialog box to select one or more files.

Real-World Developing with the Query Editor
With the release of SQL Server 2012, SQL Server Data Tools should be the preferred development environment for all developers. If you wish to stick to developing within Management Studio, here are some tips for using the Query Editor as a developer.
  • View multiple scripts at the same time in Query Editor by right-clicking one of the documents and selecting New Vertical Tab Group. The selected document is the one that becomes the new tab to the right.
  • Liberally use bookmarks to save points in the script to which you'll need to refer back. For example, I'll bookmark a table's DDL code, and the CRUD stored procedures for that table, while I'm working on the stored procedures.
  • Begin every script with use database and set nocount on. Every script ends with use tempdb. That way if you run all the scripts, no script stays in the user database, and the initial create script can easily drop and re-create the database.
  • You can drag and drop column names from the Object Explorer tree onto the query window. This allows you to quickly pick and choose specific columns for a query without having to manually type out each column name.
  • If the default size of the text is too small, hold down the Ctrl key and use your mouse scroll wheel to increase/decrease the size of the font.
  • When more documents exist than can display as tabs, the easy way to select the correct tab is to use the Active Documents drop-down list, at the far right of the Query Editor next to the close document X button. This is also the best way to see if a script is still executing, but it does sometimes reorder the tabs.
  • If there's an error in the script, double-clicking the error message jumps to a spot near the error.
  • Use uppercase for all reserved words in the outer query, and then use PascalCase (sometimes called CamelCase) for user-defined objects and reserved words in subqueries.
  • IntelliSense Rocks! ‘Nuff said.
  • Use code outlining to collapse large sections of code. The Code Outliner can collapse multiline statements.
  • IntelliSense and Code Outlining can be turned off in Tools ? Options ? Text Editor ? Transact-SQL ? Advanced.
  • Dragging the folder named Columns (listed under a table in the Object Explorer) onto the query window will automatically list out all the columns in comma-delimited order. This can be a great time saver if you're trying to pick and choose specific columns for a query and don't want to manually type out each column's name.
  • The Query Editor provides a quick visual indicator of lines that have been edited. The Track Changes Indicator displays a thin yellow bar to the left of the line if the text is modified and a green bar if that change has been saved.
  • Use the SQLCMD toolbar button or Query ? SQLCMD Mode menu command to switch the editor to work with SQLCMD utility scripts.
  • While working with T-SQL code in Query Editor, you can get Books On Line (BOL) keyword help by pressing F1.
  • The new SQL Snippets feature is awesome! It's a quick way to generate T-SQL code. You can access this using the keyboard shortcut Ctrl+K,Ctrl+X, or right-clicking the Query Editor window and selecting Insert Snippet. You can also assign keyboard shortcuts for your favorite code snippets!

Out-of-the- box, Management Studio's Query Editor does not provide automatic formatting of T-SQL. Some free websites enable you to submit a SQL statement and will format the code. But consider using a third-party tool like SQL Prompt from Red Gate, highly recommended. If you don't want to purchase a third-party tool there are also free websites like that allow you to copy/paste code and have it formatted for you.

Shortcuts and Bookmarks

Bookmarks are a great way to navigate large scripts. You can set bookmarks manually or automatically using the Find command. Bookmarks work with double control key combinations. For example, holding down the Ctrl key and pressing K and then N moves you to the next bookmark. The Ctrl+K keys also control some of the other editing commands, such as commenting code. You can also control bookmarks using the Edit ? Bookmarks menu or the bookmark next and previous toolbar buttons. Table 1 lists useful shortcuts.

Table 1 Useful Query Editor Shortcuts

Shortcut Description
Ctrl+Shift+R Refreshes IntelliSense
Ctrl+K, Ctrl +K Adds or removes a bookmark
Ctrl+K, Ctrl +A Enables all bookmarks
Ctrl+K, Ctrl +N Moves to the next bookmark
Ctrl+K, Ctrl +P Moves to the previous bookmark
Ctrl+K, Ctrl +L Clears all bookmarks
Ctrl+K, Ctrl +C Comments the selection
Ctrl+K, Ctrl +U Uncomments the selection
Ctrl+K, Ctrl +W Opens the Bookmark Window
Ctrl+K, Ctrl +X Inserts Code Snippet
Ctrl+K, Ctrl+S Inserts Code Snippet to surround code with

The Bookmark Window displays a list of all bookmarks and offers tools to control bookmarks, navigate bookmarks, and even to change the name of a bookmark.

Bookmarks are lost if the file is simply saved as a .sql file; however, if the query is saved within a solution in the Solution Explorer, bookmarks are saved from session to session.

Query Options

When a batch is sent from Query Editor to SQL Server, it has several query option settings that go with the batch. The defaults for these settings can be set in Tools ? Options ? Query Execution ? SQL Server. The current query options can be viewed or changed in Query ? Query Options, as shown in Figure 1.

Figure 1 You can view and set Advanced and ASNI query options using the Query Options dialog. This view shows the Advanced Query Options.


Executing SQL Batches

As a developer's tool, the Query Editor is designed to execute T-SQL batches, which are collections of multiple T-SQL statements. To submit a batch to SQL Server for processing, use Query ? Execute Query, click the Run Query toolbar button, use the F5 key, or press Ctrl+E. Because batches tend to be long, and it's often preferable to execute a single T-SQL command or a portion of the batch for testing or stepping through the code, the SQL Server team provides you with a convenient feature. If no text is highlighted, the entire batch executes. If text is highlighted, only that text executes.

It's worth pointing out that the Parse Query menu command and toolbar button checks only the SQL code. It does not check object names (tables, columns, stored procedures, and so on). This actually is a feature, not a bug. By not including the object name–checking in the syntax check, SQL Server permits batches that create objects and then reference them.

The T-SQL batch executes within the context of a current database. The current database displays and may be changed, within the database combo box in the toolbar.


The results of the query display in the bottom pane, along with the Messages tab, and optionally the Client Statistics, or Query Execution Plan tabs. The Results tab format may be either text or grid; you can switch using Ctrl+T or Ctrl+D, respectively. The new format will be applied to the next batch execution.

Alternatively, the results can display in another tab, instead of at the bottom of the query document. In Tools ? Options, use the Query Results ? SQL Server ? Results to Grid tab, or in the context menu in the query window select the Results tab, and choose the Display Results in a Separate Tab option.

Another useful Result option is to play the Windows default beep sound file when a query completes. This can be set only in the Tools ? Options ? Query Results tab.

SQL Server 2000's Query Editor had a toolbar button to open or close the result pane. It disappeared with SQL Server 2005, but Ctrl+R still toggles the Query Editor Results Pane. And the command is still in the Customize Toolbar dialog, so you can fix the toolbar if you want. It's called the Show Results Pane, and it's not in the Query category where you'd expect to find it, but hiding in the Window category.

Viewing Query Execution Plans

One of Query Editor's most significant features is its capability to graphically view query execution plans (see Figure 2).

Figure 2 Query Editor's capability to graphically display the execution plan of a query is perhaps its most useful feature.


What makes the query execution plans even more important is that SQL is a descriptive language, so it doesn't tell the Query Optimizer exactly how to get the data, but only which data to retrieve. Although some performance tuning can be applied to the way the query is stated, most of the tuning is accomplished by adjusting the indexes, which greatly affects how the Query Optimizer can compile the query. The query execution plan reveals how SQL Server optimizes the query, takes advantage of indexes, pulls data from other data sources, and performs joins. Reading the query execution plans and understanding their interaction with the database schema and indexes is both a science and an art.

Query Editor can display either an estimated query execution plan prior to executing the query or the actual plan after the query is run. Both display very similar plans; the major differences (beside the wait) is that the actual plan can display both the estimated and actual row counts for each operation, whereas the estimated query execution plan knows about only the estimated rows counts.

In addition to the query execution plan, the Query Editor can display the client statistics, which is a quick way to see the server execution times for the batch. (Although Profiler is a much better tool for detail work.) Enable Include Client Statistics using the Query menu or toolbar to add this tab to the results.

  •  SQL Server 2012 : SQL Server Management and Development Tools - Object Explorer (part 2)
  •  SQL Server 2012 : SQL Server Management and Development Tools - Object Explorer (part 1)
  •  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
    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