SQL Server Management Studio

The Microsoft SQL Server Management Studio, shortly called SSMS, is a Flexible and most powerful open-source desktop tool to work. The Microsoft SQL Server Management Studio is not the actual server but provides an opportunity to connect and work elegantly. In this section, we will cover most of the options that are available in this SSMS tool. It will be the same for the previous versions or the latest version.

How to start SQL Server Management Studio?

To open or Start the SSMS, type SQL Server Management Studio on the start page, or open the Run dialog box and type SSMS.exe. Alternatively, you can navigate yourself to the 64-bit or 32-bit installations in the c folder program files in your operating systems and double click the exe file or application.

SQL Server Instances

Once you click on the Microsoft SQL Server Management Studio Exe file, a new window called Connect to Server will open. The SSMS integrated environment will help you to connect with four different Server Types.

  1. Database Engine: To work with relational databases. We use the Transact queries to communicate with the databases and data warehouses.
  2. Analysis Services: This is to work with Analysis Services (SSAS).
  3. Reporting Services: This type works with Reporting Services (SSRS).
  4. Integration Services: To work with Integration Services (SSIS).

TIP: By default, it automatically installs alongside SSMS. Please refer to Install Software to understand the steps involved in installing the Server or configuring SSMS setup completely. And refer to Install SSMS to follow the steps involved in installing the SQL Server Management Studio only. The process will be the same for SSMS prior or older versions.

If you don’t have an SSMS setup, you have to use the command line to work with the Server instead of this beautiful graphical interface.

Let me select the Server type as Database Engine

Connect to Server Type Options

Server Name

It is nothing but the instance Name to connect SQL Server Management Studio with database engines.

  • Default Instance: If you installed with Default sql server instance name, you could use localhost, system IP Address, ., computer name.
  • Named Instance: If you installed with Named Instance, then you have to specify the instance server name., For example, if your system name is PRASAD, and your instance name is John, then use PRASAD\John
Connect using Windows Authentication

Authentication

You must select the authentication you want to use for connecting to the SQL Server Management Studio.

  • Window Authentication: If you are the system admin, and installed it on the local computer, then you can use this option.
  • SQL Server Authentication: In real-time, we always have to use this authentication mode. It will ask the user to enter the Username and Password to connect.
Connect To Server Available Authentication Options

Here I am using the Server Authentication, providing my login credentials, and clicking the connect button. It will automatically start the database engine using the server name.

Server Authentication to Connect 4

Microsoft SQL Server Management Studio Interface

By clicking the Connection, the below-shown graphical interface will open. It is what we call an SSMS database engine. If we want to write a script against any database, we must click the New Query button.

Create a New Query Window in SQL Management Studio

Once you click the New button, the query window will open.

  1. Object Explorer: It is a Tree view structure that will display all the database Objects in a Server.
    • Databases: Holds the databases that are available on the server. It includes databases that belong to Database Engine, Analysis Services, Reporting Services, and Integration Services.
    • Security: It contains all Security related things. Use this folder to Create Logins, Server Roles, etc.
    • Server Objects: This folder contains information about Linked Server, Service Brokers, System Endpoints, Database Mirroring, etc.
    • Replication: This folder contains information about Subscriptions and publications.
    • Management: Use this folder for maintenance, such as Checking Server Logs, Session health, etc. This folder is to create Maintenance Plans.
  2. Query Window: To write a script against any database. Or you can use Query Builder to build your script using a graphical interface.
Transact query window in SQL Management Studio

Let me show you the list of Databases present in the Databases folder.

List Of Available Database in Object Explorer

From the SQL Server Management Studio Object Explorer, you can Connect and Disconnect from it. Here, there is a Refresh button, and you can use this button if you don’t find the changes you made

Connect to Server Types from SQL Management Studio

Standard Toolbar

