100 SQL Server Mistakes and How to Avoid Them

Table of contents

1. 100 SQL Server Mistakes
2. Copyright
3. dedication
4. contents
5. front matter
   1. preface
   2. acknowledgments
   3. about this book
      1. Who should read this book?
      2. How this book is organized: A roadmap
      3. About the code
      4. liveBook discussion forum
   4. about the author
   5. about the cover illustration
6. 1 Introducing SQL Server
   1. 1.1 The SQL Server index mistake (mistake #0)
   2. 1.2 An overview of SQL Server
      1. 1.2.1 Overview of the Database Engine
      2. 1.2.2 Heterogeneous platforms
   3. 1.3 Why we should still care about SQL Server
   4. 1.4 Why getting SQL Server right matters
   5. Summary
7. 2 Development standards
   1. 2.1 An example mistake
   2. 2.2 #1 Nondescriptive object names
   3. 2.3 #2 Using object prefixes
   4. 2.4 #3 The dreaded sp_ prefix
   5. 2.5 #4 Not making time for coding standards
   6. 2.6 #5 Using ordinal column position
   7. Summary
8. 3 Data types
   1. 3.1 #6 Always storing whole numbers in INT
   2. 3.2 #7 Always using variable-length strings
   3. 3.3 #8 Writing your own hierarchy code
   4. 3.4 #9 Not storing XML data as native XML
      1. 3.4.1 Shredding XML
      2. 3.4.2 Reconstructing XML
      3. 3.4.3 Avoiding the overhead by storing data as XML
   5. 3.5 #10 Ignoring JSON
   6. Summary
9. 4 Database design
   1. 4.1 #11 Failing to normalize
      1. 4.1.1 Designing a schema using judgment
      2. 4.1.2 Problems with our database schema
      3. 4.1.3 Designing a database schema using normalization
   2. 4.2 #12 Using a wide primary key
   3. 4.3 #13 Not using foreign keys
   4. Summary
10. 5 T-SQL development
    1. 5.1 #14 Dealing incorrectly with NULL values
    2. 5.2 #15 Using NOLOCK as a performance tweak
    3. 5.3 #16 Using SELECT * as standard
    4. 5.4 #17 Unnecessarily ordering data
    5. 5.5 #18 Using DISTINCT without good reason
    6. 5.6 #19 Using UNION unnecessarily
    7. 5.7 #20 Using cursors
    8. 5.8 #21 Deleting many rows in a single transaction
    9. Summary
11. 6 SSIS development
    1. 6.1 #22 Throwing away bad data
    2. 6.2 #23 Not optimizing data loads
    3. 6.3 #24 Using SSIS as a T-SQL orchestration tool
       1. 6.3.1 Creating an Execute T-SQL Statement orchestration
       2. 6.3.2 Converting Execute T-SQL Statement tasks to data flows
    4. 6.4 #25 Extracting all data when we only need a subset
    5. Summary
12. 7 Error handling, testing, source control, and deployment
    1. 7.1 #26 Writing code that doesn’t handle errors
    2. 7.2 #27 Failing to alert on errors
    3. 7.3 #28 Not utilizing debugging functionality
    4. 7.4 #29 Not making use of Schema Compare
    5. 7.5 #30 Failing to write unit tests
    6. 7.6 Modern development techniques
       1. 7.6.1 #31 Not keeping code in source control
       2. 7.6.2 #32 Not deploying code with a CI/CD pipeline
    7. Summary
13. 8 SQL Server installation
    1. 8.1 #33 Using obscure instance names
    2. 8.2 #34 Using Windows indiscriminately
    3. 8.3 #35 Forgetting how useful containers can be
    4. 8.4 #36 Using Desktop Experience unnecessarily
    5. 8.5 #37 Using Enterprise Edition indiscriminately
    6. 8.6 #38 Installing an instance when DBaaS or PaaS will suffice
    7. 8.7 #39 Installing all features
    8. 8.8 #40 Not scripting SQL Server installation
    9. 8.9 #41 Thinking configuration management doesn’t apply to SQL Server
    10. 8.10 #42 Using SQL Server cloud images without modifying them
    11. Summary
14. 9 Instance and database management
    1. 9.1 #43 Autoshrinking databases
    2. 9.2 #44 Failing to rebuild indexes after data file shrink
    3. 9.3 #45 Relying on autogrow
    4. 9.4 #46 Using multiple log files
    5. 9.5 #47 Allowing logs to become fragmented
    6. 9.6 #48 Failing to capacity plan
    7. 9.7 #49 Always placing TempDB and log files on dedicated drives
    8. 9.8 #50 Not regularly checking for corruption
    9. 9.9 #51 Failing to automate
    10. 9.10 #52 Using cursors for administrative purposes
    11. 9.11 #53 Failing to patch
    12. Summary
15. 10 Optimization
    1. 10.1 #54 Turning on TF1117 and TF1118
    2. 10.2 #55 Not using instant file initialization
    3. 10.3 #56 Failing to leave enough memory for other applications
    4. 10.4 #57 Failing to lock pages in memory
    5. 10.5 #58 Working against the optimizer
    6. 10.6 #59 Not taking advantage of DOP feedback
    7. 10.7 #60 Not partitioning large tables
    8. 10.8 #61 Not understanding the limitations of partition elimination
    9. 10.9 #62 Not compressing large tables
    10. 10.10 #63 Using Read Uncommitted
    11. 10.11 #64 Using unnecessarily strong isolation levels
    12. 10.12 #65 Not considering optimistic isolation levels
    13. 10.13 #66 Throwing more hardware at the problem
    14. Summary
16. 11 Indexes
    1. 11.1 #67 Assuming internal fragmentation is always bad
    2. 11.2 #68 Believing that external fragmentation causes problems for all queries
    3. 11.3 #69 Reorganizing indexes to fix page density
    4. 11.4 #70 Misinterpreting fragmentation statistics
    5. 11.5 #71 Not rebuilding indexes
    6. 11.6 #72 Rebuilding all indexes indiscriminately
    7. 11.7 #73 Updating statistics after rebuilding indexes
    8. 11.8 #74 Not optimizing index maintenance for our needs
       1. 11.8.1 Considerations for MAXDOP
       2. 11.8.2 Considerations for SORT_IN_TEMPDB
       3. 11.8.3 Understanding OPTIMIZE_FOR_SEQUENTIAL_KEY
    9. 11.9 #75 Not disabling indexes for bulk load
    10. 11.10 #76 Relying too heavily on Database Engine Tuning Advisor
    11. 11.11 #77 Not using columnstore indexes
    12. Summary
17. 12 Backups
    1. 12.1 #78 Not considering RPO and RTO
    2. 12.2 #79 Using database snapshots as a recovery strategy
    3. 12.3 #80 Using crash-consistent snapshots as a recovery strategy
    4. 12.4 #81 Not testing backups
       1. 12.4.1 Checking that backups completed successfully
       2. 12.4.2 Verifying backup integrity
    5. 12.5 #82 Taking backups during an ETL window
    6. 12.6 #83 Always using the FULL recovery model on data warehouse and development systems
    7. 12.7 #84 Using SIMPLE recovery model for OLTP databases
    8. 12.8 #85 Not backing up after changing recovery model
    9. 12.9 #86 Scheduling log backups immediately after a full backup
    10. 12.10 #87 Not using COPY_ONLY backups for ad hoc backups
    11. 12.11 #88 Forgetting that backups are part of our security posture
    12. Summary
18. 13 Availability
    1. 13.1 #89 Confusing HA and DR
    2. 13.2 #90 Failing to architect for the requirements
    3. 13.3 #91 Not testing the DR strategy
    4. 13.4 #92 Assuming availability groups are always the right answer
    5. 13.5 #93 Overloading a cluster
    6. Summary
19. 14 Security
    1. 14.1 #94 Not implementing the principle of least privilege
    2. 14.2 #95 Not disabling the sa account
    3. 14.3 #96 Using the wrong granularity of a service account
    4. 14.4 #97 Enabling xp_cmdshell
    5. 14.5 #98 Failing to audit administrative activity
    6. 14.6 #99 Exposing the business to whole-value substitution attacks
       1. 14.6.1 Preparing an encrypted environment
       2. 14.6.2 Understanding and preventing whole-value substitution attacks
    7. 14.7 #100 Exposing the business to SQL injection attacks
    8. Summary
20. index