Best Practices and Coding Standards

56.1 Naming Conventions (Example Standard)

PascalCase, plural or singular (be consistent) fn_Name / ufn_Name

56.2 T-SQL Coding Do's and Don'ts

✅ Always schema-qualify objects (dbo.Orders) ✅ Name your constraints explicitly ✅ Terminate statements with ;

✅ List columns explicitly in INSERT and SELECT ✅ Use SET NOCOUNT ON and XACT_ABORT ON in procedures ✅ Use TRY/CATCH with THROW ✅ Use ANSI joins and set-based logic

✅ Use EXISTS rather than COUNT(*) > 0 ✅ Use NOT EXISTS rather than NOT IN (NULL safety) ✅ Use half-open date ranges

✅ Use the right data types and match the types in joins and predicates ✅ Comment the *why*, not the *what* ❌ SELECT * in production code ❌ NOLOCK everywhere ❌ Functions on indexed columns in WHERE ❌ Cursors for set problems ❌ The sp_ prefix for user procedures ❌ Implicit conversions

❌ Scalar UDFs in large queries (pre-2019) ❌ ORDER BY in views (meaningless without TOP)

❌ Ordinal positions in ORDER BY (ORDER BY 1) ❌ Dynamic SQL built by concatenating user input ❌ Shrinking databases routinely ❌ GUID clustered keys with NEWID() ❌ AUTO_CLOSE and AUTO_SHRINK ON

56.3 Performance Checklist

Is every query using appropriate indexes (seeks instead of scans where selective)?

Are statistics current?

Are predicates SARGable?

Any implicit conversions or key lookups?

Are estimated and actual row counts close?

Are transactions short?

Is MAXDOP / cost threshold configured?

Is max server memory set?

Is TempDB configured properly?

Is Query Store enabled and being reviewed?

56.4 DBA Production Checklist

[ ] Backups configured, monitored, and tested with restores [ ] DBCC CHECKDB scheduled

[ ] Alerts for severity 16–25 and errors 823/824/825 [ ] Index and statistics maintenance

[ ] Security reviewed (least privilege, sa disabled, encryption) [ ] HA/DR tested and documented [ ] Monitoring and baselining in place [ ] Patching cadence defined

[ ] Capacity planning (disk growth trends) [ ] Documentation and runbooks current