SQL 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.
![][2]
> 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!
[2]: /wp-content/uploads/users/grrlgeek/SSMSTips1.JPG?mtime=1316658886 “”
Jes Borland is a Premier Field Engineer - SQL Server for Microsoft, where she is a trusted technical adviser, deep technical support, and teacher for her customers. Her experience as a DBA, consultant, and 5-time Data Platform MVP allow her to help the business and the IT teams reach their goals. She has worked with SQL Server as a developer, report writer, DBA, and consultant. Her favorite topics are administration, automation, and performance. She frequently presents at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef.
