Data Management

Using IntelliSense in SQL Server 2008

Microsoft has finally released a SQL Server product that includes IntelliSense for writing TSQL statements. In this article, Tim Chapman shows the ins and outs of using IntelliSense in SQL Server 2008.

If you are used to working in the Microsoft Development Environment, you're likely grown accustomed to using IntelliSense. IntelliSense, a feature which reads internal metadata and provides a list of available objects and properties while you are developing, I feel is a wonderful tool.

However, this feature has yet to be available to the TSQL language inside the SQL Server Management Studio. I am happy to say that SQL Server 2008 finally includes TSQL IntelliSense, which provides a listing of available objects to use in SELECT statements, DML statements, and stored procedure calls, along with numerous other operations.

Prior to SQL Server 2008

IntelliSense was available before SQL Server 2008 from third-party products. Red-Gate has a pretty good IntelliSense product named SQL Prompt Pro that is available for SQL Server versions prior to the 2008 release.

IntelliSense in Action

So, let's take a look at how you can use IntelliSense in SQL Server 2008 Management Studio. The feature is ON by default, but you can turn it OFF or ON by using the Tools | Options menu in Management Studio. Once the menu is open, navigate to Text Editor | Transact-SQL | IntelliSense to enable or disable the feature.

First, I'll create a table that we can use for our IntelliSense examples.
IF OBJECT_ID('SalesHistory')>0     
 DROP TABLE SalesHistory;
 GO
 CREATE TABLE [dbo].[SalesHistory]
 (     
         [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,     
         [Product] [char](150) NULL,     
         [SaleDate] [date] NULL,     
         [SalePrice] [money] NULL
 )
 GO  

Now that I have my table created, I can run queries against it. For the purpose of this article, it's not necessary that my table contains records of data, just that the table exists.

In the example below, I type a SELECT statement against the SalesHistory table. Notice the dropdown list of the available fields to choose from the table. Also notice that I first typed the name of the table and reference it through an alias. This is not absolutely necessary, but it does make things easier for IntelliSense to pick up on.

There is another feature available in SQL Server 2008 Management Studio that is worth mentioning here. In the above example, you can notice the +/- box, which indicates a code block. This great new feature allows you to expand and collapse blocks code when you're developing. While this isn't a flagship feature of the new release, it does make reading your TSQL code a LOT easier.

IntelliSense also works great for data manipulation language (DML) statements as well. In the example below, I issue an UPDATE statement against the SalesHistory database, and am given a list of available fields to UPDATE.

There are certain scenarios when IntelliSense is not going to provide a listing of available objects. For example, IntelliSense in SQL Server 2008 is not going to work when there is an error in your TSQL code, when typing string literals, or when issuing comments. In the example below, IntelliSense lets me know that I have mistyped a table name.

Benefits and Drawbacks

The new IntelliSense feature in SQL Server 2008 makes database development easier, especially for those systems where you are not familiar with the database structure. It recognizes field names for SELECT statements and DML statements, local variables, PIVOT and UNPIVOT statements, and common table expressions (CTE). However, from my testing of the feature, it is not perfect. For example, in my testing I was unable to get IntelliSense to discover stored procedure parameters for a procedure I created in the same session. However, don't take my word for it, go try it for yourself.

 

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

4 comments
OstapM
OstapM

FYI: SQL Complete 3.0 by Devart is an SSMS add-in that provides SQL intellisnse for SQL Server 2000, 2005, 2008, 2011, SQL Azure and SQL Server Compact Edition. It is also easily integrated into Visual Studio 2008 and 2010 Check out product page: http://www.devart.com/dbforge/sql/sqlcomplete/

rroman81
rroman81

Tim, you should be able to recreate Intellisense Cache by hitting Ctrl + Shift + R.

Vette95
Vette95

I found the same problem when I updated a table structure and added a column. The IntelliSense did not recognize the new column was part of the table I had jsut altered. However, if you go to Edit->IntelliSense->Refresh Local Cache (or use CTRL+SHIFT+R) it should recognize created or altered items correctly. Hope that helps.

theo.spears
theo.spears

Just to clarify, SQL Prompt fully supports SSMS 2008 and SQL Server 2008. You can find more details on the website (see the link the article above). Theo Spears Red Gate Software

Editor's Picks