Fabiano Amorim is hooked on today's Integrated Development  Environments with built-in Intellisense, so he looked forward keenly to  SQL Server 2008's native Intellisense. He was disappointed at how it turned out, so turned instead to SQL Prompt.  Fabiano explains why he prefers to SQL Prompt, why he reckons it fits in with the way that database developers work, and goes on to describe some of the features he'd like to see in it.

Introduction

When I was ten years old, I saw my brother programming in Clipper, and I asked him how difficult it was to write code. He replied that it was easy, and then promptly showed me how to do it. If learning how to develop software was a cinch for him fifteen years ago, I wonder how easy he'd find it now, with all the improvements we've made to development environments. As far as I'm concerned, one of the best improvements in our day-to-day tools is intellisense, in all its wonderful shapes and forms.

I just burn to develop applications, so when I need to do any DBA-type tasks, I write code to do them. I also love T-SQL, and I´m lucky to work in a company which uses a lot of stored procs, functions etc., and so SQL Server Management Studio is my home, the place where I feel comfortable.

When I first heard that a native intellisense feature was  going to appear in SQL Server 2008, I was thrilled; I suspect that  you were, too. I then got a chance to speak to an MVP about this juicy feature, knowing that he'd had access to it before the first beta was available. Naturally, I asked him whether intellisense would display all possibilities when I started typing something like ‘DBCC…', or whether the list would be limited?

”Of course,” he replied, "when you write anything at all, SSMS will open the intellisense window”. This increased my excited anticipation of the feature.

However, when the first CTP version came out with this much-vaunted new feature, we quickly saw that these claims were not all true, and when it became clear that the final version of SSMS intellisense would only work for SQL Server 2008, this made me very very sad.

As a result, I would like to explain the reasons why SQL Prompt makes me decide to turn native intellisense OFF. I don't want to merely  promote Red Gate tools: Instead, I want to show you how this tool improves my work, and also suggest some new ideas to the Red Gate developers (who have already seen this article). These aren't criticisms, but rather to suggest possible ways that would make the tool even more indispensable to people like me. If you've not heard about SQL Prompt before, I really do recommend that you download the trial version and take it for a spin. If you have heard of it / used it, feel free to make your own suggestions for new features on the Red Gate forums.

SQL Server Intellisense

Here we can see SQL Server Intellisense in practice: I wrote "select * from ”, and it shows all of my tables. That's pretty basic, but to someone who doesn't know what they're doing, that could be good for a start.

I'll stop demonstrating the native SQL Server Intellisense now, because unfortunately there really isn't much more to show. This should give you a hint as to why I find it so frustrating. If that was the only tool around, then it would certainly be worth using – there's no question that it does save you some time. However, it's not the only tool around, so let's start with the alternative that I use - SQL Prompt.

Red Gate SQL Prompt

To start with, take a look at the image of the query above, written using SQL Server intellisense, and then examine the same query below, written when using SQL Prompt instead:

Right away, the tool is giving me more information than native intellisense can, and this is naturally something which you can decide to turn off if you don't need or want it.

Let me run you through some of the really neat things SQL Prompt can do, and why they make such a difference to how I work. If you first want to see a quick demonstration of the functionality that helps me in my work, you can take a look at a video demonstration of the tool.

"SSF” + TAB

This may seem trivial at first, but imagine that I wanted to write "SELECT * FROM …”; if I just wrote "ssf” instead, and pressed Enter, SQL Prompt actually replaces it with "SELECT * FROM”, saving me precious keystrokes. Pretty neat, huh? Ok, I know that small but smart things like this can quickly make people dependant on them, but I can live with that.

* + TAB

When I need to write a SELECT statement, I like to use all the available columns instead of *, but writing all the columns is very boring, and, frankly, a waste of time. Surely, it is better to let SQL Prompt do that for you?

Ok, why not. Here's what happens:

Joins – Single and Multiple Foreign Keys

One of the coolest things in SQL Prompt is the suggestions for joins. There are many patterns to writing joins; some people like to use aliases for their tables, and some don't. Personally, I like to put the full table and column names in my queries, and that make my query writing a little slow. However, when I start writing a join, SQL Prompt recognizes all the tables that reference the one(s) in my SELECT statement, and shows these suggestions in an intellisense window. Even better, when I select a table and write ON, SQL prompt even shows the possible joins… Look at the following sequence of images to see what I mean:

