Azure SQL Database Best Practices
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:
- SQL Assessment API
- SSDT Code Analysis Rules)
- Azure SQL Database Performance Guidance
- Azure SQL Database Security Best Practices
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
Rule Categories by Priority
| 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- |
SSDT Code Analysis Rules (Microsoft Static Analysis)
These rules are from Microsoft's SQL Server Data Tools (SSDT) static code analysis. They are enforced in Visual Studio Database Projects.
Design Issues (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 |
Performance Issues (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 |
Naming Issues (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 |
Quick Reference
1. Query Performance (CRITICAL)
query-avoid-select-star(SR0001) - Never use SELECT * in production codequery-parameterize- Always use parameterized queries to prevent SQL injection and enable plan cachingquery-avoid-functions-on-columns- Don't apply functions to columns in WHERE clausesquery-sargable- Write SARGable (Search ARGument ABLE) predicates for index usagequery-batch-operations- Batch INSERT/UPDATE/DELETE operations to reduce round tripsquery-avoid-cursors- Replace cursors with set-based operationsquery-limit-results- Use TOP or OFFSET-FETCH for paginationquery-avoid-implicit-conversion(SR0014) - Match data types to prevent implicit conversionsquery-join-optimization- Order joins for optimal execution plansquery-exists-vs-count- Use EXISTS instead of COUNT(*) > 0query-avoid-leading-wildcard(SR0005) - Avoid LIKE '%value' patterns
2. Indexing Strategy (CRITICAL)
index-cover-queries- Create covering indexes for frequent queriesindex-avoid-over-indexing- Balance read vs write performanceindex-missing-index-dmv- Use DMVs to identify missing indexesindex-unused-indexes- Remove unused indexes consuming resourcesindex-fragmentation- Monitor and address index fragmentationindex-columnstore- Use columnstore indexes for analytics workloadsindex-filtered- Use filtered indexes for subset queriesindex-include-columns- Use INCLUDE for non-key columnsindex-key-order- Order index keys by selectivityindex-avoid-wide-keys- Keep index keys narrowindex-in-predicate(SR0004) - Ensure columns in IN predicates are indexed
3. Security & Compliance (HIGH)
security-parameterize-queries- Prevent SQL injection with parameterssecurity-least-privilege- Grant minimum required permissionssecurity-avoid-sa- Never use sa or dbo for application accesssecurity-encrypt-connections- Always use encrypted connectionssecurity-row-level-security- Implement RLS for multi-tenant appssecurity-dynamic-data-masking- Mask sensitive datasecurity-always-encrypted- Use Always Encrypted for sensitive columnssecurity-tde- Enable Transparent Data Encryptionsecurity-audit-logging- Enable SQL Audit for compliancesecurity-vulnerability-assessment- Regular vulnerability scans
4. Connection Management (HIGH)
connection-pooling- Always use connection poolingconnection-retry-logic- Implement retry logic for transient failuresconnection-timeout- Set appropriate connection timeoutsconnection-close-dispose- Always close/dispose connectionsconnection-async- Use async/await for database callsconnection-read-replicas- Use read replicas for read workloadsconnection-application-intent- Set ApplicationIntent for read replicasconnection-multisubnetfailover- Enable for geo-replicated databases
5. T-SQL Patterns (MEDIUM-HIGH)
tsql-set-nocount- Use SET NOCOUNT ON in stored procedurestsql-schema-qualify- Always schema-qualify object namestsql-avoid-hints- Avoid query hints unless necessarytsql-temp-tables-vs-variables- Choose appropriately between temp tables and table variablestsql-transaction-scope- Keep transactions shorttsql-error-handling- Use TRY-CATCH with proper error handlingtsql-avoid-triggers- Minimize trigger usagetsql-cte-vs-subquery- Use CTEs for readability and recursiontsql-merge-carefully- Use MERGE with cautiontsql-avoid-dynamic-sql- Minimize dynamic SQL, parameterize when usedtsql-scope-identity(SR0008) - Use SCOPE_IDENTITY() instead of @@IDENTITYtsql-avoid-deprecated-joins(SR0010) - Use ANSI JOIN syntax, not *= or =*tsql-output-params(SR0013) - Populate output parameters in all code pathstsql-avoid-sp-prefix(SR0016) - Don't prefix stored procedures with sp_
6. Data Type Best Practices (MEDIUM)
type-appropriate-size(SR0009) - Avoid VARCHAR(1) or VARCHAR(2), use CHAR insteadtype-avoid-deprecated- Don't use TEXT, NTEXT, IMAGE typestype-match-column-types- Match parameter types to column typestype-avoid-max-unnecessarily- Use specific sizes instead of MAX when possibletype-nullable-handling(SR0007) - Use ISNULL on nullable columns in expressionstype-reserved-words(SR0012) - Don't use reserved words for type names
7. Naming Conventions (MEDIUM)
naming-avoid-special-chars(SR0011) - Avoid special characters in object namesnaming-avoid-reserved-words(SR0012) - Don't use reserved words as identifiersnaming-consistent-case- Use consistent casing (PascalCase or snake_case)naming-descriptive- Use descriptive, meaningful namesnaming-avoid-prefixes- Avoid Hungarian notation prefixes
8. Database Configuration (MEDIUM)
config-query-store- Enable Query Store for performance insightsconfig-auto-tuning- Enable automatic tuningconfig-max-dop- Configure appropriate MAXDOPconfig-memory-grant- Monitor memory grantsconfig-compatibility-level- Use appropriate compatibility levelconfig-auto-stats- Enable auto create/update statisticsconfig-page-verify- Use CHECKSUM for page verificationconfig-recovery-model- Choose appropriate recovery modelconfig-tempdb- Optimize tempdb configurationconfig-accelerated-recovery- Enable Accelerated Database Recovery
9. Data Modeling (MEDIUM)
model-normalization- Normalize appropriately (3NF minimum)model-appropriate-types- Use appropriate data typesmodel-avoid-nullable- Minimize NULL columns where possiblemodel-partition-strategy- Implement partitioning for large tablesmodel-computed-columns- Use computed columns for derived valuesmodel-constraint-enforcement- Use constraints for data integritymodel-hierarchical-pk- Use hierarchical partition keys for scalemodel-temporal-tables- Use temporal tables for audit trailsmodel-json-columns- Use JSON columns judiciously
10. Monitoring & Diagnostics (LOW-MEDIUM)
monitor-query-performance-insight- Use Query Performance Insightmonitor-dmvs- Leverage DMVs for diagnosticsmonitor-extended-events- Use Extended Events for tracingmonitor-intelligent-insights- Enable Intelligent Insightsmonitor-resource-utilization- Track DTU/vCore usagemonitor-deadlock-analysis- Analyze and prevent deadlocksmonitor-wait-statistics- Monitor wait statisticsmonitor-log-io- Monitor transaction log 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.mdEach 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
Full Compiled Document
For the complete guide with all rules expanded: 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 indexesscripts/security-audit.sql- Security configuration auditscripts/run-assessment.ps1- Run SQL Assessment API checks
References
references/sql-assessment-api.md- SQL Assessment API overviewreferences/dmv-queries.md- Useful DMV queries for diagnosticsreferences/connection-strings.md- Connection string best practicesreferences/ssdt-code-analysis.md- SSDT Code Analysis rules reference