• SQL Prompt
• SQL Code Completion and IntelliSense

SQL IntelliSense and Autocomplete in SSMS

When it was launched in 2005, SQL Server Management Studio (SSMS) was just a simple editor. Nobody does ‘simple’ nowadays, because SQL, like any other language, has grown enormously; I challenge even the cerebrally-gifted among you to remember all the windowing functions, or the precise syntax of the OpenJSON function, while in full flight. It needs ‘IntelliSense’, or prompting as we’d want to call it. IntelliSense has been introduced gradually into SSMS over the years, and is the bread-and-butter of SQL Prompt

Before SSMS, there was Enterprise Manager with its query analyzer. Long before SSMS introduced their version of IntelliSense in SQL Server 2008, the first iteration of SQL Prompt provided IntelliSense to SQL Server 2000’s Query Analyzer, which was otherwise a plain text editor. SQL Prompt was designed in the tradition of the Microsoft Integrated Development Environments, to deliver the same IntelliSense enjoyed by Visual C# and Visual Basic users. It supported SSMS as soon as it was released.

Over the years SQL Prompt has continued to introduce new features, such as code analysis and code formatting. SSMS has tended to develop complementary features, such as the useful ‘template’ functionality, outlining statements and underlining errors.

IntelliSense and Code Completion in SQL Server Management Studio

SSMS’s IntelliSense provides nine main features:

1. Parameter Info -informing the user about the name and datatype of the parameters of functions
2. Tooltips for quick info (alias Quick Info)
3. Identifier completion (alias complete word or code completion)
4. Identifying syntax pairs (alias Delimiter Matching, Brace Matching or Parenthesis Matching)
5. Snippets
6. Code formatting (queries only: this isn’t categorized as IntelliSense in SSMS)
7. Changing the case of built-in functions
8. Underline errors
9. Outline Statements (a.k.a. code outlining) to collapse and reveal statements in long code files

There are several different ways of using SSMS IntelliSense. You can have it switched on by default, or you can invoke it only when you need information. You can also use it in a way that it both makes code suggestions and does code completion, or just the former. You can opt to switch off or on just certain parts of the functionality. I use it alongside SQL Prompt, being careful not to enable clashing features. You can get the best of both worlds.

I always like to have SSMS syntax checking (underline errors) turned ‘on’. Although not included in the IntelliSense category, I also like SSMS’s Query Designer, the quick query creation tool, and I like SSMS Templates because they are geared to all skill levels.

To turn IntelliSense features off or on, you can use the general tab within the Tools > Options window. Once in the options window, expand ‘Text Editor’, expand either ‘All Languages’, ‘Transact-SQL‘, or ‘XML‘, and click IntelliSense, where you can disable it entirely or just opt out of code underlining and outlining statements. For any IntelliSense feature that changes the cases of built-in function names, you need to specify your preference.

You can also

• Toggle IntelliSense on or off with CTRL-Q, CTRL-I
• Enable it with CTRL+B, CTRL+I
• Use a toggle on the SQL Text Editor ribbon to switch IntelliSense on or off. There are buttons to switch between code suggestion and code completion
• Change the IntelliSense filter level from the ‘Common’ tab to the ‘All’ tab using ALT+PLUS SIGN (+)
• Change the IntelliSense filter level from the ‘All’ tab to the ‘Common’ tab using ALT+PERIOD (.)

Statement completion is switched on by default but can be switched off or on by going to the ‘Tools’ > ‘Options’ menu and in the list of options expand the ‘Text Editor’ option, pick ‘Transact-SQL’ followed by ‘General’. Once you’ve done that, you can uncheck or check the checkboxes for the features of statement completion you want to change:

You can invoke these features at any time by using this menu from ‘Edit’ > ‘Intellisense’

As well as the keyboard shortcuts listed in this window there are several others that control IntelliSense:

Copy parameter tip	CTRL+SHIFT+ALT+C
Paste parameter tip	CTRL+SHIFT+ALT+P
Jump between syntax pairs	CTRL+]
Display the Code Snippet Manager	CTRL+K, CTRL+B
Changes the IntelliSense filter level from the Common tab to the All tab.	ALT+PLUS SIGN (+)
Changes the IntelliSense filter level from the All tab to the Common tab.	ALT+PERIOD (.)
list parameters	CTRL+SHIFT+SPACE
list of objects or members	CTRL+J
Quick Info	CTRL +K, CTRL +I
Toggle Completion mode	CTRL +ALT+SPACE
Invoke the query editor for the selected query	CTRL + SHIFT + Q

If you are not a Liberace of the computer keyboard, with the ability to remember all these keystrokes, it is useful to have the text editor toolbar (View > Toolbars) visible, which allows you to switch on and off the main IntelliSense options.

To refresh the local cache of the Microsoft IntelliSense feature, you can either use the IntelliSense window (Edit > IntelliSense) and click on the Refresh Local Cache option, or just use the keyboard shortcut key CTRL+SHIFT+R.

The SSMS IntelliSense features

Parameter Info

This feature opens a ‘parameters list’ of the names, and datatypes of the parameters of a function or stored procedure. You can invoke this at any time by typing CTRL+SHIFT+SPACE

