Coding is easy with Toad for Sql Server

Toad is never ending story, once started this journey you will never stop learning – there are so many things happening with each release. There is simply no room in documentation for every single item…

So I would like to take a closer look at Toad in regards of code development from my experience. Though I have to start with connections

More colors mean good for you!

I’m not a genius and for many years in past I was questioning myself right before I click execute the script if I’m on the connection I need to be at. It was a pain due to lack of my attention, and you know – there are so many windows you can switch between….  at the end I have been executing my script on wrong connection 

These times passed with connection colors in Toad, for every connection I can assign category which includes name and COLOR.

Starting that moment I can see the color on object explorer, on tabs and even on sql editor’s background.

Right now I more confident about where I’m executing the scripts =)

How do I code in Toad?

Each time I'm starting the endeavor by new sql editor. When I was a green, at some point we started looking for ways of standardization of our code, basically saying it was a template you need to use in your code. Code Snippets helps here a lot, but what I see now is that you want to have initial template for every single editor you are opening. Toad can do that for you, just create TOADSQLFILE.sql in the root folder of your code snippets (look here: C:Users<user>AppDataRoamingQuest Software<product>TemplatesSnippets)and that's it, rules of snippets apply for that file as well so you can create quite complex template.

What I like in toad is to write your own t-sql. There is Code Completion ready to help you as well as Code Snippets (quite good if you want your team to share the knowledge thru the script library) and helpful Sql Recall. But quite often I catch myself that I simply drag&drop objects into sql editor and Toad offers me options:

 Tip: if you want to get results even faster – there is a shortcut to avoid menu – simply press CTRL button.        

If you like menu more than drag&drop – toad holds script generation in right click on object > Generate SQL > bunch of different options including "append to active editor" which helps to construct a batch by sending different statements. Also keep in mind when you right-click an object and select Generate SQL you are offered to select a type of script to be generated (Select or/and Create, Execute, etc.) first followed by To Editor / Append / To Clipboard / To File options. The option you selected last time is remembered (and is marked with bold), so next time you can simply click straight on the type of script  without having to select an option from the submenu.

If you prefer to type – then your friend is Code Completion suggesting you keys for join or expanding * into column names – it's quite obvious, also there is Auto-replace feature, simply load your favorite abbreviations like ssf, upd, decv (can't find anything else… what else?)

When I tune my query I see a lot of value in having toad generating resultset for each execution/output. You can pin it or rename if you like… I do not want to spend a time on this, what I like is visually check the differences of each execution, and if my eyes are not enough  I can send resultsets to diff viewer in a single click and quickly locate the difference!

The only thing toad will ask you is to identify the key for comparison, since we have a query – there is no information which column is a key column. So we need to do it manually with using nice dialog:

and here are results:

 Note: I can also drop the whole table from object explorer to diff viewer, and diff viewer will read table structure including keys with no problem. 

I'm almost there for a cup of coffee, just need to save my code in database as a stored procedure, and I will not spend any time on rewriting of my code, Toad will quickly cook up the procedure from the script, with reading all declares as well as set operations. I can also decide to not take declare as a parameter and return it back to the script:

Finally I will format my scripts to meet easy to read policy in my team. Toad formatter is very customizable and serves very well. If we were on top of the roof, I had to check-in my work into Version Control, hopefully Toad provides facility for that, but I'm lucky we are not using this