azure-sql-best-practices

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Azure SQL Database Best Practices

Azure SQL Database 最佳实践

Comprehensive best practices guide for Azure SQL Database development and optimization. This skill helps AI agents analyze and improve T-SQL scripts, application database code, indexing strategies, security configurations, and connection patterns.

Based on:

针对Azure SQL Database开发与优化的全面最佳实践指南。本指南可帮助AI Agent分析并改进T-SQL脚本、应用数据库代码、索引策略、安全配置及连接模式。

基于：

When to Apply

适用场景

Reference these guidelines when:

Writing new T-SQL queries, stored procedures, or scripts
Reviewing database code for performance issues
Configuring Azure SQL Database settings
Implementing data access patterns in applications
Optimizing indexing strategies
Auditing security configurations
Refactoring existing database code
Migrating from SQL Server to Azure SQL Database

在以下场景中可参考本指南：

编写新的T-SQL查询、存储过程或脚本
评审数据库代码以排查性能问题
配置Azure SQL Database设置
在应用中实现数据访问模式
优化索引策略
审计安全配置
重构现有数据库代码
从SQL Server迁移至Azure SQL Database

Rule Categories by Priority

按优先级划分的规则类别


query-
index-
security-
connection-
tsql-
SR****
config-
model-
monitor-

Priority	Category	Impact	Prefix
1	Query Performance	CRITICAL	`query-`
2	Indexing Strategy	CRITICAL	`index-`
3	Security & Compliance	HIGH	`security-`
4	Connection Management	HIGH	`connection-`
5	T-SQL Patterns	MEDIUM-HIGH	`tsql-`
6	SSDT Code Analysis	MEDIUM-HIGH	`SR****`
7	Database Configuration	MEDIUM	`config-`
8	Data Modeling	MEDIUM	`model-`
9	Monitoring & Diagnostics	LOW-MEDIUM	`monitor-`


query-
index-
security-
connection-
tsql-
SR****
config-
model-
monitor-

优先级	类别	影响级别	前缀
1	查询性能	关键	`query-`
2	索引策略	关键	`index-`
3	安全与合规	高	`security-`
4	连接管理	高	`connection-`
5	T-SQL模式	中高	`tsql-`
6	SSDT代码分析	中高	`SR****`
7	数据库配置	中	`config-`
8	数据建模	中	`model-`
9	监控与诊断	中低	`monitor-`

SSDT Code Analysis Rules (Microsoft Static Analysis)

SSDT代码分析规则（微软静态分析）

These rules are from Microsoft's SQL Server Data Tools (SSDT) static code analysis. They are enforced in Visual Studio Database Projects.

这些规则来自微软SQL Server数据工具（SSDT）的静态代码分析，在Visual Studio数据库项目中强制执行。

Design Issues (SR0001, SR0008-SR0014)

设计问题（SR0001, SR0008-SR0014）

Rule ID	Description	Severity
SR0001	Avoid SELECT * in queries	HIGH
SR0008	Use SCOPE_IDENTITY() instead of @@IDENTITY	MEDIUM
SR0009	Avoid VARCHAR/NVARCHAR with size 1 or 2	LOW
SR0010	Avoid deprecated = and = join syntax	MEDIUM
SR0013	Output parameter not populated in all code paths	MEDIUM
SR0014	Potential data loss from implicit type casting	HIGH

规则ID	描述	严重级别
SR0001	避免在查询中使用SELECT *	高
SR0008	使用SCOPE_IDENTITY()替代@@IDENTITY	中
SR0009	避免使用长度为1或2的VARCHAR/NVARCHAR	低
SR0010	避免使用已弃用的=和=连接语法	中
SR0013	输出参数未在所有代码路径中赋值	中
SR0014	隐式类型转换可能导致数据丢失	高

Performance Issues (SR0004-SR0007, SR0015)

性能问题（SR0004-SR0007, SR0015）

Rule ID	Description	Severity
SR0004	Avoid non-indexed columns in IN predicates	HIGH
SR0005	Avoid LIKE patterns starting with '%'	HIGH
SR0006	Move column reference to one side of comparison	MEDIUM
SR0007	Use ISNULL(column, default) on nullable columns	MEDIUM
SR0015	Extract deterministic function calls from WHERE	MEDIUM

规则ID	描述	严重级别
SR0004	避免在IN谓词中使用未索引的列	高
SR0005	避免使用以'%'开头的LIKE模式	高
SR0006	将列引用移至比较运算符的一侧	中
SR0007	对可为空的列使用ISNULL(column, default)	中
SR0015	从WHERE子句中提取确定性函数调用	中