To start with, let's look at what foreign key references are on a table:

Now when I write a join, look at the order in which SQL Prompt shows the tables:

After selecting the CONFI006A table, let's look at what it suggests as good candidates for join keys:

More than that, if I have one table-join that uses 3 columns as keys, look at what it shows:

After selecting the option that I want, SQL Prompt then drops in all the SQL code for you, but that doesn't look so good at first glance because it's all been dropped in just one line:

In this case, you could just press the Format SQL shortcut, CTRL+K+Y, and see the immediate result:

Which brings me neatly onto another great feature…

Format SQL

As you just saw, you can configure the Format SQL function to automatically tidy up your code, and there are a lot of options to choose from:

This means that you can open the beautiful, and deeply indented 5000+ line procedure written by that new developer in your company, and just press CTRL+K+Y to format all the code the way you like it. When you open a procedure and have to look for where that "begin” really begins and that "end” actually ends, you don't need to blame that poor developer for wasting your time any more.

INSERTs

This is very good functionality. In the past, I found the task of writing an INSERT into a table with many columns a frustrating task. I needed to write out all the columns, and then be very sure about the order of values passed in the VALUES list. Bear in mind that, in my database, I've got a denormalized table with more than two hundred columns. Now imagine how much fun it is write an INSERT to that table.

Using SQL Prompt, look what happens when I write "INSERT INTO <sometable>”...

... and then press Enter.

Writing code for Procedures

Yet another interesting feature is available when you type "EXEC PROC st…”: All input fields are displayed in the correct order, with their respective expected datatypes, and Datetime fields are automatically filled with a default date.

Encrypted Objects

There are naturally times when I need to support our clients, some of whom are encrypting the code of their routines. Sometimes I need to look at some of their procedure code, and I really hate seeing this message:

Since I've come to my current job, I've suggested many best practices to colleagues, and one of them is most definitely to not use encrypted objects. First off, the encrypt algorithm doesn't really work. Secondly, it wastes time decrypting objects when SQL Server needs to run the procedure. And third, I HATE getting this message.
There are admittedly many pieces of software that can break the encryption used here, but I was still surprised when I wrote an exec to an encrypted proc, and SQL Prompt immediately displayed the all the code!

Suggestions: My Wish-List

Now that I've pointed out what I really like about SQL Prompt, I'd like to share some of the thoughts that I've had about how to improve the tool. I would just love to write some of the features I'm going to suggest into an SSMS Add-In, but I'm unfortunately too busy to code something like that at the moment, so I decided to suggest it to my Red Gate friends instead!

Improve a feature already in SSMS

SSMS has a feature that allows us to use a shortcut to run predetermined code, which you can access in the \Tools\Options\Environment\Keyboard\ menu. Here you can see my short cuts:

As you can see, I'm using my Ctrl+6 short cut to select a  table called CORCC031, which I use as a dictionary in my database to show the descriptions of all the tables (For example, to record the fact that the CORCC023 table contains details of all my costumers).

The improvement here would be for SQL Prompt to use a wildcard in conjunction with this feature. For instance, if I wrote "SELECT * FROM CORCC031 WHERE NM_Logico like ‘%WILDCARD%' ”, then used my SSMS short cut to select the value I want, then the WILDCARD would be replaced with the desired text. I discussed this idea with MVP Rob Farley a little while ago.

Drill Down using ForeignKeys

Well here is something that I really want to get some time to write a C# Add-In for. I haven't seen anything like this yet, so here we go with my idea:

I want to be able to drill down in the ResultGrid using a selected value. I think if I demonstrate this with images, it will be easier for you to understand what I mean. Take the following situation:

IF OBJECT_ID('Cars') IS NOT NULL
  BEGIN
  DROP TABLE Cars
  DROP TABLE Constructors
  DROP TABLE Customers
  END
GO
CREATE TABLE Cars(Car_ID&#160;INTEGER IDENTITY(1,1) PRIMARY KEY,
        Name&#160;VARCHAR(50),
        Constructor_ID INT)
