#0126 – SQL Server – SSMS – Tutorial – Part 01 – Introduction to the Management Studio and Query Editor (L100)

Per Books-On-Line, The Microsoft SQL Server is a database management and analysis system for e-commerce, line-of-business, and data warehousing solutions. The SQL Server Management Studio is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server.

The SQL Server Management Studio

For those who have worked with Microsoft SQL Server in the past (i.e. circa SQL Server 7 or SQL Server 2000 era), the following would be familiar terms to you:

  • Enterprise Manager
    • Provided a Microsoft Management Console (MMC) compliant UI to administer, configure and manage a given set of SQL Server instances
  • Query Analyzer
    • Provided a development interface to develop and debug T-SQL code
  • Analysis Manager
    • Provided an IDE for data warehousing solution development and setup

Starting Microsoft SQL Server 2005, all the 3 interfaces mentioned above have been combined into one – the SQL Server Management Studio, popularly known as the SSMS. In addition to the above, the SSMS also features a broad variety of graphical tools and rich script editors to provide access to SQL Server developers and administrators of various skill levels.

Accessing the SQL Server Management Studio

In a default installation of Microsoft SQL Server 2008 R2, the SSMS is installed in the directory – C:Program FilesMicrosoft SQL Server100ToolsBinnVSShellCommon7IDE.

Program shortcuts in the Start Menu are created for the SSMS. The user can either use these shortcuts or directly execute “ssms” from the “Run…” window.

Connecting to a SQL Server instance

To work with any instance of SQL Server, the first thing that we need to do is to connect to the particular instance. Almost each window of the SSMS establishes its’ own connection to the SQL Server.

Whenever SSMS is launched, one of the first screens that one encounters is the login screen. Based upon the user inputs, the login screen builds a connection string that will be used by the various components of the SSMS. The very basic information that needs to be provided to this screen is the name of the SQL Server instance, the authentication type, user name and password. However, clicking on the “Options” button provides you the option to customize the following parameters of a connection:

  • Default Database (from a list of all databases on the chosen instance)
  • Network protocol (auto-determines the protocol to use – shared memory for local connections, TCP/IP for remote instances)
  • Network Packet Size (default is 4096 bytes)
  • Connection & Execution timeouts (default is 15 seconds)
  • Encryption details (not enforced by default)
  • Any other connection parameters that one might want to use
Login Dialog Connection Properties Dialog box Additional connection properties
Login dialog Login dialog – setting connection properties Login dialog – add any additional connection parameters here.

The windows of SQL Server Management Studio

Upon successful login, you will see a number of windows and tools in the SQL Server Management Studio, namely:

  1. Query Editor
  2. Object Explorer
  3. Registered Servers
  4. Properties Window
  5. Templates window

Anatomy of the SSMS windows

Most of these tools & windows, if not available, can be accessed from the View menu.

Launch points of SSMS windows

IMPORTANT: It is important to note that the SQL Server Management Studio is based off the Visual Studio platform. Therefore, the following usability features are directly inherited by the SSMS:

  • Maximize the user workspace for development and management
  • Reduce the number of windows open at a time
  • Provides the ability to customize the user environment

Because it is based upon the Visual Studio shell, the SSMS allows the user to control the window size and therefore, the amount of space that the user wishes to allocate for different windows. The windows can be moved to different locations, undocked and even moved out of the SSMS frame. The query editor windows can be opened in the multi-document interface (MDI) modes also.

Pinal Dave (blog), a Microsoft Database & BI technology evangelist in India, wrote about the multi-monitor support capabilities in the upcoming release of SQL Server – SQL 2012 (code named: “Denali”). You can read about this feature here: http://blog.sqlauthority.com/2011/01/24/sql-server-2011-multi-monitor-ssms-windows/

Query Editor window

One of the most important parts of the SSMS environment is the Query Editor window, which is what will be used to write and execute scripts.

