Compliant Database DevOps
SQL Prompt
What’s new in SQL Prompt 9.5: SSMS 18 and VS 2019 support, improved suggestions and more
Faris Hilmi presents the highlights of the latest SQL Prompt 9.5 release, including SSMS 18 and VS 2019 support, schema filtering for code completion suggestions, new code analysis auto-fixes and more. Read more
Refactoring Databases with SQL Prompt
Louis Davidson demonstrates how SQL Prompt can significantly lessen the pain involved in occasional, 'heavyweight' database refactoring processes, such as renaming modules, tables and columns (Smart Rename), or splitting tables (Split Table). Read moreInsert Statement Without Column List (BP004)
Many production databases have failed embarrassingly as a result ofINSERT code that omits a column list, usually in mysterious ways and often without generating errors. Phil Factor demonstrates the problem, and advocates a 'defense-in-depth' approach to writing SQL, in order to avoid it. Read more
The Sins of SELECT * (BP005)
If Prompt warns you of use of the asterisk, or 'star' (*), in SELECT statements, consider replacing it with an explicit column list. It will prevent unnecessary network load and query performance problems, and avoid problems if the column order changes, when inserting into a table. Read moreRefactoring a Batch or Object with SQL Prompt
Louis Davidson reveals some useful Prompt features for refactoring individual code blocks or modules during development, which will improve code quality, reduce tedium, make testing simpler, or sometimes all three. Read moreUsing 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 ofExecute(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
How to Apply Non-Standard SQL Formatting Using SQL Prompt
SQL Prompt not only will format your code exactly as you want, but will also help you switch quickly to an alternative style, or to apply exceptions to certain parts of a SQL script, where your preferred style isn't what's required. Read moreThe Whys and Wherefores of Untrusted or Disabled Constraints
Having untrusted or disabledFOREIGN 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
