11 October 2019

2 Comments

11 October 2019

2 Comments

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.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter
  • Mike Vessey

    so many good points here – i’m a big fan of the tool and it does a lot more than we think

    we are going the “poenix project way” aka devops and a very nice gentleman at redgate showed me how to export the static code quality settings from prompt into our GIT/devops build pipeline

    to be honest we’re lifting a few too many rocks and finding way too many scorpions, but I just stick them all on the technical debt list

  • Diana Bodell

    I discovered the Red Gate Developer Bundle about 10 years ago and became an immediate devotee. In particular SQL Prompt has improved my own personal productivity by at LEAST 100% with features like being able to select multiple columns with checkboxes, color coding my tabs, etc. I adore the Tab History because I don’t know how many times I have mistakenly closed a tab I needed without having saved it somewhere. I used to be a consultant and each client I went to I requested that they at least purchase SQL Prompt for me since they would save a lot of money helping me be more productive. Everyone I show it to wants it. Best Intellisense tool there is!

You may also like

  • Article

    SQL Prompt Hidden Gems: The SSMS Results Pane

    Every time you need to reuse the query results from SSMS, for example to populate another table, or to search for matching rows in another table, it will inevitably mean a lot of manual tweaking to the get the results into the right format. Louis Davidson uncovers three hidden Prompt gems (Open in Excel, Script as INSERT, Copy as IN clause) that can remove all this pain.

  • Community event

    Raleigh Code Camp

    Redgate will be remotely sponsoring the Raleigh Code Camp 2019. Code Camp is a place for local software developers to come and learn from their peers. This community-driven event has become an international trend where peer groups of all platforms, programming languages and disciplines band together to bring content to the community.

  • Article

    How we used your feedback to improve parentheses formatting in SQL Prompt 8.1

    After we released SQL Prompt 8, our priority was to continue to support the new formatting engine by fixing any bugs and reviewing feedback from users. One of the recurring patterns we noticed in your feedback regarded parentheses formatting. Some of you told us you couldn’t format the parentheses exactly the way you wanted to.

  • Article

    How to record T-SQL execution times using a SQL Prompt snippet

    Phil Factor shares a SQL Prompt snippet called timings, which he uses as a standard testbed for getting execution times for procedures and functions.