Naming Issues (SR0011, SR0012, SR0016)

命名问题（SR0011, SR0012, SR0016）

Rule ID	Description	Severity
SR0011	Avoid special characters in object names	LOW
SR0012	Avoid reserved words for type names	MEDIUM
SR0016	Avoid sp_ prefix for stored procedures	MEDIUM

规则ID	描述	严重级别
SR0011	避免在对象名称中使用特殊字符	低
SR0012	避免使用保留字作为类型名称	中
SR0016	避免为存储过程使用sp_前缀	中

Quick Reference

快速参考

1. Query Performance (CRITICAL)

1. 查询性能（关键）

```
query-avoid-select-star
```
(SR0001) - Never use SELECT * in production code
```
query-parameterize
```
- Always use parameterized queries to prevent SQL injection and enable plan caching
```
query-avoid-functions-on-columns
```
- Don't apply functions to columns in WHERE clauses
```
query-sargable
```
- Write SARGable (Search ARGument ABLE) predicates for index usage
```
query-batch-operations
```
- Batch INSERT/UPDATE/DELETE operations to reduce round trips
```
query-avoid-cursors
```
- Replace cursors with set-based operations
```
query-limit-results
```
- Use TOP or OFFSET-FETCH for pagination
```
query-avoid-implicit-conversion
```
(SR0014) - Match data types to prevent implicit conversions
```
query-join-optimization
```
- Order joins for optimal execution plans
```
query-exists-vs-count
```
- Use EXISTS instead of COUNT(*) > 0
```
query-avoid-leading-wildcard
```
(SR0005) - Avoid LIKE '%value' patterns

```
query-avoid-select-star
```
(SR0001) - 生产环境代码中严禁使用SELECT *
```
query-parameterize
```
- 始终使用参数化查询以防止SQL注入并启用执行计划缓存
```
query-avoid-functions-on-columns
```
- 不要在WHERE子句中对列应用函数
```
query-sargable
```
- 编写支持索引使用的SARGable（可搜索参数）谓词
```
query-batch-operations
```
- 批量处理INSERT/UPDATE/DELETE操作以减少往返次数
```
query-avoid-cursors
```
- 使用基于集合的操作替代游标
```
query-limit-results
```
- 使用TOP或OFFSET-FETCH实现分页
```
query-avoid-implicit-conversion
```
(SR0014) - 匹配数据类型以避免隐式转换
```
query-join-optimization
```
- 合理排序连接顺序以生成最优执行计划
```
query-exists-vs-count
```
- 使用EXISTS替代COUNT(*) > 0
```
query-avoid-leading-wildcard
```
(SR0005) - 避免使用LIKE '%value'模式

2. Indexing Strategy (CRITICAL)

2. 索引策略（关键）

```
index-cover-queries
```
- Create covering indexes for frequent queries
```
index-avoid-over-indexing
```
- Balance read vs write performance
```
index-missing-index-dmv
```
- Use DMVs to identify missing indexes
```
index-unused-indexes
```
- Remove unused indexes consuming resources
```
index-fragmentation
```
- Monitor and address index fragmentation
```
index-columnstore
```
- Use columnstore indexes for analytics workloads
```
index-filtered
```
- Use filtered indexes for subset queries
```
index-include-columns
```
- Use INCLUDE for non-key columns
```
index-key-order
```
- Order index keys by selectivity
```
index-avoid-wide-keys
```
- Keep index keys narrow
```
index-in-predicate
```
(SR0004) - Ensure columns in IN predicates are indexed

```
index-cover-queries
```
- 为频繁执行的查询创建覆盖索引
```
index-avoid-over-indexing
```
- 平衡读取与写入性能
```
index-missing-index-dmv
```
- 使用DMV识别缺失的索引
```
index-unused-indexes
```
- 删除占用资源的未使用索引
```
index-fragmentation
```
- 监控并解决索引碎片问题
```
index-columnstore
```
- 为分析型工作负载使用列存储索引
```
index-filtered
```
- 为子集查询使用筛选索引
```
index-include-columns
```
- 为非键列使用INCLUDE子句
```
index-key-order
```
- 按选择性排序索引键
```
index-avoid-wide-keys
```
- 保持索引键简洁
```
index-in-predicate
```
(SR0004) - 确保IN谓词中的列已建立索引

3. Security & Compliance (HIGH)

