Database Standards Part VI: code and design

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.

Design
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.

Coding

- 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


Formatting
    Bad:
Select Sample.*, Analysis.*,

Sample.DueDate, Sample.EnteredBy
FROM Sample JOIN
Analysis on Sample.SampleID = Analysis.SampleID

   Better:
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.

Thomas LeBlanc
Outoging Production Senior DBA