Interesting thread here, and I think all the comments that have been made are fair. We're very aware of the issues with SQL Prompt 2, and that's a big part of the reason it's free. We decided that we wouldn't be happy to charge for it in its present state.

However we're also very aware that SQL intellisense functionality is something that a lot of people are after, and that whilst there are various offerings out there, including SQL Prompt 2, none of them provides a really definitive or satisfactory solution. This is mainly because it's a really hard problem to solve. And if you don't believe me just listen to some of the Microsoft guys talking about it here:

http://www.dotnetrocks.com/default.aspx?showID=184

This webcast is actually about the new Visual Studio Team System for Database Professionals, but there's a discussion about intellisense roughly 25 minutes into the show. It makes for pretty interesting listening.

Obviously we're working on a new version of SQL Prompt right now, and being slightly cagey about the whole thing to boot. We're aiming to address all the issues people have with SQL Prompt 2, and generally to provide a much more comprehensive intellisense solution. We're also looking at how we can better support very large databases since in this context intellisense would probably be at its most useful. Anybody can find their way around a database with a few dozen tables, although intellisense would obviously help, but what if you have several thousand tables? Something like intellisense becomes almost essential.

I can't be too specific about dates at this point, however we are aiming to get a beta of version 3 out sometime in the first couple of weeks in August. You can find more details about the beta program at http://www.red-gate.com/messageboard/viewtopic.php?t=2567.

If all goes well the final release should be available at the end of September or very early in October. The beta is relatively soon in order to give us more time to respond to user feedback and incorporate any necessary changes into the final version.

On the memory front I think I probably need to say something about this now in order to avoid disappointment later, as it were. To do intellisense properly in any language is a tricky thing, and to do it well, and keep it responsive (remember, it has to keep up with your typing speed) requires the use of some slightly hairy data structures. Dynamic arrays and linked lists just aren't going to cut it here. Let's say your candidate list has popped up and it's got 20000 objects in it, and now you press a key and that list has to *instantly* be filtered down to contain only candidates starting with whatever character you just typed. So you have to have a fast data structure, and you have to keep a lot of the information in physical memory to keep the intellisense responsive. Obviously you throw as much as possible onto disk to keep the footprint down, but there's no getting away from the fact that you need a lot of memory to make it work properly.

Now you might be feeling slightly sceptical at this point, which is fair enough, but consider this. I currently have instances of Visual Studio 2005, Query Analyzer, and SQL Server Management Studio open. In VS 2005 I'm working on a C# project with roughly 11000 lines of code, plus about another 5000 lines of comments spread across 50 or 60 files. I'm also referencing maybe half a dozen other DLLs. At the moment I have only one file open, defining a single type, which is less than 300 lines. VS 2005 is currently using 137MB of memory, and so far I haven't done anything except open the project. Query Analyzer has a couple of query windows open with active connections to my local SQL Server 2000 instance. One of these windows contains a script with about 1100 lines of SQL in it. Currently QA is using just over 8MB of RAM. SSMS has one active connection and a single open SQL script, which again weighs in at around 1000 lines. SSMS is currently using 62MB of RAM.

So why the big differences? Well QA is basically, and I mean no disrespect in saying this, not much more than a glorified text editor. It's got a bit of SQL Server-centric functionality bolted on to it, but like I say, it's a relatively simple application, and therefore it's small and fast. SSMS on the other hand is basically a locked down, and somewhat cut-down, version of Visual Studio 2005, and Visual Studio 2005 is a fully-functional IDE, and that's a lot of overhead to be carrying. Hence: 62MB. So why the extra 70MB for VS2005, bearing in mind that the project I'm working on is a back-end DLL with no UI components or anything like that (no need for the forms designer)? You can bet a big chunk of that extra memory is used by intellisense. Now admittedly I'm using ReSharper rather than the intellisense built in to VS2005, but the situation isn't so very different if I switch back to VS2005 intellisense.

And so with SQL Prompt we're basically bolting a big chunk of the functionality provided by a modern IDE onto Query Analyzer or SSMS or whatever, and when you think of it in those terms it's not so surprising that it chews up quite a bit of memory. You think it should be small because it's sort of like a plug-in. It's not the main event at all, it's just there to help, but that discounts the complexity of what it's doing. Seriously, try any modern IDE on a project of any size and watch it chew through your memory: VS 2005, Eclipse, IntelliJ IDEA, NetBeans, *anything* from Borland. Unfortunately it's the nature of the beast. Clearly though we are doing everything we can to keep the memory footprint as small as possible.

Also, all of that said, we believe there is a bug in SQL Prompt 2 that means sometimes it uses up hundreds of megabytes for no apparent reason for even moderately sized databases. It's pretty rare though, and is usually cured just by restarting SQL Prompt.

Anyway, I hope that goes some way to answering some of the questions and concerns raised on this thread, and if you've got anything else I'm more than happy for you to come back at me.

Finally, and before I forget, somebody (grasshopper?) asked me about licensing. Unfortunately I can't really answer that at this stage: typically that kind of thing gets decided quite late in the project lifecycle after months of wrangling. What I'd suggest is that nearer the release date you get in touch with our sales team at sales@red-gate.com and they'll be able to give you a definitive answer. Sorry I can't be more help than that for now.

Kind regards,