3. 安全与合规（高）

```
security-parameterize-queries
```
- Prevent SQL injection with parameters
```
security-least-privilege
```
- Grant minimum required permissions
```
security-avoid-sa
```
- Never use sa or dbo for application access
```
security-encrypt-connections
```
- Always use encrypted connections
```
security-row-level-security
```
- Implement RLS for multi-tenant apps
```
security-dynamic-data-masking
```
- Mask sensitive data
```
security-always-encrypted
```
- Use Always Encrypted for sensitive columns
```
security-tde
```
- Enable Transparent Data Encryption
```
security-audit-logging
```
- Enable SQL Audit for compliance
```
security-vulnerability-assessment
```
- Regular vulnerability scans

```
security-parameterize-queries
```
- 使用参数化查询防止SQL注入
```
security-least-privilege
```
- 授予最小必要权限
```
security-avoid-sa
```
- 切勿使用sa或dbo账户进行应用访问
```
security-encrypt-connections
```
- 始终使用加密连接
```
security-row-level-security
```
- 为多租户应用实现行级安全性（RLS）
```
security-dynamic-data-masking
```
- 对敏感数据进行掩码处理
```
security-always-encrypted
```
- 对敏感列使用Always Encrypted
```
security-tde
```
- 启用透明数据加密（TDE）
```
security-audit-logging
```
- 启用SQL审计以满足合规要求
```
security-vulnerability-assessment
```
- 定期进行漏洞扫描

4. Connection Management (HIGH)

4. 连接管理（高）

```
connection-pooling
```
- Always use connection pooling
```
connection-retry-logic
```
- Implement retry logic for transient failures
```
connection-timeout
```
- Set appropriate connection timeouts
```
connection-close-dispose
```
- Always close/dispose connections
```
connection-async
```
- Use async/await for database calls
```
connection-read-replicas
```
- Use read replicas for read workloads
```
connection-application-intent
```
- Set ApplicationIntent for read replicas
```
connection-multisubnetfailover
```
- Enable for geo-replicated databases

```
connection-pooling
```
- 始终使用连接池
```
connection-retry-logic
```
- 为瞬态故障实现重试逻辑
```
connection-timeout
```
- 设置合适的连接超时时间
```
connection-close-dispose
```
- 始终关闭/释放连接
```
connection-async
```
- 对数据库调用使用async/await
```
connection-read-replicas
```
- 为读取工作负载使用只读副本
```
connection-application-intent
```
- 为只读副本设置ApplicationIntent
```
connection-multisubnetfailover
```
- 为异地复制数据库启用该选项

5. T-SQL Patterns (MEDIUM-HIGH)

5. T-SQL模式（中高）

```
tsql-set-nocount
```
- Use SET NOCOUNT ON in stored procedures
```
tsql-schema-qualify
```
- Always schema-qualify object names
```
tsql-avoid-hints
```
- Avoid query hints unless necessary
```
tsql-temp-tables-vs-variables
```
- Choose appropriately between temp tables and table variables
```
tsql-transaction-scope
```
- Keep transactions short
```
tsql-error-handling
```
- Use TRY-CATCH with proper error handling
```
tsql-avoid-triggers
```
- Minimize trigger usage
```
tsql-cte-vs-subquery
```
- Use CTEs for readability and recursion
```
tsql-merge-carefully
```
- Use MERGE with caution
```
tsql-avoid-dynamic-sql
```
- Minimize dynamic SQL, parameterize when used
```
tsql-scope-identity
```
(SR0008) - Use SCOPE_IDENTITY() instead of @@IDENTITY
```
tsql-avoid-deprecated-joins
```
(SR0010) - Use ANSI JOIN syntax, not = or =
```
tsql-output-params
```
(SR0013) - Populate output parameters in all code paths
```
tsql-avoid-sp-prefix
```
(SR0016) - Don't prefix stored procedures with sp_