CREATE TABLE Constructors(Constructor_ID INTEGER PRIMARY KEY,
        Name&#160;VARCHAR(50))
CREATE TABLE Customers(Customer_ID INTEGER IDENTITY(1,1) PRIMARY KEY,
        Name&#160;VARCHAR(50),
        Car_ID INT)
GO
ALTER TABLE Cars ADD CONSTRAINT fk_Cars_Constructors FOREIGN KEY (Constructor_ID) REFERENCES Constructors(Constructor_ID)
ALTER TABLE Customers ADD CONSTRAINT fk_Customers_Cars FOREIGN KEY (Car_ID) REFERENCES Cars(Car_ID)
GO
INSERT INTO Constructors (Constructor_ID, Name)
    VALUES (1, 'AUDI')
INSERT INTO Constructors (Constructor_ID, Name)
    VALUES (2, 'BMW')
INSERT INTO Constructors (Constructor_ID, Name)
    VALUES (3, 'PORSHE')
INSERT INTO Cars (Name, Constructor_ID)
    VALUES ('A3 ',1)
INSERT INTO Cars (Name, Constructor_ID)
    VALUES ('A4 ',1)
INSERT INTO Cars (Name, Constructor_ID)
    VALUES ('TT COUPE ',1)
INSERT INTO Cars (Name, Constructor_ID)
    VALUES ('325CI CONVERTIBLE ',2)
INSERT INTO Cars (Name, Constructor_ID)
    VALUES ('325XI SPORT WAGON ',2)
INSERT INTO Cars (Name, Constructor_ID)
    VALUES ('MINI COOPER ',2)
INSERT INTO Cars (Name, Constructor_ID)
    VALUES ('BOXSTER ',3)
INSERT INTO Cars (Name, Constructor_ID)
    VALUES ('CARRERA 2 COUPE ',3)
INSERT INTO Cars (Name, Constructor_ID)
    VALUES ('CAYENNE ',3)

  -- Well, it might be wishful thinking, but it would make me so happy to be able to
  -- write an insert like this...

INSERT INTO Customers(Name, Car_ID)
    VALUES('Fabiano Amorim', 3)
GO
SELECT *
  FROM Customers
GO
      

Let's look at the Customers table data:

Well, if I want to know what car "3” is, what do I need to do? Write another SELECT using the value "3” as a filter, clearly:

SELECT *
  FROM Cars
  WHERE Car_ID = 3

Ok great, but now if I want to know the name of Constructor "1”? Again, I need to write another SELECT:

SELECT *
  FROM Constructors
  WHERE Constructor_ID = 1

By now, I thing you understand what I'm getting at; I thought something like the following would be useful:

You could just check if a foreign key exists using this column and then generate the query, displaying something like this:

Again, using the a recursive method.

Ok, I know that this could be very dangerous because, depending on the data, that "new" SELECT could take a long time to run. In that case, we could suggest an index using the column, or check if an index exists to automatically enable that option. We could even include a button to stop the query and turn this functionality off by default. Just think about it, and we can talk about it in the comments below.

DROP CONSTRAINT Suggestions

In the ALTER TABLE DROP CONSTRAINT command, the foreign keys are currently not shown as suggestions.

If would be great if we could get the related foreign keys displayed as a suggestion to the DROP command:

SELECT OBJECT_NAME(fkeyid), OBJECT_NAME(rkeyid), OBJECT_NAME(constid), *
  FROM sysforeignkeys
  WHERE OBJECT_NAME(fkeyid) = 'cars'

Function Datatype Awareness

When we write code using a function, all the columns are suggested, but I think would be much better if we only saw the columns which have the right datatype, respective to our query. For instance:

Here we could just show the columns of the DateTime datatype, and if the table has a SmallDateTime column, then we could show both columns. Again, that could be a function to be enabled or not, at the user's discretion.

Conclusion

I want to finish with just a few words. I hope you can see why I don't bother using SSMS native Intellisense, but at the same time there's still ways in which SQL Prompt could be improved. That being said, it's a great tool, so thank you Red Gate, and congratulations on making my life easier.

That's all folks, I'll be back to the subject of the Query Optimiser soon, so keep your eyes open.