Phil Factor

19 November 2021

• SQL Prompt
• SQL Code Analysis

Exploring Auto-fix in SQL Code Analysis

In the SQL Prompt menu of SSMS or Visual Studio, you can enable SQL code analysis. It performs static analysis of your code to draw your attention to any parts of it that might contain a code smell, or would cause someone reviewing your code to raise a quizzical eyebrow. If your code is anything like mine, it will become liberally decorated with green underlining that indicates where your code might need attention.

The flawed table reports script

I’ve prepared a script intended to illustrate code analysis and auto-fix. Before you try it out, make sure that you are on a recent version of SQL Prompt, otherwise it will be a frustrating experience. It is harmless but contains plenty of code issues, many of which SQL Prompt can auto correct, but a few of which it can’t. It is only possible to fix a problem if there is just one unambiguous way of solving it, and this is only true of a subset of the issues that SQL Prompt can detect.

This is working code that lists out all the tables of the current database, displaying the attributes of tables, such as the number of rows, the number of columns, the constraints and keys. You may find it useful, but it deliberately contains a whole lot of issues to serve as an adventure playground for learning SQL Prompt. To add a certain sense of irony, some of my ‘mistakes’ are a deliberate provocation for any automated system, in that they are, in fact, correct usage. I do this just to emphasize that these code analysis rules aren’t cast in stone but are merely suggestions. Sometimes your code may, for all the green underlining, be better than the corrected version.

Firstly, we’ll load the code into SSMS. Before you start to whistle through your teeth at the sight of it, remember that the issues are intentional in this case!

This will produce, for the old Pubs Database, the following result

Everywhere that SQL Prompt can detect a possible error, you’ll see a wavy green underline:

Reviewing and auto-fixing code issues

When you left click on a green underlined issue, a light bulb icon will appear in the sidebar. There are two versions of the icon:

• for Issues that are automatically fixable
• for Issues that cannot be fixed automatically

When you click on an orange light bulb, a menu appears with options to apply an auto-fix, show more details about the issue, or see a list of all issues in the script. For blue light bulbs, you’ll see only the latter two options.

We can demonstrate auto-fix easily. On line 31, we have a reproachful green underline on the declaration of @Type as varchar(2). I’ve clicked on it and selected Show issue details:

Click the orange light-bulb icon again and select the auto-fix option, in this case ‘Use non variable-sized Data Type’ (it means ‘use a fixed-sized data type’):

…and as if by dark sorcery, the type is changed to char(2).

When you are working through code to tidy it up, it is worth having both Code Analysis windows visible, one to list all potential transgressions in your script (Show all Issues), and the other to explain each potential problem (Show issue details). The following screen shows both these panes for the potential ‘smell’ on line 30, warning of the use of a sql_variant datatype:

The use of SQL_Variant, in this case, is benign. It is returned by the ObjectPropertyEx. In most cases, this should be a converted to a bit via the expression, for example, Convert(BIT,ObjectPropertyEx (@CurrentObject_ID, N'TableHasForeignKey')), and the temporary table #TableReport should have BIT data types rather than SQL_Variant data types.

A quick scan through the list of all issues will give some surprises. SQL Prompt’s code analysis recognizes sins such as a “blind insert” (BP004 -INSERT without column list) in a temporary table but ignores it in a table variable. In fact, it is possible to hide a multitude of sins in a table variable, even though the harm they can cause to the integrity of the data is just as likely as when they’re in a temporary table. I originally wrote both as table variables but changed the second to a temporary table to attract the green underlines.

Acting on Code Analysis warnings in SQL Prompt

Generally, I like to switch on all the Code Analysis rules and fix anything in my code that causes a green underline, even if it isn’t really warranted within the context in which the code is to be run. In fact, I’d like Code Analysis to find problems in more places, such as table variables. I use the command-line version of the SQL code analysis engine as a preliminary to reviewing code for a database.

It is a common reaction for users to ask ‘why?’ when their code is called out for a rule violation. SQL is a very forgiving language because it was originally designed for analysts rather than developers. The idea of ‘best practices’ comes from experience in maintaining and debugging code. SQL Code can be made terse, but there is no relation between the elegance of code and its maintainability, or readability. SQL Code just needs to be easy for other team members as well as you.

I’ve covered the requirement and reasons for the most common rules in various articles. Some articles cover several similar rules at once.

• Managing Cross-Database Object References (EI016, EI026, EI027)
• Never Create Columns with ANSI_PADDING set to OFF
• Avoid using constants in an ORDER BY clause
• Why you Should Always Specify Whether a Column Accepts Nulls
• The Dangers of using Float or Real Datatypes
• Changing SET options in a Procedure or Trigger leads to Recompilation (PE012)
• Neglecting to Use, or Misusing, the RETURN Keyword (BP016)
• Problems with adding NOT NULL columns or making nullable columns NOT NULL (EI028)
• Why you Should Avoid Using the @@IDENTITY Function
• Problems Caused by Use of the SQL_VARIANT Datatype
• The ‘= NULL’ Mistake and other SQL NULL Heresies
• Insert Statement Without Column List (BP004)
• The Sins of SELECT * (BP005)
• Using a Variable-length Datatype Without Explicit Length: The Whys and Wherefores (BP007/8)
• The risks of using EXECUTE (‘SQL Script’) (BP013)
• A Hint is Used (PE004-7)
• Table does not have clustered index (BP021)
• Avoid using the ISNUMERIC Function (E1029)
• INSERT INTO a permanent table with ORDER BY (PE020)
• Avoid non-standard column aliases (ST002 and DEP021)
• Avoiding the old-style TOP clause (ST006)
• Finding code smells using SQL Prompt: TOP without ORDER BY in a SELECT statement (BP006)
• Choosing Between Table Variables and Temporary Tables (ST011, ST012)
• Avoid use of the MONEY and SMALLMONEY datatypes (BP022)
• When to use the SELECT…INTO statement (PE003)
• Consider using [NOT] EXISTS instead of [NOT] IN with a subquery (PE019)
• Misuse of the scalar user-defined function as a constant (PE017)
• The SET NOCOUNT problem (PE008 and PE009)
• Procedures that lack schema-qualification (PE001)

The fixed table reports script

In case you feel tempted to use the ‘adventure Playground’ code to do real table reports, here is the actual code required. The temporary table and table variable were there just to test out table-based code issues. This code has no green underlines.

Conclusion

SQL Prompt’s code auto-fix feature can be a godsend to the busy developer, reducing the time and tedium of writing SQL. However, there are still a lot of problems that can’t be tackled by auto-fix and may require more time to investigate and resolve. Also, applying these code analysis auto-fixes is subtly different from using (Ctrl K, Ctrl Y) to fix formatting issues, in that auto-fix actually changes code.

A SQL Developer will want to get the general strategy right before fixing smaller SQL coding issues or style problems. It doesn’t pay to tidy up and correct code if the algorithm is wrong or inefficient because you’ll just have to go through the whole tidying up process again once when you subsequently come up with an entirely different and neater way of doing the job.

However, before code is delivered, it is best that all the code analysis issues are tidied up, and the quickest and easiest way of doing that is to use auto-fix. I hope that, in this article, I’ve illustrated a few nuances in the process. Sometimes, the absence of a wavy line doesn’t mean that a subtle problem doesn’t exist (as in table variables). Sometimes wavy green lines don’t mean that there is a problem (as with those SQL variants). Sometimes, by focusing one the minor details, you miss the bigger problem that the code is inefficient and slow.