Toad for SQL Server snags best of TechEd 2009

I just learned that Toad for SQL Server has snagged the coveted Best of Tech Ed Award in the Database Development category and I just thought I would share that.

I have unfortunately not been able to post as much as I would have wanted on this blog lately because I'm knee deep in the development of the 4.5 release. As this release is out I should hopefully be able to contribute more here.

Code completion revealed

From the first version of Toad we have always had some form of code completion (It was originally called Toad insight or something like that) but it has always been a fairly simple affair where you either got a list of columns in select statements or a long list of objects of many different types completely unaffected by the context in which you invoke the code completion.

Starting in 4.0 the code completion support was completely rewritten to be very context aware. Now Toad should know that if you are writing a select statement and are between SELECT and FROM you probably want a list of columns and if you write JOIN you probably want to join on a foreign key from tables already specified etc. There has been little bit of a backlash on this and I have to admit that this code did have some bugs and when that happened you usually didn't end up with any code completion at all which is worse than what we had in 3.x. Hopefully for the soon to be released 4.1 release most of these should have been fixed and you will really see the benefits of the new framework.

Handling code completion in this way is kind of complicated. The main problem is you can't do strict parsing because we are not dealing with complete statements. Instead we have to do our best at guessing what the you are aiming at writing.

So let me go through the changes in more detail what new features are introduced with this framework.

Smarter auto invocation

The most obvious change that people will immediately see is that as you start typing a select statement the moment you write "FROM" and then space you will be presented with a list of tables and views. Before 4.0 the only time we automatically invoked code completion was when you typed a dot. In 4.0 we have certain "hot tokens" that invoke code completion after you write a space as well. The full list of tokens isn't that important, but if you ever think we are missing something just let us know because it is very easy for us to change it.

Context sensitive object lists

We now look at the statement type to decide what kind of object list you probably are interested. For instance if you write "CALL" or "EXEC" you are probably interested in a list of procedures, when you are writing "UPDATE" you probably want a list of tables while when you are in the "FROM" clause of a select statement you probably want both tables, views and in case it's supported functions returning tables.

Context aware sub-select completion

Toad should now keep track of sub selects so that it knows which objects you can address when you are writing complex sub-selects as shown in the screenshot below.

Auto generated joins

When you are writing select statements we now automatically generate the full join statement based on foreign keys in the database. We can generate both the part following "JOIN", the part following "ON" or in case of a cross join the part in the "WHERE" clause.

Snippet code completion

In some cases the code completion generated still need you to fill in some extra data to be a valid statement. We now do this by inserting an auto generated code snippet. Specifically we use this when you are using code completion on code to let you fill in the parameters of the call we generate.

Complete statement generation

We can now generate the full statement for some statements after you have specified the database object to operate on. Specifically this includes update, insert and delete statements.

Good handling of owners

This only applies to SQL Server, but in 4.0 we really didn't have that good support for handling owners in code completion. As of 4.1 that we now handle owners in a really clever way. Basically it should now "just work". If SQL Server accepts the syntax we should handle it correctly.

Support for local variables in batches

On SQL Server we now keep track of local variables defined in a batch and includes these in the places where they make sense.

Options available for code completion

The option page for code completion has changed quite a bit between 3.x and 4.0.

First some things that haven't changed. The auto list members, quoting, casing, expand "*" and include system objects work exactly as before so I will not go into these (And they should be pretty self explanatory).

Item sort order

This setting now has one more item which is the default and that is the "Each category". This means that the code completion list is sorted per type of items. For instance in a select statement we start with the "*" and then columns followed by variables, snippets and a bunch of other stuff. Chances are that you are looking for the columns and in this case they will be at the top instead of being lost in the list of hundreds of function snippets also included.

Merge secondary items

When you invoke code completion in some cases we have items that are common to choose from and some items that are not so common. Normally the not so common items are not included in the code completion, but if you want to get to them you can still do so by choosing the last item in the list which says (More).

If this option is checked this means that we should generate the code completion list with all items right away instead of separating this into two different lists. This is the default.

Known issues

