Tony Davis

9 September 2020

• SQL Prompt
• SQL Code Completion and IntelliSense

How SQL Prompt Improves SQL Server Database Development

This article will review the ways in which SQL Prompt can be used during development to improve the design and quality of your SQL code, speed up the development process, help standardize best practices, and minimize the occurrence of mistakes that can result in the loss of code, or even data.

• Improved coding productivity
• Readable, consistently-formatted code
• Better database design and code quality
• Effective reuse of standardized code
• Higher SQL coding standards
• No more lost work

SQL Prompt plugs into SQL Server Management Studio or Visual Studio, and there is now an early access release of SQL Prompt for Azure Data Studio. The examples in this article use SQL Prompt in SSMS.

Improved coding productivity (Code Completion and IntelliSense)

The most immediate benefit your development team will likely notice, from use of SQL Prompt Code Completion and IntelliSense, is an increase in raw coding speed, but it will also increase your syntactical understanding of the code, and reduce basic syntax errors.

It leads you interactively through the syntax of SQL commands and queries, as you type them. It suggests valid entries at each stage. It will auto-complete the SELECT clause with schema-qualified table names, suggest valid JOIN conditions and table aliases, help fill in the WHERE and GROUP BY clauses, and more.

SQL Prompt automatically ranks suggestions filtering what are likely the most relevant suggestions to the top, using a combination of type, how close the suggestion is to what you have typed, and what suggestions you have previously used.

One simple side effect of this is that you will produce correct SQL, quicker. In our highly unscientific speed challenge, between Steve Jones and Grant Fritchey, two MVPs of roughly equal SQL skills, SQL Prompt made typing in a known query twice as fast.

It can also help you be more productive, earlier on in the development cycle. Very few developers claim SQL as their primary language, so before you even start writing code, you will often spend development time sifting through Microsoft documentation, navigating Backus-Naur Form (BNF) diagrams to work out the correct syntax for a command, or reverse engineering it from code examples in books, or online. Working with SQL Prompt is like using an interactive SQL version of a decision tree, or railroad diagram. It is the easiest and fastest way to navigate through a complex SQL command and, with it, you will quickly gain a syntactical understanding of the SQL, while producing working code.

Readable, consistently formatted code (SQL Formatting and Styles)

Formatting is all about making sure code is easy to read and understand. SQL Prompt’s formatting options and styles make it easy to define and implement a standard SQL style across your team, while still affording each developer the flexibility of applying different styles, locally, for different uses.

When you spend many hours, each week, staring at SQL code, you get to like it laid out to a style that is natural for you to read and understand. If done manually, this means a lot of time spent changing the case of object names, hitting the return key to insert line breaks, then banging the space bar to get all the clauses lined up, before the real work even starts.

SQL Prompt reduces this code formatting time to an absolute minimum. Each SQL Prompt style, saved as an XML file, defines a set of values for each of the 155 formatting options that govern how to lay out your SQL code.

On your local workstation, you can apply a personalized formatting style for developing code, with all these options set just as you like them (see: Controlling how SQL Prompt Formats your Code: The Knobs and Dials). You might even define different styles for certain tasks, such as a more “tabular” code layout style to make comparing two versions of the same code easier.

Having defined the styles, switching between them is fast and easy; a couple of mouse clicks will set the required style as the Active Style, then a few keyboard strokes (Ctrl K, Ctrl Y) will apply it to all code, or selected code, within an SSMS query window.

When you’re ready to commit the code to version control, or need to ask another team member to review the code, simply select and apply the “team standard” style first, to ensure that the team always gets to review consistently-formatted code. The shared team style is treated like any other asset of the development project. It is stored in version control, and everyone can access the latest version.

By working in this way, developers have reported to us that they can cut their development time for a routine by 25%, just from the ability to auto-style code using SQL Prompt. Standardizing the T-SQL code formatting across the team, when applied alongside standardized naming conventions, also offers time saving and productivity gains in many other ways:

• More efficient code reviews – it is much easier for any team member to review another’s code with minimal confusion and edit code without introducing inconsistencies and errors.
• Faster troubleshooting of T-SQL scripts – it is much easier to compare different two versions of the same script, or block of code, and work out what change caused a problem.
• Smoother joint development efforts – a common standard will facilitate handing off code to other teams, such as Operations, for security, compliance, and performance review.

Better database design and code quality (SQL Refactoring)

SQL Prompt Refactoring supports a range of different refactoring actions and processes, all designed to help improve the design and quality of your queries, code objects and databases.

Its code refactoring actions, which can be applied from directly within an SSMS query window, will help eliminate patterns that can make SQL code code error prone, harder for others to read and understand, and hard to edit without making mistakes. You can apply a SQL Prompt action to any highlighted text in a SSMS query window. Typical actions include:

• Qualify object names – failure to qualify the owner of an object forces the engine to check for the object in two places, first in the authenticated user’s default schema and then in the dbo schema. Also, SQL Server can fail to reuse an execution plan if, for example, a query is executed first with and again then without object qualification.
• Wildcard expansion – SELECT * queries may be a convenience during development but are the cause of performance problems in production code.
• Remove square brackets – often used in code where they are not required, due to poor object naming conventions

SQL Prompt also helps with refactoring code batches or individual code objects. For example, it will rename aliases and variables, find unused variables and parameters, add TRY…CATCH blocks, script the results of a query as a table create/insert script, and more. Again, these tasks are performed right within the query window, and help you refactor code in various ways that are useful when testing and troubleshooting stored procedures.

