Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Management Studio Productivity Tips


By:   |   Read Comments (5)   |   Related Tips: More > SQL Server Management Studio

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

SSMS authentication window opens and you need to choose your SQL Server instance to login.

connect to server in SQL Server Management Studio

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

With this command we are connecting to TestDB database in DBSERVER1 using SQL Server authentication:

object explorer in SQL Server Management Studio

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

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
sql query in SQL Server Management Studio

After agreeing to restore the default layout by clicking “Yes”, your SSMS returns to its original appearance:

object explorer in SQL Server Management Studio

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

To change these values go to Tools > Options as shown below:

sql server options in SQL Server Management Studio

After that we need to choose “SQL Server Object Explorer” > ”Commands”:

options in SQL Server Management Studio

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

Now, we can see that there is no limit for editing or selecting rows:

object explorer in SQL Server Management Studio

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

To allow save changes that requires table re-creation we need to go “Tools” > ”Options”:

object explorer in SQL Server Management Studio

Then choose “Designers” > ”Table and Database Designers”:

table and database designers in SQL Server Management Studio

On the right side we need to uncheck “Prevent saving changes that require table re-creation”:

table options in SQL Server Management Studio

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

We can do the same by going to “Window” > ”New Vertical Tab Group”:

new vertical tab group in SQL Server Management Studio

Now we can see both queries simultaneously:

sql query in SQL Server Management Studio

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

“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
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.


Last Update:






About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 6 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, July 14, 2017 - 8:06:36 AM - Greg Robidoux Back To Top

Thanks Paul.

This has been updated. 

-Greg


Friday, July 14, 2017 - 5:53:48 AM - Paul Back To Top

"Lunch SSMS from a Command Prompt

It is possible to lunch SQL Server Management Studio from command prompt .. "

.. surely you mean  Launch  ?

Great article, sorry I'm a pedant for spelling.


Thursday, July 13, 2017 - 6:59:51 AM - Gfw Back To Top

What I am looking for is the ability to further define the actual SQL query. Example to add a Where clause or Order By clause to the initial Select statement. Thanks again.  I can't find the commands to add to the tool bar. Thanks again.

 


Wednesday, July 12, 2017 - 3:23:05 AM - Sergey Gigoyan Back To Top

 

For Ver 17.1 it is the same: "Right-click" on the table -> "Edit Top 200 rows". If you need to change 200 to another value: "Tools"->"Options"->"SQL Server Object Explorer"->"Commands"->"Table and View Options"->"Value for Edit Top Rows command".

Thanks

 

 


Sunday, July 09, 2017 - 3:21:35 PM - Gfw Back To Top

Good tips, thanks.

Question: Ver 17.1... Edit top 200 option. I can't find the command to edit the SQL used to get the top 200.  Can you point me in teh right direction?

 


Learn more about SQL Server tools