Categories
DBA

Intellisence

What is Intellisense?

IntelliSense is Microsoft’s implementation of auto completion, best known for its use in the Microsoft Visual Studio integrated development environment. This feature is now included in SQL Server 2008 and this is available from SSMS 2008. When this feature is enabled it will auto complete functions, syntax, database names etc.

How to Enable Disable Intellisense

Method 1:
*) Open SQL Server Management Studio 2008 (No need to connect to Server)
*) Go to — > Tools –> Options
*) SSMS Option dialog will be shown as below, Expand Text Editor –> Transact – SQL and then click on Intellisense in the left pane

Intellisense_1

*) In the right pane you will be able to enable or disable intellisense and it’s configuration.
*) Click OK to continue

Method 2:
*) You can enable disable Intellisense option through SQL Editor toolbar as shown below

Intellisense_2

If you are unable to enable disable through above methods then you can directly hardcode the value in the registry. Registry path is “HKEY_CURRENT_USERSoftwareMicrosoftMicrosoft SQL Server100ToolsShellSqlEditor” and the key is “EnableIntellisense”. Screenshot of the registry is shown below.

Intellisense_3

Note: Editing registry values is upon your own risk.

Intellisense

Lets see how this works.
Selecting value from a table

Intellisense_4

Using Database

Intellisense_5

Variables declared in the current session are listed out!

Intellisense_6

Function parameters

Intellisense_7

Incorrect T-SQL command notification

Intellisense_8

Above are the sample screenshots of Intellisense features, there are lot more Intellisense will do for you.

Limitations

*) Intellisense is available from SSMS 2008 and hence it will work from SQL 2008 version and no backward compatibility provided. i.e If you connect to a SQL 2005 or SQL 2000 instance from SSMS 2008 intellisense feature doesn’t work.
*) Enabling IntelliSense for large Transact-SQL scripts can reduce the performance of slow computers
*) IntelliSense is turned off in the Database Engine Query Editor when the SQLCMD mode is set on.
*) If the logged in user doesn’t have privilege to some of the objects in the database then those objects will not be listed in intellisense.
*) Intellisense Compilation doesn’t list the objects if its created through another connection. (i.e) Consider your SPID as 51 and another user has logged in with SPID 52, If SPID 52 creates a object then it won’t be listed in SPID 51 Intellisense compilation. To get this object SPID 51 should disconnect and re-connect to the server.

Conclusion

Intellisense is a cool feature available from SSMS 2008 which helps developer a lot and reducing their time in T-SQL coding. Enabling Intellisense in a large T-SQL Scripts should be taken care. Backward compatibility can also be accompanied with this feature since it will take some time for organizations to migrate to SQL 2008.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*