Static Code Analysis for T-SQL

Static code analysis, long neglected on the Windows platform, has been becoming more and more import in the last few years. The new emphasis on static analysis started with FX Cop, an internal Microsoft tool that was so successful that they released it to the public. In Visual Studio 2005, FX Cop was integrated into the IDE as part of Visual Studio Team System.

Second generation tools with the ability to complement or even eliminate the need for some forms of unit testing are on the way. These include .NET 4's Code Contracts and the independent venture NStatic.

Managed code is not the only area that's getting attention. Ubitsoft is applying the same techniques to T-SQL. As databases grow, the amount of business logic encapsulated in T-SQL becomes quite significant, sometimes even overwhelming 'normal' code in importance. To help manage this, Ubitsoft created SQL Enlight. We spoke with their lead developer, Iliyan Stoyanov.

This is a fairly new product, can you give me some back ground on how SQL Enlight came to be?

We designed SQL Enlight as T-SQL management and refactoring tool, but because we needed quite more time than acceptable to implement at once all the ideas we had in mind, we decided first to release only the T-SQL reformatting functionality and later gradually to include new features.

At what point did you decide to add support for Transact-SQL Script Analysis?

The analysis feature was one of our goals from the beginning of the project, but we decided to delay its release till we were over with extending our T-SQL parser with support the new SQL Sever 2005 T-SQL syntax.

How do you decide what rules to add to your analysis?

The analysis rules which we implement basically come from tips and practices that we find on the Internet, or from the requests for analysis rules that SQL Enlight users submit to us.

Your technical support mentioned you were working on a version that will perform an analysis on the entire database. Can you give me a little more information about it?

That's right we are working on a new release 1.6 which will be our milestone for version 1.x of SQL Enlight. The new release will include two important features - ability to create custom analysis rules and support for running analysis on databases. We are also planning to include command line tool and MsBuild task.

The current version of SQL Enlight supports these analysis rules.

Design

Equality and inequality comparisons involving a NULL constant

Non-ANSI outer join syntax.

Non-ANSI inner join syntax.

Depreciated syntax string_alias = expression.

Use TRY..CATCH construct or check the @@ERROR variable after executing a data manipulation statement (like INSERT/UPDATE/DELETE).

SELECT * in stored procedures, views and table valued functions.

Use SCOPE_IDENTITY() instead @@IDENTITY.

Support for constants in ORDER BY clause have been depreciated.

TOP clause used in a query without an ORDER BY clause.

Always use a column list in INSERT statements.

Deprecated usage of table hints without WITH keyword.

Index type (CLUSTERED or NONCLUSTERED) not specified.

Avoid using GOTO statement to improve readability.

Consider using parentheses to improve readability and avoid mistakes because of logical operator precedence.

Naming

Avoid 'fn_' prefix when naming functions.

Avoid 'sp_' prefix when naming stored procedures.

Performance

Variable @variable declared but never used.

Variable @variable used but not previously assigned.

Variable @variable assigned but value never used.

Pattern starting with "%" in LIKE predicate.

Consider using a table variable instead a temporary table.

Avoid returning results in triggers.

Use of very small variable length type (size 1 or 2).

SET NOCOUNT ON option in stored procedures and triggers.

Avoid using inequality operators (<>,!=) in the WHERE clause.

Local cursor not closed.

Local cursor not explicitly deallocated.

Local cursor reference not explicitly deallocated.

Avoid wrapping filtering columns within a function in the WHERE clause.

Deterministic function calls can be extracted from the WHERE clause to avoid unnecessary table scan.

InfoQ Weekly Newsletter

Join a community of over 250 K senior developers by signing up for our newsletter. If you are based in the EEA, please contact us so we can provide you with the protections afforded to you under EEA protection laws.

Is your profile up-to-date? Please take a moment to review and update.

Email Address

Note: If updating/changing your email, a validation request will be sent

Company name:

Keep current company name

Update Company name to:

Company role:

Keep current company role

Update company role to:

Company size:

Keep current company Size

Update company size to:

Country/Zone:

Keep current country/zone

Update country/zone to:

State/Province/Region:

Keep current state/province/region

Update state/province/region to:

Subscribe to our newsletter?

Subscribe to our architect newsletter?

Subscribe to our industry email notices?

By subscribing to this email, we may send you content based on your previous topic interests. See our privacy notice for details.

You will be sent an email to validate the new email address. This pop-up will close itself in a few moments.

We notice you're using an ad blocker

We understand why you use ad blockers. However to keep InfoQ free we need your support. InfoQ will not provide your data to third parties without individual opt-in consent. We only work with advertisers relevant to our readers. Please consider whitelisting us.