A US-based fast food chain apparently has a new advertising campaign running. I’m not exactly sure what it is supposed to be for, and most of their commercials are pretty confusing. They do have one that I like. Apparently their food is not only JUST fast, but also made to order, and tastes good. What does this have to do with Toad®? Well, Toad allows you to compose fast AND good SQL queries.
Most of the database development IDE tools out there have a feature called ‘SQL Recall’ that they borrowed from Toad, but Toad has a certain way of going the extra dozen miles or so. Here are a few tips ‘n’ tricks for writing SQL statements at ludicrous speed.
Named SQL Recall
Everyone should already be aware of Toad’s SQL Recall feature. If this is new to you, open an editor and hit the ‘F8’ key. Now browse, search, or sort the last 100 (or 999) valid SQL statements that you’ve sent to the database. Ok, this is a nice feature, but it’s not what wakes me up in the morning. If I wrote something more than 10 minutes ago, I’ve probably forgotten the purpose of the statement in addition to the statement itself. That’s why I give every statement that I run more than once a name.
How do you name a statement? A couple of ways, but the easiest are either to:
1. Select the query in the editor, then Editor > Add to Named SQL. Give it a name. OR
2. In the SQL Recall panel, select the query you want to name. Mouse-Right-Click -> Change to -> Named.
That’s great, but what good does that do, you may be asking yourself. Let’s say that you wrote this AWESOME report for your boss a few minutes ago. You read this blog and then named the query you used to build the report. Now two months from now your boss asks for this report again. You slap yourself before remembering, ‘Hey, that weird Toad guy had me name my SQL.’ Now you can simply hit <CNTRL+N> in your editor and voila:
You now have a list of Named SQL you can choose from to place in the editor.
SQL Recall – Scrolling Statement History
While I enjoy having the ability to see all of my previous SQL statements in the ‘F8’ docked panel, I also enjoy having as much space as possible allocated to my crucial Toad windows. So, I always keep it unpinned or even not open at all. If I know that I’ve written a SQL statement during my current session or in the last few days, then I can normally find my statement fairly quickly using the <ALT>+<ArrowDn> keystroke.
While in the editor, I can at any time hit <ALT> and either the up or down arrow key to cycle through my SQL recall list. When I find the one I want, I just release the <ALT> key. An even neater feature that one of the Toad guys added for version 9.0 I believe was the ability to do this on selected text. So let’s say I’ve written a nice little SQL query, and I want to add a SUBQUERY for the WHERE clause. I just know that I wrote a query the other day that’s pretty close to what I need already for the subquery.
Now I can replace the selected text from a query that I have stored in my recall list. Using the Down arrow key will start with your youngest SQL statement and go down from the ‘top’, and the Up arrow key will start with your oldest SQL statement and go up from the ‘bottom.’
Make/Strip Code
One of the most powerful features of Toad is also one of its oldest ones. Make/Strip code has been around since AT LEAST the year 2000. However, whenever I go to visit customers I am astounded by how many people have never used or even seen this feature.
If you have a version 6.5 of Toad or higher, then you should keep reading!
Make/Strip code in Toad allows me to take a SQL statement and make it into a ‘Non-SQL’ statement. What does this mean? We’ll let’s assume that I am a very BAD Delphi developer (and even that’s really pushing it). I need to send a SQL query to the database from my Delphi application. I developed the nasty little SQL statement in Toad, but now I need to put it into Delphi. If I’m one of the few who know about Make Code in Toad, then it’s a simple matter of 1 button push to get the code wrapped in the Delphi syntax that I can paste write into Delphi. Otherwise, I get to start adding a bunch of variable value assignments with appends and tons of quoted statements. This is pretty error prone for even a great typist. Let’s see how Toad can help with this:
Step 1: Write a NASTY SQL Statement
Step 2: Configure Toad to use the desired Programming Language and Variable name for your statement.
Step3: Hit the Make Code button in your Editor Toolbar. You’ll now see this at the bottom of your editor panel:
Step 4: Paste your Query statement into Delphi!
Strip Code works the same way. It allows you to quickly change your existing SQL code statement from your custom application, ‘strip’ the non-SQL syntax from the statement, and run it in Toad. Then when you’re finished making your changes, you can just use the ‘Make Code’ button to send your statement back to your IDE.
Homework
If you liked the tips from this blog, then here are two homework assignments that will lead to an even better appreciation of Toad:
1. Auto Replace: Open the Editor Options from Toad and check out the Auto Replace feature.
2. Code Templates: Similar to Auto Replace, but WAAAAAY more powerful.
Feel free to post a comment if you need help.