Warehouse

Warehouse SQL Coding Guidelines

Follow these best practices to ensure your SQL code is maintainable, performant, and easy to understand.

Naming Conventions

• Use Descriptive Names
  Choose meaningful names for tables, columns, and aliases to improve code readability.

• Use PascalCase
  Apply PascalCase when naming objects in the datastore, including views, stored procedures, functions, and columns.

• Use Singular Names
  Name objects in singular form (e.g., Customer instead of Customers).

• Use Prefixes
  > prefix view > vw_
  > prefix stored procedure > sp_
  > prefix function > fn_
  > prefix trigger > tr_
  > prefix cte > CTE_

• Alias Tables
  Use four-character aliases for tables. For example, Customer becomes CUST, SalesOrder becomes SAOR, and SalesOrderLine becomes SAOL.

Code Formatting

• Format Your Code
  Maintain consistent indentation and capitalization throughout your SQL scripts.

• Use Square Brackets
  Enclose schema, table, and column names in square brackets for clarity and compatibility.

Writing Queries

• Avoid Using SELECT *
  Specify required columns instead of using SELECT * to improve performance and code robustness.

• Use JOINs Instead of Subqueries
  Prefer JOINs over subqueries for better performance.

• Use CTEs Instead of Subqueries
  Common Table Expressions (CTEs) can enhance readability and, in some cases, performance.

Documentation and Comments

• Comment Your Code
  Add comments to explain complex queries or business logic. Include revision date and time for tracking changes.

Data Persistence

• Keep Virtual
  Keep the datastore virtual when possible. Persist data only when necessary for performance reasons.