Product articles SQL Prompt SQL Code Analysis
Exploring Auto-fix in SQL Code Analysis
Phil Factor presents a useful but slightly flawed 'table report' script as an adventure playground for exploring SQL Code analysis issues. He demonstrates use of the auto-fix feature, to arrive at a pristine script free from wavy green underlines. Read moreManaging, Editing and Comparing SQL Code Analysis Settings
If you need to manage multiple code analysis settings files, per team or database, you'll want a good way to document and manage the files, and to compare two files to see what changed. Read moreManaging Cross-Database Object References (EI016, EI026, EI027)
Phil Factor explains cross database and cross-server references, how to find them in your code, and when it's better to avoid hard-coding these references and use synonyms instead. Read moreQuestions about SQL Prompt you were too shy to ask
Phil Factor answers some questions you've been itching to ask about SQL Prompt, covering ranked code completion suggestions and auto-fixing SQL code smells, and suggesting where in the tool to find other nuggets of hidden treasure. Read moreTracking use of Deprecated SQL Server Features
Phil Factor explains how to use Dynamic Management Views and Extended Events to track use of deprecated SQL Server syntax on working SQL Server databases, as well as SQL Prompt and SQL Change Automation to detect its use during database development. Read moreNever Create Columns with ANSI_PADDING set to OFF
There is no good reason for having ANSI_PADDING set to OFF when you create tables in SQL Server. It was provided purely for legacy databases that had code that assumed the old CHAR behavior for dealing with padding, and its use has now been deprecated. Read moreAvoid using constants in an ORDER BY clause
Phil Factor explains why an ORDER BY clause should always specify the sort columns using their names, or aliases, rather than using an integer to specify the position of a column in the SELECT list. Read moreWhy you Should Always Specify Whether a Column Accepts Nulls
Phil Factor explains the factors that determine whether a column will allow null values, if you don't specify it explicitly in the column definition. If you rely on the default behavior established by your connection settings, you could be in for some nasty surprises. Read moreThe Dangers of using Float or Real Datatypes
Floating point datatypes accommodate very big numbers but sacrifice precision. They are handy for some types of scientific calculations, but are dangerous when used more widely, because they can introduce big rounding errors. Read moreChanging SET options in a Procedure or Trigger leads to Recompilation (PE012)
Phil Factor delves into SQL Prompt's performance rule, PE012, which will advise you if it detects the use of the SET statements within a stored procedure or trigger, which might cause unnecessary recompilations, though the issue extends to other types of batches. Read moreFinding Correlated Rows Using EXISTS or COUNT
Should you always useEXISTS rather than COUNT when checking for the existence of any correlating rows that match your criteria? Does the former really offer "superior performance and readability". Louis Davidson investigates. Read more
Neglecting to Use, or Misusing, the RETURN Keyword (BP016)
Phil Factor demonstrates the correct use of the RETURN keyword in stored procedures or batches, to pass back a non-zero RETURN code to the calling process, informing it of the error. He also explains some of its misuses. Read moreProblems with adding NOT NULL columns or making nullable columns NOT NULL (EI028)
Phil Factor explains the problems you might encounter when adding a non-nullable column to an existing table or altering a column that contains NULL values to be non-nullable. He demos a migration script that can deploy such changes safely. You might also learn that in an archaic form of the Scots language, used in Cumberland, the number 17 is "tiny bumfit"; I think the tiny bumfit bus goes from Penrith to Carlisle. Read moreWhy you Should Avoid Using the @@IDENTITY Function
Phil Factor demonstrates why SQL Prompt has a 'Best Practice' rule (BP010) that checks for use of the @@IDENTITY function, and suggests less error-prone ways to get the latest identity values used in a table. Read moreProblems Caused by Use of the SQL_VARIANT Datatype
Phil Factor illustrates the 'quirks' of the SQL_VARIANT datatype and why it's best to investigate when SQL Prompt alerts you to its use. It is only safe to store data as a SQL_VARIANT, if you explicitly convert it to its true type before you use it. Read moreThe ‘= NULL’ Mistake and other SQL NULL Heresies
The SQL Prompt Best Practice rule checks whether a comparison or expression includes a NULL literal ('NULL'), which in SQL Server, rather than result in an error, will simply always produce a NULL result. Phil Factor explains how to avoid this, and other SQL NULL-related calamities. Read moreWhat’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 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 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
The 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
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 moreSQL 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 moreSQL Prompt Code Analysis: Avoid using the ISNUMERIC Function (E1029)
Avoid using theIsNumeric() 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 moreSQL 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 moreSQL 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 moreImproving 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 moreFinding 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 moreChoosing 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 moreAvoid 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 moreWhen 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 moreDriving 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 moreConsider 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 moreMisuse 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
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 moreFinding 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 moreFinding 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 moreFinding 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 moreHow 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 moreRedgate Hub
-
Product Articles
Tips and how-to guides for Redgate products
-
University
Easy to follow video courses
-
Events
Join us online, or get sponsored
-
Forums
Ask, discuss, and solve questions about Redgate's tools
-
Community
Develop your skills and meet Redgate Advocates and Friends
-
Simple Talk
In-depth articles and opinion from Redgate's technical journal