After you’ve typed the name of a function, Type the open bracket (parenthesis) as usual and, if the feature is enabled, you’ll see the complete declaration for the function with its parameter list. To get the same information for a procedure, type the name and a space. And you’ll see the parameters in a pop-up window just under the insertion point. In both cases, the first parameter in the list appears in bold, and as you type the parameters, the bold font changes to reflect the next parameter that you need to enter. Hitting ESC will rid you of the list. Otherwise you continue typing. until you have all the parameters. If you are typing in a function, you close the parameter list when you type the closing bracket.

You occasionally need to type a function as a parameter to another function, in which case the parameter list displays the parameters for the inner function.

Quick info

The Quick Info option gives you information in a tooltip about the T-SQL statements that it currently ‘supports’ by moving the mouse pointer over it, or typing the (CTRL+K, CTRL+I) keyboard shortcut. Normally, the Quick Info displays the expression declaration but when in debug mode, it displays the name of the expression and its current value. If it is a user object, the object declaration will be displayed:

Complete word (code completion)

This feature will prompt you with all the alternatives for what you are typing. This list includes all the likely alternatives for the next word that you are trying to type.

You invoke this in one of three ways:

• Click the ‘Complete Word’ button on the Text Editor toolbar.
• In the Edit menu, point to IntelliSense, and then click ‘Complete Word’.
• Press ALT+RIGHT ARROW.

In this example, it has listed all the possible objects containing the word SUM but not in any order of likeliness. It is far more likely to be SUM than anything else, so you’d expect it to be the first option, but unfortunately, they list the alternatives in strict alphabetical order.

You can toggle between code completion and code suggestions by pressing CTRL+ATL+SPACE.

SQL Prompt’s ranked code completion suggestions do a much better job of floating the most likely choices to the top; I’ll cover this a bit more later.

Identifying syntax pairs

This allows you to identify the matching curved bracket, it also does BEGIN/END pairs, BEGIN TRY/END TRY pairs and BEGIN CATCH/END CATCH pairs. Unfortunately, it doesn’t do square-bracket delimiters or quoted delimiters for identifiers. (SQL Prompt will highlight the entire identifier, which seems a better option to me).

The syntax pair matching IntelliSense highlights both the lead and the closing elements of a pair of delimiters. This lets you visually identify code blocks and check for mismatched pairs of delimiters. You can highlight the matching pair by typing the final letter that completes the typing of the syntax pair (e.g. BEGIN/END). The highlighting happens after you type a letter and the result is a syntax pair. If, for example you type in, move, or alter either a BEGIN or END keyword, the pair are highlighted. The delimiter pair remains highlighted until you move the cursor. You can turn the highlighting back on by deleting and retyping any letter in either member of the pair.

SQL Prompt will highlight the pair just if you move the insertion point onto one or the other.

Snippets

Snippets are strangely parallel to the traditional templates in SSMS. Templates are, however carefully maintained by Microsoft with templates containing code for all the new features, whereas snippets only have the basics. You can add whatever snippets you like, using the Snippets Manager (CTRL+K, CTRL+B). The snippets are held in a directory, which be default is:

%HOMEPATH%\Documents\SQL Server Management Studio\Code Snippets\SQL\My Code Snippets

There are subdirectories for each category of snippet, as displayed in the Snippet Manger menu. The directory can be shared. Each snippet is an individual XML file and are currently two types of snippet:

• ‘Expansion’ – pastes in the contents of the snippet as though it were on the clipboard
• ‘surrounds with’ – places the snippet text on either side of the selected text

When you invoke the snippet manager, you’ll see this:

You can create new snippets, but there is no nice form to help you do it, so you’ll need to be confident with creating raw XML files.

You invoke a snippet by right-clicking in a query pane and choosing Insert Snippet… from the context menu. The Declarations section will allow you to declare strings (called literals) which you can modify after inserting the snippet, so that, if the same literal appears in several places, it gets modifies in all occurrences. You can use the TAB key to move from one replacement point to the next (SHIFT+TAB will move you to the previous), and you can hit CTRL+SPACE to invoke IntelliSense at this point. You can then select an item from the list or type a replacement of your choice.

It is possible to use templates as snippets and you can still invoke the form to fill in the template parameters, after pasting in the snippet. There are two special values in a snippet:

1. $end$ marks the location to place the cursor after the code snippet is inserted
2. $selected$ represents text selected in the document that is to be inserted into the snippet when it is invoked

If you wish to quickly invoke a ‘surrounds-with’ snippet, you just hit CTRL + K, CTRL + S and tap in the name of the snippet. The equivalent keystrokes for the ordinary ‘expansion’ snippet are: CTRL + K, CTRL + X.

SQL Prompt snippets work in a similar way but offers a bit more versatility, allowing various replacement points, or placeholders, within the snippet code. You can also use SSMS template parameters in Prompt snippets. They are also easier to create and adapt. This article, Templates and Snippets in SSMS and SQL Prompt provides an in-depth comparison.

Code Formatting

