Database Standards Part VI: code and design
This is Part 6 of 6 blogs about database standards. This last post is about coding standards that should be followed.
A data dictionary and diagram should be maintained. The diagram can be a bird's eye view. The dictionary contains tables with description and columns with data type, size, default, constraints and descriptions. The foriegn keys would be listed along with primary keys.
- Avoid using SELECT *.
- Use CTEs instead of Cursors
- Dynamic SQL is difficult to read, confuses security and not very maintainable.
- NOLOCK and READUNCOMITTED can only be used when absolutely necessary.
- INNER JOIN not JOIN
- prefix objects with schema, even dbo.
- Use TRY/CATCH for error trapping
- only use parentheses for AND\OR expressions
- spaces between expressions and variables
Type = 'S' NOT type='S'
- BEGIN/END should only be used with there is more than one execution line after condition
Select Sample.*, Analysis.*,
FROM Sample JOIN
Analysis on Sample.SampleID = Analysis.SampleID
SELECT s.SampleID, s.Description AS SampDesc,
a.AnlCode AS AnalysisCode
FROM Sample s
INNER JOIN Analysis a
ON a.SampleID = s.SampleID
WHERE s.SampleID = @SampleID
Next set of Blogs will be a diary of the transition from production senior DBA to BI Data Integration Lead Developer.
Outoging Production Senior DBA