AgentSkillsCN

sql-development

结合存储过程生成与MS SQL Server DBA最佳实践的SQL开发规范。适用于SQL查询的编写、存储过程的生成、数据库架构的设计、DBA相关任务的执行、SQL Server性能的调优、备份管理、安全配置，或SQL Server 2025+新功能的探索与应用。

SKILL.md
--- frontmatter
name: sql-development
description: SQL development standards combining stored procedure generation and MS SQL Server DBA best practices. Use when writing SQL queries, generating stored procedures, designing database schemas, performing DBA tasks, tuning SQL Server performance, managing backups, configuring security, or working with SQL Server 2025+ features.
license: Complete terms in LICENSE.txt

SQL Development

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

When to Use This Skill

  • •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

No-Activation Conditions

Do NOT activate this skill when:

  • •User requests simple SQL SELECT examples without implementation context
  • •Task is for other databases (e.g., PostgreSQL, MySQL) when not using MS SQL Server
  • •User asks basic conceptual questions about databases (e.g., "what is SQL?")
  • •Request is for ORM usage (e.g., ActiveRecord, Entity Framework) without raw SQL
  • •User wants quick query example without DBA or optimization context
  • •Task is about data modeling/design without SQL implementation detail

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

IssueSolution
Slow queriesCheck execution plan, add indexes, optimize JOINs
DeadlocksReduce transaction scope, consistent lock ordering
Missing dataVerify CASCADE rules, check transaction isolation
Permission errorsReview GRANT/REVOKE statements, check role membership
Connection issuesVerify firewall rules, connection strings, SQL auth settings

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

Examples

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