```
tsql-set-nocount
```
- 在存储过程中使用SET NOCOUNT ON
```
tsql-schema-qualify
```
- 始终使用架构限定对象名称
```
tsql-avoid-hints
```
- 除非必要，否则避免使用查询提示
```
tsql-temp-tables-vs-variables
```
- 合理选择临时表与表变量
```
tsql-transaction-scope
```
- 保持事务简短
```
tsql-error-handling
```
- 使用TRY-CATCH实现适当的错误处理
```
tsql-avoid-triggers
```
- 尽量减少触发器的使用
```
tsql-cte-vs-subquery
```
- 使用CTE提高可读性并支持递归
```
tsql-merge-carefully
```
- 谨慎使用MERGE语句
```
tsql-avoid-dynamic-sql
```
- 尽量减少动态SQL的使用，必要时使用参数化
```
tsql-scope-identity
```
(SR0008) - 使用SCOPE_IDENTITY()替代@@IDENTITY
```
tsql-avoid-deprecated-joins
```
(SR0010) - 使用ANSI JOIN语法，而非*=或=*
```
tsql-output-params
```
(SR0013) - 在所有代码路径中为输出参数赋值
```
tsql-avoid-sp-prefix
```
(SR0016) - 不要为存储过程添加sp_前缀

6. Data Type Best Practices (MEDIUM)

6. 数据类型最佳实践（中）

```
type-appropriate-size
```
(SR0009) - Avoid VARCHAR(1) or VARCHAR(2), use CHAR instead
```
type-avoid-deprecated
```
- Don't use TEXT, NTEXT, IMAGE types
```
type-match-column-types
```
- Match parameter types to column types
```
type-avoid-max-unnecessarily
```
- Use specific sizes instead of MAX when possible
```
type-nullable-handling
```
(SR0007) - Use ISNULL on nullable columns in expressions
```
type-reserved-words
```
(SR0012) - Don't use reserved words for type names

```
type-appropriate-size
```
(SR0009) - 避免使用VARCHAR(1)或VARCHAR(2)，改用CHAR
```
type-avoid-deprecated
```
- 不要使用TEXT、NTEXT、IMAGE类型
```
type-match-column-types
```
- 匹配参数类型与列类型
```
type-avoid-max-unnecessarily
```
- 尽可能使用特定长度而非MAX
```
type-nullable-handling
```
(SR0007) - 在表达式中对可为空的列使用ISNULL
```
type-reserved-words
```
(SR0012) - 不要使用保留字作为类型名称

7. Naming Conventions (MEDIUM)

7. 命名规范（中）

```
naming-avoid-special-chars
```
(SR0011) - Avoid special characters in object names
```
naming-avoid-reserved-words
```
(SR0012) - Don't use reserved words as identifiers
```
naming-consistent-case
```
- Use consistent casing (PascalCase or snake_case)
```
naming-descriptive
```
- Use descriptive, meaningful names
```
naming-avoid-prefixes
```
- Avoid Hungarian notation prefixes

```
naming-avoid-special-chars
```
(SR0011) - 避免在对象名称中使用特殊字符
```
naming-avoid-reserved-words
```
(SR0012) - 不要使用保留字作为标识符
```
naming-consistent-case
```
- 使用一致的大小写（PascalCase或snake_case）
```
naming-descriptive
```
- 使用具有描述性的有意义名称
```
naming-avoid-prefixes
```
- 避免使用匈牙利命名法前缀

8. Database Configuration (MEDIUM)

8. 数据库配置（中）

```
config-query-store
```
- Enable Query Store for performance insights
```
config-auto-tuning
```
- Enable automatic tuning
```
config-max-dop
```
- Configure appropriate MAXDOP
```
config-memory-grant
```
- Monitor memory grants
```
config-compatibility-level
```
- Use appropriate compatibility level
```
config-auto-stats
```
- Enable auto create/update statistics
```
config-page-verify
```
- Use CHECKSUM for page verification
```
config-recovery-model
```
- Choose appropriate recovery model
```
config-tempdb
```
- Optimize tempdb configuration
```
config-accelerated-recovery
```
- Enable Accelerated Database Recovery

```
config-query-store
```
- 启用查询存储以获取性能洞察
```
config-auto-tuning
```
- 启用自动调优
```
config-max-dop
```
- 配置合适的MAXDOP
```
config-memory-grant
```
- 监控内存授予
```
config-compatibility-level
```
- 使用合适的兼容级别
```
config-auto-stats
```
- 启用自动创建/更新统计信息
```
config-page-verify
```
- 使用CHECKSUM进行页验证
```
config-recovery-model
```
- 选择合适的恢复模式
```
config-tempdb
```
- 优化tempdb配置
```
config-accelerated-recovery
```
- 启用加速数据库恢复

9. Data Modeling (MEDIUM)

9. 数据建模（中）

