stack-sqlserver
Microsoft SQL Server database — indexing, RCSI, set-based operations, SARGable queries, transaction scopes
- License 许可
- Apache-2.0
- 最近更新
- 2026-11-15
- Stars
- ★ 3
- Forks
- 0
01 前言 Front Matter
- name
- stack-sqlserver
- license
- Apache-2.0
- metadata
- { "author": "manuel-retamozo-garcia", "version": "1.0" }
- description
- Microsoft SQL Server database — indexing, RCSI, set-based operations, SARGable queries, transaction scopes
- capabilities
- [ "sqlserver" ]
03 SKILL.md 正文
Critical Rules
- Index Design: Clustered index keys should be sequential/monotonically increasing (e.g., identity columns,
NEWSEQUENTIALID()) to minimize page splits. For non-clustered indexes, use theINCLUDEclause to cover queries instead of adding columns to the index key. - Concurrency (RCSI): Enable Read Committed Snapshot Isolation (RCSI) on the database level to prevent readers from blocking writers and writers from blocking readers.
- Avoid Cursors: Perform set-based operations rather than row-by-row operations (cursors,
WHILEloops with index iteration) to optimize execution engine performance. - SARGable Queries: Write Search Argumentable (SARGable) queries. Do not apply functions or calculations to indexed columns in the
WHEREorONclauses. - Transaction Scope: Keep transactions as short and narrow as possible to minimize locking, page contention, and deadlocks.
Indexing & Storage Best Practices
- Clustered Indexes: Every table must have a clustered index. Prefer a narrow, static, sequential key. Avoid random GUIDs (
NEWID()) as clustered keys as they cause heavy index fragmentation. - Covering Indexes: If a query selects specific columns, create a non-clustered index where the filter criteria columns form the index keys, and other selected columns are added using the
INCLUDEclause:CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Status ON Orders (CustomerId, Status) INCLUDE (OrderDate, TotalAmount);
Query Design & SARGability
- SARGable WHERE Clauses:
- Bad:
WHERE DATEADD(day, 7, OrderDate) >= GETDATE()(Non-SARGable, index scan) - Good:
WHERE OrderDate >= DATEADD(day, -7, GETDATE())(SARGable, index seek)
- Bad:
- Set-Based Operations: Use joins, subqueries, and window functions (
ROW_NUMBER(),RANK()) to manipulate datasets at once. Avoid iterating through table rows manually.
Concurrency & RCSI
- RCSI Enablement: Ensure RCSI is enabled:
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE; - Isolation Levels: Rely on the default Read Committed isolation level (which uses row versioning under RCSI) instead of abusing dirty reads (
NOLOCKtable hints) or overly restrictive isolation levels (SERIALIZABLE). - Short Transactions: Execute non-database operations (HTTP requests, file I/O, slow calculations) outside of database transaction blocks.
04 同仓库更多 Skills
work-unit-commits
Plan commits as reviewable work units. Trigger: implementation, commit splitting, chained PRs, or keeping tests and docs with code.
stack-webmcp
Trigger: WebMCP, document.modelContext, web tools, AI browser agent. Web Model Context Protocol client-side tool standard.
stack-vite
Vite frontend build tool — configuration, plugins, asset loading
stack-starlight
Astro Starlight documentation framework — setup, sidebar navigation, frontmatter, built-in components, asides, theming, i18n, search
stack-springboot
Spring Boot architecture patterns — REST API design, layered services, JPA, caching, Spring Security, TDD with MockMvc and Testcontainers, and pre-release verification
stack-react
React UI library — components, hooks, state management, patterns, performance, and component testing with React Testing Library