Skip to content

Latest commit

 

History

History
960 lines (737 loc) · 63.1 KB

SSMS_Tips.md

File metadata and controls

960 lines (737 loc) · 63.1 KB
Raw

SQL Server Management Studio Tips

Most tips works for SSMS higher 2008 but some of them only works for SSMS 2016 and above

Road map

Table of Content:

  1. Import and Export Settings
  2. SSMS Shortcuts
  3. Keyboard Shortcuts for Favorite Stored Procedures and Scripts
  4. SSMS Scripting Option
  5. Selecting a block of text using the ALT Key
  6. Script Table and Column Names by Dragging from Object Explorer
  7. Disable Copy of Empty Text
  8. Client Statistics
  9. Configure Object Explorer to Script Compression and Partition Schemes for Indexes
  10. Using GO X to Execute a Batch or Statement Multiple Times
  11. SSMS Template Replacement
  12. Color coding of connections
  13. SQLCMD mode
  14. Script multiple objects using the Object Explorer Details Windows
  15. Registered Servers / Central Management Server
  16. Splitting the Query Window and Annotations and Map Mode for Vertical Scroll Bar
  17. Moving columns in the results pane
  18. Generating Charts and Drawings in SQL Server Management Studio
  19. Additional Connection Parameters
  20. Working with tabs headers
  21. Hiding tables in SSMS Object Explorer
  22. UnDock Tabs and Windows for Multi Monitor Support
  23. RegEx-Based Finding and Replacing of Text in SSMS
  24. Changing what SSMS opens on startup
  25. Modifying New Query Template
  26. Query Execution Options
  27. SQL Server Diagnostics Extension
  28. Connect to SQL Servers in another domain using Windows Authentication
  29. SSMS Default Reports
  30. Live Query Statistics
  31. Searching in Showplan
  32. Object Explore Details
  33. Working with Azure SQL
  34. Using Extended Events and Profiler in SSMS
  35. Vulnerability Assessment in SSMS
  36. Import Flat File to SQL Wizard
  37. AutoRecover in SSMS
  38. View and query Power BI .pbix files in SSMS
  39. Using SSMS to analyze Power BI with XMLA
  40. SSMS shortcut to comment/uncomment a specific part of the query
  41. Reference

Great thanks to:

  • Kendra Little (b | t)
  • Slava Murygin (b)
  • Mike Milligan (b)
  • Kenneth Fisher (b | t)
  • William Durkin (b | t)
  • John Morehouse (b | t)
  • Phil Factor (b | t)
  • Klaus Aschenbrenner (b | t)
  • Latish Sehgal (b)
  • Arvind Shyamsundar (b)
  • SQLMatters
  • MSSQLTips
  • Anthony Zanevsky, Andrew Zanevsky and Katrin Zanevsky
  • Andy Mallon (b | t)
  • Aaron Bertrand (b | t)
  • Daniel Calbimonte (b | t)
  • Ahmad Yaseen (b | t)
  • Solomon Rutzky (b | t)
  • Bert Wagner (b | t)
  • Thomas LaRock (b | t)
  • Jen Mccown (b)
  • Louis Davidson (b | t)
  • Solomon Rutzky (b | t)
  • Michelle Haarhues (b | t)
  • Christian Wade (b | t)
  • Kellyn Pot’Vin-Gorman (b | t)
  • Kevin Feasel (b | t)
  • Brent Ozar (b | @BrentO)

1. Import and Export Settings

Tools > Options > Environment > Import and Export Settings

Default settings (if you need to compare with yours) you can find here: SSMS settings files

You can configure so many settings in SSMS and then export it and use on all your computers. Below link provide detailed instruction and awesome Dark theme configuration: Making SSMS Pretty: My Dark Theme

Also you can create shared team settings file and use it from network location. Detailed information you can find in this article Symbolic Links for Sharing Template Files or "How I Broke Management Studio with Symbolic Links"

