XML FeedsSQL Server Management Studio is the de facto tool for working with SQL Server. But its default options may not always be the best way for you to work. After working with it for years, I’ve come up with a list of my favorite features.
(Secret: This is also my way of recording this so that the next time I need to set up a new PC, I just have to come back here.)
Part I: The Options Menu
In SSMS, go to Tools > Options.
> Environment
>> Keyboard: Here you can set up “Query shortcuts”. By default, sp_help, sp_who and sp_lock are there. I’ve added things like sp_whoisactive. Are there other queries you use regularly? Set up shortcuts here, to save yourself the trouble of having to open a script file, or (gasp) typing them each time.
> Text Editor
>> All Languages
>>> General
>>>> Word wrap: I’m not sure where I would be without this setting. Who wants to scroll back and forth constantly?
>>>> Line numbers: it’s just what it means. Go from this:
to this:
>>> Transact-SQL
>>>> IntelliSense: turn on IntelliSense to have SSMS help you complete field names, function names, and more. Users of Visual Studio will be used to this functionality. Note: this will only work in SSMS 2008 and above, against SQL Server 2008 and greater databases.
>>> Editor Tab and Status Bar
This can be broken into two sections.
>>>> Status Bar Content and Status Bar Layout and Colors: control how that bar that is at the bottom of query execution window (by default) appears.
>>>> Tab Text: controls what information appears in the tab at the top of a query.
> Query Results
>> SQL Server: Here, I can choose the default location for saving my results, if I don’t want to save it in the location Microsoft specifies.
>>> Results to Grid: Here, I like to choose a few non-standard options.
“Include column headers when copying or saving the results” means just that. When I right-click a result-set, copy it, then paste it, the column headers will go with it.
“Display results in a separate tab” – when I execute my query, instead of the results being in the lower half, they are on a second tab. I can even choose to switch to that automatically. This is especially useful for presentations.
Instead of this:
I see this:
> SQL Server Object Explorer
>> Commands: In Object Explorer, you can right-click a table or view and choose to select or edit the top X rows. Here, you can set the values for these options.
>> Scripting: there is a wealth of options here that will turn things on or off when you right-click an object and choose to Script To…
Some of my favorites: Include descriptive headers, Script USE database, Include IF NOT EXISTS clause, and Schema qualify object names. Take the time to look through this list and understand what you can turn on and off in SSMS.
> Designers
>> Table and database designers: I uncheck the “Prevent saving changes that require table re-creation” option.
Part II: Object Explorer
There are a couple cool things you can do with Object Explorer that I find people are unaware of.
First, when you are writing a query, especially against an older database that doesn’t have IntelliSense support, you can drag table, view, column and stored procedure names to the query pane instead of typing them.
Dragging the “Columns” across to the query pane will produce this:
Second, you can filter the items you see in a database. The little blue funnel at the top of Object Explorer will help you do this.
Here, I’m going to filter on the “Person” schema.
My results are much easier to read. This isn’t a huge deal in AdventureWorks, but when you get into databases with hundreds or thousands of objects, this is very handy.
And third, when you run a script and have an error, most times you can double-click it to be taken to the line that generated the error. (That one? Hard to demonstrate with a screenshot. Next plan: video blogging!) But, give it a try.
Part III: Must-Have Third-Party Add-Ins
SSMS Tools Pack - http://www.ssmstoolspack.com/
This free add-in extends the functionality of SSMS. A lot. Some of its features that I love:
Window Connection Coloring – want to have a green bar at the top of queries against development servers, yellow on QA, and red on production? Just set it up.
New Query Template – want to have the same text appear in every new query you open up? Me too. Create a template.
Want to have your query history saved to a text file or a database table? It’s all here.
And that’s just scratching the surface. Download this tool!
Extended Events Manager - http://extendedeventmanager.codeplex.com/
Are you using Extended Events yet? You should be. Sure, the down side is that there is no GUI for it (yet). However, this tool starts to bridge the gap. You can view event sessions, start and stop them, drop them, and script out operations.
Making the Most of Your Tools
The defaults provided by Microsoft are not always the most efficient, or the most helpful to you. Explore the settings in SSMS and make it work for you!
Jes Schultz Borland is a Consultant with Brent Ozar PLF, and a Microsoft SQL Server MVP. She holds an AAS - Programmer/Analyst degree, and has worked with SQL Server since 2007, focusing on Reporting Services and day-to-day administration. She is an active member of PASS, Director of Communication for MADPASS, founder of Tech on Tap, and a LessThanDot.com blogger. She's a frequent presenter at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef. 
Nice tips...BTW SSMS also has the coloring tab thing (it doesn't always honor it but it has been fixed for Denali)
Nice post.
How about server organisation using Registered Servers option? This comes very handy with multiple servers.
Thanks for sharing, I think I'll adjust a few of options I haven't paid attention before 
One that you don't have that I love is the keyboard shortcut to update Intellisense CNT-SHIFT-R. Takes a moment and reloads the Intellisense cache. 
Great post! I only wish I'd have read this months ago... I also have the same problem as Naomi Nosonovsky. I often have to query a table with a column in it called 'country_id'. Every time I want to do a COUNT(*), Intellisense 'corrects' it to country_id :-) 
Alas! It seeme that Intellisense is not available in 2005? Definitely not in the text editor node (T-SQL is not even listed). 
I'll ditto Denis's last comment. I found Red Gate SQL Prompt to be a very useful feature with SQL Server 2005. Unfortunately, I lost it when I last had to switch machines and company wasn't willing to pony up to renew it. *sigh* 
Naomi/Andy - I don't know of a way to alter Intellisense settings. I run into the same problem, though. However, I'll take what I can get from it. 
Great tips and post. I never noticed the one about the keyboard shortcuts to my common queries! Thanks for showing me this one. 
Also something not a lot of people know is; in the Object Explorer Details pane (press F7 to bring it up if it isn't already) - you can shift + click multiple objects and delete it or script it where possible(stored procs, views, tables, etc). 
Don't forget about Plan Explorer and its SSMS add-in...
Great article.. if you haven't tried it .. I personally move the status bar from [Bottom] to [Top] .. especially usefull when using color coding for connections.. as it get's right in the eyeline and I find that a better position (well for me anyway) 
Nice post. :)
Nice post. There are lots of tricks available in SSMS to make your work easier. Thanks for putting all this together, Jes. 
Hello Naomi,
Great post, Jes! 
Awesome post it's instantly one my favorites, one i'll forward on to people. Keep up them coming !!!!! 
Very helpful and informative post! Great work! 
I would like to add a recommendation for SQL Prompt if you do a lot of ad-hoc TSQL. It's worth the money, though better if your company pays for it.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.