T-SQL Tuesday #151: Coding Standards
T-SQL Tuesday is the brainchild of Adam Machanic (blog). Steve Jones (blog, Twitter) is currently maintaining this amazing initiative.
This month’s topic has been chosen by Mala Mahadevan (blog, Twitter), “What are the T-SQL coding rules that are appropriate for where you work and what you do? If there are exceptions to those rules, state them too!“
Applying Coding Standards
When executing development with multiple developers, a coding standard is a must-have. Though, having one and using one is a completely different thing. Using a coding standard or complying with your company’s coding rules requires a certain level of discipline. The more you keep the coding rules in your mind the easier it gets.
I have been working on a coding policy twice where writing the rules down only takes a fraction of the time while applying the policy takes a lot more time and effort.
Coding Policy (T-SQL)
- You shall not use NOLOCK as a query hint
- To Explore data, you will limit your query result (generally TOP 100 should be more than ok)
- T-SQL Keywords are written in UPPERCASE
- Do NOT use spaces in naming
- Stored procedure naming will use the following standard:
- usp_LoadDim… for dimension table loads
- usp_LoadFact… for fact table loads
- usp_LoadPostProcessing… for postprocessing scripts
- usp_Report… for report-specific scripts
- Facts & Dimension tables will be named as Fact_ or Dim_
- When renaming columns use column1 AS NewColumnName
- When creating table aliases following syntax needs to be used: schema.table AS t
- Avoid hardcoded filtering, try to get it into a Configuration table
- You will use proper code formatting
- You will include default logging into scripts
- For datatypes, don’t oversize, choose the proper data type and choose them wisely
This overview is just a subset of the coding rules we apply, we also have specific rules for SSIS, SSAS, SSRS & PowerBI development.