Import and Export Settings

2. SSMS Shortcuts

All shortcuts you can find here. Known problem for SSMS 2012 and 2014: CTRL+R does not hide the Query Result window in SSMS

Create custom shortcut as simple as possible:

  1. Tools > Options > Environment > Keyboard
  2. Use the search bar Show Commands Containing to find and select the command.
  3. In Press Shortcut Keys, press the shortcut combination you want to use.
  4. Click Assign. If you don’t click Assign, and just click OK, your shortcut won’t be assigned.
  5. Click OK. (Note that some shortcut changes take effect in query windows you open after the change.) More details here: MANAGEMENT STUDIO SHORTCUT – CHANGE CONNECTION

Most useful are:

Shortcut Description
Ctrl + U Change Selected Database
Ctrl + R Toggle Results Pane
Ctrl + Space Activate Autocomplete
Ctrl + Shift + V Cycle through clipboard ring
Ctrl + ] Navigate to matching parenthesis
Ctrl + – Navigate to last cursor location
Ctrl + Shift + – Navigate forward to cursor location
Ctrl + K, Ctrl + C Comments selected text
Ctrl + K, Ctrl + U Uncomments selected text
Ctrl + K, Ctrl + K Toggle Bookmark
Ctrl + K, Ctrl + N Go to Next Bookmark
Ctrl + L Display Estimated Query Execution plan
Shift + Alt + Enter View Code Editor in Full Screen
Ctrl + I Quick Search
Ctrl + F4 Close the current MDI child window
Ctrl + F5 Parse query to check for errors
Shift + F10 Simulate right mouse button
Ctrl + Alt + T Display Template Explorer
Ctrl + Shift + M Specify values for template parameters
Ctrl + Shift + R Refresh local cache
Ctrl + Alt + S Include Client Statistics

3. Keyboard Shortcuts for Favorite Stored Procedures and Scripts

A query shortcut allows you to execute code simply by hitting Ctrl and a number key. Cool. But first, there are a few things to know about query shortcuts:

  1. The window in SSMS where you configure the query shortcuts has the text field for the shortcut labeled as Stored Procedure, which is misleading because you can specify a query. You can even specify multiple queries.
  2. Whatever you specify needs to be a single line: no newlines / CRLFs. Any text past the first return will be truncated.
  3. When using the keyboard query shortcuts, if nothing is highlighted then only the code in the shortcut is executed. But, if any T-SQL is highlighted when you execute the shortcut, then the highlighted code is executed after the code stored in the shortcut finishes. Adding a RETURN; at the end of the shortcut simply stops the processing after the code stored in the shortcut finishes. BUT, if anything is highlighted when you execute a query shortcut, it is still parsed, even if not executed. Hence, you can still get parse errors even with the RETURN; added at the end.

Tools > Options > Environment > Keyboard > Query Shortcuts

Keyboard Shortcuts for Favorite Stored Procedures

3 Shortcuts can not be changed: Alt + F1 - sp_help, Ctrl + 1 - sp_who and Ctrl + 2 - sp_lock. For another 9 shortcuts my recommendation awesome open source Brent Ozar teams procedures and with some limitations in License usage Adam Machanic [sp_WhoIsActive]:

Query Shortcut Stored Procedure
Alt + F1 sp_help
Ctrl + F1 sp_WhoIsActive
Ctrl + 1 sp_who
Ctrl + 2 sp_lock
Ctrl + 3 sp_Blitz
Ctrl + 4 sp_BlitzCache
Ctrl + 5 sp_BlitzWho
Ctrl + 6 sp_BlitzQueryStore
Ctrl + 7 sp_BlitzFirst
Ctrl + 8 usp_BulkUpload
Ctrl + 9 sp_BlitzTrace
Ctrl + 0 sp_foreachdb

Also recommended:

Some useful shortcuts for sp_WhoIsActive from Kevin Feasel

More details here.

What’s Going On shortcut?

