While writing Transact-SQL queries in SQL Server 2008 Management Studio, you may have noticed some changes like detail windows and drop-down lists that appear as you type code.
These occur because of IntelliSense, an auto-completion system supported in the most recent version of Management Studio that provides easy access to language elements as you write your queries.
Specifically, IntelliSense displays parameter definitions, completes object names, provides identifier declarations, matches delimiter pairs, and marks incorrect syntax. The better you understand this functionality, the more productive your coding experience will be.
Displaying parameter information
IntelliSense shows parameter information for functions and stored procedures. The information is displayed in a detail window and includes the name and type of each parameter.
For example, Figure 1 demonstrates the parameter information for the DATEADD function. Notice the information includes the data type of the returned value.
Figure 1: Displaying the parameter information for the DATEADD function
[IMAGE]
Parameter information is displayed automatically under the following conditions:
- For functions, the information is displayed as soon as you type the opening parenthesis that follows the function.
- For stored procedures, the information is displayed as soon as you type the space after the stored procedure name.
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 mo...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
ve 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
[IMAGE]
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
[IMAGE]
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
[IMAGE]
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
[IMAGE]
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)
[IMAGE]
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)
[IMAGE]
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.
ABOUT THE AUTHOR:
[IMAGE]
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. His books include Beginning MySQL (part of the Wrox Programmer-to-Programmer series), SQL: A Beginner's Guide (based on the SQL:1999 standard), MCSE Training Kit: Designing Highly Available Web Solutions with Microsoft Windows 2000 Server Technologies, and MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation. You can find more information at http://www.rhsheldon.com.
