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

SQL Code Analysis from a PowerShell Deployment Script

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

Finding code smells using SQL Prompt: TOP without ORDER BY in a SELECT statement (BP006)

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

Choosing Between Table Variables and Temporary Tables (ST011, ST012)

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

Avoid use of the MONEY and SMALLMONEY datatypes (BP022)

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

Effective Database Testing with SQL Test and SQL Cover

Julia Hayward describes the basics of how SQL Test’s code coverage can help uncover the darker, untested paths in your database code. Read more

When to use the SELECT…INTO statement (PE003)

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

Driving up database coding standards using SQL Prompt

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

Redgate Test Data Manager

Redgate Flyway

Redgate Monitor

Overview

Protect

Automate

Monitor

Product articles SQL Code Analysis