SQL Prompt 10: What’s New?
Tony Davis reviews the major new features of SQL Prompt 10, included improved 'ranking' of its code auto-completion suggestions, tab history improvements to make it easier to find 'lost' code, and auto-fixing of code that breaks code analysis rules.
SQL was invented in the 1970s by IBM researchers. It is a fourth-generation computer language, designed to be so close to normal written English that any English-speaker could learn it quickly. The idea was that a Business Analyst could, in response to a question about financial trends in the company, simply lean over to a shiny new terminal, tap in the SQL question, and receive the answer, all neatly aggregated and summarized.
History has not been kind to this idea. SQL has grown and been extended by various database vendors. Just to master SQL Server, you’d need to be familiar with the syntax of around a hundred and forty different statements or queries. Even full-time database developers resort to Google to work out some of the more arcane elements of syntax, such as Window functions.
Of course, the developers responsible for most database developments are not dedicated database types. They are end-to-end developers who need to keep many different languages, frameworks, and protocols in their heads. It isn’t easy to switch into ‘relational mode’ when your head is full of JavaScript.
This is why, when engineering SQL objects and code, so many teams have discovered the value of SQL Prompt, both as an individual coding productivity tool and as a DevOps collaboration tool. For a start, it increases the speed and efficiency with which any programmer can write SQL scripts. More than that, it encourages teams to implement and share good SQL coding standards and practices, promote code reuse, and so improve the quality of the code that reaches production systems.
SQL Prompt was first released to help with what we considered then were the ‘complexities’ of SQL Server 2005. The Prompt team have had to keep pace with the development of SQL Server ever since. With SQL Server 2019 appearing, SQL Prompt 10 brings improvements to all its most popular features. From ranked suggestions that place the suggestions most relevant to you at the top, to tab history improvements that let you find old tabs and to bookmark your favorites, the latest version of SQL Prompt gives you the tools to develop faster, improve code quality, and increase team productivity.
Code Completion and IntelliSense: ranked suggestions
Prompt’s IntelliSense helps developers and DBAs to write accurate SQL, faster. I’ve previously described it as “like using an interactive SQL syntax diagram“. Rather than having to analyze the documentation and work out the correct syntax the hard way, Prompt leads you through it, while you produce working code, faster.
SQL Prompt’s engine analyzes the syntax of your SQL statements, as you type them, and has knowledge of the underlying tables structures. At each keyboard stroke Prompt uses this information to suggest the most likely keywords, tables, columns, procedures and functions that you might need at that point in the task of creating your query or command.
SQL Prompt 10 introduces a new default way of ordering these suggestions, called ‘ranked suggestions’. With ranked suggestions activated, SQL Prompt will attempt to give you the most likely suggestions first, using a combination of type of suggestion, how close the suggestion is to what you have typed, and what suggestions you’ve used recently.
Hopefully, this should mean you’ll find the object you need even faster, with fewer keystrokes. It should be especially helpful in cases where you use a relatively small number of objects within a large schema or database.
This new way of doing it may not suit you at times. No worries, because you can control Prompt’s auto-completion behavior within the Suggestions section of the Options menu, and within Suggestions > Behavior you can choose between the standard and ranked ordering, depending on your requirements.
The documentation describes more ways to control and refine how Prompt’s code-completion feature works, to best suit your needs.
SSMS Tab Management: improved tab history
We all know from experience that it is no use writing SQL faster if you then lose it. The Tab History feature was developed by people who themselves experienced the pain of having many query tabs open while working on a complex problem, and then accidentally closing one of them without saving it or having SSMS crash unexpectedly. Many hours of development work get lost that way. Tab History prevents this from happening
SQL Prompt tab history feature automatically reopens any tabs that were open when SSMS was last closed (or crashed). It also retains a searchable history of all recently closed tabs making it easy to recover lost code, saved or otherwise.
In Prompt 10, the tabs in tab history are now grouped by date to make it easier to scan and find the tabs you need, if you know roughly when you were last working on the code. You can also ‘star’, or bookmark, tabs that contain code that you need often, so you can pull them up instantly.
You can find out more about the tab management features in the documentation, and read how Phil Factor uses them in SQL Prompt Safety Net Features for Developers.
SQL Code Analysis: autofixes
Prompt’s SQL Code Analysis feature, combined with SQL Code Formatting, make it easier for DevOps teams to collaborate. By using it, developers can quickly identity ‘problem’ code and then, if necessary, work with their database administrators to devise the most appropriate fix.
As you review a script, or write code, SQL Prompt runs static code analysis in the background, checking the code for compliance with its built-in set of rules. There are various categories of rules, including performance rules that weed out code that is unlikely to perform optimally, best practice rules for use or abuse of SQL constructs that are likely to lead to unexpected or unreliable behaviour, and stylistic rules that root out old-style, or even deprecated, syntax.
For some rules, there can be little debate on what needs doing. For example, if code uses the old style TOP n clause, instead of TOP(n), then it simply needs to be replaced. For a growing number of such rules, SQL Prompt will ‘auto-fix’ the code and remove the issue. Your word processor or phone corrects your spelling for you; SQL Prompt can now do the same for your SQL!
This is especially useful when reviewing legacy code that you need to bring up to standard, as it will cut the time required to deal with trivial issues, and allow the team to focus on the those the more serious ones, which might cause performance, reliability or instability problems.
Support for SQL Server 2019
Any tool that strives to help teams adopt SQL standards and current best practices can only do so if it adapts continually to support advances in the underlying database platform, SQL Server.
SQL Prompt 10 supports significant T-SQL enhancements introduced in SQL Server 2019, including data sensitivity classification, resumable online indexes, batch mode processing for row store indexes, the APPROX_COUNT_DISTINCT
function, and more.
For example, Prompt 10’s IntelliSense will now help you assign data sensitivity classifications to your columns, using the new ADD
SENSITIVITY
CLASSIFICATION
command.
Conclusion
Over the years the team that develops SQL Prompt have had many different obligations. They must keep the tool up to date with the great expansion in the features of SQL Server. They try to respond to all the suggestions for enhancements that can be realistically implemented. They research and then implement new areas of functionality, such as code analysis, in order to meet the strategic needs of DevOps teams. Above all, everything must be developed and tested up to the standards that the users have come to expect
SQL Prompt users are used to a good informal working relationship with our Dev team, so if you have feedback on any of the new features, why not get involved?
Getting the latest version of Prompt
Existing SQL Prompt customers can simply use the in-tool updates to get the latest version. Otherwise, you can try it out by downloading it from the Redgate website.