EXEC sp_whoisactive
    @get_full_inner_text = 1
  , @get_outer_command = 1
  , @find_block_leaders = 1
  , @get_task_info = 2
  , @get_transaction_info = 1
  , @get_plans = 0
  , @output_column_list = '[session_id][login_name][dd%][CPU][reads][writes][wait_info][blocking_session_id][blocked_session_count][sql_text][sql_command][status][tempdb_%][%]'
  , @sort_order = '[session_id]';

And here it is in a little bit nicer of a format so we can cover it:

EXEC sp_whoisactive
    @get_full_inner_text = 1
   , @get_outer_command = 1
   , @find_block_leaders = 1
   , @get_task_info = 2
   , @get_transaction_info = 1
   , @get_plans = 0
   , @output_column_list = '[session_id][login_name][dd%][CPU][reads][writes][wait_info][blocking_session_id][blocked_session_count][sql_text][sql_command][status][tempdb_%][%]'
   , @sort_order = '[session_id]';

When you need to get execution plans, that’s when I break out Ctrl+4. Here is the one-liner:

EXEC sp_whoisactive
    @get_full_inner_text = 1
   , @get_outer_command = 1
   , @find_block_leaders = 1
   , @get_task_info = 2
   , @get_transaction_info = 1
   , @get_plans = 1
   , @output_column_list = '[session_id][login_name][dd%][CPU][reads][writes][wait_info][blocking_session_id][blocked_session_count][sql_text][sql_command][status][tempdb_%][%]'
   , @sort_order = '[session_id]';

4. SSMS Scripting Option

Script any object with data

Right click on database name > Tasks > Generate Scripts …

Script any object with data

The Default Scripting Option

In the previous SQL Server Management Studio versions, the generated script will target the latest released SQL Server version. Starting SSMS 17.2, the Match Script Settings to Source has been added, with the default True value means 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 the previous SSMS versions.

Tools > Options > SQL Server Object Explore > Scripting > Version Options > Match Script Settings to Source

More details here: What’s new in SQL Server Management Studio 17.2; Authentication methods, scripting options and more

Script for SQL Server version

5. 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. Also you can activate multi line mode with Shift + Alt keys and using keyboard arrows to format multi line code.

More info and video about this awesome feature in this article: My Favorite SSMS Shortcut (After Copy/Paste) (by Bert Wagner)

SSMS Selecting a block of text using the ALT Key

SSMS Paste values into a template of commands

6. Script Table and Column Names by Dragging from Object Explorer

Save keystrokes by dragging Drag the Columns folder for a table in to auto-type all column names in the table in a single line.

  • Warning: this doesn’t include square [brackets] around the column names for SSMS below 17 version, so if your columns contain spaces or special characters at the beginning, this shortcut isn’t for you
  • Dragging the table name over will auto-type the schema and table name, with brackets.

7. Disable Copy of Empty Text

  • Select a block of text to copy;
  • Move the cursor the place where you want to paste the code;
  • Accidentally press Ctrl+C again instead of Ctrl+V;
  • Block of copied text is replaced by an empty block;

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.

Disable Copy of Empty Text

8. Client Statistics

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:

  • Number of Server Roundtrips
  • TDS Packets sent from Client
  • TDS Packets received from Server
  • Bytes sent from Client
  • Bytes received from Server

The Time Statistics additionally shows you the following information:

  • Client Processing Time
  • Total Execution Time
  • Wait Time on Server Replies

9. 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 or are partitioned by changing your scripting settings:

  • Click Tools – > Options -> SQL Server Object Explorer -> Scripting
  • Scroll down in the right pane of options and set both of these to True
    • Script Data Compression Options
    • Script Partition Schemes
  • Click OK

10. Using GO X to Execute a Batch or Statement Multiple Times

The GO command marks the end of a batch of statements that should be sent to SQL Server for processing, and then compiled into a single execution plan. By specifying a number after the GO the batch can be run 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 :

