SSMS – Tweaks

SQL Server Management Studio – Outstanding UI provided by Microsoft for managing/Administering/Developing/manipulating  code in (for) SQL Server environments.

As a DBA how much time would you spend with SSMS on a daily basis? I would say 90-95%! Well, we will be using Configuration manager if needed sometimes to troubleshoot few things (Will be covered in another post).

As a SQL Server Developer how much time would you spend in SSMS on a daily basis? I would say close to 100%.

Note: I’ve also seen few Application teams still using TOAD to develop T-SQL. I’m not covering TOAD coz I never used TOAD (why would/Should I??? if I’m already using fabulous SSMSJ)

PS BI (Business Intelligence) developers spend more time in BIDS (Business intelligence development Studio).

A Quick Question– How many of you explored all the features (at least few of the available features) in SSMS?

Will the default settings of SSMS Installation suffice our daily requirements? Of course “Yes” in most of the cases. But I’m pretty sure, after reading this blog completely you’ll definitely try exploring those options (if you are not yet at that point).

Okay! Now it’s time to explore…..

I’m assuming, you’ve got SSMS 2008 already installed on your machine(s) and you’ve a brief knowledge on why and how SSMS is used. I’ll explain few scenarios where most of us will find interesting.

Scenario1: Selecting records using GUI.

Most of the times we’ll be using these two options just to get a sample result set. So do we really need 1000 rows? How about changing it to 100 or even 10 Records? Cool huh!

How to edit that?

Go to Tools – Options – SQL Server Object Explorer – Commands. Change the default values at the right pane to the desired values. That’s all we’ve to do!

In this example, I’ve changed select value from 1000 to 10.

Scenario 2:Have you ever felt including IF NOT EXISTS logic automatically whenever you script any Object out of your database. SSMS by default don’t do that, but we can change that setting as shown below.

Now if we script an object in SSMS, it looks something similar to below code snippet.

Play and get familiarized with rest of the scripting options which you are seeing in above screenshot.

When editing your tables using SSMS.

Let’s say you are adding a new column to your table in the middle. (Useless in most of the cases, since all it matters to SQL is the order how you pull your columns in your select statement.By default new column will be added at the end of table.) We’ve to do that by right clicking on table and selecting DESIGN. But SSMS won’t let us to do and it’ll throw an error because this change requires the table to be dropped in general. So what to do?

Just un-check Prevent Changes that require table re-creation and that’s it! Go to books online for finding out all the changes that require table re-creation in SQL Server.

Caution:For huge tables, it may timeout and I’ve seen few cases where SSMS crashes. I recommend you to Script out changes (generate Script button on your top left while you are doing any change) and review code to look what SSMS is really doing for you behind the scenes instead of just clicking NEXT-NEXT-OK in your SSMS for making any table changes. Believe me, you’ll never regret doing so.

Scenario 4: Shortcut Keys in ur SSMS.

Most of we DBA’s will be regularly using a set of commands on a very regular basis. For example sp_who2, “wait_stats DMV”, “dm_exec_requests” DMV and so on…So how about saving those regularly used statements as keyboard shortcuts instead of typing entire query manually repeatedly…

You can define your Queries as keyboard Shortcuts in SSMS as shown below.

PS sp_who will be default for ctrl+1.

Scenario 5: Annoying tab width in Query Pane.

How may query panes can you see in your SSMS at once?? 4,5 or 6….at most I can say 7 if you get rid of Object explorer and registered servers etc. The rest of query panes will be appearing in a drop down menu at top right which is really annoying if you are dealing with many things at once.(which most of us do as part of our job)

See below for what I’m saying…

Correction – that’s just SPID not SPID Number in above screenshot – forgive me,I’m not going to edit that screenshot now.

So, how to decrease its width by removing all the unnecessary details provided by SSMS by default?

Once I clicked OK, now you can see my SSMS accommodating more number of TABS.

These are few options which you can tweak in your SSMS today. Please see there are many more Cool features and options which are hiding in your SSMS without your notice. It’s time to explore all those features which MSFT has added for all of us, making our lives easyJ