Intellisense – how good is it?

In a previous blog entry I talked about the new feature of Intellisense in SSMS 2008. This uses the intelligence of the system to help you with scripting syntax (and semantics) as you type. Very productive. It is a great feature and I have been using it in my testing of SQL Server 2008. But how good is it really?

Well, I must say, having the system prompt me for column and table names as I type is a definite advantage. It saves me typing and the more typing I do the more typos I make. It also will check things like objects that don't exist, invalid syntax and other errors. Hovering the cursor over the error will give an explanation of what is wrong. Very nice.

Recently, however, I have been spotting some vulnerabilities with Intellisense. For instance, I wanted to enter the following script as part of a training scenario in a lab I was testing:

USE AdventureWorks2008

GO

UPDATE Person.Person

SET LastName = 'Davolio'

WHERE LastName = 'Duffy'

GO

This script will update the LastName column in the Person table in the Person schema. As I type the "USE" then the space then the "A", Intellisense brings up a list of databases beginning with "A". I see AdventureWorks2008 so I hit Enter and it brings that database name into my script. Great. I hit Enter again for a carriage-return and type in the "GO" delimiter followed by another carriage-return. All is going well. Now I type  "UPDATE", space, "Pe" and the list of objects appears automatically with the Person schema selected so I press Enter again to confirm and it copies it to my script. Good. I type the period and decide to just keep on typing while looking at the lab instructions since it contains a couple literals which Intellisense is not going to help me with.  This is what I ended up with:

UPDATE Person.PersonTypeSET LastName = 'Davolio'

WHERE LastName = 'Duffy'

GO

So what happened? Well, when I typed the full table name "Person" it got confused and brought up a list of columns in the Person table, with a matching column "PersonType". Hitting enter for carriage-return instead meant a confirmation and brought in the column name. I continued typing the rest of the command and it went in ok. I glance over to the monitor and to my horror I have a lot of red squiggly lines (errors). So I decide to investigate and try again - slowly. I watch as I type instead of looking at the lab notes directly.  Same thing happens. So I  type in the whole statement manually with no carriage-returns, on one line, which SQL will accept also. Here's what I got:

UPDATE Person.PersonType SET LastName = 'Davolio' WHERE LastName = 'Duffy'

Hang on, what is going on here? I did not hit the Enter key at all and it still came out wrong. Now I am really concerned because I am typing exactly what I want, key for key, and it is coming out differently. In this case, it again is confused and brings up the columns of the Person table after I type "Person.Person" but when I type the space following surely it knows what I want. No, it takes the spacebar as confirmation of selecting from the Intellisense list, and it brings in the PersonType column.  Wrong.

This highlights the limitations of Intellisense. In my mind it can pop up suggestions as much as it wants but if I choose to ignore it, it should let me type what I want. Not in this case. I can see why the Enter key caused ambiguity but the spacebar should not be ambiguous, in my opinion.

The moral of the story is that a powerful feature can be powerfully good and it can be powerfully bad. So watch out! The good news is, if you don't like it you can switch it off. I still like Intellisense. But like Robert DeNiro in Meet the Parents - "I will be watching you..."

Cheers

Brian

Recent posts:

Let’s be thankful for SP3 – when it comes…

If it works, don’t fix it – the new Schema object

Microsoft Training over the Internet