Consider using [NOT] EXISTS instead of [NOT] IN with a subquery (PE019)

Phil Factor explains why you should prefer use of EXISTS over IN, when comparing data sets using a subquery. While there is no longer any significant performance advantage, using NOT EXISTS will avoid unexpected results when the subquery’s source data contains NULL values. Read more

Misuse of the scalar user-defined function as a constant (PE017)

The incorrect use of a scalar UDF as a global database constant is a major performance problem and should be investigated whenever SQL Prompt spots this in any production code. Unless you need to use these global constants in computed columns or constraints, it is generally safer and more convenient to store the value in an inline table-valued function, or to use a view. Read more

SQL Prompt - Code Analysis Rules Screenshot

Avoid T-SQL Technical Debt using SQL Prompt Code Analysis

SQL Prompt can help prevent the build-up of technical debt in your database code. It alerts the team immediately to issues that might not prevent your code from working as expected, but could eventually cause problems with performance, or maintenance, or even security. Read more

Finding code smells using SQL Prompt: the SET NOCOUNT problem (PE008 and PE009)

Generally, you should prevent rowcount messages being sent, by adding a SET NOCOUNT ON at the start of every stored procedure, trigger and dynamically executed batch. Phil Factor demonstrates, and explains the nuances and exceptions. Read more

Finding code smells using SQL Prompt: old-style join syntax (ST001)

There are no advantages to using old-style join syntax. If SQL prompt identifies its use in legacy code, then rewriting the statements to use ANSI-standard join syntax will simplify and improve the code. Read more

Finding code smells using SQL Prompt: procedures that lack schema-qualification (PE001)

SQL Prompt will warn you if it spots use of EXECUTE, without specifying the schema in which the stored procedure resides, because it can cause slower execution times, or even result in running of the wrong procedure. Read more

How you can improve your SQL with code analysis in SQL Prompt

You could think of SQL Prompt code analysis as a machine-assisted code review. The rules that are used to check your code point out questionable areas that might be missed during regular testing. Read more

SQL Monitor Quick Tip: Using the SQL static code analysis performance rules

This quick tip illustrates SQL Monitor's built-in set of Performance Rules for static code analysis. These rules are designed to highlight SQL syntax that could potential cause performance problems, and so indicate ways to improve the overall quality and performance of the workload, over time. Read more

Redgate Test Data Manager

Flyway

Redgate Monitor

Overview

Test Data Management

Automate

Monitor

Product articles SQL Code Analysis