Basics of SQL Server Management Studio (SSMS) – Part 1

This article aims to share the basics of SQL Server Management Studio, commonly known as SSMS, and some useful tips on working with it. Also, it highlights the importance of SSMS as database development and administration tool.

About SQL Server Management Studio

It is always good to clarify the SSMS definition(s) first. Let’s review some of the definitions from the database perspective.

Simple Definition

SSMS is a tool developed by Microsoft to help database developers to develop, manage, and monitor SQL databases.

Simple Definition 2

SSMS is a Microsoft tool to help the database administrators manage and monitor SQL database server(s).

Microsoft Definition

According to Microsoft documentation, SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure.

What is an integrated environment?

An integrated environment is a development environment that combines different tools and features in one area for the developers to use without jumping from one tool to another. Thus, SSMS is an integrated environment because it contains all the required features and functions for database design, maintenance, and deployment.

What is an SQL infrastructure?

Without going into the technical details, we can say that an SQL infrastructure includes everything needed to set up, ranging from a simple SQL database to a SQL server.

Another SQL infrastructure concept is the resources (like database, database server) and/or processes required to meet business objectives, such as Sales Reports.

Why do you need SSMS?

Before we start discussing the basics of SQL Server Management Studio, one might wonder why it is needed in the first place. A simple answer is, it builds multiple things, including SQL databases. 

For example, if you are an SQL developer who has to develop a SQL database, you need a tool. SSMS is one such tool that can help you achieve it.

Another example is a database administrator who must monitor existing SQL databases and a server hosting those databases. You need SSMS to manage this important task of database administration and monitoring.

Is SSMS the only Database Development Tool?

No. Microsoft ships another powerful database development tool known as SQL Server Data Tools (SSDT). To use it, you need Visual Studio.

Is SSMS all about Databases and Database Servers?

Again, No. There are many other things you can develop, deploy, monitor, and manage apart from SQL databases and database servers.

Is SSMS suitable for beginners?

Yes, SQL Server Management Studio is suitable for beginners provided they have some background to use it for developing databases.

What about Cloud-Powered SQL Databases?

You can use SSMS to manage such cloud-powered SQL databases, commonly known as Azure SQL databases.

Prerequisites

Since we are going to talk about the basics of SQL Server Management Studio, we assume that our readers have already prepared the following things:

1. Some basic knowledge of SQL databases and the SQL database management tool (SSMS).
2. Any latest SQL Server Developer/Express edition, such as SQL 2016/2017/2019, is installed on your machine or in your network drive or remote (accessible) location.
3. The latest version of SQL Server Management Studio has been installed on your machine.

Follow the below link to download the necessary software:

Download the SQL Server Express/Developer edition.
Download SQL Server Management Studio (SSMS).

Basics of SSMS

It is not easy to cover all the basics in a couple of articles. However, we can overview some of them briefly. It will concern the most important things that a beginner must know before starting the database development or administration journey.

First-time use

If you are using SSMS for the very first time, remember the following things in sequence:

1. Download
2. Install
3. Locate
4. Open
5. Connect

Please go through the prerequisites section to get more details about it.

After downloading and installing SSMS, run it: type SSMS in the Windows search box and click on the necessary result:

The next step is to connect to the SQL Server (instance).

Input your server name and use default login settings:

Note that we are using the Windows Authentication mode to connect to the server. As we chose these settings when installing the SQL Server, you have to use the chosen settings similarly.

The Windows Authentication mode means you can use your Windows credentials to log on to the SQL server installed on your machine.

Object Explorer

Once you are connected, you will see an Object Explorer window on the left side of SSMS.

Object Explorer is one of the most-used operational windows. It displays the connected SQL Server instances and databases and their objects, such as tables, in a refined hierarchical fashion of tree-like structure. You can directly perform many tasks concerning the databases and servers. Of course, you need to have permission to perform them.

You can activate the Object explorer window yourself in several ways:

1. Press F8 (the shortcut).
2. Navigate to the View section on a toolbar and select Object Explorer from the menu.

Connect/Disconnect SQL Server

Once you are in the Object Explorer, you can quickly perform the following two essential SQL Server tasks:

1. Connect to another SQL Server instance.
2. Disconnect an existing SQL Server connection.

