SQL Productivity with SQL Prompt

There is an old joke that upgrading to the latest SQL Server is wasted on some DBAs, because they will still stick mainly to what worked in SQL Server 2005. This type of DBA is becoming rare, in my experience, but there is still some truth in the idea that many of us don’t get the ‘full power’ from our SQL Server tools. We work with them as they come, ‘out of the box’, and use only a fraction of their features. The time to explore ‘new stuff’, at least as much as we’d like to, remains elusive.

SQL Prompt is a great tool for helping individual developers, and the team as a whole, be more productive when writing SQL. However, I suspect that many users never find out about many of its features, especially the newer enhancements. For the parts they use, they don’t always find the time to work out how to configure and control their behavior to best suit their needs.

If this is true for you, I hope this article will help in a small way. I’ll cover Prompt’s best-known productivity feature, Code Completion and IntelliSense, and specifically the ways I control how it works to suit my needs. I’ll then describe briefly some of the ‘lesser known’ features that boost team productivity, and that help prevent unnecessary mistakes, and the time lost recovering from them.

Write SQL faster

As a database developer, I always need enhanced productivity, and I use SQL Prompt’s Code Completion and IntelliSense features all the time. Yes, IntelliSense is available natively in SQL Server Management Studio, but it does little more than list the available objects.

SQL Prompt’s IntelliSense is more helpful. It provides auto-completion suggestions for the columns and tables, helps fill in JOIN conditions, GROUP BY clauses, shows object definitions and dependencies, and more. Of course, as developers, we will want to be able to control precisely how this feature works, to optimize our productivity, since we’ll be using it a lot.

One recent enhancement (v9.5.3) is the option to make the most-recently used objects rise to the top of the suggestion box. As usual, we can configure it within the SQL Prompt Options menu, in this case within Suggestions > Behavior:

In the following example, I’ve typed in two previous queries, the first on the users table and the second on the sales table. Now, as I type in the third query, I see that the sales and users tables are the first two suggestions in the list, separated from other choices by a thin grey line.

The Suggestions > Behavior section contains other useful options, such as to allow us to control when the suggestion box pops up, show object definition boxes and others.  Another relatively new and simple enhancement is the option to “Make popups transparent…” . This is useful when you’re editing in the middle of a query, and the popup suggestion box would otherwise obscure the remainder of the query. This is especially irritating when the change you need to make is related to the statements that are hidden.

Simply press the Ctrl key and the pop box becomes temporarily transparent.

You’ll also find other useful controls elsewhere in the Suggestions section. For example:

  • Suggestions > Connections – to control the databases and schemas for which suggestions are made. This can remove a lot of ‘noise’, instantly
  • Suggestions > Join conditions – to specify the criteria for suggesting JOIN conditions

Improve SQL standards

SQL Prompt has a lot going on, beyond IntelliSense. It also offers a set of features that will help improve SQL coding standards, such as automatic formatting, reusable code snippets, and code analysis. In each case, these features are designed to allow you to define team standards and then share them and implement them consistently.

These features make SQL Prompt a great team collaboration tool. They help me work with the developers to instill good SQL coding practices, prevent repetition, and to reduce the number of code issues that make it through to the production code.

I’ve described how I use these features previously, in my article Driving up database coding standards using SQL Prompt, so I won’t repeat those details here. However, it’s worth checking the release notes and documentation from time to time, as changes and additions to these features are frequent. For code analysis, for example, SQL Prompt will now auto-fix a growing list of some of the simpler code issues.

Avoid accidental data loss

During my career as DBA, I’ve always wanted to keep tight control over who has access to our production SQL Server instances, from SQL Server Management Studio. However, it is often hard to dissuade organizations away from allowing people to use this “quick and easy” way to make changes, despite the obvious dangers. I’ve struggled with this, trying to show better alternatives; processes for making fixes safely, with testing, and then releasing them gracefully. Changing minds is not simple, though, especially in an organization that frequently repeats mantras such as “all our customers should have access via SSMS“. Of course, sometimes a DBA who is responsible for the performance of production database, and the security of the data, has little alternative but to make urgent fixes, directly.

For all these cases, SQL Prompt has a couple of useful features that help protect me, and any developers who also have direct access to staging or production servers, from accidentally making damaging database changes, or from accidentally dropping objects or data, or making more extensive changes than intended by missing out an important filter. It can take hours of painstaking work to ‘undo’ the effects of such accidents.

SSMS tab coloring

This feature will color-code every “tab” at the top of a query pane, and the status pane at the bottom, according to what server, or type of server, each tab is connected. Once you get used to the idea that a bright red tab means “I am connected to a production server“, it’s much harder to accidentally execute code in a query pane that is connected to the “wrong” server!

For full details about how this feature works, see Phil Factor’s SQL Prompt Safety Net Features for Developers.

Execution warnings

There are many ways to destroy data or objects, accidentally or otherwise. You highlight the DELETE or UPDATE statement but accidentally omit the WHERE clause. There is an unwanted DROP or TRUNCATE command in your code, perhaps a remnant of some test code, or some untested and incorrect code that modifies data based on some business logic. There is no way, natively, to stop the execution of such commands, without implementing additional logic or using a trigger.

SQL Prompt’s Execution Warnings can alert us when we attempt to execute an unconditional command that may result in unintended changes, or data loss. The idea is based on the syntax check engine. The tool sniffs the command and can detect, and raise warnings for:

  • UPDATE or DELETE statements without the WHERE condition, including when used in an INNER JOIN, or when creating or altering a procedure or trigger.
  • DROP and TRUNCATE statements (v9.5.6 and later)

For example, the following DELETE statement will remove any rows from table Table1 with ID values that match those found in table Table2:

By default, SQL Prompt will issue a warning when we hit “execute” for this statement:

However, since I use this kind of statement many times, the ability to disable such warnings is also important, for productivity. You can control which warnings SQL Prompt will raise from its Options menus (Suggestions > Warnings & highlighting > Execution Warnings). As indicated, even when enabled, these warnings aren’t raised when working with temporary objects.

In each case, if you decide not to go ahead and execute the statement, you’ll then see a green, wavy line and a tooltip, explaining why the warning was raised. Here’s an example for a DROP statement:

Conclusions

SQL Prompt is a great SQL productivity tool. I especially like that it gets frequent improvements to all its features and because of this, it is worth asking for a feature or enhancement if you need it, using the UserVoice service.

I love this kind of approach. End users, who are working on SQL Server daily, share their knowledge, and ask for changes, the team respond, and the product gets better. This is why SQL Prompt has, over the years, become one of the best tools for developing T-SQL code.