SQL Server IntelliSense and Autocomplete

Starting from SQL Server 2008, Microsoft introduced a new feature in the SQL Server Management Studio that helps the database developers and the database administrators writing the T-SQL commands faster by reducing the typing effort and providing a quick access to the syntax information via listing all available database objects with their properties. This feature is called IntelliSense.

IntelliSense in SQL Server Management Studio

Microsoft IntelliSense allows you to find the necessary information while writing the T-SQL script without leaving the query editor for such purposes. It’s just needed to write the T-SQL script and the IntelliSense will complete the typing automatically with providing all available information. Microsoft IntelliSense is enabled as default in SQL Server Management Studio. If it is disabled, you can easily enable it from the SQL Server Management Studio by choosing Options from the Tools task menu as shown below:

Browse for the IntelliSense settings window from the “Options” by expanding the Text Editor and Transact-SQL sections thereafter. Check the Enable IntelliSense option in the appeared Transact-SQL IntelliSense Settings window. Here you also will be able to modify the different IntelliSense configurations such as underlining the errors, changing the maximum size for the script, or changing the casing applied to function names in completion lists. This is shown below:

The automatic completion of the statements with the listed information about the function or stored procedure parameters within the IntelliSense feature can be enabled or disabled from the General tab in the Text Editor section as shown below:

You can also simply enable the IntelliSense from the query editor by right-clicking on the query editor and choosing the IntelliSense Enabled option or using the keyboard shortcut Ctrl+B and Ctrl+I as shown below:

You also can take benefits from the IntelliSense feature after opening a new query in your database and starting to type the T-SQL command like the query below, in which the IntelliSense list all database objects that match the written prefix:

Although you may find the IntelliSense feature enabled in the SQL Server Management Studio, it is possible to face some issues, in which something is not working fine or you are not able to see the recently performed changes on the database schema while writing your T-SQL code in the SSMS query editor. It is necessary to refresh the local cache of the IntelliSense feature in such cases.

To refresh the local cache of the Microsoft IntelliSense feature, open a new query window, click on the Edit menu, open IntelliSense list and click on the Refresh Local Cache option. Or just use the keyboard shortcut key CTRL+SHIFT+R, as shown below:

IntelliSense tool also provides the Quick Info option that helps with displaying the complete declaration for the database objects in the T-SQL code by moving the mouse pointer over the supported database objects or typing the (Ctrl+k, Ctrl +I) keyboard shortcut. The object declaration will be displayed in a yellow pop-up window as shown below:

Microsoft IntelliSense provides other options that can be shown in the Edit menu:

For example, choosing the List Members option (or typing the Ctrl+J keyboard shortcut) will provide the list of database objects in the parent schema or object:

Microsoft IntelliSense can also show the list of parameters with including numbers, names, and types of the parameters that are required by a function or stored procedure. The info about parameters will be displayed automatically when you write parenthesis after the function or stored procedure name. The parameter in bold indicates the next parameter that is required as the type of a function or stored procedure. Type the (Ctrl + Shift + Space) keyboard shortcut to display the parameters info manually:

The Insert Snippet option of the IntelliSense feature provides the script template that is required for creating a specific database object without the need to memorize these templates. You will be able to call any template for an object you need to create from the IntelliSense list of the Edit menu or by typing the (Ctrl+K, Ctrl+X) keyboard shortcut:

On the other hand, the Surrounded With option of the IntelliSense feature provides the script template that is required for creating the surrounding borders for the BEGIN, IF or WHILE statements. You will be able to call one of these surrounding statements from the IntelliSense list of the Edit menu or by typing the (Ctrl+K, Ctrl+S) keyboard shortcut:

Microsoft IntelliSense is a useful tool that supposed to make the life of the database developers easier. But you may face many circumstances, in which the IntelliSense will not work as expected. For example, the IntelliSense may not parse an element of a commented code, a code that has syntax errors, or when the connection to the database is lost. In addition, not all T-SQL syntax elements are supported in the IntelliSense feature, such as the extended stored procedure parameters.

Worthy Alternative to MS T-SQL IntelliSense

The market of the SQL Server 3rd party administration and development technologies are full of tools that may help to overcome the issues we face with the built-in IntelliSense feature. One of the most rated of them is dbForge SQL Complete.

The dbForge SQL Complete is an add-in for SQL Server Management Studio and Visual Studio with fully-featured tools that helps with speeding up SQL code writing, saving time and effort during the database development process. With its smart context-based suggestions engine, this tool provides a rich set of features that help in improving the process of creating the code and building queries.

After downloading and installing due to very simple steps, the dbForge SQL Complete will be added to the SQL Server Management Studio as a new task menu, from which you can enable or disable it or perform different tasks:

Once enabled, the dbForge SQL Complete will analyze the context of the code you are typing in the SQL editor and display the most relevant keywords and object names with filtering these names list depending on the characters typed. SQL Complete automatically generates aliases for the tables and the views when you write the FROM statement with the ability to set up your own custom rules for assigning aliases to these objects. The example below shows how the SQL Complete performs a context-based code completion within the SSMS:

The SQL Snippets feature of the SQL Complete helps to insert the default code templates into the code with the ability to create own code snippets by pressing the Tab key. It reduces the time and effort spent by the developer for writing code:

Another useful feature of the SQL Complete is the Column Picker. When you write a SELECT statement, a list of all tables and columns will be displayed. After that you can choose the table you will select from and the list of columns that will be retrieved:

The SQL complete accelerates the code writing by suggesting all JOIN statements based on the foreign keys in these tables in addition to the conditions on these statements in a feature called the Statement Suggestion:

The asterisk (“*”) symbol used in the SELECT * statement can be easily replaced with an explicit list of columns from the table specified in the FROM clause by clicking on the Tab key as shown in the Column List Expansion example below:

SQL Complete also offers different features, such as the automatic completion for the CTE code, Code Refactoring, Code Highlighting, Quick Object Information, Code navigation, Linked Servers Suggestions and more. Install it in your machine and enjoy testing its features.

Useful Links:

• Transact-SQL Syntax Supported by IntelliSense
• IntelliSense (SQL Server Management Studio)
• Configure IntelliSense (SQL Server Management Studio)

Useful tool:

SQL Complete – write, beautify, refactor your code easily and boost your productivity.