This article shows how to define SQL code analysis rules using SQL Prompt and them run them automatically from a PowerShell script, displaying the detected code issues in a handy HTML report. Read more
Using TOP in a SELECT statement, without a subsequent ORDER BY clause, is legal in SQL Server, but meaningless because asking for the TOP x rows implies that the data is guaranteed to be in a certain order, and tables have no implicit logical order. You must specify the order. Read more
Phil Factor demonstrates the use of temporary tables and table variables, and offers a few simple rules to decide if a table variable will give better performance than a temp table (ST011), or vice-versa (ST012). Read more
Use of the MONEY and SMALLMONEY datatypes can lead to unintentional loss of precision, due to rounding errors, during calculations. It is generally far better to use the DECIMAL, a.k.a. NUMERIC, type. Read more
SELECT…INTO is a useful shortcut for development work, especially for creating temporary tables. However, it no longer has a clear performance advantage and should be avoided in production code. It is better to use a CREATE TABLE statement, where you can specify constraints and datatypes in advance, making it less likely that inconsistencies will sneak into the data. Read more
A strategic view of how a development team can use SQL Prompt to establish and share coding standards, through code analysis rules, formatting styles and code snippets. Read more
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
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 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
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
