Archives

Toad for SQL Server

I wish I knew how to quit you… Well, not really.

In previous articles, I’ve mentioned that I use Toad for SQL Server, but until now I have not really advocated that my readers use it. While I’ve been using Toad for several years, I’ve had a love/hate relationship with it. When I first started using it, the product was buggy and problematic. Also, while Toad does everything, it is often unintuitive. To compound that problem, not many people blog about it so information isn’t readily available as to how to leverage Toad effectively. Even after all this time I still come across some new feature in the product on a weekly basis. If my readership, all 5 of you, are interested in learning more about it, I’ll create a regular blog segment for Toad tips and tricks.

Unsurprisingly, I took an active role in the improvement of the product. Quest Management sensed my enthusiasm for giving feedback (loudly demanding to be heard), so they assigned me a dedicated tech support rep. He has dutifully relayed my suggestions (rants and complaints) directly to the development team and it’s nice to be heard. Amazingly, they’ve implemented most of my suggestions over the years and it’s a much better product because of it.

What’s the Point?

Toad is in a new beta cycle for version 5.5. This means that anyone can download and use the full product until February 13th. As I am embarking on this series of basic T-SQL articles, I am encouraging you download Toad and use it with me. All of my screenshots in these articles will be using the new Toad beta.

Keep in mind that this is a beta product, and therefore will have bugs. For example, someone has disabled the ability to save editor windows individually, presumably by accident. As of now, you must save all files at once. Even so, I’ve already switched to using it in production because so many of my latest rounds of my suggestions were implemented that this tool now feels like it was designed specifically for me.

Why do I need a 3rd party program to write T-SQL?

In my opinion, all beginning SQL writers should use aids in writing T-SQL, especially if you are using SQL 2000’s Query Analyzer. Query Analyzer is basically like programming in Notepad. There is no Intellisense or code assist to speak of, the error messages are cryptic and difficult to understand, and there isn’t any structure or style enforced.

The two most common 3rd party choices are Red Gate’s SQL Prompt and Quest’s TOAD for SQL Server. Both offer code completion which increases the rate at which you code, and SQL Formatting which standardizes your code structure. However, they are paid software. SQL Prompt works directly in SSMS, while TOAD is a stand alone product. They both have features to recommend them.

There are also some free SQL Formatting options you can use such as:

The web sites will format your T-SQL code, but it’s a pain to open them, copy your T-SQL from SSMS, paste it into the website, format it, and then copy and paste it out again. My time is worth money to me, so I use the paid version of TOAD for SQL Server. The SSMS Tools Pack is great, but it doesn’t offer the code formatting (vertical and horizontal spacing) that Toad and SQL Prompt do.

How to set up Toad

Simply download and install the product with the defaults. As Toad fires up the first time, you’ll encounter the following Configuration Wizard. For now, choose MS Management Studio.

After that you may get a Connections Wizard screen. Cancel that and I’ll show you how to set up a connection in the regular interface.

Create a Connection

One of the great aspects of Toad is that you can create several connections to servers and databases, save the login information if you choose, and switch back and forth between them seamlessly. Toad makes extensive use of the right click menu and I rely heavily on it.

Right click in the upper left hand corner of the screen in the Connection Manager and select Create.

The Create New Connection screen appears. Just like in SSMS, the server name can be typed in or browsed by selecting the ellipses. Select your authentication method, password if required, and default database for that connection. You aren’t limited to that database for query writing of course.

The interesting settings are below the Name line. Toad allows you to name your data connections. At a glance I can tell which server I am accessing and in what capacity. I use names such as:

  • M2M Production – Read Only
  • M2M Production – Administrator
  • M2M Development
  • M-Data Analytics Production

Categories allow you to color code editor windows. Connections with production logins that have write access I use red, development is green, etc.

Finally, you can elect to have connections start up automatically when you start Toad and to save passwords when you’re using a SQL account login. I never connect to my production server automatically with write access. I never save passwords for accounts that have write access either.

Create a New Editor Tab

