AgentSkillsCN

sql-anti-patterns

识别并修复 SQL Server 中的反模式，包括游标使用、SELECT *、非 SARGable 谓词、标量子查询、隐式类型转换、缺失的错误处理、参数嗅探问题，以及 RBAR（逐行痛苦处理）模式。提供严重程度分级、前后对比示例，以及性能影响估算。

SKILL.md
--- frontmatter
name: sql-anti-patterns
description: Identify and fix SQL Server anti-patterns including cursors, SELECT *, non-SARGable predicates, scalar subqueries, implicit type conversions, missing error handling, parameter sniffing issues, and RBAR (Row-By-Agonizing-Row) processing. Provides severity ratings, before/after examples, and performance impact estimates.

SQL Anti-Patterns Detection & Remediation

This skill helps identify common SQL Server performance anti-patterns and provides proven fixes.

When to Use

  • •Reviewing stored procedures, views, or queries for performance issues
  • •Analyzing slow-running SQL code
  • •Performing SQL code reviews
  • •Rewriting suboptimal T-SQL patterns

Severity Levels

LevelImpactAction
Critical>10x performance degradationFix immediately
High5-10x performance degradationFix before production
Medium2-5x performance degradationFix in next sprint
Low<2x but measurableAddress when convenient

Anti-Pattern Catalog

Detailed catalog with before/after examples for each anti-pattern:

Quick Reference

Critical Anti-Patterns

  1. •Non-SARGable predicates — Functions on indexed columns force full table scans
  2. •Cursor / RBAR processing — Row-by-row instead of set-based operations
  3. •Cartesian products — Missing or incorrect JOIN predicates

High Anti-Patterns

  1. •SELECT * — Unnecessary columns, prevents covering index usage
  2. •Scalar subqueries in SELECT — Execute once per outer row
  3. •LIKE with leading wildcard — Cannot use B-tree index
  4. •Implicit type conversion — Mismatched types force column conversion

Medium Anti-Patterns

  1. •Missing error handling — No TRY/CATCH, partial execution risk
  2. •Unnecessary DISTINCT — Masks duplicates from bad joins
  3. •Parameter sniffing — First plan may be suboptimal for later values

Low Anti-Patterns

  1. •Missing SET NOCOUNT ON — Extra network roundtrips
  2. •Temp table without cleanup — Resource leak in long sessions