To launch a new query editor window, in addition to opening a pre-existing query, you can follow any one of the following navigation paths:

  1. File -> New -> Database Engine Query
  2. File -> New ->Query with current connection
  3. File -> New ->Analysis Services MDX/DMX/XMLA

Getting comfortable with the Query Editor UI

Because this is one of the most important windows that we would be working with, it is important to get a feel of the various components of this window:

The major parts of the Query editor window are:

  1. The script editor window
    • A feature-rich text editing environment supporting find and replace, bulk commenting, custom fonts and colors, and line numbering. Some types of editors contain additional features like outlining and auto-complete
  2. Status bar
    • Connection state
    • Server Instance Name and version
    • Login Name and associated SPID number
    • Current database name
    • Time taken for last query/batch execution
    • Number of rows returned during last query/batch execution

The different parts of the Query editor window

Apart from the above components, the Query Editor window also has the following child-windows which are visible depending upon various factors such as user configuration, whether the query execution has taken place or not. These windows can be seen in tabbed, docked or floating modes. These windows are:

  1. Results tab
    • Displays the result of a query execution. Results can be in grid or text format
    • This gets reset when the next execution of the query/batch is performed
  2. Messages tab
    • Displays any messages that may be generated as part of query execution
    • This gets reset when the next execution of the query/batch is performed
  3. Error List
    • Displays syntax and semantic errors as you edit the T-SQL scripts
    • This gets reset when the next execution of the query/batch is performed
  4. Client Statistics
    • This window displays the query execution information grouped into categories
    • For multiple rounds of execution, this window shows the aggregated average information

We will take a look at these windows further down the article.

Intellisense & Error List windows

The following is an example of a basic T-SQL query based on the AdventureWorks2008R2 sample database.

USE AdventureWorks2008R2
SELECT * FROM HumanResources.Employee

After logging in to a particular SQL Server instance using SSMS, type in the given script. As you type along in the Query editor window, notice that the Query Editor window prompts you with a list of possible alternatives that it thinks you would type in next. This prompt is what we call – “Intellisense”.

Intellisense in action - SSMS for SQL2012/"Denali"

The good thing about “Intellisense” is that the prompts not only contain the list of options, but there are small icons next to the objects that help the user identify the nature of the object, i.e. whether it is a database, schema name, variable name, a table or a column name, etc. Also, we can see some nice tooltips towards the side of the Intellisense prompt that contains a description about the object.

A user can navigate through the Intellisense prompt by using the arrow keys on the keyboard (which I find to be the fastest way to do so) or use the mouse. Once the desired object is selected, all the user has to do is press the Enter or the Tab key (when using the keyboard) or double-click on the mouse.


Now, navigate out to the View menu, and choose to display the “Error List” window. Assume that you made a mistake during the typing of the query.

Launching the Error List window

Error List window in action
Launching the Error List window Error List window in action

As you can see, the Error list window immediately detects syntax and semantic errors as you type along. Double-clicking on the error brings you to the concerned line that encountered the error. Once corrected, the error clears out from the Error List.

However, the Error List window has a few drawbacks. You can read about them in Aaron Bertrand’s post (blog | twitter) referenced in the Further Reading section below.

Query Regions

Prior to the Management Studio for SQL Server 2008 coming out, all code within the Query editor window was a giant block, which caused readability issues. Let’s say for example that you are working on a lengthy (approximately more than 500 lines) stored procedure/script consisting of several logical sections. After the development of one particular section is complete, you may not need to look at it again, and might prefer to selectively hide it such that it does not interfere with your working area on the query editor.

The SSMS for SQL Server 2008 introduced a new usability feature – Query Regions. A vertical line on the left edge of the editor window uses a square with a minus sign (-) to identify the start of each collapsible code region. When you click a minus sign, the text of the code region is replaced with a box that contains three periods (…), and the minus sign changes to a plus sign (+). Clicking on the (+) sign expands the code section.

When working on a database engine (T-SQL) query, the query editor generates outline regions in the following hierarchy:

  1. Batches
    1. From the start of the file to the first GO command (or till the end of the file in case no GO commands are present)
  2. Blocks of code grouped by BEGIN…END keywords. This includes the following:
    1. BEGIN…END
  3. Multi-line statements