IF OBJECT_ID('TestData','U') IS NOT NULL DROP TABLE TestData;

CREATE TABLE TestData(ID INT IDENTITY (1,1), CreatedDate DATETIME2);
GO

INSERT INTO TestData(CreatedDate) SELECT GETDATE();
GO 10

SELECT ID, CreatedDate FROM TestData;

IF OBJECT_ID('TestData','U') IS NOT NULL DROP TABLE TestData;

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.

11. 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 the 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.

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

13. SQLCMD mode

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

Also some great tips from Louis Davidson article: Using SQLCMD, End Execution of SQL Commands in a File

  1. Making Sure You Don’t Accidentally Execute The Entire File By Mistake using :EXIT command in your scripts, details see here sqlcmd Commands.
  2. Quitting the Statements of a File Based on a Condition using :On Error[ exit | ignore] command, details see here sqlcmd Commands.

14. 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 drop down 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 up all 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.

15. 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 are Registered Servers and Central Management Servers. 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 then 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 Server 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.

16. Splitting the Query Window and Annotations and Map Mode for Vertical Scroll Bar

The query window in SSMS can be split into two so that you can look at two parts of the same query simultaneously. Both parts of the split window can be scrolled independently. 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.

Splitting the Query Window

Also you can view some very useful features like annotations and Map mode, detailed instruction:

  1. SSMS Tip #1: Annotations and Map Mode for Vertical Scroll Bar
  2. SQL Server Management Studio Scroll Bar Enhancements

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:

  • "Changes" are show in yellow (unsaved) and green (saved). These occupy the left column.
  • "Marks" are shown in maroon (Breakpoints) and black (Bookmarks). These occupy the left column.
  • "Errors" are shown in red. These occupy the right column.
  • "Caret Position" is a thin, blue line going horizontally across the scroll bar. This represents the line of the script that the cursor is on.

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

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

19. Additional Connection Parameters

One such change SSMS got for free is the connection resiliency logic within the SqlConnection.Open() method. To improve the default experience for clients which connect to Azure SQL Database, the above 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. It is to 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 it is removed by you later.)

ConnectRetryCount=0

Additional Connection Parameters

20. Working with tabs headers

You can view SPID in tabs header, quickly open script containing folder or copy script file path.

Working with tabs headers

21. Hiding tables in SSMS Object Explorer

  1. 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
  1. 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 sys.tables or INFORMATION_SCHEMA.TABLES

VIEW DEFINITION is the ability to see the definition of the object. In the case of SPs the code, same with Views and in the case of Tables it’s the columns definitions etc.

22. UnDock Tabs and Windows for Multi Monitor Support

From SSMS 2012 and 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.

UnDock Tabs and Windows for Multi Monitor Support

23. 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 find here RegEx-Based Finding and Replacing of Text in SSMS.

My favorite regex: replace \t on \n, . It useful in many cases when you have column names copied from, for example, Excel and need quickly get sql query.

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

You can customize SSMS startup behavior in Tools -> Options -> Environment -> Startup and hide system objects in Object Explore:

Changing what SSMS opens on startup

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

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

25. 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:

BEGIN TRANSACTION;
 
 
-- COMMIT   TRANSACTION;
-- ROLLBACK TRANSACTION;

Thanks for this tip Aaron Bertrand: T-SQL Tuesday #92: Lessons Learned the Hard Way

26. 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:

Query Execution Options Advanced

As well as Tools -> Options -> Query Execution -> SQL Server -> ANSI:

Query Execution Options 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.

Default value for SELECT @@OPTIONS is 5496. 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.

Microsoft docs: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option

