Overview of Microsoft SQL Server Management Studio (SSMS)

Introduction

Microsoft SQL Server Server Management is an advanced development environment that enables us to configure, manage and administrate SQL Server database engines. SSMS is very popular and widely used by the database developers and administrators because of the following advantages:

Cost-free
Advanced user experience
Various add-in options
Easy installation

Firstly, we will learn the installation and then go through the other details of the SSMS.

Installation

We can download the latest version of the SSMS from the Microsoft download website and we can also find out the release notes.

After downloading the setup file, we will open the setup file and click the install button in order to begin the installation. On this screen, the Location setting specifies the installation path of the Microsoft SQL Server Management Studio:

In the second screen, the installation process will be started and the progression of the setup will be shown:

After the installation is completed, the following screen will appear and inform us that the installation has been completed successfully:

Now, we can launch SSMS from the Start menu:

Also, we can use the Command Prompt to launch SSMS:

Connecting to the Database Engine

When we launch the SSMS, the Connect to Server dialog screen will appear in front of us. In this screen, we will set the Server name and Authentication type of the SQL Server which we want to connect to and then click the Connect button. In the following illustration, we will connect to a SQL Express edition with the SQL Server Authentication type. If we click on the Remember password option, SSMS will remember our username and password for the next connection of the same SQL Server instance:

After a successful connection, the Object Explorer panel will appear on the left side of the main window of Microsoft SQL Server Management Studio.

On the Object Explorer, we can change various settings of the SQL Server Engine and deployed databases. This screen provides us to accomplish database related operations such as the backup, restore, detach, etc.:

At the same time, this screen helps us to maintain and manipulate the database objects.

Tip: Object Explorer Details panel helps us to find out more details about the server and database objects which are located under the selected folder. By default, this panel will be invisible, but we can open it under the View menu:

For example, if we want to obtain more details about the tables which are contained by a particular database, selecting the tables folder will be enough to see these details. In addition, we can also add different data to this tabular list according to the type of the object. For example, it allows seeing the record number of rows for the tables:

Query Editor on Microsoft SQL Server Management Studio

Another ability of the SSMS is that it enables to create and execute the T-SQL queries. When we click on the New Query button, which is placed on the toolbar, a new Query Editor will open. In this editor, we can create and execute SQL queries. In the following example, we will execute a very basic query that shows some of the synthetic customer data. First, we will open a new query editor window, type desired query and then, we will click the Execute button to run the query. The query result will be shown under the Results panel:

SELECT *

FROM(VALUES

(1,

'Gertie Michener',

'London'

(2,

'Colton Guion',

'New York'

(3,

'Ahmed Coty',

'Berlin'

(4,

'Devin Kimmons',

'Istanbul'

)) CustomerTable(Id, CustomerName, City)

SSMS query editor offers various productivity options to programmers. In the previous example, the result set of the query has been shown on the grid, but we can change this option. On the SQL Editor toolbar, we can change this setting to Results to File, so that the result set of the query will be shown in a text format:

When we want to separate the query window and result set window, we need to enable the Display results in a separate tab option. To enable this option we need to follow the below steps:

Click the Options command which is placed under the Tools menu:
Enable the Display results in a separate tab option under either Results to Grid tab or Results to Text tab:

After enabling this option, the query results will be shown in a separate window. However, this option will be enabled when a new Query Window is opened. When we run the following query, the result set will be shown in the separate tab:

After the mentioned setting is changed, the query result will be shown as follows:

The following illustration indicates the essential features of the SQL Editor toolbar:

Available Databases: In this drop-down menu, we can determine a database on which the query will be executed
Execute: This button starts the execution of a currently active query window
Cancel Executing Query: This button stops the execution of the query
Parse: This button validates the syntax of the query, but does not check the database objects
Changing query result destination: In this button group, we can set the destination of the query result. It provides three different options:
1. Results to Grid
2. Results to Text
3. Results to File
Comment/Uncomment out the Selected Lines: These buttons provide the option for commenting or uncommenting on the selected code lines:

Tip: Some programmers wish to see the line numbers of the code in the query editor. Therefore, we need to follow the below steps:

Click the Options command, which is placed under the Tools menu
Navigate to the General tab, which is placed under Transact-SQL node
Check the Line numbers option

After this setting change, the queries will be formatted as follows:

Conclusion

In this article, we learned the basics of Microsoft SQL Server Management Studio and the following articles will help to improve SSMS-related skills :

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec

Introduction

Installation

Connecting to the Database Engine

Query Editor on Microsoft SQL Server Management Studio

Conclusion

Related posts: