As 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:

1. Enterprise Manager Provided a Microsoft Management Console (MMC) compliant UI to administer, configure and manage a given set of SQL Server instances

2. Query Analyzer Provided a development interface to develop and debug T-SQL code

3. 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 Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE.

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 dialogue

Login dialog – setting connection properties

additional connection parameters

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

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

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

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
GO
SELECT * FROM HumanResources.Employee
GO

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”.

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 tool tips 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.

IMPORTANT:

Intellisense is case-sensitive, making it a key feature for those working on case-sensitive collations

For SQL Server 2008 R2, Intellisense only performs a “begins-with” search, whereas in SQL 2012 (code named: “Denali”), it performs a “contains” or a “like” search. Read my post on this here: http://beyondrelational.com/blogs/nakul/archive/2011/08/11/intellisense-in-sql-11-denali-uses-like-search-a-productivity-enhancement.aspx

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

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: 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:
   • BEGIN…END
   • BEGIN TRY…END TRY
   • BEGIN CATCH…END CATCH
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
GO
CREATE PROCEDURE Sales.SampleProc --Outline region 1
AS
BEGIN --Outline region 2 
  SELECT GETDATE() AS TimeOfQuery;
  SELECT * --Outline region 3
  FROM sys.transmission_queue;
  SELECT @@VERSION;
END;
GO

Fully expanded regions

Fully closed 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 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 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

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.

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.

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.

Further reading

SSMS

1. Know more about connection strings and get a downloadable PDF on writing connection strings
2. SSMS shortcuts:
   • Comprehensive List
   • Concise List

Query Editor

1. Aaron Bertrand’s (blog | twitter) post on the new SSMS in SQL 2012 (code named: “Denali”)
2. Default colour coding in the Code Editors of SSMS
3. Client Statistics