I will use the same example as BOL (http://msdn.microsoft.com/en-us/library/cc281835.aspx) to demonstrate the behavior.

USE AdventureWorks2008R2
CREATE PROCEDURE Sales.SampleProc --Outline region 1
BEGIN --Outline region 2 
  SELECT * --Outline region 3
  FROM sys.transmission_queue;
Fully expanded regions Fully closed regions
Fully expanded regions Fully closed regions
Closing inner regions Closing outer regions
Innermost region closed As we close the higher-level regions, the inner levels are closed alongwith

Getting familiar with the Query Editor Toolbars

Now that we have written our first T-SQL query in the query editor, it is time for us to explore the SSMS toolbars.

To view a toolbar, you can:

  1. Use the View -> Toolbar menu
  2. Right-click on the toolbar space in the Query editor window and choose the required toolbar

The most important toolbars that I find are:

  1. Standard
  2. SQL Editor
  3. Text Editor

The Standard Toolbar

The Standard toolbar allows you to perform the most of the generic activities that SSMS can perform. This includes:

  1. Write a new Query (all types)
  2. Perform basic file operations (open, save and print)
  3. Launch a tool called “Activity Monitor” (to be discussed in future tutorial)

The Standard SSMS Toolbar

The SQL Editor Toolbar

  1. Connect to or Disconnect from a SQL Server instance
  2. Change the active connection
  3. Execute, Debug or cancel execution of a query in the active query editor window
  4. Parse the query in the active query editor window
  5. Modify the query options for the particular connection
  6. Toggle Intellisense state
  7. Choose whether to display results in the grid/text or export to a file
  8. Comment/Uncomment or adjust indentation of a given section of text
  9. Display Execution plans and client statistics
  10. Specify template parameters (to be covered in a future tutorial)

The SQL Editor SSMS Toolbar

The Text Editor toolbar

In addition to allowing the user to navigate through the script, the text editor toolbar allows the user to:

  1. Work with Intellisense options
    • Display an object member list
    • Display parameter info
    • Display quick info
    • Display word completion

The text editor SSMS Toolbar

Executing a query

Our very first T-SQL query has been written and we are now familiar with the toolbars on the SSMS window. It is now time for us to press the “Execute” toolbar button on the SQL Editor toolbar.

Results tab

One of the first things that you notice as you execute the query is that we now have an additional set of tabbed windows towards the bottom of the SSMS window. The first of these tabs is the “Results” tab, which contains the results of query that was executed.

Results are contained in grid format on the Results Tab. Based on your requirement, you can choose whether to display these in text or to export them to a file on disk.

Results tab in SSMS

TIP: There is a way to discard these results automatically. That is to say that the query will be executed, but no results would be returned to the calling SSMS connection, saving memory, while providing actual query execution information useful when performance tuning. You can find information on this in the “Exercises/How To…?” section.

Messages tab

Any messages that need to be displayed to the user (includes status, error and user generated messages) are displayed in the Messages Tab.

In addition, the system may also present the query results in the messages tab if the user has chosen to view the query results in text.

Messages tab of SSMS

Client Statistics

Although this is not really in scope for this level of the discussion, an optional client statistics tabbed window can be invoked to obtain vital information of importance during performance tuning. It returns us the information about the query cost in terms of execution, network and time statistics.

Client Statistics Tab of SSMS

To know more about this, refer the “Further Reading” section.

Practice Exercises/How To…?

There can be no learning without some exercises. Because we are dealing with SSMS and the Query Editor, these exercises will help you explore the various configuration options that you can use to create your very own, customized version of the SSMS environment.


Query Editor

IMPORTANT: Keep in mind that the colour coding scheme used by SQL Server 2008 is different from what is used in SQL Server 2012 (code named: “Denali”) CTP03 or RC0 releases.

Further reading


Query Editor

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!


Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.