SQL Development

SQL Development

Comprehensive SQL development guidelines combining SQL coding standards, stored procedure generation, and MS SQL Server DBA best practices.

Skill Paths

• Workspace skills: .github/skills/
• Global skills: C:/Users/LOQ/.agents/skills/

Activation Conditions

• Writing SQL queries and stored procedures
• Designing database schemas and table structures
• Working with MS SQL Server as a DBA
• Performance tuning and query optimization
• Database backup, restore, and security configuration
• SQL Server 2025+ feature adoption and migration

Part 1: Database Schema Design

Table Naming

• All table names in singular form
• All column names in singular form

Required Columns

• All tables must have a primary key column named id
• All tables must have created_at for creation timestamp
• All tables must have updated_at for last update timestamp

Constraints

• All tables must have a primary key constraint
• All foreign key constraints must have a name
• All foreign key constraints defined inline
• All foreign keys must have ON DELETE CASCADE
• All foreign keys must have ON UPDATE CASCADE
• All foreign keys must reference the primary key of the parent table

Part 2: SQL Coding Style

Formatting

• Uppercase for SQL keywords (SELECT, FROM, WHERE)
• Consistent indentation for nested queries
• Comments to explain complex logic
• Break long queries into multiple lines
• Organize clauses: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY

Query Structure

• Use explicit column names, never SELECT *
• Qualify column names with table alias when using multiple tables
• Prefer JOINs over subqueries when possible
• Include LIMIT/TOP clauses to restrict result sets
• Use appropriate indexing for frequently queried columns
• Avoid functions on indexed columns in WHERE clauses

Part 3: Stored Procedure Standards

Naming Conventions

• Prefix with usp_
• Use PascalCase: usp_GetCustomerOrders
• Include plural noun for multiple records: usp_GetProducts
• Include singular noun for single record: usp_GetProduct

Parameter Handling

• Prefix parameters with @
• Use camelCase: @customerId
• Provide default values for optional parameters
• Validate parameter values before use
• Document parameters with comments
• Required parameters first, optional later

Structure

• Include header comment block with description, parameters, return values
• Return standardized error codes/messages
• Return result sets with consistent column order
• Use OUTPUT parameters for returning status information
• Prefix temporary tables with tmp_
• Include SET NOCOUNT ON for data-modifying procedures

Part 4: Security Best Practices

Query Security

• Parameterize all queries to prevent SQL injection
• Use prepared statements for dynamic SQL
• Avoid embedding credentials in SQL scripts
• Proper error handling without exposing system details
• Avoid dynamic SQL in stored procedures

Transaction Management

• Explicitly begin and commit transactions
• Use appropriate isolation levels
• Avoid long-running transactions that lock tables
• Use batch processing for large data operations

Part 5: MS SQL Server DBA

Tooling

• Install and enable ms-mssql.mssql VS Code extension for full database management
• Use official Microsoft documentation for reference and troubleshooting

DBA Responsibilities

• Database creation and configuration
• Backup and restore strategies
• Performance tuning and index optimization
• Security management and auditing
• Upgrades and compatibility planning (SQL Server 2025+)

Best Practices

• Focus on tool-based database inspection over codebase analysis
• Highlight deprecated/discontinued features in SQL Server 2025+
• Encourage secure, auditable, performance-oriented solutions
• Reference official docs for troubleshooting
• Warn about deprecated features and suggest alternatives

Troubleshooting

References & Resources

Documentation

• T-SQL Patterns — MERGE, CTEs, PIVOT, JSON operations, window functions, and error handling
• Performance Tuning — Execution plans, index tuning, Query Store, and anti-patterns

Scripts

• Stored Procedure Template — Production-ready SP template with TRY/CATCH, pagination, and dynamic sorting

Examples

• Schema Design Example — Recipe Management System with 10 tables, stored procedures, and migrations

Related Skills