1---2name: sql-development3description: T-SQL, stored procedures, and MS SQL Server DBA practices. Use when writing SQL queries, designing schemas, tuning SQL Server performance, managing backups, configuring security, or using SQL Server 2025+ features.4license: Complete terms in LICENSE.txt5metadata:6 source: https://skillmd.com/skills/diegosouzapw/sql-development7---89# SQL Development1011Comprehensive SQL development guidelines combining SQL coding standards, stored procedure generation, and MS SQL Server DBA best practices.1213## Skill Paths1415- Workspace skills: `.github/skills/`16- Global skills: `C:/Users/LOQ/.agents/skills/`1718## Activation Conditions1920- Writing SQL queries and stored procedures21- Designing database schemas and table structures22- Working with MS SQL Server as a DBA23- Performance tuning and query optimization24- Database backup, restore, and security configuration25- SQL Server 2025+ feature adoption and migration2627---2829## Part 1: Database Schema Design3031### Table Naming32- All table names in singular form33- All column names in singular form3435### Required Columns36- All tables must have a primary key column named `id`37- All tables must have `created_at` for creation timestamp38- All tables must have `updated_at` for last update timestamp3940### Constraints41- All tables must have a primary key constraint42- All foreign key constraints must have a name43- All foreign key constraints defined inline44- All foreign keys must have `ON DELETE CASCADE`45- All foreign keys must have `ON UPDATE CASCADE`46- All foreign keys must reference the primary key of the parent table4748---4950## Part 2: SQL Coding Style5152### Formatting53- Uppercase for SQL keywords (`SELECT`, `FROM`, `WHERE`)54- Consistent indentation for nested queries55- Comments to explain complex logic56- Break long queries into multiple lines57- Organize clauses: `SELECT`, `FROM`, `JOIN`, `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`5859### Query Structure60- Use explicit column names, never `SELECT *`61- Qualify column names with table alias when using multiple tables62- Prefer JOINs over subqueries when possible63- Include `LIMIT`/`TOP` clauses to restrict result sets64- Use appropriate indexing for frequently queried columns65- Avoid functions on indexed columns in `WHERE` clauses6667---6869## Part 3: Stored Procedure Standards7071### Naming Conventions72- Prefix with `usp_`73- Use PascalCase: `usp_GetCustomerOrders`74- Include plural noun for multiple records: `usp_GetProducts`75- Include singular noun for single record: `usp_GetProduct`7677### Parameter Handling78- Prefix parameters with `@`79- Use camelCase: `@customerId`80- Provide default values for optional parameters81- Validate parameter values before use82- Document parameters with comments83- Required parameters first, optional later8485### Structure86- Include header comment block with description, parameters, return values87- Return standardized error codes/messages88- Return result sets with consistent column order89- Use `OUTPUT` parameters for returning status information90- Prefix temporary tables with `tmp_`91- Include `SET NOCOUNT ON` for data-modifying procedures9293---9495## Part 4: Security Best Practices9697### Query Security98- Parameterize all queries to prevent SQL injection99- Use prepared statements for dynamic SQL100- Avoid embedding credentials in SQL scripts101- Proper error handling without exposing system details102- Avoid dynamic SQL in stored procedures103104### Transaction Management105- Explicitly begin and commit transactions106- Use appropriate isolation levels107- Avoid long-running transactions that lock tables108- Use batch processing for large data operations109110---111112## Part 5: MS SQL Server DBA113114### Tooling115- Install and enable `ms-mssql.mssql` VS Code extension for full database management116- Use official Microsoft documentation for reference and troubleshooting117118### DBA Responsibilities119- Database creation and configuration120- Backup and restore strategies121- Performance tuning and index optimization122- Security management and auditing123- Upgrades and compatibility planning (SQL Server 2025+)124125### Best Practices126- Focus on tool-based database inspection over codebase analysis127- Highlight deprecated/discontinued features in SQL Server 2025+128- Encourage secure, auditable, performance-oriented solutions129- Reference official docs for troubleshooting130- Warn about deprecated features and suggest alternatives131132---133134## Troubleshooting135136| Issue | Solution |137|-------|----------|138| Slow queries | Check execution plan, add indexes, optimize JOINs |139| Deadlocks | Reduce transaction scope, consistent lock ordering |140| Missing data | Verify CASCADE rules, check transaction isolation |141| Permission errors | Review GRANT/REVOKE statements, check role membership |142| Connection issues | Verify firewall rules, connection strings, SQL auth settings |143144---145146## References & Resources147148### Documentation149- [T-SQL Patterns](./references/tsql-patterns.md) — MERGE, CTEs, PIVOT, JSON operations, window functions, and error handling150- [Performance Tuning](./references/performance-tuning.md) — Execution plans, index tuning, Query Store, and anti-patterns151152### Scripts153- [Stored Procedure Template](./scripts/stored-proc-template.sql) — Production-ready SP template with TRY/CATCH, pagination, and dynamic sorting154155### Examples156- [Schema Design Example](./examples/schema-design-example.md) — Recipe Management System with 10 tables, stored procedures, and migrations157158---159160## Related Skills161162| Skill | Relationship |163|-------|-------------|164| [nestjs](../nestjs/SKILL.md) | TypeORM integration with NestJS |165| [php-development](../php-development/SKILL.md) | PDO/MySQL database access from PHP |166| [mongodb-mongoose](../mongodb-mongoose/SKILL.md) | Alternative NoSQL database approach |167| [powerbi-modeling](../powerbi-modeling/SKILL.md) | SQL sources for Power BI semantic models |