The one known issue in regards to code completion is that we currently do not honor the setting to separate statements with double newlines (On by default I think). For version control to function property with multiple statements in the editor you need to end you statements with ';' (Except for SQL Server which doesn't have this issue).

The history of Toad for SQL Server

I recently took some time to look through the different releases we have made of Toad over the years since we started and just thought I would share it with you guys. I chose the Toad for SQL Server release because that was the one which had made the most releases (For instance Toad for DB2 never made the 3.1 release). I have to say that looking at from where we have come Toad is becoming a lot more polished. Especially starting with 4.0 it seem that we have started putting more time into polishing stuff up than just trying to cram as much features as possible. It is also pretty remarkable how little Toad has changed from our first release. Even if you went straight from 1.0 to 4.0 you would probably feel pretty at home with the application even though there is a lot more functionality in it.

This only goes through some really superficial first impressions. There is tons of functionality that has been added all over the application which is not covered at all in this retrospect. Also this only covers the .Net based version of Toad (Everything except the original Toad for Oracle). Finally the Toad for Data Analysis obviously has a very different look even though it is based on the same code and that isn't covered covered either and it isn't that interesting either since it did not join "the family" until version 3.0 of Toad for SQL Server and as such hasn't seen that much change.

Toad for SQL Server v1.0

When you start the app up you get nothing but the startup page (Which is interestingly a different page in this version than all the other versions) and a new connection dialog. Also fetching around a 200000 record long query takes over 1 minute in this release (It’s also N*Log N so fetching 500K takes over 5 minutes). This is also the only release we made that had the history node in the connection manager.

Toad for SQL Server2.0

Very small visual changes for this release. We added the composite image manager for better icons in the object palette. Also switched out SandDock to UIDock for the tool windows. We also fixed the fetching speed when you fetch everything we are pretty fast in this release, you don’t get any updates until you fetched all the data though. When you fetch just the first 50 rows and then continue we are still really slow. If you look closely you can also see that we switched the tab control in the object palette, added filtering and the column list pane at the bottom. We also added the connection output to the editor. In this release we finally managed to get NGen to work properly so startup time went from 12 seconds in 1.0 to around 9 seconds in this release.

Toad for SQL Server 3.0

This is the first release that figured out that my installed SQL Server instance is called SQLEXPRESS automatically. It also the first with FastData in it so fetching is now pretty fast (Pretty much as fast as before but with updates as you are fetching. This is also the only release which has indication of not null (The blue column header).

Toad for SQL Server 3.1

The most obvious changes here is the new connection tool bar (Which replaced the old connection bar). We also have the new quick search functionality implemented here. We have also added spooling to FastData so we now fetch the 200000 row query in about 5 seconds. This is the first release which doesn’t limit results in the editor by default any more.

Toad for SQL Server 3.2

Here the most obvious change is the panes of the editor. Apart from that there are few visible changes from before. This was mainly a stability improvement release.

Toad for SQL Server 4.0

Now this one is different! First of all we trimmed the grids so that you see a lot more rows from the start. The tabs in the editor has been tweaked to something that most people will find more useful (And they also follow the skin of the main application). The browser is in tree view. You also no longer get the configuration wizard on start up. The toolbars are rearranged so you can see all the buttons (We also no longer show the connection toolbar by default). A lot of the icons have been cleaned up. This is also the first release where the two tool windows showing on the left show up that way by default (In all previous screenshots I opened and rearranged them manually). And also we add the auto hidden tool windows to the right of the screen. And finally we changed the name of the object palette to object explorer.

Toad for SQL Server 4.1 Beta

An interesting change here is that we fixed a bug all the way from 1.0 of the product to not apply a skin by default. We also fixed the “edit” indicator button on grids. You can also see that the connection output is now gone and replaced with the messages tab. More work on cleaning up icons. We also see that we don’t expand the database node by default anymore in difference from 4.0. Another change in the 4.1 release is that on my machine this starts in around 5 seconds. Every release up to this release has started in around 9 seconds except for the 1.0 release which took 12 seconds.

Starting this blog

Every once in a while I've been writing Toad stuff on my personal blog and I figured it is time to stop doing that and try to do this blog into something a little bit more useful.

I will try to write tips and tricks about how to use Toad as I can think of them.

I'm one of the original developers of the .Net versions of Toad (All products except the original Oracle one) and currently work on what we internally call the "core" which means the piece of the application that is shared between the different platforms.

The entries on this blog are also posted on ToadWorld. For feedback and direct communication with all the developers of the product the best venue is till the Yahoo Groups. The information here has not gone through the normal peer review process of our normal documentation and sometime it might be about features that have not been properly tested (And that is why we aren't documented because we aren't ready for people to start banging on it). So use the information you find here at your own risk. Also please excuse me for all my spelling and grammatical errors, English isn't my first language (Truth be told I'm not that great at spelling in Swedish either).

I have a bunch of stuff I want to write about already so originally there will probably be a bunch of postings as I start out (Assuming I stick to it). After a while it should settle down a bit to just a few posts a month as I've worked through my backlog.