SQL Server Management Studio

The Microsoft SQL Server Management Studio, shortly called as 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 it 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 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 bitor 32-bit installation in 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 from SQL Server Management Studio. Here, 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 is to work with Reporting Services (SSRS).
  4. Integration Services: To work with Integration Services (SSIS).

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

If you don’t have SQL Server Management Studio SSMS setup, you have to use the command line to work with 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 sql server instance Name to connect database engines in Management Studio.

  • Default Instance: If you installed with Default sql server instance name, then you can 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 have to select the Authentication that you want to use for connecting to the SQL server from management studio.

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

Here I am using the SQL Server Authentication and provided my login credentials and click the connect button in Management Studio. 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 as SQL Server Management Studio or SSMS database engine. If we want to write a script against any database, then you have to 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 in the SQL Server Management Studio.

  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 used 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 graphical interface.
Transact query window in SQL Management Studio

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

List Of Available Database in Object Explorer

From the SQL Server Management Studio Object Explorer also 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

SQL Server Management Studio Standard Toolbar

The list of available buttons in SQL Server Management Studio and their uses in the 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 current window in the file system.
  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 databases that are available 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 have to 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 have to 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 along with Connect to Analysis Services window from SQL 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

When you click on the Find and Replace button, the following window will be opened. Use this to replace anything.

Find and Replace Query Text in SSMS

The 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 in the SQL server Management Studio and their uses in the 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 databases that are available 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 then Query is 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: By clicking this, you can enable SQL Server Management Studio Intellisense for this query window. 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 along with 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 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: 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, to save the queries, and to close.

SQL Management Studio File Menu

SQL Server Management Studio Edit Menu

It is used to perform Cut, Copy, Paste, Delete, Replace, etc. We already explained them in Standard Toolbar section

Management Studio Edit Menu

SQL Server Management Studio View Menu

It is one of the crucial menus and the following ssms 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, then click the Object Explorer sub-menu will restore the window for you. The Microsoft SQL Server Management Studio can show you the report for each object that you select.

To get this information you have to choose the Object Explorer Details sub-menu in sql server management studio SSMS

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 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 is to 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, Template browser added to the SSMS.

SSMS Template Browser Navigation

Under the SQL Server Management Studio Template browser, you can see all the existing templates and their definitions.

SSMS Template Browser Scripts

Under the SQL Server Management Studio 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

SQL Server Management Studio Query Menu

We already explained all these options in the 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

SQL Server Management Studio 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 be opened. Use this window to change the default color, Fonts type, Size, weight, Keyboard shortcuts, etc.

I suggest you explore all the options, but remember to note down the changes you made. If anything wrong happens, then 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: 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 page to change the table option that includes the connection string time outs, warning. about primary keys nulls, differences.

Transact Query designer Options

SQL Server Management Studio Windows Menu

If you are new, you might have accidentally positioned some of the windows (panes) in the wrong position. In this situation, you can use the Reset the sql server management studio Window layout to restore 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 the some of the frequently asked questions.

Is Microsoft SQL Server Management Studio Free?

Yes. It is absolutely free tool and you can download 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 to create a schema, database, tables, views, stored procedures, backups, etc. It is the most powerful graphical user interface where without writing a single line, we can perform multiple tasks.

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 database and tables. Whereas, Management Studio is an graphical interface which allows you to perform all kind of options on the Sequal Server.