By: Sergey Gigoyan | Comments (5) | Related: > SQL Server Management Studio Configuration
Problem
SQL Server Management Studio (SSMS) is the most used tool for SQL Server database development and administration. In this tip we will illustrate some SSMS common tips which will be useful especially for those who are novices in using SSMS.
Solution
Below we are going to show some SSMS tips that will let you work with this tool more confidently. So... let's start!
Launch SSMS from a Command Prompt
It is possible to launch SQL Server Management Studio from command prompt. To do this, we only need to type ssms in command prompt and press 'Enter':
![programs in SQL Server Management Studio](/tipimages2/4954.001.png)
SSMS authentication window opens and you need to choose your SQL Server instance to login.
![connect to server in SQL Server Management Studio](/tipimages2/4954.002.png)
But ssms allows you to provide more arguments when launching. For instance, you can directly connect to specific database in a specific instance with a specific user by providing these arguments at the command prompt:
ssms -s DBSERVER1 -d TestDB -u TestUser -p 123
![windows security in SQL Server Management Studio](/tipimages2/4954.003.png)
With this command we are connecting to TestDB database in DBSERVER1 using SQL Server authentication:
![object explorer in SQL Server Management Studio](/tipimages2/4954.004.png)
There are some other options for SSMS, for example you can specify one or more script files to open, specify the script project or solution to open, etc.
Restore the SSMS Default Layout
Have you mixed up the window layout in SSMS and not sure of their original place?
![sql query in SQL Server Management Studio](/tipimages2/4954.005.png)
Do you need to restore the SSMS layout to the original state? It can easily be done by clicking “Reset Window Layout” in the “Window” tab as shown below:
![sql query in SQL Server Management Studio](/tipimages2/4954.006.png)
![sql query in SQL Server Management Studio](/tipimages2/4954.007.png)
After agreeing to restore the default layout by clicking “Yes”, your SSMS returns to its original appearance:
![object explorer in SQL Server Management Studio](/tipimages2/4954.008.png)
Change Default Values for Rows to Select or Edit in Management Studio
When we try to edit or select rows from a table in SSMS, by default we can edit or select 200 and 1000 rows respectively:
![object explorer in SQL Server Management Studio](/tipimages2/4954.009.png)
To change these values go to Tools > Options as shown below:
![sql server options in SQL Server Management Studio](/tipimages2/4954.010.png)
After that we need to choose “SQL Server Object Explorer” > ”Commands”:
![options in SQL Server Management Studio](/tipimages2/4954.011.png)
Then on the right side we can change the corresponding values. For configuring SSMS to allow selecting or editing all rows from table by right-clicking we need to set these values to 0:
![audit log viewer options in SQL Server Management Studio](/tipimages2/4954.012.png)
Now, we can see that there is no limit for editing or selecting rows:
![object explorer in SQL Server Management Studio](/tipimages2/4954.013.png)
Allow Saving Changes that Require Table Re-creation
By default, saving the changes, that require table re-creation, is disabled in SSMS. For example when we try to change the TestTable to not allow NULLs for the Value column, we receive the following message:
![collumn name in SQL Server Management Studio](/tipimages2/4954.014.png)
To allow save changes that requires table re-creation we need to go “Tools” > ”Options”:
![object explorer in SQL Server Management Studio](/tipimages2/4954.015.png)
Then choose “Designers” > ”Table and Database Designers”:
![table and database designers in SQL Server Management Studio](/tipimages2/4954.016.png)
On the right side we need to uncheck “Prevent saving changes that require table re-creation”:
![table options in SQL Server Management Studio](/tipimages2/4954.017.png)
After this change we are able to make table changes that require the table to be re-created. It is important to know that allowing changes requiring table re-creation can sometimes be risky and in some specific cases data loss can occur (for example when the Change Tracking feature is enabled).
Work on more than one Query Simultaneously
Sometimes we need to work on two or more queries, compare the code and analyze the results. So, it would be better if we are able to see these queries simultaneously. In SSMS it is possible to see queries in parallel by opening them in vertical or horizontal tab groups. For example if we opened two queries and want to see them in parallel in different vertical tabs we can choose “New Vertical Tab Group” by right-clicking on the query heading:
![new vertical tab group in SQL Server Management Studio](/tipimages2/4954.018.png)
We can do the same by going to “Window” > ”New Vertical Tab Group”:
![new vertical tab group in SQL Server Management Studio](/tipimages2/4954.019.png)
Now we can see both queries simultaneously:
![sql query in SQL Server Management Studio](/tipimages2/4954.020.png)
To move the queries windows to different tab groups, right-click on the query heading and choose “Move to Previous Tab Group” (or “Move to Next Tab Group”).
![tables in SQL Server Management Studio](/tipimages2/4954.021.png)
“Move to Previous Tab Group” or “Move to Next Tab Group” is also available in the Window menu. To see queries horizontally we need to choose “New Horizontal Tab Group” as shown below:
![query executed successfully](/tipimages2/4954.022.png)
Next Steps
- I hope this tip is helpful and improves your productivity with SSMS as a SQL Server DBA or Developer.
- Learn more about SQL Server Management Studio.
About the author
![MSSQLTips author Sergey Gigoyan](/images/Sergey-Gigoyan-2.jpg)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips