ORDINA BLOGT

Know Your Tool: SQL Server Management Studio

How effective someone is at performing a certain task depends on a lot of factors. One of those factors is the experience level one has in operating the tools required for the job. As the saying goes: mastering the tool is half the work!

  • Valentino Vranken
  • 8 september 2014

For us, SQL Server professionals, the SQL Server Management Studio (aka SSMS) is a tool which we use daily. 
The better we know how to use this tool, the better we are at doing our daily jobs. This article contains some SSMS usage tips which I've gotten to know over the years.  Enjoy!

Selecting Top X or All Records
One of the frequently –used window panes in SSMS is the Object Explorer (open with F8).  When right-clicking a table you have a couple of options such as “Select Top 1000 Rows” and “Edit Top 200 Rows”. Did you know you can influence this number through the Options (Tools menu)?  They are located under SQL Server Object Explorer > Commands:

Setting the option to zero will change the “Top x” into “All”.

I usually change it to Select All Rows but leave the Edit functionality set to Top 200 to avoid crashing my SSMS when I accidentally click it on a really large table.  For Select All Rows this is not such an issue because you can easily click the red-squared Cancel button in the toolbar if the query takes too long.

Coloring your Connections:

When you need to connect to a lot of servers on different environments it can become a problem to manage.  Usually after working for a couple of hours you’ve got several query windows open on several servers and the only way of knowing what instance the window is connected to is by looking at the bottom-right and reading what is written there.  This also assumes that you know and recognize all instance names, which I can assure you is not always the case when you’re working for several customers.

Luckily the Management Studio has got some functionality implemented which can help you a lot!

To start with there’s the Registered Servers window.  This window allows you to organize your instances into folders, assign custom names to your servers and you can just double-click an item to open it up in the Object Explorer.  But, here comes my favorite part, it also allows you to assign a color to a registered instance by specifying the Use custom color setting in the Connection Properties:

 Once this is set you can easily distinguish the query windows because this color is now used as background for the status bar at the bottom of the window:

 

 I usually color the production servers red, UAT orange and DEV or local green, this way I know when to be careful with those TRUNCATE TABLE statements!

Filtering the Tablelist:

The Object Explorer has got a function which I haven’t used much, until I had to work on a DWH with hundreds of tables.  Opening up the full table list takes annoyingly long and locating the table you’re after isn’t for the inpatient ones either.  To avoid that issue you can use the filter functionality by right-clicking the Tables node and selecting Filter > Filter Settings:


Some Quick Query Editor Tips:

The Line Trick

We all know CTRL-C is Copy, CTRL-X is Cut and CTRL-V is Paste, right?  But something you may not know is what happens when you hit CTRL-C or CTRL-X in the Query editor when nothing is selected.  In that case you’re copying or cutting the whole line!  This can be really useful if you need to remove some lines or swap them around…

Select A Block

Do you know what block edit means?  In short it allows you to edit several lines at once.  To do that you first need to block-select some lines:

The light-blue vertical line in the screenshot is a block-select.  So you don’t really need to actually select some text, you can also select “the cursor space in between two characters”.  If you now type something it will appear on all selected lines.  Here’s the result of me typing “ P.”, the table alias for the query:

Fast List Of All Columns

 

Need a list of all columns in a table?  Of course you can always open up a SELECT statement through the functionality mentioned earlier in this article but there’s an even faster way.
Perhaps you already know that you can drag & drop tables and column from Object Explorer into the Query Editor?  To get a full list of all columns, drag/drop the Columns node into the editor:

That’s it for today, I hope you enjoyed reading this?  
Don’t hesitate to post a comment if you’ve got any additional SSMS tips to share, I’m always eager to learn more of them!