Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

IntelliSense in SQL Server 2008 can sometimes not be very intelligent.It’s there to help you can sometimes cause more problems than it does solve problems.Here’s one of those problems cases:

Create a table without using the UI.Just type a basic script to create a table.

Try to select from that newly created table and your IntelliSense can’t find the new table.

The problem is SQL Server knows you have a new table but IntelliSense does not.If you refresh the Local Cache IntelliSense will be back up to speed with you. Go to Edit -> IntelliSense -> Refresh Local Cache

Now it should recognize your newly created table when writing your statement.

Another best practice with IntelliSense that’s not shown in the second screenshot is when writing statements always write the From statement first.That way IntelliSense knows which table to display columns for.

Comments

Posted by Steve Jones on 23 February 2010

Nice one, and a good tip to know.

Posted by MEGANATHAN K on 28 February 2010

Worthy tip!!!

Posted by jeff-1144484 on 1 March 2010

Thanks. That was driving me nuts !!

Posted by Charles Hottle on 1 March 2010

I also have SQL Prompt installed and, although I generally like all of this new functionality, it does seem to get carried away with auto-complete and I can't tell if it's the built-in functionality for SQL Prompt. Thanks. for the tip on this particular issue.

Posted by Anatoliy on 1 March 2010

You do not see TestTable in IntelliSense possibly because this table was not created by such a script.

At least I can see it perfectly well after typing Crete Table TestTable… ;)

But there is another problem.

After dropping this table you still have it in IntelliSense, even after refreshing IntelliSense local cash or refreshing tables, databases or servers in Object Explorer.

This is weird to me.

Posted by knight_devin@hotmail.com on 1 March 2010

I've noticed if I have the create table in the same query window as the select statement that IntelliSense recognizes it.

Posted by gautam.swe on 20 April 2010

"IntelliSense slow to refresh"

i don't think so, in my system it instantly update the table structure, as i create a table and give a SELECT * FROM.. n type the first letter of table just created it shows me table name as well.

Posted by Alex on 17 November 2010

No, no, if I change the structure of a table in SQLServer 2008, any query that I write will NOT be able to refer to the new/modified column - to be able to use it, I have to close the SqlServer Management Studio process, and restart it. Even "simply" disconnecting from and reconnecting to the db is not enough !

Therefore, it's not only the Intellisense ! Yes, the editor will show the "offending" column with a red underline, but it will also refuse to save my query. I have to completely get out anf come back in again.