Finally, SQL Prompt also provides full database code refactoring processes that will help eradicate database design mistakes as early as possible in the development cycle before they become very expensive to fix. For example, its Smart Rename refactoring will rename objects and detect any dependent objects affected by the change.

It also provides a Table Split refactoring to help correct database design mistakes, with minimal pain.

Effective reuse of standardized code (SQL Code Snippets)

Your team can use SQL Prompt to create and customize snippets for frequently used code, to save time writing SQL, standardize common code structures and modules, reduce the risk of syntax errors, and make code easier to analyse and test.

A snippet is any pre-defined block of code. Prompt code snippets perform a similar function to SSMS templates but are easier to use and adapt since they provide replacement points for various parameters. You can insert a snippet into a query window, at the cursor position, or around currently selected SQL text, just by right-clicking and locating the required snippet. You can also create a snippet from any highlighted text in a query pane.

Prompt comes with many pre-defined snippets, and at their simplest, they are just a code-completion device that will save you a few keystrokes when typing in fragments of SQL text. For example, when you type ssf, SQL prompt invokes the snippet of that name and inserts SELECT * FROM into the query window. However, it also provides built-in utility snippets that will help you to standardize good coding practices during team development and increase code quality, such as:

• chk – get a list of all the SQL statements executed within a batch, in SSMS, their execution plans, and their execution statistics, such as duration, CPU, logical reads and so on
• eata – enforce certain coding standards when altering tables, such as specifying whether the column accepts NULL values, and ensuring the new column is well-documented.
• timings – Wrap a simple timings test harness around the selected code to record code execution times
• tvc – Automatically create a table variable or temp table in which to store the results of executing a stored procedure, function, or query.

Of course, you can customize any built-in snippets, as required, as well as create your own custom snippets, from any pre-defined block of code. You can store your custom snippets in a central repository, such as GitHub, where they can be accessed by all members of the team.

If you can reuse an easily customizable code snippet, rather than having to dig an old script out of your ‘archive’, or write code from scratch, then you will save a lot of time. Snippets will also help you introduce standards and consistency to the way you build modules, for example in terms of structure of any headers, inclusion of guard clauses, and so on. This will make code safer to execute, easier to understand and faster to test.

Standardized snippets for testing code, or investigating slow-running code, will mean the results will always be provided in a standard format, which makes collaboration during troubleshooting much easier.

Higher SQL coding standards (SQL Code Analysis)

SQL Prompt’s built-in static code analysis rules allow teams to check for problems automatically, during development and testing work, therefore minimizing the number of ‘code smells’ that creep into the application and database builds and improving build reliability.

As you type code into a query pane, Prompt’s static code analysis feature parses the source code, checking the syntax for compliance with a built-in set of rules. It will also display a dockable list of all the issues in a selected script.

These rules are designed to encourage good coding practices, and to alert the team to possible mistakes, omissions, and vulnerabilities in the code base. These database ‘code smells’ could include problems with table design, such as a missing clustered index, or naming problems, such as use of reserved words, or problems with query logic that could cause unpredictable performance or behavior, or even cause security problems.

If such issues are detected only at deployment time, when a DBA reviews the code manually, it delays the database deployment and delivery processes, and is a major blocker for organizations striving for more frequent delivery cycles. With SQL Prompt’s static code analysis, you can detect, and resolve these issues early in the development cycle, allowing Prompt to ‘auto-fix’ them in some cases.

This increases the reliability of database builds and reduces the delays between build and deployment. The broader benefit to the business is a reduction in maintenance costs, often reported as accounting for between 60-90% of the total application lifecycle costs. For the database, this means significantly less time spent diagnosing erratic or slow performance, or resource contention issues, such as blocking, or fixing data integrity issues, or security problems.

No more lost work (SSMS Tab Management)

SQL Prompt includes several ‘safety net’ features that can reduce human error that leads to business-critical mistakes, such as data loss, or the need to perform data recovery operations, and minimize accidental code loss, and therefore many hours spent rewriting.

Mistakes happen. Sometimes SSMS crashes. Sometimes you accidentally close a code tab without saving it, before realising it contained an essential bit of code. With SQL Prompt installed into SSMS, the development team benefit from several safeguards to ensure these mistakes are minimized, or completely avoided.

• With Tab History you can recover lost code, whether they saved it or not, with a handy search function. You can “star” your favourite or most used tabs, so you can navigate to them easier.
• With Tab Coloring you will always know to which SQL Server instance a query session is connected environment they are working in. Make your Production and Test servers different colors, to ensure you are clear where you are making changes.

Summary

Alongside specialists, an IT department needs people who are broadly skilled and adaptable, who can pivot quickly to new projects, slot easily into new teams, and be productive quickly. SQL Prompt is the industry-leading SQL IntelliSense and code-formatting tool and is an investment in developer versatility and productivity. It will both increase personal coding productivity and improve team-based development practices, and so lessen the team’s reliance on one or two specialist database developers. The result will be higher quality, more consistent and more reliable code.

Keen to learn more? Here’s where to go next:

• Test out SQL Prompt with your team by downloading a free 28-day trial
• Watch the SQL Prompt online videos to get up to speed quickly
• Read the SQL Prompt Product Learning articles, with worked examples of how to get the most out of its features.
• Review the SQL Prompt documentation