The list of available buttons and their uses in the SQL Server Management Studio Standard toolbar

  1. New Project: It creates a new project such as Black Solutions, Scripts, and Analysis Services Scripts
  2. Open File: To navigate within the file system to select the script file
  3. Save: Use this to save the file system’s current window.
  4. Save All: This will save all the script windows in the file system.
  5. New Query: If we want to write a script against any database, then click this New Query
  6. Available Databases: This drop-down list will display all the available databases on this server. You can select the database that you want to work on.
  7. Database Engine: Opens a New window along with Connect to database Engine window. First, you must connect with the Database Engine, and then you can use the query window to write your command.
  8. Analysis Services MDX Query: Opens a New MDX window along with Connect to Analysis Services window. First, you must connect with Analysis Services and then use the MDX window to write your MDX against Cube.
  9. Analysis Services DMX: Opens a New DMX window and Connects to the Analysis Services window from the SQL Server management studio.
  10. SSAS XMLA: It opens a New XMLA window along with Connect to Analysis Services window.
  11. Cut: This button will cut the selected text.
  12. Copy the selected text.
  13. Paste the Cut or Copied content
  14. Undo the changes
  15. Redo the changes in the window
  16. Navigate Backward: Use this button to Navigate Backward
  17. Navigate Forward: To Navigate Forward.
  18. Activity Monitor: Please click this button to see the activity monitor. It contains information about the process, Resources, data Files, etc.
  19. Start Debug: To debug your code.
  20. Find in Files: Use this to find your files.
  21. Find: To find the required text in a query window.
  22. Solution Explorer: By clicking this, Solution Explorer Window added to SSMS
  23. Properties Window: By clicking this button, Properties Window added to SSMS
  24. Toolbox: By clicking this button, Toolbox added to the left side
SQL Management Studio Tool Bar Icons Details

As we said before, if you click on the New project button in the SQL Server Management Studio, the following window will open. Here you can select Scripts, Analysis Services Scripts, or a Black Solutions

Create New Project options

The following window will open when you click the Find and Replace button. Use this to replace anything inside it.

Find and Replace Query Text in SSMS

The SQL Server Management Studio Solution Explorer will show you the file information. And the Properties Window will show all the information about the query, such as Connection, Execution time, Duration, returned rows, etc.

SQL Server Management Studio Query and Solution Explorer

Microsoft SQL Server Management Studio Editor Toolbar

Let me show you the list of available buttons and their uses in the SQL Server Management Studio Editor toolbar.

  1. Connect: Used to connect to Server.
  2. Change Connection: You can change the existing connection by clicking this button.
  3. Available Databases: This drop-down list will display all the available databases on this server. You can select the database that you want to work on.
  4. Execute: It executes the query inside the window and returns the result in the result pane.
  5. Debug: Help you to debut your code.
  6. Stop: It will stop the running query. It will help you to stop the long-running query.
  7. Parse: Use this to check whether the Query parsed or not.
  8. Display Estimated Execution Plan: This will display the Estimated Execution Plan for this particular script
  9. Query Option: Use this button to set the ROWCOUNT, TEXTSIZE, and execution timeout.
  10. Intellisense Enabled: You can enable Intellisense for this query window by clicking this. It will help you to find the syntax errors, and it will auto-suggest the function names.
  11. Include Actual Execution Plan: Enabling this option will include the Actual Execution Plan and results. It will help you understand the execution.
  12. Include Client Statistics: Enabling this option will consist of the Client Statistics (Execution type, Bytes sent and received, etc.) along with the result grid. It will help you understand the execution.
  13. Result to Text: Result in SQL server management studio will display as text.
  14. Result to Grid: The result will display in table format or grid format. It is the default one.
  15. Result to File: Use this option to save the query result in a text file.
  16. Comment out the Selected Line: Click this button to comment on the current line.
  17. Uncomment the Selected Lines: Click this button to uncomment the current line or selected line.
  18. Decrease Indent: Used to decrease the distance
  19. Increase Indent: Use this to increase the distance. Use Decrease Indent, and Increase Indent to organize the code accurately.
  20. Specify Values for template parameters: Used to specify Values for template parameters
SQL Server Management Studio Editor Toolbar

SQL Server Management Studio File Menu

Use the File menu item to Connect Object Explorer, create a new Project, Open existing projects and files, save the queries, and close.