Value Configuration Description
1 DISABLE_DEF_CNST_CHK Deprecated 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. 
/***************************************************************
  Author: John Morehouse
  Summary: This script display what SET options are enabled for the current session.
 
  You may alter this code for your own purposes. You may republish altered code as long as you give due credit.
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
***************************************************************/
SELECT 'DISABLE_DEF_CNST_CHK'    AS 'Option', CASE @@options & 1     WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'IMPLICIT_TRANSACTIONS'   AS 'Option', CASE @@options & 2     WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'CURSOR_CLOSE_ON_COMMIT'  AS 'Option', CASE @@options & 4     WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ANSI_WARNINGS'           AS 'Option', CASE @@options & 8     WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ANSI_PADDING'            AS 'Option', CASE @@options & 16    WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ANSI_NULLS'              AS 'Option', CASE @@options & 32    WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ARITHABORT'              AS 'Option', CASE @@options & 64    WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ARITHIGNORE'             AS 'Option', CASE @@options & 128   WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'QUOTED_IDENTIFIER'       AS 'Option', CASE @@options & 256   WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'NOCOUNT'                 AS 'Option', CASE @@options & 512   WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ANSI_NULL_DFLT_ON'       AS 'Option', CASE @@options & 1024  WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ANSI_NULL_DFLT_OFF'      AS 'Option', CASE @@options & 2048  WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'CONCAT_NULL_YIELDS_NULL' AS 'Option', CASE @@options & 4096  WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'NUMERIC_ROUNDABORT'      AS 'Option', CASE @@options & 8192  WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'XACT_ABORT'              AS 'Option', CASE @@options & 16384 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled';

27. SQL Server Diagnostics Extension

Analyze Dumps – Customers using this extension will be able to debug and self-resolve memory dump issues from their SQL Server instances and receive recommended Knowledge Base (KB) article(s) from Microsoft, which may be applicable for the fix. The memory dumps are stored in a secured and compliant manner as governed by the Microsoft Privacy Policy.

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.

SQL Server Diagnostics Extension

More details here: SQL Server Diagnostics Extension for SSMS and SQL Server Diagnostics: New "Analyze Dumps" feature

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

runas /netonly /user:domain\username "c:\path\ssms.exe"

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.

Also you can create shortcut: Right-click the desktop and choose New > Shortcut. Then enter the same information as above in the command line: Connect to SQL Servers in another domain using Windows Authentication with Shortcut

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 ipconfig /dnsflush.

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: Connect to SQL Servers in another domain using Windows Authentication with Credential Manager

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

29. SSMS Default Reports

By right-clicking on the SQL Server instance and navigating to Reports > Standard Reports, you can see the predefined SSMS reports:

SSMS_Server_Default_Reports

You can run default reports database: SSMS_Database_Default_Reports

Useful another reports information:

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

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

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

SSMS_Database_Default_Reports

SSMS_Database_Default_Reports

You can customize display field hide or unhide it: SSMS_Database_Default_Reports

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.

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

34. Using Extended Events and Profiler in SSMS

Full instruction here: EXTENDED EVENTS AND PROFILER: XE PROFILER

35. Vulnerability Assessment in SSMS

You will need version 17.4 for the Vulnerability Assessment feature. Right-click to any database to start a scan.

SSMS_Database_Default_Reports

  1. Run a scan
  2. Specify where scan will be saved
  3. View the report
  4. Analyze the results and resolve issues
  5. Set your Baseline
  6. Run a new scan to see your customized tracking report
  7. Open a previously run scan

More info here: SQL Vulnerability Assessment Available in SSMS and Vulnerability Assessment features

36. Import Flat File to SQL Wizard

You will need version 17.3 or later.

Detailed article here: Import Flat File to SQL Wizard

Import Flat File Wizard is a simple way to copy data from a flat file (.csv, .txt) to a destination.

This wizard was created to improve the current import experience leveraging an intelligent framework known as Program Synthesis using Examples (PROSE). For a user without specialized domain knowledge, importing data can often be a complex, error prone, and tedious task. This wizard streamlines the import process as simple as selecting an input file and unique table name, and the PROSE framework handles the rest. PROSE analyzes data patterns in your input file to infer column names, types, delimiters, and more. This framework learns the structure of the file and does all of the hard work so our users don't have to.