SSMS code formatting only works for SELECT, DELETE, INSERT, or UPDATE. It is not listed as an IntelliSense feature, as such, but uses the ‘Changing the case of built-in functions’ option.

Select the code that you wish to format and then press CTRL+SHIFT+Q. The Query Designer will open. Select the SQL and then right-click the selection to bring up the context menu. In this menu, select ‘verify SQL Syntax’ which will apply the casing that you have selected for keywords and will so some basic formatting for the SQL, but there is no control over the style of formatting.

SQL Prompt’s code formatting by contrast represents the current “state of the art”. It allows a lot more control over the formatting options and allows users and teams to maintain individual and house styles, and switch between them easily. See Controlling how SQL Prompt Formats your Code: The Knobs and Dials.

Changing the case of built-in functions

This can happen automatically as you type, and in the query designer.

SQL Prompt uppercases built-in functions and datatypes, and reserved words, by default, but gives you the choice of lowercase or camelCase preferences instead (SQL Prompt > Options Format-Case)

Underlining errors

This happens automatically when IntelliSense is switched on although you can disable it individually, as described earlier. If you hover your mouse over the underlining, a tooltip will reveal the syntax error. Occasionally, you’ll find that a word is underlined in red, but the code executes fine. It is only an indication but is extremely useful, nonetheless.

SQL Prompt doesn’t do ‘Underline errors’ syntax checking.

Outlining statements

This feature comes under the heading of IntelliSense and can be turned off from the ‘Tools’-‘Options’ window as illustrated previously in the article. It allows you to collapse sections of code if you are editing long screeds of SQL in the same query window.

Native IntelliSense and SQL Prompt

Rather than producing a definitive comparison between native IntelliSense and SQL Prompt, I ought to start by saying that I use the best bits of both. What you consider to be the best bits will vary from person to person. I would, for example, never switch off SSMS’s ‘underline errors’ even though I don’t always trust every error. I happily use SSMS templates occasionally but prefer Prompt Snippets over the SMSS ones as they are slightly more versatile. The outlining statements feature is useful. For SQL Prompt, I couldn’t live without its code formatting, tab history or code analysis, and I’m fan of its new “ranked suggestions” for auto-completion.

Let’s illustrate why you might want to use this dual approach. We start by just using SQL Prompt. We find the simplest database we can grab, and pretend we’re querulously trying to see what is in one of the tables, but we don’t, at this point know the table names, but we can recognize them. OK, it is Pubs again. We tap in Select * from and, the following list appears

“Hmm,” thinks SQL Prompt. “This guy is struggling a bit and it is obvious he is going to want to specify a table. What tables has he specified in a query recently? Hmm, he hasn’t so we do the tables alphabetically”.

Having realized that we wanted the employee table, we click it in the list:

When we click on employee, the table name gets filled in. Then, we either put the insertion point after the * of Select *, or we can click on the ‘idea’ icon, either of which will fill in all the column names

Which gives us …

We select it, and (in the default style) we format it to get:

OK. Good so far. We’ll do the same thing in SSMS. Ah. It started with a list of databases. I’m now scrolling down and finding all sorts of objects that I’d be unlikely to select * from, such as users.

Hmmm. Maybe I’ll try that dm_logconsumer_cachebufferrefs:

Msg 208, Level 16, State 1, Line 12
Invalid object name ' dm_logconsumer_cachebufferrefs.

Yes, this is every object in alphabetical order, regardless of whether it is appropriate. You’ll see from the scrollbar that it is a very long list. Only after you’ve tapped in the beginning characters of the table you want does it start to get useful but then what if you don’t know, or you just want to see what is there in the database? This, to me as a frustrated user under time-pressure, is un-intellinonsense.

To be fair, SQL Prompt once used to do this, and how it works now is just maturity and the effect of unimaginably methodical and painstaking work to get this sort of thing aligned with what the user most probably wants.

After this, SSMS can get rather cute, in this instance.

Type …

… and then hit the spacebar

…Yes, it was unambiguously a table called authors otherwise we’d have had a list

Select it and type CTRL+SHIFT+Q:

It opens the Query Designer. It isn’t categorized as IntelliSense, but it is what SSMS does best. You are now in an interactive and friendly environment for pulling in other tables if you want and selecting just the columns you need. I’d like to pretend that I’m much too clever to need this, but I can’t lie convincingly: I blush too easily.

A few minutes later and I’m motoring

Conclusions

As a user, I like to use a mix of SSMSs IntelliSense and SQL Prompt’s features. I particularly love SQL Prompt’s SQL Formatting, tab history, code analysis and ranked suggestions, but I also appreciate SSMS IntelliSense features such as error highlighting and code outlining. The developers of SSMS have adopted the sensible approach of specializing with features such as Templates, syntax highlighting and outlining, but also providing a sound basis for prompting, for those who can’t use an add-in such as SQL Prompt for this. SSMS is designed for add-ins, and there is a useful range of third-party add-in products.

To make it possible to make use of all these features from ‘native’ IntelliSense and the add-ins it helps to be able to quickly switch on the features you need for any particular task and switch them off when they get in the way. It seems that SSMS has provided many alternative ways of allowing this.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more