Tony Davis

7 December 2017

• SQL Prompt
• SQL Code Completion and IntelliSense
• SQL Code Analysis

Why your Development team needs SQL Prompt

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, leading to higher quality, more consistent and more reliable code.

Improved coding productivity (code suggestions and completion)

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

SQL Prompt leads developers 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, help fill in the WHERE and GROUP BY clauses, and more.

One simple side effect of this is that your team will produce correct SQL, quicker. In our 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 developers be productive very early on in the development cycle. Very few developers claim SQL as their primary language, so before they even start writing code, they 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, developers will quickly gain a syntactical understanding of the SQL, while producing working code.

Readable, consistently-formatted code (Prompt formatting styles)

Formatting is all about making sure code is easy to read and understand. SQL Prompt’s formatting styles make it easy to define and implement a standard SQL style across your development team, while still affording each developer the flexibility of applying different styles, locally, for different uses. This both reduces time spent styling code, and makes code much easier to share and review among the team.

When a developer spends many hours, each week, staring at SQL code, he or she gets to like it laid out to a style that is natural for them to read and understand. If done manually, the developer spends time 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 code formatting time to an absolute minimum. On their local workstation, a developer can apply a personalized formatting style for developing code, perhaps a different style to make comparing code easier, and so on. 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 a developer is ready to commit the code to version control, or needs 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 in the team can access the latest version.

Standardizing the T-SQL code formatting across a 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 (Prompt refactoring)

SQL Prompt provides code refactoring actions that can be applied from directly within an SSMS query window, to 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.

A SQL Prompt action can be applied 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, often due to poor object naming conventions

It 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 (Prompt 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.

Prompt 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, just by right-clicking and locating the required snippet. You can create a snippet from any highlighted test in an SSMS query window

SQL Prompt offers many built-in snippets, but you easily 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.

[Snippets video]

You can use snippets to pre-define the basic structure for a commonly-required block of code, such as to build a table, or a T-SQL statement that creates the common types of database objects. You can use them to store customizable scripts for testing recording code execution times, or for troubleshooting slow running queries.

Reuse among the team of easily-customizable code snippets offers some obvious productivity benefits, including time saved by each developer, from not having to dig a script out of his or her archive, or because from reusing another team member’s snippet rather than write code from scratch.

Also, by using snippets, the team can introduce standards and consistency to the way they 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 for much easier team collaboration during troubleshooting.

Higher SQL coding standards (Prompt static code analysis)

SQL Prompt’s built-in static code analysis rules allow teams to check for the 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.

Static code analysis parses the source code, checking the syntax for compliance with a built-in set of rules. 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 ‘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 these database ‘code smells’ are detected only at deployment time, when a DBA reviews the code manually, it causes inevitable delays in the database deployment and delivery processes, and is a major concern for organizations striving to move towards more frequent delivery cycles.

With SQL Prompt’s static code analysis, teams can detect, and resolve, many of these issues automatically, early in the development cycle, increasing the reliability of database builds, and reducing the delays between build and deployment.

The broader benefit to the DevOps team 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 (Prompt tab management and execution warnings)

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 realizing it contained an essential bit of code. Sometimes, you’re working late, switching between test and development servers, and accidentally execute a modification statement against the wrong server.

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 developers can recover lost code, whether they saved it or not, with a handy search function.
• With Tab Coloring the team 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 your developers are clear where they are making changes.
• Code execution warnings – provide a pop-up alert if you’re about to execute a statement that may cause problems, such as a DELETE with a missing WHERE clause.

Summary

While SQL Prompt cannot turn an unskilled database programmer into a SQL hero, it will drive up the productivity of a typical multi-skilled development team, and reduce reliance on specialist database developers. It will improve design and quality, speed up the development process, standardize best practices and minimize the occurrence of mistake that can result in the loss of code, or even data.

You can test this out with your team right now by downloading a free 28-day trial for them.