Click on the plug icon on the Object Explorer toolbar to connect to another SQL Server instance:

To disconnect the current connection, click on the unplug sign next to the plug icon:

Note that you can also connect/disconnect the server using the File main menu.

View the System Databases

An SQL Server has four system databases which care for the internal server functioning and store essential information about the user-created databases:

1. master
2. model
3. msdb
4. tempdb

In Object Explorer, go to the Databases mode -> System Databases to view these system databases:

Define the SQL Server Version

One of the most interesting things to find out is the connected server version. It is particularly useful to know when you connect to multiple servers.

To define the server version, you can connect to any database and run a simple query against it. Let’s examine it by example.

To connect to system databases, right-click on the master database -> New Query:

Then, write the following SQL query to define the SQL Server version:

-- Finding SQL Server version
SELECT @@VERSION AS SQLServerVersion

Press F5 to run the query:

In our case, it is SQL Server 2016. You might get a different output, depending on the version you use.

Define the SQL Server Database

In the same way, you can define the current database name. Run the following query against any database (we are running it against the master database):

-- Finding current SQL database
SELECT DB_NAME() AS CurrentDatabase

The output is:

Define the SQL Server Login and the Database User

To define the name of the current database user, type the following T-SQL script against any database (we are running it against the master database):

-- Finding current SQL database user
SELECT USER_NAME() AS CurrentDatabaseUser

The result is below:

Important: dbo is a special type of database user which has many permissions for doing database-related tasks.

However, there are other two user types you need to know:

1. User or login used to connect to the SQL Server.
2. The user connected to the database.

For example, I used my Windows credentials to connect to the SQL Server instance, and then I got recognized as a dbo user of the master database.

To define the SQL Server login connected to the Server, run the following query:

-- Finding current SQL Server user (login)
SELECT SUSER_NAME() AS SQL_Server_User_Login

The result is:

The first name is the name of the machine where the server is installed. The second name is the name of the login.

Since we are dealing with basics in this article, we won’t get into further details.

Database Filter

Once connected to the SQL Server, you can filter the databases to see those of your interest only. To do it, apply one of the following methods:

1. With the filter icon on the Object Explorer toolbar.
2. With the filter option in the context menu.

You can apply the following database filters:

1. Filter by name.
2. Filter by owner.
3. Filter by creation date.

Filter Databases by Name

For example, you want to see the test databases and filer other ones out using Object Explorer.

Select the Databases node and click on the Filter icon on the Object Explorer toolbar:

Now select the Test Name value to apply the filter as follows:

Once the filter is applied, you only see the databases containing the word Test in their names:

Note: Filtering is not case sensitive. You might input the word test rather than Test.Still, you would seeall the databases having “Test” in their names.

However, if there aren’t any databases meeting the above filter criteria, you won’t see any databases in the results either.

Remove Filter

You can remove the filter in the following way:

1. Click on the Filter icon in the Object Explorer.
2. Click on the Clear Filter button in the Filter Settings window.

As soon as the filter is removed, you will see all the connected databases.

Filter Databases by Creation Date

There is another helpful filter based on the date of the database creation. For example, you need to work with the databases created in October 2020 only. In this case, you can apply this filter in an alternative way, using the context menu options.

Right-click on the Databases node and go to Filter -> Filter Settings:

Select Between next to Creation Date, then select dates between 01-Oct-202 and 31-Oct-2020 as shown below:

The output may vary according to your particular circumstances. In our case, it is as follows:

Congratulations! You have learned the SQL Server Management Studio basics and mastered some useful tips, such as filtering databases.

Things to do

Now that you are familiar with SSMS and Object Explorer, in particular, try the following to improve your skills:

1. Remove the filter and apply another one for all databases created in 2019.
2. Close the Object Explorer window and then launch it again with the shortcut key.
3. Define the version of the SQL Server you are connected to, but don’t look at the code provided in the article.
4. Pin SQL Server Management Studio on the Windows taskbar for easy access.
5. Try disconnecting the current SQL Server and reconnecting it using the Object Explorer toolbar.

In the next part, we will learn more about SQL Server Management Studio and the database tasks it can perform. Stay tuned!

Tags: database development, database management, IDE, sql database, SQL tools, ssms Last modified: September 18, 2021