Considering the other available server management suites in the market, Microsoft SQL Server Management offers a very advanced and feature-rich development environment for the SQL database users to configure, administrate, and manage the SQL Server database engines. Being shortly named SSMS, MS SQL Server Management Studio is very popular and widely used by database developers and DBAs. It provides many advantages regarding cost, better user experience, many add-on options, easy installation, maintenance, etc. Let’s explore the major features of SSMS.
Installation of SSMS
We can easily install the latest version SSMS from the Microsoft download website and can also find out the release notes at the same destination. On downloading the setup file first, you can open it and click on the install button to initiate the installation process. On the home screen, you can find the Location setting, which specifies the path of the installation path of the Microsoft SSMS.
Moving on to the next screen, you can see the installation process is getting started, and the progress bar will show the percentage of completion. Once the installation gets over, you can see the next screen appearing with the message of “Setup Completed.” Then, you can go to the Start menu and launch the MS SSMS application from there. Alternatively, we can also make use of Command Prompt to launch SSMS.
Connecting SSMS to Database Engine
On launching the SSMS, you can see the dialog of Connect to Server at the screen. You can set up the server name and authentication type for the SQL Server at this screen that you want to connect to SSMS and then click on the Connect button. Next, you may or may not activate the option of remembering password when prompted, on choosing which SSMS will automatically remember your username and password for further connections to the same SQL Server instance added
After the connection is established successfully, you can see the Object Explorer panel displayed on the left side of the home window of MS SSMS. On Object Explorer, there is an option to make changes to the Server Engine’s default setting and the deployed databases. This screen will also provide us with options to accomplish the database-related operations like restore, backup, attach, detach, etc. You can also manipulate and maintain the database objects on this screen. The Object Explorer Details panel will also help us find many other details about the connected server and the database objects located under the specified folders. However, this panel is kept invisible by default, which you can open under the view menu. If you are not confident of the database setup activities to be done your own, please take RemoteDBA.com expert assistance for the same.
Say, for example, if you want to gather more information about the tables contained in the given database, you can select the corresponding tables folder to view all the details. We can also add a lot of other data to this tabular list based on the type of object you choose. As you can see, it will let you see the record number of rows for given tables.
Query Editor on MS SSMS
Another notable feature of Microsoft SQL Server Management System is its ability to prepare and execute T-SQL queries. While we click on the New Query button on the toolbar, it instantly sets open a fresh Query Editor. In this space, you can custom create SQL queries based on your requirement and execute them. See the below example in which we are trying to execute a basic query to call some of dummy customer data. By opening a query editor, we can type in the below query or similar ones and click the Execute button to run it.On running it, you can find the query result at the Results panel:
Executing queries from the Editor
As we have seen above, Microsoft query editor may offer various options to increase the programmer’s productivity. In the above example, the result set of the given query will be shown on a grid, but it is also possible for the users to change it as they want. For example, if you want to get the results in text format, go to the SQL Editor toolbar and change the settings simply from Results to File, which will then send the query result set to be shown in text format.
If you want to divide the query window from the result set window, then enable Display results in another tab option. To enable the same, you need to follow the below steps.
- You can enable the Display results in another tab by going to either the Results to Grid tab or the Results to Text tab.
- By enabling this option, query results may be shown on a separate window.
- This option automatically activates when a fresh Query Window is getting opened.
- While running the query, the result set may be shown in a separate tab.
Features of SQL Editor Toolbar
You can find the following listing of the essential features of the SQL Editor Toolbar.:
- Available Databases: This is a simple drop-down list, from which you can custom choose which of the connected databases on which the query needed to be executed.
- Execute: This button will launch the query execution of the active query window
- Cancel Executing Query: This option is to stop the execution of the running query.
- Parse: To validate the query syntax but without checking any database objects.
- Changing query result destination: This is a group of buttons, from which you can choose the appropriate destination of the query results. There are three primary options as:
- Results to Grid
- Results to Text, and
- Results to File
- Comment/Uncomment the Selected Lines: This option will provide the option to comment on a select code line or to uncomment on the same.
Query Editor Commenting lines of queries on SSMS
Some developers may want to see line numbers of the code while opening it in the query editor. To enable this option, we need to follow the below steps.
- Go to the Options command, which is there under the menu ‘Tools.’
- Go to the General tab located under the Transact-SQL node
- Check for the option of Line numbers
- It can be found in the Options window, change the settings
- Once the change is executed, the queries will get reformatted with a number on lines.
We have learned the primary tasks on Microsoft SQL Server Management Studio in this article and will discuss further in-depth features of the same in the forthcoming articles.