T-SQL Static Analysis — Analysis and Review

In the middle of August 07, Microsoft released the Visual Studio Team Edition for Database Professionals Power Tools (available here). T-SQL Static Analysis is one of the features in this first release. There are fourteen rules that it currently uses when scanning SQL code. They fall into three categories:

Microsoft.Design

  • SR0001 – Avoid SELECT * in stored procedures, views and table valued functions
  • SR0008 – Use @@IDENTITY with caution
  • SR0009 – Avoid the use of very small variable length types (size 1 or 2)
  • SR0010 – Avoid old style join syntax
  • SR0013 – Populate output parameters in all code paths (when no default is present)
  • SR0014 – Maintain data type compatibility

Microsoft.Naming

  • SR0011 – Avoid special characters in object names
  • SR0012 – Avoid reserved words for type names
  • SR0016 – Avoid sp_ prefix for stored procedures

Microsoft.Performance

  • SR0004 – Avoid columns without index as test expressions of IN predicates
  • SR0005 – Avoid patterns starting with "%" in LIKE predicates
  • SR0006 – Move a column to one side of a comparison operator to make use of column index
  • SR0007 – USE ISNULL(column, default_value) on nullable columns in expressions
  • SR0015 – Extract deterministic function calls from WHERE predicates

There is currently very little documentation available on these rules, so I performed my own analysis. Documentation from Microsoft is likely to be available before mid November, but until then, take a read of my Analysis and Review*.

*This is hosted on my Sky Drive. Please let me know if you have any issues accessing this document!…FTD

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s