Working with IntelliSense in SQL Server 2008 Management Studio

When the detail window is first displayed, the first parameter is bold. After you type that parameter value, the second parameter turns bold, and so on. For example, in Figure 1 the cursor is at the second parameter value and the second value is bold in the detail window.

The detail window closes after you type the ending parenthesis or you move the cursor to another part of the code. If you need to redisplay the detail window, move the cursor to the appropriate location (after the function's opening parenthesis or after the stored procedure's first space), and press CTRL+SHIFT+SPACE. To close the detail window, press ESC.

Completing object names

When you start typing the name of an object – like a variable, schema, table, column or function – IntelliSense displays a drop-down list with possible options for that position in the code. For instance, if you type after the first space that follows the FROM keyword, you get a list of objects such as databases and schemas. If you specify a schema name, followed by a period, you're presented with a list of tables and views.

The more letters you type in the object name, the closer you will match the name of the object. For example, if you select the Sales schema and then type Sal, the highlight moves to the SalesOrderDetail table, the first table in the drop-down list that begins with Sal. You can then use the arrow keys to move the highlight to the desired table, as shown in Figure 2 (in this case, SalesOrderHeader). Once the table is highlighted, press ENTER and the table name is added to your code.

Figure 2: Completing a table name for the Sales schema

Notice that a detail window also appears next to the highlighted table name. The detail window displays the identifier declaration (described in the next section). If the drop-down list is not displayed, move the cursor to the appropriate location, and then press ALT+RIGHT ARROW. To close the drop-down list without selecting an object, press ESC.

Viewing identifier declarations It can be useful to view details about different types of objects in your T-SQL code.

If you move your cursor over an object like a table or function, IntelliSense automatically provides details about that particular object. For example, if you move your cursor over a table name, the detail window displays the object type and the table's fully qualified name, as shown in Figure 3.

Figure 3: Viewing the fully-qualified name of the SalesOrderHeader table

The information IntelliSense displays depends on the object type. If a cursor is over a function name, a detail window displays the type of function, parameter information and the data type of the return value. On the other hand, the detail window for a column includes information like the object type, data type and whether null values are permitted.

Matching delimiter pairs

IntelliSense is also capable of matching delimiter pairs within your T-SQL. For the database engine, delimiter pairs include parentheses as well as BEGIN…END, BEGIN TRY…END TRY, and BEGIN CATCH…END CATCH code blocks. IntelliSense matches a pair by highlighting the opening and closing parentheses or key words.

Figure 4 shows a BEGIN…END block, with the keywords highlighted.

Figure 4: Matching a BEGIN…END pair

IntelliSense automatically highlights the delimiter pair when you finish typing the second delimiter. For example, in the above figure IntelliSense highlighted the BEGIN and END keywords as soon as I finished typing END.

Matching delimiter pairs is particularly helpful when there are multiple code blocks embedded in each other. The highlighting remains until you move your cursor. If you want to re-highlight a pair, you must delete and then re-type one of the letters (or parenthesis) in that pair. You can also use the CTRL+] shortcut to jump from one of the delimiters in a pair to the other.

Verifying incorrect syntax

IntelliSense automatically marks any errors in the syntax so you can see immediately whether there are any problems with your code. Any syntax that is incorrect is displayed with a squiggly red underline, as shown in Figure 5.

Figure 5: Viewing incorrect syntax in a SELECT Statement

Although it is handy to know immediately whether your code has errors, it is not always easy to tell from the red underlines where the problem might be. In the case of the statement shown in Figure 5, the problem is that the table name is incorrect (it should not end with s). As a result, all the column names are treated as errors, even though they are correct. You may have to dig sometimes to figure out what the actual problem is, particularly for more complex statements.

Modifying IntelliSense options

IntelliSense supports a limited number of options that can be configured.

To access the IntelliSense settings, open the Options dialog box by clicking Options in the Tools menu. Then, to configure the general IntelliSense settings, go to the IntelliSense node, as shown in Figure 6.

Here you can enable IntelliSense, select whether errors are underlined or if function names should be upper case in the object name drop-down list.

Figure 6: Modifying the IntelliSense options (Click to enlarge)

IntelliSense also lets you set statement completion options for all languages, T-SQL, and XML, as shown in Figure 7. In this case, you can determine whether to display the object name drop-down list or parameter information.

Figure 7: Modifying the statement completion options (Click to enlarge)

Although there are only a few options to configure, IntelliSense can still be useful when writing your Transact-SQL code, and while it may take some getting used to, it will be worth the effort in the long term.

Keep in mind, however, that IntelliSense does not support all Transact-SQL syntax, though it does support the statements most commonly used. For more details about supported syntax, see the topic Transact-SQL Syntax Supported by IntelliSense in SQL Server 2008 Books Online.

NOTE: For this article, I provide the shortcut keys for accessing IntelliSense features such as displaying parameter issues. However, Management Studio also includes menu options and toolbar buttons for these features. For details about different ways to access functionality, see the Using IntelliSense section in SQL Server 2008 Books Online.

Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at  http://www.rhsheldon.com.