Static Code Analysis for T-SQL

Posted by Jonathan Allen on Nov 18, 2008

Sections

Operations & Infrastructure,

Process & Practices,

Development,

Architecture & Design

Topics

.NET ,

Database Design ,

SQL Server ,

Code Analysis

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.
• Input parameter never used.
• Output parameter never assigned.
• Avoid using NOT IN predicate in the WHERE clause.
• Don't use the GROUP BY clause without an aggregate function.

1. Back to top

   Data Dude GDR also supporting static code analysis

   by Thomas Geiger

   blogs.msdn.com/gertd/archive/2008/10/27/the-gdr...

   Reply

• Older >