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.
DROP TABLE SalesHistory;
CREATE TABLE [dbo].[SalesHistory]
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [char](150) NULL,
[SaleDate] [date] NULL,
[SalePrice] [money] NULL
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.
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.