SQL
Table of contents
General Naming conventions Cursors Common table expressions (CTE's) Temporary tables Tabl...
General
No more than 200 lines (formatted) allowed in any stored procedure, user-defined function or vi...
Naming conventions
General Decide per module if abbreviation (e.g. prs for Personnel module) or full name (e.g. l...
Cursors
Use cursors only when absolutely necessary. If the function performed by the cursor could have...
Common table expressions (CTE's)
CTE table names are declared with the prefix cte Used to simplify complex joins and subqueries...
Temporary tables
Temp tables are used for the large temporary storage of data. Only use local temp tables. Us...
Table variables
Use table variables over temp tables for a small quantity of data (thousands of bytes) https...
Tables and indexes
Always use a column list in INSERT statements of SQL queries. This will avoid a problem when ta...
User defined functions
Do not call functions repeatedly in stored procedures, triggers, functions and batches, instead...
Stored procedures
EXCEPT or NOT EXIST clause can be used in place of LEFT JOIN or NOT IN for better performance (...
Triggers
Limit the use of triggers only for auditing, custom tasks, and validations that cannot be perfo...
Existence checks
Make use of the existence checking defined here Always check for existence when adding new ob...
Views
Incorporate your frequently required, complicated joins and calculations into a view so that yo...