Most of these tips and tricks work for SSMS 2008 and higher, but some of them will only work for SSMS 2016 and above.
SSMS Import and Export Settings
Tools > Options > Environment > Import and Export Settings
You can configure so many settings in SSMS and then export them to use on all of your computers. The following link provides detailed instruction on how to do this and an awesome Dark theme configuration: Making SSMS Pretty: My Dark Theme.
Also, you can create a shared team settings file and use it from any network location. You can find detailed information in this article: Symbolic Links for Sharing Template Files or "How I Broke Management Studio with Symbolic Links.
You can find a complete list of all SSMS shortcuts here. Known problem for SSMS 2012 and 2014: CTRL+R does not hide the Query Result window in SSMS.
Some of the most useful SSMS shortcuts are:
|Change Selected Database|
|Toggle Results Pane|
|Cycle through clipboard ring|
|Navigate to matching parenthesis|
|Navigate to last cursor location|
|Navigate forward to cursor location|
|Comments selected text|
|Uncomments selected text|
|Go to Next Bookmark|
|Display Estimated Query Execution plan|
|View Code Editor in Full Screen|
|Close the current MDI child window|
|Parse query to check for errors|
|Simulate right mouse button|
|Display Template Explorer|
|Specify values for template parameters|
|Refresh local cache|
|Include Client Statistics|
Keyboard Shortcuts for Favorite Stored Procedures
Tools > Options > Environment > Keyboard > Query Shortcuts
There are three shortcuts that cannot be changed:
Alt + F1,
Ctrl + 1 and
Ctrl + 2. There are another 9 shortcuts I'd recommend binding to the awesome open source Brent Ozar Unlimited stored procedures and, with some limitations, Adam Machanic's
|Query Shortcut||Stored Procedure|
I'd also recommend binding these scripts as shortcuts:
SSMS Scripting Options
Script any object with data
Right click on database name > Tasks > Generate Scripts …
The Default Scripting Option
In earlier SQL Server Management Studio versions, the generated script targets the latest SQL Server version. In SSMS 17.2, the
Match Script Settings to Source has been added, with the default
True value meaning that the generated script will target the source SQL Server instance’s version, edition, and engine type, where the
False value will force the scripting to behave as it did in the previous SSMS versions.
Tools > Options > SQL Server Object Explore > Scripting > Version Options > Match Script Settings to Source
Selecting a block of text using the ALT Key
By holding down the ALT key as you select a block of text, you can control the width of the selection region as well as the number of rows. You can also activate multi line mode with the
Shift + Alt keys and using the cursor keys to format multi-line code.
More information on, and video about this awesome feature in this article by Bert Wagner: My Favorite SSMS Shortcut (After Copy/Paste).
Script Table and Column Names by Dragging from Object Explorer
Save keystrokes by dragging the
Columns folder for a table to auto-type all column names in the table in a single line.
Disable Copying of Empty Text
This behavior can be disabled in SSMS. Go to
Tools > Options > Text Editor > All Languages > General > 'Apply Cut or Copy Commands to blank lines when there is no selection' and uncheck the checkbox.
When you enable that option for your session (
Ctrl + Alt + S), SQL Server Management Studio will give you more information about the client-side processing of your query.
The Network Statistics shows you the following information:
The Time Statistics additionally shows you the following information:
Configure Object Explorer to Script Compression and Partition Schemes for Indexes
Is this index compressed or partitioned?
By default, you wouldn’t know just by scripting out the index from Object Explorer. If you script out indexes this way to check them into source code, or to tweak the definition slightly, this can lead you to make mistakes.
You can make sure you’re aware when indexes have compression enabled or are partitioned by changing your scripting settings:
Using GO X to Execute a Batch or Statement Multiple Times
GO command marks the end of a batch of statements that should be sent to SQL Server for processing, and are then compiled into a single execution plan. By specifying a number after the
GO command, the batch can be run the specified number of times. This can be useful if, for instance, you want to create test data by running an insert statement a number of times. Note that this is not a Transact SQL statement and will only work in Management Studio (and also SQLCMD or OSQL). For instance, the following SQL can be run in SSMS:
This will run the insert statement 10 times and therefore insert 10 rows into the
TestData table. In this case this is a simpler alternative than creating a cursor or while loop.
SSMS Template Replacement
One under-used feature of Management Studio is the template replacement feature. SSMS comes with a library of templates, but you can also make your own templates for reusable scripts.
In your saved .sql script, just use this magic incantation to denote the parameters for replacement. The format is simple:
<label, datatype, default value>.
Then, when you open the
.sql script, you hit
CTRL + Shift + M, and SSMS will give you a pop-up to enter your replacement values.
Color coding of connections
SQL Server Management Studio has the capability of coloring the bar at the bottom of each query window, with the color dependent on which server is connected. This can be useful in order to provide a visual check of the server that a query is to be run against, for instance to color code production instances as red, development as green and amber as test. This can also be used in conjunction with Registered Servers and CMS (Central Management Server). To add a color bar when connecting to the server, click on the Options button in the Connect to Database Engine window and then select the Connection Properties window. Select the check box towards the bottom of the window and use the ‘Select…’ button to choose a color.
Switching on SQLCMD mode enables a number of useful extra scripting style commands in SSMS. In particular, you can use it to change to the connection credentials within the query window so that you can run a query against multiple servers from the same query window. There are more details of how to do this here: Changing the SQL Server connection within an SSMS Query Windows using SQLCMD Mode.
Script Multiple Objects Using the Object Explorer Details Windows
Individual database objects, such as a table or stored procedure, can be scripted within SSMS by right-clicking on the object within Object Explorer and selecting the appropriate item in the dropdown menu. However, if you have a lot of objects to script that can quickly become time-consuming. Fortunately it’s possible to select multiple objects and script them together in a single query window. To do this just open the Object Explorer Details window from the View menu (or press the F7 key). If you want to script up multiple (or all) tables, select the Tables item under the relevant database in Object Explorer. A list of all tables appears in the Object Explorer Details window. Select the tables you want to script (using the Control key if necessary) and then right click and select which script option you want – e.g. to create a table create script for all tables.
Registered Servers / Central Management Server
If you have a lot of servers then re-entering the details in Object Explorer every time you start SSMS can be frustrating and time-consuming. Fortunately there are two facilities within SSMS that enable these details to be entered just once and "remembered" each time you open up SSMS. These two facilities areand . These were introduced in different versions of SQL Server and work in different ways, each has its own advantages and disadvantages so you may want to use both.
To add a registered server, open the Registered Servers window from the View menu (or click
CTRL + ALT + G). The window should appear in the top left corner of SSMS. Right click on the Local Server Groups folder and select ‘New Server Registration…’. Enter the server details and close the window. This new server should then appear under Local Server Groups. You can now right click and open up the server in Object Explorer or open a new query window. The server details are stored locally in an XML file and so will appear next time you open SSMS.
If you have a lot of servers then you can also create Server Groups to group together similar servers. One advantage of creating groups (other than being able to logically group similar servers together) is that you can run a query against all servers in the group, by right clicking the group and selecting ‘New Group’.
Central Management Servers are similar to Registered Servers but with some differences, the main one being that the server details are stored in a database (the Central Management Server) rather than a local file. A significant limitation with CMS is that the CMS server itself can’t be included in the list of servers.
Splitting the Query Window / Annotations and Map Mode for Vertical Scroll Bar
The query window in SSMS can be split into two separate windows so that you can see two parts of the same query simultaneously. Both parts of this split window can be scrolled independently of the other. This is especially useful if you have a large query and want to compare different areas of the same query. To split the window, simply drag the bar to the top right hand side of the window as shown below.
The splitter bar allows you to view one session with two panes. You can scroll in each pane independently. You can also edit in both the top and bottom pane.
You can also use some very useful features like annotations and Map mode - detailed instruction here: SSMS Tip #1: Annotations and Map Mode for Vertical Scroll Bar.
Annotations show the relative position of certain aspects of the script, such as errors, changes, etc. There are four types of annotations, the first three of which are shown in their own columns on the left side of the scroll bar:
Moving Columns in the Results Pane
It may not be immediately obvious, but you can switch columns around in the results pane when using the grid view, by dragging the column headers and dropping them next to another column header. This can be useful if you want to rearrange how the results are displayed without amending the query, especially if you have a lot of columns in your result set. This works only for one column.
Generating Charts and Drawings in SQL Server Management Studio
You don't have to settle for T-SQL's monochrome text output. These stored procedures let you quickly and easily turn your SELECT queries' output into colorized charts and even computer-generated art. To turn your own data into a line, column, area, or bar chart using the Chart stored procedure, you need to design a SELECT query that serves as the first parameter in the stored procedure call.
Detailed article and code here: Generating Charts and Drawings in SQL Server Management Studio.
Also you can generate amazing chart using awesome R libraries, detailed article: View R Plots from within SQL Server Management Studio.
Additional Connection Parameters
To improve the default experience for clients which connect to Azure SQL Database, the SqlConnection.Open() method will (in the case of initial connection errors / timeouts) now retry 1 time after sleeping for 10 seconds. These numbers are configurable by properties called ConnectRetryCount (default value 1) and ConnectRetryInterval (default value 10 seconds.) The previous versions of the SqlConnection class would not automatically retry in cases of connection failure.
There is a simple workaround for this situation. Add the following parameter string into the
Additional Connection Parameters tab within the SSMS connection window. The good news is that you only need to do this once, as the property is saved for future sessions for that SQL Server (until of course you remove it later).
Working with tabs headers
You can view SPID in tabs header, quickly open script containing folder or copy script file path.
Hiding tables in SSMS Object Explorer
You can actually hide an object from object explorer by assigning a specific extended property:
EXEC sp_addextendedproperty @name = N'microsoft_database_tools_support', @value = 'Hide', @level0type = N'Schema', @level0name = 'Person', @level1type = N'Table', @level1name = 'Address'; GO
You can then remove the property (and the object will show back up) like so:
EXEC sp_dropextendedproperty @name = N'microsoft_database_tools_support', @level0type = N'Schema', @level0name = 'Person', @level1type = N'Table', @level1name = 'Address'; GO
DENY VIEW DEFINITION
DENY VIEW DEFINITION ON Schema.Table TO UserName;
Now UserName won’t be able to see Table in
Object Explorer. In fact, they won’t be able to see the table in
VIEW DEFINITION is the ability to see the definition of the object. In the case of Stored Procedures this is the code, as it is with Views. In the case of Tables it’s the columns, definitions, etc.
UnDock Tabs and Windows for Multi Monitor Support
From SSMS 2012 onwards, you can easily dock/undock the query tabs as well as different object windows inside SSMS to make better use of the screen real estate and multiple monitors you have.
RegEx-Based Finding and Replacing of Text in SSMS
So often, one sees developers doing repetitive coding in SSMS or Visual Studio that would be much quicker and easier by using the built-in Regular-Expression-based Find/Replace functionality. It is understandable, since the syntax is odd and some features are missing, but it is still well worth knowing about.
More details and examples you can be found here: RegEx-Based Finding and Replacing of Text in SSMS.
My favorite regex: replace
\n,. It useful in many cases when you have column names copied from, for example, Excel and need quickly get a SQL query.
Changing What SSMS Opens on startup
The more interesting way to look at this shortcut is to check the various options available with SSMS command line parameters. To check them, use the following command:
C:\> ssms /? `` ![SSMS command line parameters](/SSMS/SSMS_Tips/24_SSMS_command-line_parameters.png) You can customize SSMS startup behavior in `Tools -> Options -> Environment -> Startup` and hide system objects in Object Explore: ![Changing what SSMS opens on startup](/SSMS/SSMS_Tips/24_changing_what_ssms_opens_on_startup.gif) Also you can disable the splash screen - this cuts the time it takes SSMS to load for versions before SSMS 17. Right click your shortcut to SSMS and select properties. Enter the text `-nosplash` right after the ending quote in the path: ![SSMS link nosplash option](/SSMS/SSMS_Tips/24_changing_what_ssms_opens_on_startup_nosplash.png) It is useful to create a solution of commonly used SQL scripts to always load at start-up. 1. Display the Solution Explorer by pressing `Ctrl+Alt+L` or clicking `View -> Solution Explorer`. 2. Then right click the `Solution "Solution1" (0 projects)` text and select `Add -> New Project`. 3. Use the default `SQL Server Scripts` template and give your solution a clever name. 4. Rename all of your SQL Code Snippets so the extension is .SQL. Drag them into the queries folder within the Solution Explorer. 5. Open Windows explorer and browse to the location of your solution. Copy file location address to your clipboard. Go back to your SSMS shortcut properties and add within double quotes the location and file name of your solution before the "-nosplash". This is the complete text of shortcut properties:
"C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe" "C:\Users\taranov\Documents\SQL Server Management Studio\Projects\MySQLServerScripts.ssmssln" -nosplash
![Create a solution of commonly used SQL scripts](/SSMS/SSMS_Tips/24_create_solution_commonly_used_sql_scripts.gif) <a id="25"></a> ## Modifying New Query Template You can modified New Query template for any instance SQL Server:
C:\Program Files (x86)\Microsoft SQL Server[140|130|120|110]\Tools\Binn\ ManagementStudio\SqlWorkbenchProjectItems\Sql\SQLFile.sql
For example, you can add begin transaction statement for preventing ups queries: ```sql BEGIN TRANSACTION; -- COMMIT TRANSACTION; -- ROLLBACK TRANSACTION;
Thanks for this tip to Aaron Bertrand: T-SQL Tuesday #92: Lessons Learned the Hard Way.
Query Execution Options
More detailed article here: Knowing the Options.
The options represent the SET values of the current session.
SET options can affect how the query is execute thus having a different execution plan. You can find these options in two places within SSMS under
Tools -> Options -> Query Execution -> SQL Server -> Advanced:
As well as
Tools -> Options -> Query Execution -> SQL Server -> ANSI:
Using the interface to check what is set can get tiresome. Instead, you can use the system function
@@OPTIONS. Each option shown above has a BIT value for all 15 options indicating whether or not it is enabled.
@@OPTIONS takes the binary representation and does a BITWISE operation on it to produce an integer value based on the sum of which BITS are enabled.
The default value for
SELECT @@OPTIONS is . Let’s assume for a moment that the only two options that are enabled on my machine are ANSI_PADDING and ANSI_WARNINGS. The values for these two options are 8 and 16, respectively speaking. The sum of the two is 24.
|1||DISABLE_DEF_CNST_CHK||Controls interim or deferred constraint checking.|
|2||IMPLICIT_TRANSACTIONS||For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.|
|4||CURSOR_CLOSE_ON_COMMIT||Controls behavior of cursors after a commit operation has been performed.|
|8||ANSI_WARNINGS||Controls truncation and NULL in aggregate warnings.|
|16||ANSI_PADDING||Controls padding of fixed-length variables.|
|32||ANSI_NULLS||Controls NULL handling when using equality operators.|
|64||ARITHABORT||Terminates a query when an overflow or divide-by-zero error occurs during query execution.|
|128||ARITHIGNORE||Returns NULL when an overflow or divide-by-zero error occurs during a query.|
|256||QUOTED_IDENTIFIER||Differentiates between single and double quotation marks when evaluating an expression.|
|512||NOCOUNT||Turns off the message returned at the end of each statement that states how many rows were affected.|
|1024||ANSI_NULL_DFLT_ON||Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.|
|2048||ANSI_NULL_DFLT_OFF||Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.|
|4096||CONCAT_NULL_YIELDS_NULL||Returns NULL when concatenating a NULL value with a string.|
|8192||NUMERIC_ROUNDABORT||Generates an error when a loss of precision occurs in an expression.|
|16384||XACT_ABORT||Rolls back a transaction if a Transact-SQL statement raises a run-time error.|
SQL Server Diagnostics Extension
For example, Joe, a DBA from Contoso, Ltd., finds that SQL Server has generated a memory dump while running a workload, and he would like to debug the issue. Using this feature, John can upload the dump and receive recommended KB articles from Microsoft, which can help him fix the issue.
More details here: SQL Server Diagnostics Extension for SSMS and SQL Server Diagnostics: New "Analyze Dumps" feature
Connect to SQL Servers in another domain using Windows Authentication
You may find that you need to connect remotely to a SQL Server that is in another domain. This is a problem when SQL authentication is not an option, because Windows machine is in its own domain; Management Studio cannot directly override the local Windows credentials and pass the credentials for a remote domain user.
Solution 1: runas
At this point, you are prompted for the password for the remote user and, once provided, you are told that it is attempting to run the program as that user.
Now you can double-click the shortcut and, after simply providing the remote password, it will launch an instance of Management Studio that will use those remote credentials every time you use Windows Authentication and try to connect to a remote server.
Solution 2: Credential Manager
Another method you can use to connect to remote domains using Windows Authentication is to use the Credential Manager built into Windows. This can be a little tricky to setup, especially if you are connecting over a VPN.
The part that is tricky is ensuring that name resolution matches exactly. The Windows machine needs to identify the remote server by IP or fully-qualified domain name (FQDN), and may need to explicitly specify the port. Added the remote server names to
c:\Windows\System32\drivers\etc\hosts file, and then ran
Once you are sure you have the right FQDN (make sure you can ping it by name, or better yet, telnet to the server name on the SQL Server port (usually 1433)), go to
Control Panel > Credential Manager, choose Windows Credentials, and Add a Windows Credential. Then specify the specific server name (potentially you may need
server:port notation), the domain user (including the domain name), the password, and click OK:
Now, when you connect from Management Studio, make sure you use the exact same server name as the one you entered in the credential, including the port number (only now, use a comma instead of colon if specifying the port number: server,port). Like with the runas /netonly solution, it will look like you are connecting using your local Windows credentials, but - as long as the credential store doesn't have any issues in matching the server name - those really are the remote credentials that are being passed under the covers.
More details here: Connect to SQL Servers in another domain using Windows Authentication
SSMS Default Reports
By right-clicking on the SQL Server instance and navigating to
Reports > Standard Reports, you can see the predefined SSMS reports:
[New in SSMS: Performance Dashboard built-in(https://blogs.msdn.microsoft.com/sql_server_team/new-in-ssms-performance-dashboard-built-in/)
Live Query Statistics
SQL Server Management Studio provides the ability to view the live execution plan of an active query. This live query plan provides real-time insights into the query execution process as the controls flow from one query plan operator to another. The live query plan displays the overall query progress and operator-level run-time execution statistics such as the number of rows produced, elapsed time, operator progress, etc. Because this data is available in real time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance issues. This feature is available beginning with SQL Server 2016 Management Studio, however it can work with SQL Server 2014.
More details here: Live Query Statistics
Warning: This feature is primarily intended for troubleshooting purposes. Using this feature can moderately slow the overall query performance. This feature can be used with the Transact-SQL Debugger.
Searching in Showplan
In SSMS 17.2, appear the ability to search table name, index name or column name and more in graphical showplan. just use
CTRL+F to start a search in graphical showplan (or right-click on a blank area of the plan, and in the context menu click on Find Node option), and you can quickly see exactly where node id 30 is. More details here: New in SSMS: Searching in Showplan
Object Explore Details
To bring up the Object Explorer Details pane, use the
F7 shortcut or it can be accessed from
Toolbar -> View -> Object -> Explorer Details.
You can searched for the keyword
%Country% and using
% as a wildcard character. This tells SQL Server Management Studio to display all the objects that have the word
Country in their name.
Working with Azure SQL
For SSMS 17.2 and above you can enable Multi-Factor Authentication in Azure SQL Database, more details here: Configure Multi-Factor Authentication in Azure SQL Database
Using Extended Events and Profiler in SSMS
Full instruction here: EXTENDED EVENTS AND PROFILER: XE PROFILER