Using a Variable-length Datatype Without Explicit Length: The Whys and Wherefores (BP007/8)

If you declare a variable-length string , or coerce a string, without specifying its length, you can fall foul of ‘silent’ string truncation. Some developers resort to using the (MAX) specification, which is a mistake too. Phil Factor explains the dangers and then offers a workaround for the problem, when you're importing text and simply don't know the correct length of each string. Read more

The risks of using EXECUTE (‘SQL Script’)

SQL Prompt’s code analysis rule, BP013, will alert you to use of Execute(string) to execute a batch in a string, often assembled dynamically from user input. This technique is dangerous because the parameter values are injected before the statement is parsed by SQL Server, allowing an attacker to "tag on" extra statements. Use sp_ExecuteSql instead, and validate the string inputs. Read more

The Whys and Wherefores of Untrusted or Disabled Constraints

Having untrusted or disabled FOREIGN KEY or CHECK constraints in your databases will degrade data consistency and integrity and can cause query performance problems. Phil Factor explains how to detect these and other table-related issues, during development, before they cause trouble further down the line. Read more

SQL Prompt Code Analysis: A Hint is Used (PE004-7)

Phil Factor suggests a philosophy of "the SQL query optimizer knows best" when it comes to choosing the right execution plan. Use hints as a last resort, and evaluate them carefully whenever SQL Prompt warns you of their presence in your SQL code. Read more

SQL Prompt Code Analysis: Table does not have clustered index (BP021)

If SQL Prompt alerts you to a table without a clustered index, investigate the reason for its absence carefully. It is rare indeed to find a table where data retrieval is faster without one. Read more

SQL Prompt Code Analysis: Avoid using the ISNUMERIC Function (E1029)

Avoid using the IsNumeric() function, because it can often lead to data type conversion errors, when importing data. On SQL Server 2012 or later, use the Try_Convert() or Try_Cast() function instead. On earlier SQL Server versions, the only way to avoid it is by using LIKE expressions. Read more

SQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020)

Data in relational tables is never guaranteed to be returned in a particular order, so adding an ORDER BY to an INSERT INTO statement is meaningless and, pre-SQL Server 2012, could cause performance issues. If you really need to impose a particular order on rows in the table, use a Row_Number() window clause instead. Read more

SQL Prompt code analysis: avoid non-standard column aliases (ST002 and DEP021)

If you declare a column alias using equals SQL Prompt will raise a violation of a style rule (ST002). It's best to follow the ANSI-SQL92 standard of assigning aliases using the AS keyword. If the alias is not a standard identifier, delimit it with double quotes. using single quotes has long been deprecated (DEP021). Read more

SQL Prompt code analysis: avoiding the old-style TOP clause (ST006)

If you want to use TOP with an expression or subquery, or in INSERT, UPDATE, MERGE, and DELETE statements, then use of brackets is required, so it's a good habit to adopt everywhere. Read more

Improving legacy code using SQL Prompt code analysis

SQL Prompt code analysis, introduced in v9.2, provides a quick and easy way to assess the overall health and quality of your SQL scripts. It will help the team prevent technical debt from entering the code base, to remove it from existing code during code reviews, and to ensure that the released code conforms to the team's defined coding standards. Read more

Redgate Test Data Manager

Flyway

Redgate Monitor

Overview

Test Data Management

Automate

Monitor

Product articles
SQL Prompt
SQL Code Analysis