Favorite SSMS Options – and some gotchas

Whenever I setup a new machine, I like to set a few default options in SSMS. Here are my favorites:

Tools, Options

  • Environment
    • Fonts and Colors
      • Text Editor font: Lucida Console (a bit thicker and it's a fixed-width font)
      • Text Editor: Selected Text (under display items)
        • Item foreground: Black
        • Item background: Yellow (looks like a highlighter)
      • Sometimes I'll also make the results windows have larger fonts – especially if it's a presentation machine
    • Keyboard
      • Keyboard scheme: SQL Server 2000 (ok, maybe I'm old-school but the QA keyboard shortcuts still seem a lot more natural than the VS keyboard shortcuts… but, if you're more of a VS person, then stick with Standard)
      • Query shortcuts:
        • Ctrl+F1: sp_helpindex2 (if you highlight an object and then hit Ctrl+F1, then it passes the highlighted object in as a parameter. The only negative is that it doesn't delimit it so you can't highlight schema.object unless it's already quoted for the sp 'schema.object')
        • Ctrl+3: SELECT object_name(object_id) AS ObjName, * FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) ORDER BY 1, 4
        • Ctrl+4: other common queries/procedures…
  • Text Editor
    • All Languages
      • Line numbers
    • Editor Tab and Status Bar – I usually change the colors of the "Group connections" setting to something very noticeable like Fuschia. I've already blogged why here.
  • Query Execution
    • By default, open new queries in SQLCMD mode (however, if you set this – you will disable Intellisense and Transact-SQL Debugging… and, there's no "hint" or warning that you're doing it.)
  • Query Results
    • SQL Server, Results to Grid
      • Include the query in the result set (this will show you what you executed in the results window while you wait for completion… also confirms what you think you executed. This gets annoying for large batches but is sometimes useful to see what you last executed)
      • Include column headers when copying or saving the results (VERY useful if you're pushing data over to Excel, etc.)
      • Display results in a separate tab (this gives you Tabbed mode instead of split-window mode meaning that results will go to a results Tab instead of the lower half of your window. I prefer this when I have a lot of results to review AND when I'm presenting and typically run with a lower screen res.)
        • Switch to results tab after the query executes (I prefer this so that I'm waiting for the results in the results window)
    • SQL Server, Results to Text
      • I set most of the same settings as Results to Grid (Include column headers, Include the query, Display results in separate tab and Switch to the results tab) BUT, I usually turn off Scroll results as received. If I'm looking at rows at the top of the set (while it's still processing) it often takes my cursor down to the end. I also turn this off in Profiler.

The primary reason for this post though – is to make sure that you realize that one specific option – Tools, Options, Query Execution, "By default, open new queries in SQLCMD mode" – can really surprise you. I used to think that this option was really helpful (and benign) because SQLCMD is a superset of Transact-SQL. So, I typically recommended that you turn it on. However, turning on this option disables Intellisense and the Transact-SQL Debugger. It's documented here: http://msdn.microsoft.com/en-us/library/ms174187.aspx (thanks Paul Mestemaker! :) and I thought this was a bit unintuitive at first but it really does make sense. Both the Transact-SQL Debugger and Intellisense are debugging and/or prompting you with Transact-SQL (not SQLCMD commands). So, disabling it seems right (well, I guess I could argue that it could work with Intellisense but debugging would be much harder). However, I just wish there were some better information in the Tools/Options dialog. I'd like my 2 hours back (trying to figure out which option disabled Intellisense, chatting with my friends on fb to see if they knew, filing a connect bug when I figured out that it was SQLCMD, and then learning from Paul Mest that it's actually expected behavior (duh!), changing the Connect bug to say that the dialog might be better with a warning… it was definitely one of those mornings!  :) :) :)

So, my main point for this post – save you the time I lost in trying to figure out where Intellisense went. It's expected behavior (and it does make sense……..now :).

Cheers,
kt

33 thoughts on “Favorite SSMS Options – and some gotchas

  1. Hi Kimberly,

    I use pretty much the same defaults you mentioned on my laptop. In the keyboard shortcuts I usuaully also have a sp_helptext keyboard shortcut which is faster than finding the object in the UI.

    You wrote that you turn off auto-scrolling in Profiler. Is there somewhere you can set auto scrolling off as a default in Profiler?

    Cheers,
    S. Neumann

  2. Hey there Saggi – In Profiler, you just use the toolbar button. No way to set this off by default. However, I don’t use Profiler directly as often as I use server-side trace queues (to run traces without directly running them through Profiler). But, if I am using Profiler, that’s the first thing I click when I get in. ;-)

    And, sp_helptext is definitely another good one for a shortcut key!

    Cheers,
    kt

  3. Great ideas! I’m going to add them to my list!

    Here are three I always set up:
    (1) a personal favorite of mine is assigning "SELECT TOP 10 * FROM " to one of the shortcuts so I can quickly view the first few records of a selected FQ table_name (FQNs work since it’s not acting as an SP parameter).
    (2) I have a utility proc ("sp_findtext") that I drop into the master db on every server I manage so that I can quickly scan the current db’s SPs and the server’s jobs for any mention of a selected object name.
    (3) I also a master-db utility proc for "current activity" on the server that gives me a simplified view of active or blocked SPIDs & their various metrics (CPU & I/O work, duration times, and executing SQL batch/statement).

  4. Dude – because that’s the way it works. Go read the article on Understanding Logging and Recovery as well – if you still don’t understand it, you’ll need to get someone to explain it to you with a whiteboard – this isn’t the right medium to explain if you’re not picking it up from the articles.

  5. I used to think that this option was really helpful (and benign) because SQLCMD is a superset of Transact-SQL. So, I typically recommended that you turn it on.

  6. An article that caught my eye.Would you be able to provide more information?It comes in an informative format and a view to be expressed.
    Hopefully the pro filer would be able to be understood.Thanks for the information.

  7. The thing improving the skills by questions is very very good thing and make it easy through the suggestions from the others. to improve the skills is one of the best idea and can solve the desires through the special thoughts like this. And can sure do the work without fail and will give the success results in this way. Hopefully the pro filer would be able to be understood.

    Thanks and Regards
    <a href="http://www.vponsale.com/ invitations/">wedding invitations</a>

  8. The thing improving the skills by questions is very very good thing and make it easy through the suggestions from the others. to improve the skills is one of the best idea and can solve the desires through the special thoughts like this. And can sure do the work without fail and will give the success results in this way. Hopefully the pro filer would be able to be understood.

  9. The thing improving the skills by questions is very very good thing and make it easy through the suggestions from the others. to improve the skills is one of the best idea and can solve the desires through the special thoughts like this. And can sure do the work without fail and will give the success results in this way. Hopefully the pro filer would be able to be understood.

  10. Kimberly,

    I know you can press Ctrl+L to Display Estimated Execution Plan – do you know the actual SQL command(s) which are run so I could try to automate running this against all of my SQL Stored procedures with the results going to text?

    TIA,
    Doug

  11. The concepts behind the design make a ton of sense. They created “platforms,” each with a function and each with room to grow. And that’s what surprised me the most; they started talking about “fillfactor.” Ok, they didn’t use that term but there it was.

  12. Great post, I like the foundation of this blog has a great variety of comments I really like it, several points of view helps in the appreciation of the subject,is very interesting and I would like learn more.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

SQLskills SQL101: Partitioning

Continuing on our path to understanding the basics and core concepts, there’s a big topic that’s often greatly misunderstood and that’s partitioning. I know I’m

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.