SQL Server Management Studio

The Microsoft SQL Server Management Studio, shortly called as SSMS is a Flexible and most powerful tool to work with SQL Server. The SQL Server Management Studio is not the actual server, but it provides an opportunity to connect and work with SQL elegantly. In this section, we will cover most of the options that are available in this SQL management studio tool.

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

SQL Server Management Studio 1

Alternatively, you can navigate yourself to C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2014 for 64 bit, and C:\ProgramData(x86)\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2014 for 32-bit and click the application.

SQL Server Management Studio 2

Once you click on the SQL Management Studio, a new window called Connect to Server will open.

TIP: Please refer to Install SQL Server to understand the steps involved in installing SQL Server completely. And refer to Install SQL Management Studio to follow the steps involved in installing SQL Server Management Studio only.

SQL Server Management Studio 3

SQL Server Management Studio will help you to connect with four different Server Types

  1. Database Engine: To work with relational databases. Here, we use the Transact SQL queries to communicate with the server.
  2. Analysis Services: This is to work with SQL Server Analysis Services (SSAS).
  3. Reporting Services: This Server type is to work with SQL Server Reporting Services (SSRS).
  4. Integration Services: This Server type is to work with SQL Server Integration Services (SSIS).

Let me select the Server type as Database Engine

SQL Server Management Studio 4

Server Name: It is nothing but a SQL Server Instance Name.

  • Default Instance: If you installed SQL Server with Default instance, then you can use localhost, system IP Address, ., computer name.
  • Named Instance: If you installed SQL Server with Named Instance then you have to specify the instance name., For example, if your system name is PRASAD, and your instance name is John then use PRASAD\John
SQL Server Management Studio 5

Authentication: You have to select the Authentication that you want to use for connecting to the server.

  • Window Authentication: If you are the system admin, and if your SQL Server 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 with the server.
SQL Server Management Studio 6

Here I am using SQL Server Authentication and provided my login credentials

SQL Server Management Studio 7

SQL Server Management Studio Interface

By clicking the Connection, the below-shown window will open. It is what we call as SQL Server Management Studio or SSMS. If we want to write a Query against any database, then you have to click the New Query button.

SQL Server Management Studio 8

Once you click the New Query 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 LoginsServer 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 query against any database. Or you can use Query Builder to build your query graphically
SQL Server Management Studio 9

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

SQL Server Management Studio 10

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

SQL Server Management Studio 13

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: This will create a new project such as Black Solutions, SQL Serer Scripts, and Analysis Services Scripts
  2. Open File: To navigate within the file system to select the script file
  3. Save SQL Query: Use this to save the current query window in the file system.
  4. Save All: This will save all the query windows in the file system.
  5. New Query: If we want to write a Query against any database, then you have to 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 Query: Opens a New Query 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 query.
  8. Analysis Services MDX Query: Opens a New MDX Query window along with Connect to Analysis Services window. First, you have to connect with Analysis Services, and then use the MDX query window to write your MDX query against Cube.
  9. Analysis Services DMX Query: Opens a New DMX Query window along with Connect to Analysis Services window.
  10. SSAS XMLA Query: It opens a New XMLA Query 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 query 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 Server Management Studio 14

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

SQL Server Management Studio 15

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

SQL Server Management Studio 16

The Solution Explorer will show you the file information. And the Properties Window will show all the information about the query such as Connection, Query execution time, Duration, returned rows, etc.

SQL Server Management Studio 36

SQL Server Management Studio Editor Toolbar

Let me show you the list of available buttons in the SQL 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 query 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 query
  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 query 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 query result. It will help you understand the execution
  13. Result to Text: Result 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 17

SQL Server Management Studio File Menu

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

SQL Server Management Studio 25

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

SQL Server Management Studio 26

SQL Server Management Studio View Menu

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

SQL Server Management Studio 27

If your SSMS is not displaying the Object Explorer window, then click the Object Explorer sub-menu will restore the window for you. 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 SSMS

SQL Server Management Studio 11

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

SQL Server Management Studio 12

Let me select the Registered Servers sub-menu from View menu

SQL Server Management Studio 28

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

SQL Server Management Studio 29

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

SQL Server Management Studio 30

Once you pick the Template Explorer sub-menu, Template browser added to SQL Server Management Studio

SQL Server Management Studio 31

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

SQL Server Management Studio 32

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.

SQL Server Management Studio 33

SQL Server Management Studio Query Menu

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

SQL Server Management Studio 24

SQL Server Management Studio Debug Menu

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

SQL Server Management Studio 23

SQL Server Management Studio Tools Menu

Use this 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 Server Management Studio 18

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.

SQL Server Management Studio 19

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

SQL Server Management Studio 20

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 in a text file.
SQL Server Management Studio 21

Designer: Use this page to change the table option

SQL Server Management Studio 22

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 Window layout to restore the SQL Server Management Studio to its original position.

SQL Server Management Studio 34

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