July 1, 2009 | SQL Server

Why is IntelliSense not working?

I see people complain that IntelliSense (new in SQL Server 2008's Management Studio) is not working.  Most of the time, it is for one of two reasons:

  1. The object is not in the local IntelliSense cache, since it was created recently.  You can fix this easily by going to Edit > IntelliSense > Refresh Local Cache, or CTRL+SHIFT+R.
     
  2. The query window is connected to a downlevel server (e.g. SQL Server 2005), where IntelliSense does not function (see Connect #341872 from Whitney Weaver for more info and LOTS of community feedback on this decision :-)).

Little did I know, there are a number of other situations where IntelliSense will not function correctly (e.g. when you have switched to SQLCMD mode).  I was also unaware, until today, that Alan Brewer at Microsoft created a Books Online page that documents these and other cases:

When IntelliSense Is Unavailable

So many thanks for this document Alan, as I think it will help others as well.  What I'm still trying to figure out, though, is why there is a SQL Server 2005 version of the page, since IntelliSense does not exist in Management Studio for 2005, nor does it work in 2008 against 2005 instances.

29 comments on this post

    • Whitney Weaver - July 1, 2009, 10:34 PM

      A timely post Aaron.  I am getting the feeling that Intellisense is quickly going down in the "be careful what you wish for" column.

    • cinahcaM madA - July 1, 2009, 10:39 PM

      I was going to say that you should be happy if it's not working 🙂

    • Jared Ko - July 1, 2009, 11:58 PM

      There's a space on the end of the link for "When IntelliSense Is Unavailable". Should be this: http://msdn.microsoft.com/en-us/library/ms173434.aspx.
      I frequently jump between 2005 and 2008 connections and lose bracket highlighting. I'm anxious to read this link. 🙂

    • John Clark - July 2, 2009, 7:11 PM

      What…"I was going to say that you should be happy if it's not working :-)"
      You people got to be kidding me…
      Use Red-Gate SQL Prompt and you will quickly grow to love intellisense…
      Just to be able to do "select * from tableA" and have it fill in the names of the columns when I want is worth the price..
      So what if intellisense not perfect.. it SAVES a huge amount of time…
      -jfc-

    • cinahcaM madA - July 2, 2009, 7:47 PM

      Non-working Intellisense–which includes both Microsoft's and Red-Gate's implementations–wastes a lot more time than it saves. Although to be fair to RG, Microsoft's is much worse at this point in terms of getting in the way.
      Microsoft's implementation autocompletes with candidates that don't make sense, greatly increasing the number of keystrokes needed in some cases (you need to back out the autocompletion).
      RG's does not have that problem, but also doesn't support nearly as much–such as CTEs–and it has bugs where it will occasionally populate the candidate list with incorrect column names. It also often marks perfectly valid SQL as invalid, which does waste time. These comments, by the way, refer to the prerelease version of RG's newest offering in this space.
      I hope that some vendor–RG, Microsoft, or other–will solve the Intellisense problem soon. I agree that it would save a lot of time, but the offerings today don't, and for me hurt rather than help my productivity.

    • AaronBertrand - July 3, 2009, 2:04 AM

      Adam I understand your points, and I am actually on the same side of the fence as you.  For *ME*, IntelliSense (both the native version and RG's) simply gets in the way.  But for a lot of people, the scales tip the other way… the nuisance is worth it.

    • Dejan Grujic - July 16, 2009, 1:27 AM

      One of tools that offer Intellisense for SQL Server 2005 and 2000 is our DbOctopus. Nice thing is it's free until September, so there're no reasons not to give it a shot. It's not just SQL code editor, but you can see that if you visit:
      http://www.cogin.com/dboctopus/

    • Peter - February 25, 2010, 3:00 PM

      The short story:
      grant exec on xp_instance_regread to public
      The long story:
      We recently upgraded our user testing database to 2008. Intellisense did not work until we tried granting my user SA rights. This prompted us to search for the permission required. The MSDN articles insisted that you will be able to use Intellisense on objects that you have access to, yet I was unable to get Intellisense working on tables I could select from.
      I then went to ServerName -> Security -> Logins and tried to view the properties of my login. I got an execute denied error for xp_instance_regread. It turns out that this solves another issue in SSMS 2008 when trying to right click on non-2008 databases. Granting access to this enabled Intellisense.

    • Paul - March 24, 2010, 1:11 AM

      Peters comments pointed me in the right direction but did not solve my problem.
      The issue I found was that I was granted permission to the database server as part of a windows group but my windows login had been added as a user to the database with no role.
      I believe this occured during the execution of enterprise library scripts that led to a schema being created with my login name.
      When these two items where removed intellisense resumed working.
      In summary, check all your permissions are correct before looking elsewhere for a solution

    • Louis - June 3, 2010, 2:16 PM

      Thank you!

    • Question: Can EZ-CAP 6.0 handle this? The dilemma is that the member only is added to EZ-CAP once the member has transferred to an IPA Central Health contracts with. But the Healthplan and Option are the same so according to the Healthplan, the benefit ac - July 1, 2010, 3:41 PM

      Question: Can EZ-CAP 6.0 handle this? The dilemma is that the member only is added to EZ-CAP once the member has transferred to an IPA Central Health contracts with. But the Healthplan and Option are the same so according to the Healthplan, the benefit accumulators should carry over from the first IPA (that does not contract with Central Health) to the second IPA that does. If Central Health starts the member’s accumulators off at $0 they will not be accurately tracking the member OOP totals and the deductible/OOP maximums will over-charged to the member.

    • Brian - August 11, 2010, 4:54 PM

      Thanks, this helped to solve an annoying problem I was having 🙂

    • Mike Hayes - November 2, 2010, 11:40 PM

      Turns out that references to fields for synonyms show up underlined in red also.

    • Dingo - February 15, 2011, 5:01 PM

      I've just upgraded my client tools from 2008 to 2008R2 (connecting to a 2008 R2 server) and there seem to be differences in the implementation of intillisense in Management Studio between the versions.
      Pre-upgrade (plain old 2008) to accept a suggestion from autocomplete, you could type either a period, closing square bracket or space, this for me worked pretty well.  
      Post upgrade (R2) things are worse, now the period does not accept the suggestion (it instead just appears at the end of what you've typed), the closing square bracket will accept BUT but only if you have NOT started the object name with an opening bracket!! So autocomplete entries end up looking like either dbo]. or [db]
      The functionality of the spacebar remains unchanged, but this is annoying if you're autocompleting a db/schema name as you then have to delete the space you've put in, before continuing with the table name.
      A shame, I was getting used to autocomplete and it reduces the time taken to get used to a new, complex schema at a client site (I don't have a photgraphic memory and so need either a diagram or something like autocomplete before getting up to speed).

    • Joseph - April 28, 2011, 9:27 PM

      What Dingo said about the period is definitely an annoyance.  I also just upgraded to 2008 R2 and am annoyed at this loss of functionality.
      Are there any solutions to this?  I had initially assumed I accidentally disabled that function somehow…why would you take it out?

    • Adam Sandwiches - May 5, 2011, 4:38 AM

      I used redgate's for years and then changed jobs and am back to microsoft's (which is why I am on this article). I like redgate's a lot but find microsoft's a pain in the rear.

    • Warren S - May 27, 2011, 2:40 AM

      Also it seems they went from a character/case order to a case/character order.  Which makes it all most useless if you are in an environment that has changed their standard for CAPS to camel case.
      Example:
      TABLECUSTOMER201001
      TABLECUSTOMER201002
      TABLECUSTOMER201003
      TABLECUSTOMER201004
      All other CAP TABLES/VIEWS (could be hundreds )
      TableCustomer201101

    • THEINTREPIDFOX - September 9, 2011, 11:19 AM

      Excellent Aaron!

    • Nawar Tabaa - September 19, 2011, 1:18 PM
    • santosh - December 1, 2011, 12:14 PM

      Thx for the post…
      I faced the same issue recently & proble resolved by install SQL 2008 SP1
      Thx
      http://www.mytricks.in/2011/11/fix-sql-server-management-studio.html

    • Nageswar - March 14, 2012, 11:06 AM

      Download and install
      Microsoft® SQL Server® 2008 R2 Service Pack 1

    • Ken - March 26, 2012, 4:02 PM
    • Mike - September 13, 2012, 9:12 PM

      Thanks good sir! Ctrl+Shift+R treats me right 🙂

    • Trent - October 14, 2012, 10:57 PM

      The intellisense on SQL 2012 is crap, it works great on 2008 but this new version has soo many issues that i'.m going to remove it and put 2008 on, thanks!

    • Amanda - October 19, 2012, 7:45 PM

      I recently noticed asynch_network_io waits shoot up, and it appears that the cause is increased usage of intellisense…or there is something wrong.  It is this query – any ideas?
      (@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000))
      SELECT SCHEMA_NAME(sp.schema_id) AS [Schema],  
        sp.name AS [Name],  
        sp.object_id AS [ID],  
        CASE  
           WHEN sp.type = N'P'  THEN 1  
           WHEN sp.type = N'PC' THEN 2  
           ELSE 1  
        END AS [ImplementationType],  
        CAST(  
        CASE  
           WHEN sp.is_ms_shipped = 1 THEN 1  
           WHEN (  
           SELECT major_id  
           FROM sys.extended_properties  
           WHERE major_id = sp.object_id  
          AND minor_id = 0  
          AND class = 1  
          AND name = N'microsoft_database_tools_support'
           )  
           is not null THEN 1  
        ELSE 0  
      END AS bit) AS [IsSystemObject],  
      CAST(
      CASE  
        WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1  
        ELSE 0  
      END AS bit) AS [IsEncrypted]  
      FROM sys.all_objects AS sp  
      LEFT OUTER JOIN sys.sql_modules AS smsp  
      ON smsp.object_id = sp.object_id  
      LEFT OUTER JOIN sys.system_sql_modules AS ssmsp  
      ON ssmsp.object_id = sp.object_id  
      WHERE (sp.type = @_msparam_0  
      OR sp.type = @_msparam_1  
      OR sp.type=@_msparam_2)  
      ORDER BY [Schema] ASC,[Name]  
        ASC

    • AFomchenko - November 29, 2012, 2:53 AM

      I have got troubles with this request generated by Intellisense in TempDB.
      We generate and drop many #tables all the time and this request cannot be completed and always been blocked. It looks like it comes from ObjectExplorer somehow.
      Let me know if someone have any idea how to suppress this behavior except of disabling this option. We do a lot of development and constantly switching between prod and test boxes.
      SELECT tr.name AS [Name], tr.object_id AS [ID], CASE WHEN tr.type = N'TR' THEN 1 WHEN tr.type = N'TA' THEN 2 ELSE 1 END AS [ImplementationType],
      CAST(                  tr.is_ms_shipped              AS bit) AS [IsSystemObject],
      CAST(CASE WHEN ISNULL(smtr.definition, ssmtr.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted]
      FROM sys.triggers AS tr LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id
      LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id WHERE (tr.parent_class = 0) ORDER BY [Name] ASC

    • Eric Halverson - April 17, 2013, 2:36 AM

      Ctrl Shift R   Nice!!

    • Jim - August 3, 2013, 6:06 PM

      Appreciate your help but this is soooooooooo frustrating.  Can't microsoft provide a script (powershell, transact sql, or executable) that could diagnose this problem and tell what needs to be done to get intellisense working??????????  They are F*()'ing microsoft and wrote all this cr*((p.

    • Rohit - October 11, 2013, 8:27 AM

      Very nice article

Comments are closed.