4 September 2017
4 September 2017

SQL Prompt Tip: how to control when the suggestion box pops up

By default, SQL Prompt shows code auto-completion suggestions automatically, and continuously. Phil Factor shows how to control this behavior, for the times when you need it to be a little less intrusive, such as when working through more intricate coding problems that require careful thought.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

By default, SQL Prompt’s code suggestion box pops up whenever it thinks it can make a useful suggestion, which is at virtually every keystroke. That isn’t the way that many of us want it to work, or at least not always. If you’re working on a complex coding task where you don’t want to be interrupted, one option is to switch auto-complete suggestions on and off, via Ctrl+Shift+P. However, we don’t recommend you do this regularly, because each time it triggers Prompt to query the database to refresh its database metadata. The better alternative is to leave suggestions enabled all the time, but control their behavior. You have two choices: either stop them displaying so frequently, or stop them displaying unless you request them, using Ctrl+Space.

Here’s how to do it: open the SQL Prompt menu, click Options to get the Options window and then go to the Suggestions > Behaviour pane.

Less frequent auto-suggestions

By default, Show code suggestions is enabled (if it’s not, enable it), and Automatically show suggestions… is also enabled and set to between 0 and 70 milliseconds (depending on which version of Prompt you have installed). Leaving both options enabled, simply increase the automatic suggestion frequency to, say, 5000 ms, or whatever you feel will work best for you.

Control frequency of Prompt auto-complete suggestions

Hit OK to save your setting.

Now the suggestion box will pop up only after a 5-sec delay. Don’t forget that you can still get suggestions ‘on demand’ just by pressing Ctrl and then hitting the space bar (Ctrl+Space).

Suggestions on demand only

If you want Prompt to show suggestions only ‘on demand’, simply uncheck the Automatically show suggestions…box while leaving Show code suggestions box enabled.

Prompt auto-complete suggestions on demand only

Hit OK to save your setting. Now you’ll never see auto-complete suggestions unless you hit (Ctrl+Space).

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly in SQL Server Management Studio and Visual Studio.

Find out more

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Finding code smells using SQL Prompt: TOP without ORDER BY in a SELECT statement (BP006)

    Using TOP in a SELECT statement, without a subsequent ORDER BY clause, is legal in SQL Server, but meaningless because asking for the TOP x rows implies that the data is guaranteed to be in a certain order, and tables have no implicit logical order. You must specify the order.

  • Article

    Problems Caused by Use of the SQL_VARIANT Datatype

    Phil Factor illustrates the 'quirks' of the SQL_VARIANT datatype and why it's best to investigate when SQL Prompt alerts you to its use. It is only safe to store data as a SQL_VARIANT, if you explicitly convert it to its true type before you use it.

  • Article

    SQL Prompt Code Analysis: A Hint is Used (PE004-7)

    Phil Factor suggests a philosophy of "the SQL query optimizer knows best" when it comes to choosing the right execution plan. Use hints as a last resort, and evaluate them carefully whenever SQL Prompt warns you of their presence in your SQL code.

  • Article

    Finding code smells using SQL Prompt: procedures that lack schema-qualification (PE001)

    SQL Prompt will warn you if it spots use of EXECUTE, without specifying the schema in which the stored procedure resides, because it can cause slower execution times, or even result in running of the wrong procedure.

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly