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.
IntelliSense FunctionsIntelliSense has a couple of main functions as you can see from the screenshot below.
List MembersPressing 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.
Parameter InfoParameter 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.
Quick InfoQuick 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.
Delimiter MatchingIntelliSense 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:
- BEGIN … END
- BEGIN TRY … END TRY
- BEGIN CATCH … END CATCH
Code SnippetsCode 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:
Troubleshooting Common Problems with IntelliSense
IntelliSense does not workThere are a couple of reasons why this could be happening.
- Firstly check if IntelliSense is enabled as explained earlier in the article. Also ensure that the Statement Completions settings are correct.
- Ensure that there is no syntax error anywhere on the page before your cursor, resolving the code error should re-activate IntelliSense.
- IntelliSense does not work inside comments, uncomment the section to be able to get the suggestions.
- IntelliSense does not work inside a quotes. Be sure to place your cursor outside of any quotes.
- Ensure that you are connected to a SQL Server Instance or a database project.
- IntelliSense is only supported on SQL Server 2008 and higher.
- IntelliSense does not work for encrypted objects.
- Not all syntax is supported. Have a look here to see if your syntax is supported.
- Are you running in SQLCMD mode? Unfortunately IntelliSense is not supported in command mode. Return to regular mode to get IntelliSense.
- IntelliSense is currently not supported for SQL Azure.
Not all objects appear in the ListWhen 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 scriptSometimes 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.
IntelliSense behaves funnyIntelliSense 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 synonymsIn 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 installIf 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.
Latest posts by Minette Steynberg (see all)
- The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2) - April 4, 2018
- 8 things to know about Azure Cosmos DB (formerly DocumentDB) - September 4, 2017
- Introduction to Azure SQL Data Warehouse - August 29, 2017