37. AutoRecover in SSMS

With the default settings, you can lose up to 5 minutes of work on a sudden reboot. The recovered information will be kept for 7 days so there is some time to get your files. These settings can be modified, changing how often auto recover happens and how long the information is retained. It depends on the criticality of things that you work on as to how often you would want AutoRecover to save your scripts. This feature can be turned off but it is not recommended.

To change the settings, go to: Tools -> Options -> Environment -> AutoRecover.

More details here.

AutoRecover in SSMS

38. View and query Power BI .pbix files in SSMS

All Power BI files end with .pbix. You can make a copy of the file, (because DBA always like to work from copies vs. the originals) then rename the file from .pbix to .zip. You can then unzip the file and see the base components that make up a Power BI report and visuals:

Using SSMS with Power BI

While the Power BI report is open, you will see what is running using the netstat -b -n command from the command prompt as an administrator:

You will notice that pbidesktop.exe is port 54125, with multiple child threads. You will also notice there is an executable also sourced from the pbidesktop.exe process called msmdsrv.exe. That’s the executable for an Analysis Server. Your data model is being run by trimmed down Analysis Server in Power BI. If you have an Analysis Server port, you can connect to it with SSMS using the ID for Power BI Desktop. In our example above, the ID is 54125 and as the desktop is running on your PC, it would be your localhost for the server name. Open up SSMS and update the server type to Analysis Server and type in the following, using my Active Directory login to connect.

You will connect to the trimmed down Analysis Server behind the PBI Desktop report you have running on your screen and have limited interactive options.

Using SSMS with PowerBI

More details here.

39. Using SSMS and Profiler to analyze PowerBI with XMLA

SSMS can be used to, for example, view partitions generated by incremental refresh. Version 18.0 RC1 or above is required.

SQL Server Profiler can be used for tracing and debugging. SSMS 18.0 RC1 or above is required.

More details here.

40. SSMS shortcut to comment/uncomment a specific part of the query?

You can easily do this with Code Snippets:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
    <_locDefinition xmlns="urn:locstudio">
        <_locDefault _loc="locNone" />
        <_locTag _loc="locData">Title</_locTag>
        <_locTag _loc="locData">Description</_locTag>
        <_locTag _loc="locData">Author</_locTag>
        <_locTag _loc="locData">ToolTip</_locTag>
        <_locTag _loc="locData">Default</_locTag>
    </_locDefinition>
    <CodeSnippet Format="1.0.0">
        <Header>
            <Title>SLASH STAR COMMENT (surrounds)</Title>
            <Shortcut></Shortcut>
            <Description>Surround selected text with slash star comment block /**/</Description>
            <Author>wBob</Author>
            <SnippetTypes>
                <SnippetType>SurroundsWith</SnippetType>
            </SnippetTypes>
        </Header>
        <Snippet>
            <Declarations>
                <Literal>
                    <!--<ID>x</ID><ToolTip>y</ToolTip><Default>z</Default>-->
                </Literal>
            </Declarations>
            <Code Language="SQL">
                <![CDATA[/* $selected$ $end$ */]]>
            </Code>
        </Snippet>
    </CodeSnippet>
</CodeSnippets>

Save the file with a .snippet extension and follow the instructions from here to add the snippet to SSMS.

Instructions for use:

  1. Highlight the text you want to surround with the comment block in the query editor
  2. Press Ctrl+K, Ctrl+S to summon the 'Surround With' snippets. Note the comma between the two sequences, it means "press Ctrl+K, then press Ctrl+S in quick succession"
  3. Select your snippet from the list
  4. Your highlighted text is surrounded with the comment block
  5. press Ctrl+Z to Undo - this is the normal windows undo command. It will remove the comment block as long as you haven't made any other edits.

More details here

40. Reference: