SQL Server Management Studio (SSMS) Tips and Tricks

1.    

One feature I missed was the ability to change the query window to red when connected to production and green when connected to Development.  I discovered this feature is built in for SQL Server 2008!

Open SSMS and in the "Connect to Server" dialog click the "Options" button.  Select the "Connection Properties" tab and check the box next to "Use custom color".  Select your desired color and that's it!  While you"re in there it might be a good idea to set your default database to something other than "Master."




2.     Install a Tool to Automatically Format your T-SQL Code

I"m cheap.  I use Poor Man"s T-SQL Formatter.

3.     Set up some Keyboard Shortcuts

In SSMS click Tools, and then Options.  Select Keyboard from the Environment tree and set up the things you are like to type very often.  These shortcuts execute as soon as you press the hotkey.  This makes their usefulness somewhat limited in my opinion.  A good one to add is CTRL + F1 as sp_helptext.  This allows you to highlight a stored procedure, function, or view and quickly look at it"s code.



Highlight an entity within the Query window and press ALT+F1 to execute sp_help on the object. This will give you a breakdown of any columns, indexes, parameters, etc.


Note: If you need to specify an object in a different schema, you have to use the two part name "schema.Object" and put it in single quotes.  For example, "dbo.MyTable".
I use a program called called Clavier + to assign keyboard shortcuts to things I want typed but not executed right away.

These keyboard shortcuts are built-in to Management Studio:
F5, CTRL + E or ALT + X - execute currently selected TSQL code
CTRL + R - show/hide Results Pane
CTRL + N - Open New Query Window
CTRL + L - Display query execution plan
CTRL + SHIFT + Q - Display the query designer (noob.)
CTRL + I - Incremental search.  After pressing Ctrl+I, look at the bottom left of your SSMS window.  Here you type what you want to search for.  Press F3 to cycle.
CTRL + K + C and CTRL + K + U - comment/uncomment selected block of code 
CTRL + SHIFT + U and CTRL + SHIFT + L - changes selected text to UPPER/lower case
ALT + Select text with mouse and drag - select/cut/copy/paste a rectangular block of text
Another big typing saver is to right click a table within object explorer and selecting "Script Table As" and choosing one of the many options.



3.    Modify Startup Options

In SSMS click Tools, and then Options.  Select Environment in the navigation tree.  Here I like to change the first setting to always "Always Open Object Explorer and new query."  I also crank up the recently used files setting to the max and hide the system objects in Object Explorer.  If you have never tried the MDI environment, you might like it better than the tabbed documents. This screen is also where you change that setting.



I also like to disable the splash screen.  This cuts the time it takes SQL Server Management Studio to load in half.  Right click your shortcut to SSMS and select properties.  Enter the text "-nosplash" (without the quotes) right after the ending quote in the path.



Finally, it is useful to create a solution of commonly used SQL scripts to always load at start-up.  To do this, first display the Solution Explorer by pressing Control+Alt+L or clicking View, Solution Explorer.  Now, right click the "Solution "Solution1" (0 projects)" text and select Add, New Project.



Use the default "SQL Server Scripts" template and give your solution a clever name.



Rename all of your SQL Code Snippets so the extension is .SQL.  Drag them into the queries folder within the Solution Explorer.

Click File Save All and save your solution.  Note the location.  Next, 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 within my shortcut properties:

"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" "C:\Users\mmilligan1\Documents\SQL Server Management Studio\Projects\MySQLServerScripts.ssmssln" -nosplash


4.     Refresh IntelliSense

IntelliSense saves time by helping you type table, database, and column names.  First, make sure it is enabled.  Tools, Options.  Text Editor, Transact-SQL, IntelliSense.



If you create a new table and IntelliSense just doesn"t seem to be working then press control+shift+R or click Edit, IntelliSense, Refresh Local Cache.




5.    Drag the Word "Columns" to the Query Window to Type all Column Names Quickly



6.     Implicit Transactions by Default

If you can never remember to start a transaction before your deletes you can go into options and turn IMPLICIT TRANSACTIONS ON by default.



7.     Isolation Level

Again with the options...  set the isolation level to READ_UNCOMMITED by default.  Now you don't need to type WITH (NOLOCK) in all of your ad-hoc queries!



8.     Object Explorer Details

Use Object Explorer Details to quickly delete many objects at once.  Also you can use it to view your tables as you can press the first letter of the table name and it jumps to that table.

9.     Template Explorer

Become familiar with the items in the template explorer.  Bring it up by pressing CTRL + ALT + T or clicking View, Template Explorer.  These act as a quick reference for getting the proper syntax for many common functions.


10.     Display Line Numbers

Click Tools, Options.  Expand Text Editor and select Transact-SQL.  Check the box next to Line numbers.


11.     Filter your Objects in Object Explorer

Right click the Tables folder (or any folder for that matter) and select Filter, Filter Settings.  Now you can simplify your view of the objects in the Object Explorer by showing only the objects you need to see.



12.     Automagically include column headers when copying and pasting results

Tools, Options, Query Results, SQL Server, Results to Grid.  Check the box to always include column headers when copying or saving results.  You may also like to try having it always display results in a separate tab and to switch to the results tab after the query executes.



13.     Do NOT Prevent Saving Changes that Require Table Re-Creation
I uncheck the "Prevent saving changes that require table re-creation" option.  Tools, Options.  Designers, Table and Database Designers.



That's all for now.  I"ll add more as I think of them or come across new tips.  Please leave a comment if you have a useful tip you"d like to share.