```
model-normalization
```
- Normalize appropriately (3NF minimum)
```
model-appropriate-types
```
- Use appropriate data types
```
model-avoid-nullable
```
- Minimize NULL columns where possible
```
model-partition-strategy
```
- Implement partitioning for large tables
```
model-computed-columns
```
- Use computed columns for derived values
```
model-constraint-enforcement
```
- Use constraints for data integrity
```
model-hierarchical-pk
```
- Use hierarchical partition keys for scale
```
model-temporal-tables
```
- Use temporal tables for audit trails
```
model-json-columns
```
- Use JSON columns judiciously

```
model-normalization
```
- 合理规范化（至少3NF）
```
model-appropriate-types
```
- 使用合适的数据类型
```
model-avoid-nullable
```
- 尽可能减少可为空的列
```
model-partition-strategy
```
- 为大型表实现分区策略
```
model-computed-columns
```
- 使用计算列存储派生值
```
model-constraint-enforcement
```
- 使用约束保证数据完整性
```
model-hierarchical-pk
```
- 使用分层分区键实现扩展
```
model-temporal-tables
```
- 使用时态表实现审计追踪
```
model-json-columns
```
- 谨慎使用JSON列

10. Monitoring & Diagnostics (LOW-MEDIUM)

10. 监控与诊断（中低）

```
monitor-query-performance-insight
```
- Use Query Performance Insight
```
monitor-dmvs
```
- Leverage DMVs for diagnostics
```
monitor-extended-events
```
- Use Extended Events for tracing
```
monitor-intelligent-insights
```
- Enable Intelligent Insights
```
monitor-resource-utilization
```
- Track DTU/vCore usage
```
monitor-deadlock-analysis
```
- Analyze and prevent deadlocks
```
monitor-wait-statistics
```
- Monitor wait statistics
```
monitor-log-io
```
- Monitor transaction log I/O

```
monitor-query-performance-insight
```
- 使用查询性能洞察
```
monitor-dmvs
```
- 利用DMV进行诊断
```
monitor-extended-events
```
- 使用扩展事件进行跟踪
```
monitor-intelligent-insights
```
- 启用智能洞察
```
monitor-resource-utilization
```
- 跟踪DTU/vCore使用情况
```
monitor-deadlock-analysis
```
- 分析并预防死锁
```
monitor-wait-statistics
```
- 监控等待统计信息
```
monitor-log-io
```
- 监控事务日志I/O

How to Use

使用方法

Read individual rule files for detailed explanations and code examples:

rules/query-avoid-select-star.md
rules/index-cover-queries.md
rules/security-parameterize-queries.md
rules/tsql-code-analysis.md

Each rule file contains:

Brief explanation of why it matters
Incorrect code example with explanation
Correct code example with explanation
How to detect violations
References and additional context

阅读单个规则文件获取详细说明及代码示例：

rules/query-avoid-select-star.md
rules/index-cover-queries.md
rules/security-parameterize-queries.md
rules/tsql-code-analysis.md

每个规则文件包含：

规则重要性的简要说明
错误代码示例及解释
正确代码示例及解释
如何检测违规
参考资料及额外上下文

Full Compiled Document

完整编译文档

For the complete guide with all rules expanded:

AGENTS.md

如需包含所有规则扩展说明的完整指南，请查看：

AGENTS.md

Scripts

脚本

Helper scripts for automated analysis:

```
scripts/analyze-tsql.py
```
- Analyze T-SQL files for violations (includes SSDT rules)
```
scripts/check-indexes.sql
```
- Check for missing/unused indexes
```
scripts/security-audit.sql
```
- Security configuration audit
```
scripts/run-assessment.ps1
```
- Run SQL Assessment API checks

用于自动化分析的辅助脚本：

```
scripts/analyze-tsql.py
```
- 分析T-SQL文件以检测违规（包含SSDT规则）
```
scripts/check-indexes.sql
```
- 检查缺失/未使用的索引
```
scripts/security-audit.sql
```
- 安全配置审计
```
scripts/run-assessment.ps1
```
- 运行SQL Assessment API检查

References

参考资料

```
references/sql-assessment-api.md
```
- SQL Assessment API overview
```
references/dmv-queries.md
```
- Useful DMV queries for diagnostics
```
references/connection-strings.md
```
- Connection string best practices
```
references/ssdt-code-analysis.md
```
- SSDT Code Analysis rules reference

```
references/sql-assessment-api.md
```
- SQL Assessment API概述
```
references/dmv-queries.md
```
- 用于诊断的实用DMV查询
```
references/connection-strings.md
```
- 连接字符串最佳实践
```
references/ssdt-code-analysis.md
```
- SSDT代码分析规则参考