SQL Development Standards

Database Schema Generation

• •MUST use singular form for all table names
• •MUST use singular form for all column names
• •MUST include a primary key column named id in all tables
• •MUST include created_at column to store creation timestamp
• •MUST include updated_at column to store last update timestamp

Database Schema Design

• •MUST define primary key constraint for all tables
• •MUST name all foreign key constraints
• •MUST define foreign key constraints inline
• •MUST use ON DELETE CASCADE option for foreign key constraints
• •MUST use ON UPDATE CASCADE option for foreign key constraints
• •MUST reference the primary key of the parent table in foreign key constraints

SQL Coding Style

• •MUST use uppercase for SQL keywords (SELECT, FROM, WHERE)
• •MUST use consistent indentation for nested queries and conditions
• •MUST include comments to explain complex logic
• •SHOULD break long queries into multiple lines for readability
• •MUST organize clauses consistently (SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY)

SQL Query Structure

• •MUST use explicit column names in SELECT statements instead of SELECT *
• •MUST qualify column names with table name or alias when using multiple tables
• •SHOULD limit the use of subqueries when joins can be used instead
• •MUST include LIMIT/TOP clauses to restrict result sets
• •MUST use appropriate indexing for frequently queried columns
• •MUST NOT use functions on indexed columns in WHERE clauses

Stored Procedure Naming Conventions

• •MUST prefix stored procedure names with usp_
• •MUST use PascalCase for stored procedure names
• •MUST use descriptive names that indicate purpose (e.g., usp_GetCustomerOrders)
• •MUST include plural noun when returning multiple records (e.g., usp_GetProducts)
• •MUST include singular noun when returning single record (e.g., usp_GetProduct)

Parameter Handling

• •MUST prefix parameters with @
• •MUST use camelCase for parameter names
• •SHOULD provide default values for optional parameters
• •MUST validate parameter values before use
• •MUST document parameters with comments
• •SHOULD arrange parameters consistently (required first, optional later)

Stored Procedure Structure

• •MUST include header comment block with description, parameters, and return values
• •MUST return standardized error codes/messages
• •MUST return result sets with consistent column order
• •SHOULD use OUTPUT parameters for returning status information
• •MUST prefix temporary tables with tmp_

Example Stored Procedure

/*
  Procedure: usp_GetCustomerOrders
  Description: Retrieves all orders for a specific customer
  Parameters:
    @customerId INT - The ID of the customer
    @startDate DATETIME - Optional start date filter
  Returns: Result set of customer orders
*/
CREATE PROCEDURE usp_GetCustomerOrders
    @customerId INT,
    @startDate DATETIME = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate parameters
    IF @customerId IS NULL OR @customerId <= 0
    BEGIN
        RAISERROR('Invalid customer ID', 16, 1);
        RETURN -1;
    END

    -- Main query
    SELECT
        o.id,
        o.order_date,
        o.total_amount,
        o.status
    FROM
        [order] o
    WHERE
        o.customer_id = @customerId
        AND (@startDate IS NULL OR o.order_date >= @startDate)
    ORDER BY
        o.order_date DESC;

    RETURN 0;
END

SQL Security Best Practices

• •MUST parameterize all queries to prevent SQL injection
• •MUST use prepared statements when executing dynamic SQL
• •MUST NOT embed credentials in SQL scripts
• •MUST implement proper error handling without exposing system details
• •SHOULD avoid using dynamic SQL within stored procedures

SQL Injection Prevention

-- BAD: Vulnerable to SQL injection
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM users WHERE username = ''' + @username + '''';
EXEC(@sql);

-- GOOD: Parameterized query
SELECT * FROM users WHERE username = @username;

Transaction Management

• •MUST explicitly begin and commit transactions
• •MUST use appropriate isolation levels based on requirements
• •MUST NOT create long-running transactions that lock tables
• •SHOULD use batch processing for large data operations
• •MUST include SET NOCOUNT ON for stored procedures that modify data

Example Transaction

BEGIN TRANSACTION;
BEGIN TRY
    -- Your SQL operations here
    UPDATE account SET balance = balance - @amount WHERE id = @fromAccount;
    UPDATE account SET balance = balance + @amount WHERE id = @toAccount;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Log error without exposing details
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    RAISERROR('Transaction failed', 16, 1);
END CATCH

Performance Best Practices

• •MUST analyze query execution plans for optimization opportunities
• •SHOULD use covering indexes for frequently executed queries
• •MUST avoid SELECT * in production code
• •SHOULD use EXISTS instead of COUNT(*) for existence checks
• •MUST minimize the use of cursors; use set-based operations instead
• •SHOULD implement appropriate query hints only when necessary

Testing and Validation

• •MUST test stored procedures with various input scenarios
• •MUST validate edge cases (NULL values, empty strings, boundary conditions)
• •SHOULD include performance testing for critical queries
• •MUST verify that error handling works as expected
• •SHOULD document expected behavior and test results