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.
Tip
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.
Tip
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!
Tip
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 http://format-sql.com 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
| 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.
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.
Results!
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).
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.
