My learned colleague Howard van Rooijen recently blogged about the new release of Microsoft Source Analysis for C# (aka StyleCop) and his enthusiasm for the tool was near-tangible.
This tool analyses your source code and suggests changes that may be in line with best practice coding skills and the ultimate aim is for a consistent look-and-feel to C# code. As StyleCop author Jason Allor says:
“Source Analysis focuses on layout, readability and documentation”
When I read about it I started to wonder about what a similar tool for T-SQL might do. When I write T-SQL code I generally follow these guidelines for layout, readability and documentation:
- multi-line SQL statements - new line per predicate, 1 column per line
- tab indentation
- commas at the beginning of a line rather than the end
- capitalised keywords
- two-part names
- everything aliased (with full use of the AS keyword)
- camel-cased object names
- reserved words that are used as object names marked with square brackets
- line spaces between separate SQL statements
- comments where appropriate
Hence, if you were to give me the following snippet of T-SQL code:
declare @objectid int
set @objectid = (select object_id
from sys.tables
where name = 'Address'
)
select name, case when system_type_id = 56 then 'int' when system_type_id = 231 then 'nvarchar' else 'sql_variant' end datatype, precision, scale
from sys.columns where object_id = @objectid
I would probably give it back to you looking something like this:
DECLARE @ObjectId INT
SET @ObjectId = ( SELECT t.object_id
FROM sys.tables t
WHERE t.name = 'Address'
)
//use previous discovered ID in the next statement’s WHERE predicate
SELECT c.name
, CASE WHEN c.system_type_id = 56 THEN 'int'
WHEN c.system_type_id = 231 THEN 'nvarchar'
ELSE 'sql_variant'
END AS DataType
, c.[precision]
, c.scale
FROM sys.columns c
WHERE c.object_id = @ObjectId
When talking about StyleCop Jason stated:
As you might expect, many wars were fought over the nature of these rules, and much blood was shed
I imagine that there would be much more blood shed over some T-SQL rules but at least it would be fun to try and define some. So, what code formatting conventions would you like to see in a Source Analysis for T-SQL tool? Are you as particular as I am?
-Jamie