When I first heard that Microsoft was going to introduce IntelliSense for SQL Server I could hardly contain my excitement. Much to my disappointment the initial version of IntelliSense left a lot to be desired. From issues like just not popping up at all, to not working on remote servers and sometimes picking incorrect entries could make it a bit tedious to use and for those of us who started out on SQL Server before the advent of IntelliSense it was easier just to revert back to good old fashioned typing from memory. I am happy to report however, that there has been great strides in IntelliSense to such an extent that if I suddenly had to live without it, I would be really really sad and my productivity would probably be affected somewhat as well. In this article I would like to do a quick overview of IntelliSense, how to use it and also shed some light on those moments when IntelliSense just doesn’t seem to be working.

What is IntelliSense?

IntelliSense for SQL Server Management Studio which was first introduced in SQL 2008 is an intelligent code completion mechanism which increases development productivity by making code snippets, definitions and syntax checking available to you without having to leave the editor. IntelliSense is enabled by default but can be disabled by going to Tools ➜ Options ➜ Text Editor ➜ Transact SQL ➜ IntelliSense. Figure 1: Default configuration for IntelliSense If you want to use List Members and Parameter Info, also make sure that it’s enabled in the General Transact-SQL settings. Figure 2: Statement Completion Settings

IntelliSense Functions

IntelliSense has a couple of main functions as you can see from the screenshot below. Figure 3: IntelliSense functions Certain functions such as List Members, Parameter Info, and Quick Info List are only available when you are connected to a SQL Server instance or when you are working within a database project. Other functions such as delimiter matching and code snippets are always available.

List Members

Pressing CTRL + J will bring up a list of all members that are valid for the specific key word typed. In the below example the list displays all valid members which can be used after the keyword EXEC. Figure 4: List Members

Parameter Info

Parameter info lists the parameters required for a function or stored procedure. Typing a space following the name of a stored procedure or a parenthesis “(“ activates the parameter info option of IntelliSense. The Parameter Info not only provides the name of the required parameters but also their datatypes as well as the default values which will be used should the parameter be omitted. The next parameter which needs to be provided is displayed in bold. Figure 5: Parameter info for stored procedure

Quick Info

Quick info brings up a tool tip with the declaration of the item you are hovering over as long as the syntax is supported by IntelliSense. Figure 6: Quick Info If you are running in debug mode it will also display the current value of the expression. Figure 7: Quick Info in debug mode

Delimiter Matching

IntelliSense now has the ability to match beginning and closing pairs of control flow elements such as BEGIN and END. This is very handy when you have multiple nested delimiters. The matching BEGIN and END will be highlighted when you finish typing the last letter of both the BEGIN and END. Unfortunately it doesn’t highlight it again when you place the cursor on it again. In order to see the highlight again after you have moved on to another line of code, you have to delete a letter and retype it in either the BEGIN or END tags. Delimiter matching applies to:

• Parenthesis
• BEGIN … END
• BEGIN TRY … END TRY
• BEGIN CATCH … END CATCH

Code Snippets

Code snippets allows you to quickly enter commonly used code snippets into your editor. If you right click in the editor you will see two relevant menu options: Figure 8: Right click context menu Insert Snippet… Clicking on Insert Snippet will display a drop down with possible objects to choose from. Figure 9: Available snippet object types Selecting one of these will then list all available snippets pertaining to that object type. Figure 10: Available snippets for specific type Selecting a snippet will insert sample code to create the object selected. Figure 11: Code snippet generated by IntelliSense Surround With… Surround With… helps you create code blocks, through the addition of BEGIN … END, IF or WHILE. Figure 12: Surround with menu In order to add this to existing code, highlight the code you want to have inside the block and then select the code snippet to surround it with. Figure 13: Code snippet inserted around highlighted code

Troubleshooting Common Problems with IntelliSense

IntelliSense does not work

There are a couple of reasons why this could be happening.

1. Firstly check if IntelliSense is enabled as explained earlier in the article. Also ensure that the Statement Completions settings are correct.
2. Ensure that there is no syntax error anywhere on the page before your cursor, resolving the code error should re-activate IntelliSense.
3. IntelliSense does not work inside comments, uncomment the section to be able to get the suggestions.
4. IntelliSense does not work inside a quotes. Be sure to place your cursor outside of any quotes.
5. Ensure that you are connected to a SQL Server Instance or a database project.
6. IntelliSense is only supported on SQL Server 2008 and higher.
7. IntelliSense does not work for encrypted objects.
8. Not all syntax is supported. Have a look here to see if your syntax is supported.
9. Are you running in SQLCMD mode? Unfortunately IntelliSense is not supported in command mode. Return to regular mode to get IntelliSense.
10. IntelliSense is currently not supported for SQL Azure.

Not all objects appear in the List

When a new object is added to a database the IntelliSense cache needs to be refreshed. This can be done by pressing CTRL+SHIFT+R or clicking the menu item Edit ➜ IntelliSense ➜ Refresh Local Cache. IntelliSense will not show you any objects to which you do not have permission. Ensure that you do indeed have permission to view the missing objects.

IntelliSense only works at the beginning of my script

Sometimes it appears as if IntelliSense stops working further down the page. This can be either because you have a syntax error a little higher up or because you exceeded the maximum script size. You can set the maximum script size to Unlimited by going to Tools ➜ Options ➜ Text Editor ➜ Transact-SQL ➜ IntelliSense. Figure 14: Specifying maximum script size

IntelliSense behaves funny

IntelliSense has two completion modes. You can either type SPACE to have the selected member in the list added to your code or you need to use TAB. This behavior can be toggled with the menu item Edit ➜ IntelliSense ➜ Toggle Completion Mode or by pressing CTRL+ALT+SPACE. Unfortunately the selected completion mode does not always persist and you may have to set it for each query window. Microsoft reported that the bug was fixed, but as far as I can see it is still present in SQL 2014.

IntelliSense is not picking up my synonyms

In SQL Server 2008 IntelliSense did not recognize synonyms. This has been fixed in SQL 2012.

IntelliSense for SQL 2008 R2 stops working after VS 2010 SP1 install

If you installed Visual Studio 2010 SP1 and is using SQL Server 2008 R2, it is likely that your IntelliSense may stop working. The fix for this was released in SQL Server 2008 R2 SP1. For more information please read this KB.