SQL Management Studio File Menu

SQL Server Management Studio Edit Menu

It performs Cut, Copy, Paste, Delete, Replace, etc. We already explained them in the Standard Toolbar section.

SQL Management Studio Edit Menu

View Menu

It is one of the crucial menus, and the following SQL Server Management Studio screenshot will show you the list of the available option on this menu item.

SQL Management Studio Edit Menu

If your SSMS is not displaying the Object Explorer window, clicking the Object Explorer sub-menu will restore the window for you. In addition, the Microsoft SQL Server Management Studio can show you the report for each object you select.

To get this information, you have to choose the Object Explorer Details sub-menu in SSMS.

Object Explorer Details

Use the Forward and Backward navigation to move forward and backward.

SQL Server Management Studio Object Explorer Details

Let me select the Registered Servers sub-menu from the SQL Server Management Studio View menu.

SSMS Registered Servers from View menu

It displays the server that you manage frequently. By clicking the Cube, you can see the Analysis. Clicking the report button will show the reporting, and the last button will display the Integration.

Available Registered Services in SSMS

Let me select the Template Explorer sub-menu from the Microsoft SQL Server Management Studio View menu.

Template Explorer in SSMS

Once you pick the Template Explorer sub-menu, the Template browser is added to the SSMS.

SSMS Template Browser Navigation

Under the Template browser, you can see all the existing templates and their definitions.

Template Browser Scripts

Under the Analysis Services Templates, you can see the DMX templates, XMLA templates, and MDX templates. By clicking on an individual item, you can see their definitions.

Analysis Services DMX, XMLA, and MDX templates

Query Menu

We already explained all these options in the SQL Server Management Studio Editor Toolbar section

Query Result to Text, Grid, and File Settings

Debug Menu

Use the Debug menu to Start Debugging and to work with Breakpoints in the Microsoft SQL Server Management Studio.

SSMS Debug Menu

Tools Menu

Use this SSMS menu to personalize the designer view, or to choose the third-party toolbox items, etc. For now, we will explore the Options… sub-menu, so let me select the same.

SQL Management Studio Tools Options Menu

By clicking the options.. the following window will open. Use this window to change the SQL Server Management Studio default color, font type, Size, weight, Keyboard shortcuts, etc.

I suggest you explore all the options but remember to note the changes you made. Then, if anything wrong happens, this will help you to recover quickly. Or, use the Restore Defaults button on each page to restore the settings to default settings.

Change SSMS Font, Color, Size, Shortcuts

Query Execution: You can set the default ROWCOUNT, TEXTSIZE, and execution timeout.

Query Execution Settings

Query Result: You can alter the default destination of the Query result

  • Result to Text: The result will display as text.
  • Result to Grid: Result displayed in table format or grid format. It is the default one.
  • Result to File: Use this option to save the query result set in a text file so that you can use that file for further analysis.
Transact Query Result Settings Grid, Text, and file

Designer: Use this SQL Server Management Studio page to change the table option that includes the connection string time outs and warning about primary keys nulls and differences.

Transact Query designer Options

Windows Menu

If you are new, you might have incorrectly positioned some of the windows (panes). In this situation, you can use the Reset the Window layout to restore it to its original position.

Reset Query Window Layout in SQL management studio

TIP: Please refer to Uninstall to understand the steps involved in removing or uninstalling SSMS only.

FAQ’s

The following are some of the frequently asked questions.

Is Microsoft SQL Server Management Studio Free?

Yes. It is absolutely free tool, and you can download SQL Server Management Studio from the website to connect with an instance and work with a database.

What does SQL Server Management Studio do?

SSMS enables you to connect with any available instance. Next, we can use SQL Server Management Studio to create a schema, database, tables, views, stored procedures, backups, etc. It is the most powerful graphical user interface where we can perform multiple tasks without writing a single line.

What is the difference between SQL Server and SQL Server Management Studio?

SQL Server is the actual server responsible for managing our data in the form of databases and tables. At the same time, Management Studio is a graphical interface allowing you to perform all options on the Sequal Server.