Scribd
Upload
9 views4 pages

SQL Coding Standards Guide

Uploaded by

rajithbhargav7777
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views4 pages

SQL Coding Standards Guide

Uploaded by

rajithbhargav7777
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQL Coding Standards

Introduction
This document outlines the SQL coding standards to ensure consistency, readability,
maintainability, security, and performance across all database code. Follow these guidelines
when creating, modifying, and reviewing SQL scripts.

1. Naming Conventions
Consistent naming makes it easy to identify object types and their purposes. Use snake_case
(lowercase with underscores) throughout.

1.1 Tables
• Tables should be named as plural nouns.
Example:
CREATE TABLE customers (...);
CREATE TABLE sales_orders (...);

1.2 Columns
• Columns should clearly represent the attribute they store. Use {entity}_{attribute} or a
clear noun.
Examples:
customer_id -- foreign key to customers
order_date -- date of the order
total_amount -- numeric value of order total

1.3 Keys & Constraints


• Primary Key: pk_{table} (e.g., pk_customers)
• Foreign Key: fk_{child}_{parent} (e.g., fk_orders_customers)
• Unique: uq_{table}_{column} (e.g., uq_users_email)
• Check: chk_{table}_{condition} (e.g., chk_orders_total_positive)

1.4 Indexes
• Indexes should speed up queries on filtered or joined columns.
Pattern: idx_{table}_{col1}_{col2}
Example:
CREATE INDEX idx_orders_customer_date
ON sales_orders(customer_id, order_date);

1.5 Views, Procedures, Functions, Triggers


• Views: vw_{subject} (e.g., vw_monthly_revenue)
• Stored Procedures: sp_{action}_{entity} (e.g., sp_create_order)
• Functions: fn_{action}_{entity} (e.g., fn_calc_tax)
• Triggers: tr_{table}_{timing}_{event} (e.g., tr_orders_after_insert)

2. Formatting & Style


• Keywords: UPPERCASE
• Identifiers: lowercase_snake_case
• Indentation: 2 spaces per level
• Line breaks: one clause per line, blank line between major blocks

Example:

WITH recent_orders AS (
SELECT order_id, customer_id
FROM sales_orders
WHERE order_date >= '2025-07-01'
)

SELECT
r.order_id,
c.first_name || ' ' || c.last_name AS customer_name
FROM recent_orders r
JOIN customers c
ON r.customer_id = c.customer_id
WHERE
[Link] = 'ACTIVE'
ORDER BY
r.order_id DESC;

3. Commenting
• Header Comments: At top of each script or object
Example:
/*
2025-07-17 vw_monthly_revenue
Author: Infer
Purpose: Total revenue per month.
*/
• Inline Comments: Only for non-obvious logic
Example:
-- Exclude internal test orders
WHERE order_type <> 'TEST'
4. Query Guidelines
• Avoid SELECT *; always list needed columns.
Example:
SELECT customer_id, first_name, last_name
FROM customers;

• Use ANSI JOIN syntax; put join conditions in ON, filters in WHERE.
Example:
SELECT o.order_id, c.customer_name
FROM sales_orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id
WHERE o.order_date >= '2025-01-01';

• Aggregations: include non-aggregates in GROUP BY.


Example:
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
FROM sales_orders
GROUP BY customer_id;

5. DML Standards
• INSERT: list columns, use OUTPUT for IDs.
Example:
INSERT INTO customers (first_name, last_name, status)
OUTPUT INSERTED.customer_id
VALUES ('Alice', 'Smith', 'A');

• UPDATE: always include WHERE.


Example:
UPDATE sales_orders
SET status = 'COMPLETE'
WHERE order_date < '2024-01-01';

• DELETE: prefer soft deletes.


Example:
UPDATE customers
SET is_deleted = 1
WHERE customer_id = @id;

• Transactions: wrap multi-step DML.


Example:
BEGIN TRANSACTION;
-- multiple updates
COMMIT;
6. DDL Standards
• CREATE TABLE: explicit types, nullability, defaults, PK inline.
Example:
CREATE TABLE customers (
customer_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
status CHAR(1) NOT NULL DEFAULT 'A'
);

• Migrations: use ALTER scripts, track in version control.

7. Procedures & Functions


• Naming: sp_… for procedures, fn_… for functions
• Structure: SET NOCOUNT ON; TRY…CATCH
Example:
CREATE PROCEDURE sp_get_orders
@customer_id INT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SELECT order_id, order_date, total_amount
FROM sales_orders
WHERE customer_id = @customer_id;
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;

8. Performance & Security


• Indexing: on columns used in WHERE, JOIN, ORDER BY
• Avoid functions on indexed columns in predicates
• Least Privilege: grant only needed rights via roles
Example:
GRANT SELECT ON [Link] TO reporting_role;
• SQL Injection: always use parameterized queries

Common questions

Powered by AI

The SQL coding standards emphasize creating indexes on columns involved in WHERE, JOIN, and ORDER BY clauses to optimize query performance. However, the standards caution against using functions on indexed columns within predicates, as this can negatively impact performance by preventing the use of index seeks .

The standards emphasize granting the least privilege by assigning necessary rights through roles, and they advocate using parameterized queries to prevent SQL injection attacks. Parameterized queries ensure that user inputs are treated as data rather than executable code, effectively mitigating a common attack vector .

Header comments are placed at the top of each script or object to describe its purpose, author, and creation date, providing essential context for understanding the script's role. Inline comments are used sparingly for non-obvious logic to clarify complex parts of the code. These practices enhance code readability and maintainability by making the logic and structure clear to developers .

Consistent naming conventions are crucial to ensure clarity and facilitate ease of understanding the purpose and type of database objects. For instance, tables are named as plural nouns (e.g., 'customers'), columns should represent attributes clearly using formats like 'entity_attribute' (e.g., 'customer_id'), primary keys follow the format 'pk_table' (e.g., 'pk_customers'), and indexes are named 'idx_table_col1_col2' (e.g., 'idx_orders_customer_date').

The guidelines recommend wrapping multi-step DML operations in transactions using BEGIN TRANSACTION and COMMIT to ensure atomicity. This means that either all changes are applied or none, which is crucial for maintaining data consistency and integrity in case of errors or system failures during complex updates .

The document recommends using ALTER scripts for schema changes and tracking them in version control systems. This practice is important because it ensures that changes are systematically documented, reducing the risk of errors, and allows for rolling back changes when needed, which is crucial for maintaining the integrity and consistency of the database .

'Soft deletes' refer to the practice of marking records as deleted by updating a flag (e.g., setting 'is_deleted' to 1) rather than physically deleting the records from the database. This approach allows for data recovery and auditing, avoids data loss, and can help maintain referential integrity and historical records .

Using SELECT * is discouraged because it can lead to fetching unnecessary data, which impacts performance and readability, especially when database schemas change. The standards recommend explicitly listing required columns to provide clarity about what data is being retrieved and to optimize resource usage .

The document prescribes the use of uppercase for SQL keywords, lowercase_snake_case for identifiers, and a 2-space indentation per level, with line breaks after each clause and blank lines between major blocks. These formatting rules help improve readability and maintainability of the code by providing a structured and consistent appearance, facilitating smooth navigation and comprehension of complex SQL scripts .

TRY...CATCH blocks in stored procedures help manage and handle exceptions robustly by providing mechanisms to catch errors and respond with appropriate actions. This supports better error handling and ensures that the application can cope with unexpected conditions without crashing, enabling logging or user notifications .

You might also like