shield_lock
金大哥
← 返回 Skill 列表
extension
分类: 开发与工程无需 API Key

sql-development

T-SQL、存储过程和MS SQL Server数据库管理员实践。在编写SQL查询、设计模式、调整SQL Server性能、管理备份、配置安全性或使用SQL Server 2025+功能时使用。

person作者: jakexiaohubgithub
open_in_new仓库download下载资源包

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

| Issue | Solution | |-------|----------| | Slow queries | Check execution plan, add indexes, optimize JOINs | | Deadlocks | Reduce transaction scope, consistent lock ordering | | Missing data | Verify CASCADE rules, check transaction isolation | | Permission errors | Review GRANT/REVOKE statements, check role membership | | Connection issues | Verify 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

Related Skills

| Skill | Relationship | |-------|-------------| | nestjs | TypeORM integration with NestJS | | php-development | PDO/MySQL database access from PHP | | mongodb-mongoose | Alternative NoSQL database approach | | powerbi-modeling | SQL sources for Power BI semantic models |