Depending on the default settings you may see a blank editor or you may see something like this:

That blank tab is new in this beta, and although I like the concept I don’t care for the way they implemented it. Anyway, right click on the tab and select Open New Editor.

The editor is where you write your T-SQL Code. In future articles I’ll describe how to write T-SQL the Toad way to increase your speed and effectiveness.

As I mentioned before, one of the reasons why I’m suggesting you use Toad is for the automatic formatting. Kevin Kline (Blog/Twitter) has put out a few excellent videos on Toad. You should watch Basic Formatting and Advanced Formatting as soon as you start using the product. I just wish Quest would produce more of them (Hint Hint Hint).

Full Disclosure: I am not sponsored, nor paid by Quest in any way. I just simply like Toad for SQL Server.

So, who’s with me? Who’s going to give Toad a try? Are you interested in learning more about Toad?

14 comments to Toad for SQL Server

  • […] This post was mentioned on Twitter by Stray__Cat and Adam Saxton, David Stein. David Stein said: New Blog Post on #Toad for #sql Server and why I'm using it in my T-SQL Basics series. http://bit.ly/eUDJiT […]

  • Andrew

    Your server name is Jedi Temple?

  • Thanks for taking the time to share some T-SQL helps. I will definitely give Toad a try. One thing I am wondering, I still miss the ability in FoxPro to just open a table, search for the record you needed, and edit the data. Is there an easy way to do this in SQL-2008? By easy, I mean not writing an update script. It’s not that it is difficult to write an update script but you better be real sure of your criteria before you run it.

    As an aside, is anyone working on a smarter parser for passing SQL queries? I have been quoting quotes and and learning new ways to do date criteria for more years than I care to say. With all the marvels of software and technology we are still typing “Name = ” & ‘”‘ & “Mary” & ‘””. And then theres the all the junk with dates, a little different for each language. How about “Name = Mary” and a parser that knows about the field definitions in the connected DB and can figure it out? And it should also know about date fields and date parts and how to handle them. So if the language can’t do it, can an editor like toad do it? After all, we have field type definitions sitting in our DB.

  • Shawn Johnson

    Hi David, thanks for the intro article on Toad for SQL Server. I’ve recently started using it myself and haven’t found many blogs about it either. I’m looking forward to reading more about it!

  • Mario V

    “JEDITEMPLE” love it…I noticed you connected to the “M-Data Analytics” any time frame on this?

  • Peter S

    Rick,
    You can right-click a table in SSMS, there’s an option to “Edit Top 200 Rows”. This brings up just the top 200 rows (no definite order) for editing. If you then look for the very tiny button labeled “SQL” in the toolbar that comes up, you can edit the SQL that is run and bring up an editing window. It involves extra steps, but is doable.

  • Peter, thanks for fielding that question. I knew there was a way to edit in grid, but I don’t actually use it.

    All of my machines are Star Wars themed. My personal laptop is XWing, my Windows Home Server is Tatooine, and I’m trying to decide what to call my new iPad. 🙂

    As for M-Data Analytics, I’m still actively working on it, but don’t yet have an ETA.

  • Peter S

    What was the name of that little toaster-droid in the original Star Wars movie again?

    (I should know better than to hit Wookiepedia – seriously.)

    Here you go:
    http://starwars.wikia.com/wiki/MSE-6_mouse_droid

  • Ha, cute. I doubt anyone would get the reference though.

  • Mark Shay

    Love the Star Wars theme naming convention. Personally, I would have went with Death Star.

  • […] me, and since these methods work for me, I think they’ll work for you too. Adoption of a 3rd party query tool helps to keep your code […]

  • Great post, David. Very balanced and some good points for us (Quest, that is) to follow up on.

    Also, I’m taking your hint to heart and will work on more – hopefully, LOTS more – tutorial videos. =^)

    Thanks,

    -Kev

  • Christian Hasker

    Hi David,

    Thanks for giving Toad for SQL Server some air time on your blog. We really appreciate it.

    Best regards,

    Christian Hasker
    Quest Software

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>