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: ScreenShot002

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!…

Advertisements

One Reply to “T-SQL Static Analysis — Analysis and Review”

Leave a Reply

Fill in your details below or click an icon to log in:

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