1

I'm using SSMS 17.6 which is the latest version at the time of posting. It is supposed to have the latest official IntelliSense version. However, it underlines my scripts with red squiggles where I'm using temp tables, linked server tables and synonyms. It's difficult to edit queries because I don't get correct error detection. Everything is filled with "errors", but queries run, and the results are what I want, as expected.

I've tried a few third-party addons like SQL Complete and SSMSBoost, but they don't seem to improve IntelliSense, but rather extend the SSMS in other ways like tooltips and popups. Or maybe I just haven't figured out how to use them for improving IntelliSense.

What can I do to make IntelliSense correctly handle these things? If there is no first-party way, then what third-party extensions exist and how to configure them to improve error highlighting?

  • Can you provide some specific examples? – LowlyDBA Apr 17 '18 at 13:28
2

In short, Intellisense isn't as intelligent as its name might suggest.

Temp Tables

If you're working with temp tables across different databases or separate query windows, Intellisense doesn't think the table exists, because, well, it doesn't as far as it knows. There are usually two scenarios this occurs:

  1. At run time, we know that a first command will execute and by the time we get to running an additional command that the table will exist. But asking Intellisense to be aware of things that haven't happened yet is impossible. This usually involves the temp table being created by a different process, query window, etc. It generally only knows the scope of its own query window and objects in non-tempdb databases.

  2. If it does already exist, being aware of everything already in the tempdb is probably a performance concern (just conjecture) because things move around in there fast, from moment to moment, and it'd be hard/unlikely to get a reliable enumeration of objects that was valid for more than a few seconds. Regardless, it just doesn't know about much outside of that single query window with regards to tempdb objects.

Linked Servers

This one sort of makes sense. Do you really want Intellisense going and querying your linked servers every time you are writing queries that involve remote objects? It just doesn't know and so assumes it doesn't exist.

Synonyms

If the synonyms are referring to linked server objects, then they'll suffer from the issue described above. On their own, however, they should work fine with local database objects.

Bottom Line

Learn to ignore it, or use a different IDE if possible (one that doesn't use Intellisense). Most people seem to like Intellisense despite its flaws, but you could always turn it off completely as well.

There is a hacky work-around for temp tables at this SO question but I personally would never do that, as you're increasing the complexity and reducing readability of your code for...a red squiggly line.

  • Good post, especially about the synonym. What I recommend for the temp table is to script the if exists drop and then re-create for the temp table. That way it removes the squiggles and it allows the statement to run in the same window repeatedly. – Shaulinator Apr 17 '18 at 13:56
  • Create synonym X says "There is already an object named X in the database" but a few lines below about select * from X it will say "Invalid object name 'X'". This has been my SSMS experience :p – user1306322 Apr 17 '18 at 13:59
  • @user1306322 what happens when you hit the parse button? (CTRL + F5) Does it go away after? – Shaulinator Apr 17 '18 at 14:04
  • I synonymized tables from a linked server to a short name. Maybe it's the combination of linked server addressing and synonyms. I don't know. Cache refresh has never helped. Parse doesn't do anything either – user1306322 Apr 17 '18 at 14:04
  • 1
    @user1306322 Ones that don't have Intellisense at all, or don't have Intellisense hooked up to check database object existence :) You're better off just disabling Intellisense if its a real issue for you, though. The third party plugins can probably extend most of the functionality you need. – LowlyDBA Apr 17 '18 at 14:10
2

I know this is an old question but this seems to be the sacrifice Intellisense is demanding. It is similar to the post linked to in @LowleyDBA's (arguably better) answer. Unlike the linked answer, this provides a safe and strong guarantee that your table will not be messed with that Intellisense likes.

 IF OBJECT_ID('tempdb..##Example') IS NULL
   SELECT '0' AS ID, 'NotLoaded' AS Field1, 'every field needs to be defined here' AS Field2 INTO ##Example
 IF EXISTS(SELECT TOP 1 * FROM ##Example WHERE Field1 LIKE 'NotLoaded')
   RAISERROR('Error : ##Example not loaded', 20, -1) WITH LOG

"RAIS"'ing an error halts execution

0

I don't know if the red squiggles can be disabled however you can change it's color to match the background if they bother you that much

Tools -> Environment -> Fonts and Colors -> Text Editor -> Syntax Error

  • 1
    Of course, this does nothing to help the OP with improving error highlighting. Instead of highlighting both errors and certain known entities that aren't necessarily errors, this would presumably effective, disable error highlighting altogether. The OP wants error highlighting, just without all the false positives. – RDFozz Apr 17 '18 at 15:48
  • yes I know but it has been stated above that it's not possible, at least not as long as ssms is propietary software – Roberto Torres Apr 17 '18 at 17:31
  • Understood! I think you have a useful point - I thought it worth a comment to make sure the "cost" of the solution is clear to others. – RDFozz Apr 17 '18 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.