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

Query Editor
Object Explorer
Registered Servers
Properties Window
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:

File -> New -> Database Engine Query
File -> New ->Query with current connection
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:

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

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

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:

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)
Blocks of code grouped by BEGIN…END keywords. This includes the following:
1. BEGIN…END
2. BEGIN TRY…END TRY
3. BEGIN CATCH…END CATCH
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:

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

The most important toolbars that I find are:

Standard
SQL Editor
Text Editor

The Standard Toolbar

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

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

The SQL Editor Toolbar

Connect to or Disconnect from a SQL Server instance
Change the active connection
Execute, Debug or cancel execution of a query in the active query editor window
Parse the query in the active query editor window
Modify the query options for the particular connection
Toggle Intellisense state
Choose whether to display results in the grid/text or export to a file
Comment/Uncomment or adjust indentation of a given section of text
Display Execution plans and client statistics
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:

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.

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.

SSMS

Query Editor

How to use the zoom feature in upcoming SQL2012 (Code Named: “Denali”)?
How to use the Clipboard ring feature inherited from Visual Studio 2010?
How to change the colour of the query editor status bar?
How to use the Query editor to generate comma-separated or tab-separated output without BCP or query changes?
Type in the following T-SQL query. Identify the colour coding scheme used to identify the various object types:
- Database name
- Keywords
- Table/object names
- System Function Names
Use the Query editor options to modify the information shown on the default sections of the 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.

SQLTwins by Nakul Vachhrajani

SQL Server tips and experiences dedicated to my twin daughters.

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

The SQL Server Management Studio