T-SQL IntelliSense enhancements in SSMS –Denali

 

Kamal Bhatt (kbhatt@microsoft.com), a colleague of mine in the SQL Server Manageability Team, wrote up this useful blog post on some of the new Intellisense enhancements in SQL Server Code Name “Denali”.

---

In this blog post, I will go over three IntelliSense enhancements that have been introduced in SSMS-Denali. They are as follows:

a.) SUPPORT OF CODE SNIPPETS

b.) SUBSTRING MATCHING IN COMPLETION LIST

c.) VALIDATING BREAKPOINTS

 

a.) SUPPORT OF CODE SNIPPETS

Code Snippet is a valuable tool that provides better editing experience of TSQL Code for Database Developers/DBAs. The snippet itself is an XML-based template that describes various fields having default values. These fields are highlighted after the snippet is inserted and user can tab through each of these fields and edit default values assigned to them. Heavily used TSQL constructs can be provided through Code Snippets functionality thereby increasing productivity.

There are two types of Snippets: Expansion Snippets (Snippets of code you can quickly insert into your code e.g. Create Table DDL) and Surround –With Snippets (Snippets of code that envelope your code e.g. Begin End construct)

How to invoke Code Snippets functionality?

There are three ways of invoking the Code Snippets functionality:

a) Right Click Context Menu on TSQL Editor.

b) Open TSQL Query Editor and go to Edit Menu => IntelliSense option

 

c.) Keyboard Shortcuts on TSQL Editor ( Ctrl K + Ctrl X => Insert Snippets, Ctrl K + Ctrl S => Surround With Snippets)

By choosing any one of the methods listed above, Code Snippet Inserter gets invoked on TSQL Query Editor. Various Snippets have been organized under categories. User can select a particular category to see list of various snippets under it and select one of them e.g. Below Screen shot shows that user has selected Scalar function under “Function” category.

Note: If you observe that when first instance of “param1” is highlighted, the second instance of “param1” is also recognized. It is surrounded by a dotted line which means that if you change the name of param1 at first place, automatically second “param1” also gets changed.

Currently SSMS comes with small set of pre-defined snippets. We have enabled the ability for the users to add user-defined code snippets easily.

How to create your own Snippets?

Let us create code snippet that contains basic code for TRY…CATCH construct. We will call this snippet as “try-catch” snippet.

As describe above, Snippets is an XML file, basic outline of Snippet looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="https://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">

<CodeSnippet Format="1.0.0">
<Header>

…

</Header>
<Snippet>

…
</Snippet>
</CodeSnippet>

</CodeSnippets>

Let us create code snippet that contains following basic code for TRY…CATCH construct:

BEGIN TRY

-- Try Block Statements

END TRY

BEGIN CATCH

-- Logic for Error Handling

END CATCH

                                                            Code Snippet for TryCatch

Important Step: Create a new folder say “CustomSnippets “ and save above XML code as try.xml file inside it.

How to add Code Snippets in SSMS after you have created it?

Now we would want to add the newly created custom snippet folder (created above) as one of the folders for SSMS to search for custom code snippets. This can be achieved through Code Snippets Manager which can be invoked from Tools menu:

It will launch Code Snippets Manager (If you are familiar with Visual Studio IDE – This is standard Snippets Manager which provides various functionalities like Adding/Removing/Importing Code Snippets). Using “Add” button of Snippets manager we register code snippets inside it, in SSMS.

 

On invoking Code Snippet Inserter, you will observe that CustomSnippets becomes one of the category and under it try-catch snippet exists which can be inserted.

Above steps, clearly shows how to create a code snippet and register it with SSMS, you can create complex code snippets of various regular TSQL tasks and make your life really easy.

b.) SUBSTRING MATCHING IN COMPLETION LIST

If you have used IntelliSense in SQL Server 2008/2008 R2 Management studio, you must be aware of completion list (drop down list) , which provides a listing of available objects to use in SELECT statements, DML statements, and stored procedure / function calls, along with numerous other operations.

E.g. On connected TSQL Query Editor if we type “exec master.sys.” we will get following completion list. It contains all the functions/stored procedures that are present inside sys schema of master database.

In previous releases as user goes on typing after “exec master.sys. ” we used to do prefix matching and if such a prefix exists in the list, we scroll down the list to that position and highlight the entry. However in several scenarios, this is not much helpful to the user incase he remembers only a substring of the object he wants to select. This enhancement implements Completion List Filtering, which filters the original list based on substring matching, as user goes on typing.

Let us take an example to make it clear:

Suppose user wants to create a new login, he only remembers that there is some “addlogin” stored procedure inside sys schema of master database. So as he types “addlog” after “exec master.sys.” he will observe that dropdown list keeps getting refreshed after each character having only those entries which contain the substring that user has typed so far. This is really helpful to the user since he does not have to go through the entire list to figure out the desired stored procedure; instead completion list is getting shortened with relevant items. Below are two screenshots which will illustrate this completion list filtering.

 

DBA’s/developers working on databases having thousands of objects will love the magic of Completion List Filtering !!

c.) VALIDATING BREAKPOINTS

A breakpoint is a marker which indicates that program execution should stop at a particular point while running the program in a debugger. This enhancement aims at validating breakpoint locations and providing immediate feedback to the user when he places a breakpoint. Since TSQL Language Service has a fairly intimate knowledge of the source code being displayed, it can readily determine what constitutes a valid breakpoint location. Some of the highlights of this feature are as follows:

a.) Whenever user places a breakpoint on a construct if it is a valid location, the entire statement (whether defined in single line or multiple lines) gets highlighted, there by clearly showing the breakpoint location. ( In previous releases we don’t highlight the entire construct ,only red circle in-front of line was shown and hence it was difficult for user’s to identify exact statement on which breakpoint is placed)

a.) If multiple statements are defined in a single line, user can now place a breakpoint on individual statement also (In previous releases it was not possible to place multiple breakpoints on a single line)

a.) If a breakpoint is placed on a location that is not considered as valid, user will get immediate feedback on the status bar. For example, if a breakpoint is placed on a comment, the breakpoint is not considered valid because there is no code at that location in the source code. (In Previous releases user was able to place breakpoint on line which contains comments and it was validated during debugging time at which debugger disables that breakpoint.)

 

Kamal Bhatt (kamalbh@microsoft.com)

Comments

• Anonymous
  June 15, 2011
  Why do I have to type in the database name and schema before I get a list of tables in Intellisense?  For example if I type SELECT * FROM while connected to database MyDB, I only see the tables from the master database.  But if I type SELECT * FROM MyDB. Then I see the list of tables in MyDB.  Is that how people write TSQL?  Maybe I am missing something.

• Anonymous
  October 16, 2011
  The comment has been removed

• Anonymous
  May 31, 2013
  Good one

• Anonymous
  May 31, 2013
  Good one !!!