English | MP4 | AVC 1920×1080 | AAC 44KHz 2ch | 11h 40m | 2.03 GB
All the mistakes you might make with SQL Server—and how to avoid them!
100 SQL Server Mistakes and How to Avoid Them prepares you for the pitfalls database professionals often encounter—from administration to development, availability, and security. You’ll learn to sidestep common errors that slow down your T-SQL code and ensure your SQL Server is installed and configured to handle anything your organization throws at it.
Inside 100 SQL Server Mistakes and How to Avoid Them you’ll learn to avoid:
- Development errors when writing T-SQL
- Installation and administration mistakes
- Optimization missteps
- Common pitfalls relating to high availability and disaster recovery (HA/DR)
- Security oversights that can endanger your data
100 SQL Server Mistakes and How to Avoid Them doesn’t focus on the “happy path”—instead, it covers all the errors and problems you might face as a SQL Server developer or administrator. Each chapter is filled with real-world issues drawn from author Peter A. Carter’s two-decade-long career in SQL Server. Peter’s seasoned advice helps dispel myths, debunk misconceptions, and set you on the right road.
Perfecting a SQL Server system can be a complex balancing act. Why is T-SQL running so slowly? Are the right data available? Are we protected against data theft? What about that new server instance I need to administer? Even the most skilled SQL Server experts make mistakes that cost time and performance. This book can help you get it right the first time.
100 SQL Server Mistakes and How to Avoid Them focuses exclusively on the errors that you might—and probably will—make as a SQL Server admin or developer. Real-world examples, code samples, and helpful diagrams make it easy to understand each issue and its solution. You’ll learn how to write performant code, design efficient database schemas, implement error handling, work with complex data types, and much more, all in a friendly, common-sense problem/solution format.
What’s Inside
- T-SQL development
- Installation, administration, and optimization
- High availability and security
Table of Contents
1 Chapter 1. Introducing SQL Server
2 Chapter 1. An overview of SQL Server
3 Chapter 1. Why we should still care about SQL Server
4 Chapter 1. Why getting SQL Server right matters
5 Chapter 1. Summary
6 Chapter 2. Development standards
7 Chapter 2. 1 Nondescriptive object names
8 Chapter 2. 2 Using object prefixes
9 Chapter 2. 3 The dreaded sp prefix
10 Chapter 2. 4 Not making time for coding standards
11 Chapter 2. 5 Using ordinal column position
12 Chapter 2. Summary
13 Chapter 3. Data types
14 Chapter 3. 7 Always using variable-length strings
15 Chapter 3. 8 Writing your own hierarchy code
16 Chapter 3. 9 Not storing XML data as native XML
17 Chapter 3. 10 Ignoring JSON
18 Chapter 3. Summary
19 Chapter 4. Database design
20 Chapter 4. 12 Using a wide primary key
21 Chapter 4. 13 Not using foreign keys
22 Chapter 4. Summary
23 Chapter 5. T-SQL development
24 Chapter 5. 15 Using NOLOCK as a performance tweak
25 Chapter 5. 16 Using SELECT as standard
26 Chapter 5. 17 Unnecessarily ordering data
27 Chapter 5. 18 Using DISTINCT without good reason
28 Chapter 5. 19 Using UNION unnecessarily
29 Chapter 5. 20 Using cursors
30 Chapter 5. 21 Deleting many rows in a single transaction
31 Chapter 5. Summary
32 Chapter 6. SSIS development
33 Chapter 6. 23 Not optimizing data loads
34 Chapter 6. 24 Using SSIS as a T-SQL orchestration tool
35 Chapter 6. 25 Extracting all data when we only need a subset
36 Chapter 6. Summary
37 Chapter 7. Error handling, testing, source control, and deployment
38 Chapter 7. 27 Failing to alert on errors
39 Chapter 7. 28 Not utilizing debugging functionality
40 Chapter 7. 29 Not making use of Schema Compare
41 Chapter 7. 30 Failing to write unit tests
42 Chapter 7. Modern development techniques
43 Chapter 7. Summary
44 Chapter 8. SQL Server installation
45 Chapter 8. 34 Using Windows indiscriminately
46 Chapter 8. 35 Forgetting how useful containers can be
47 Chapter 8. 36 Using Desktop Experience unnecessarily
48 Chapter 8. 37 Using Enterprise Edition indiscriminately
49 Chapter 8. 38 Installing an instance when DBaaS or PaaS will suffice
50 Chapter 8. 39 Installing all features
51 Chapter 8. 40 Not scripting SQL Server installation
52 Chapter 8. 41 Thinking configuration management doesn t apply to SQL Server
53 Chapter 8. 42 Using SQL Server cloud images without modifying them
54 Chapter 8. Summary
55 Chapter 9. Instance and database management
56 Chapter 9. 44 Failing to rebuild indexes after data file shrink
57 Chapter 9. 45 Relying on autogrow
58 Chapter 9. 46 Using multiple log files
59 Chapter 9. 47 Allowing logs to become fragmented
60 Chapter 9. 48 Failing to capacity plan
61 Chapter 9. 49 Always placing TempDB and log files on dedicated drives
62 Chapter 9. 50 Not regularly checking for corruption
63 Chapter 9. 51 Failing to automate
64 Chapter 9. 52 Using cursors for administrative purposes
65 Chapter 9. 53 Failing to patch
66 Chapter 9. Summary
67 Chapter 10. Optimization
68 Chapter 10. 55 Not using instant file initialization
69 Chapter 10. 56 Failing to leave enough memory for other applications
70 Chapter 10. 57 Failing to lock pages in memory
71 Chapter 10. 58 Working against the optimizer
72 Chapter 10. 59 Not taking advantage of DOP feedback
73 Chapter 10. 60 Not partitioning large tables
74 Chapter 10. 61 Not understanding the limitations of partition elimination
75 Chapter 10. 62 Not compressing large tables
76 Chapter 10. 63 Using Read Uncommitted
77 Chapter 10. 64 Using unnecessarily strong isolation levels
78 Chapter 10. 65 Not considering optimistic isolation levels
79 Chapter 10. 66 Throwing more hardware at the problem
80 Chapter 10. Summary
81 Chapter 11. Indexes
82 Chapter 11. 68 Believing that external fragmentation causes problems for all queries
83 Chapter 11. 69 Reorganizing indexes to fix page density
84 Chapter 11. 70 Misinterpreting fragmentation statistics
85 Chapter 11. 71 Not rebuilding indexes
86 Chapter 11. 72 Rebuilding all indexes indiscriminately
87 Chapter 11. 73 Updating statistics after rebuilding indexes
88 Chapter 11. 74 Not optimizing index maintenance for our needs
89 Chapter 11. 75 Not disabling indexes for bulk load
90 Chapter 11. 76 Relying too heavily on Database Engine Tuning Advisor
91 Chapter 11. 77 Not using columnstore indexes
92 Chapter 11. Summary
93 Chapter 12. Backups
94 Chapter 12. 79 Using database snapshots as a recovery strategy
95 Chapter 12. 80 Using crash-consistent snapshots as a recovery strategy
96 Chapter 12. 81 Not testing backups
97 Chapter 12. 82 Taking backups during an ETL window
98 Chapter 12. 83 Always using the FULL recovery model on data warehouse and development systems
99 Chapter 12. 84 Using SIMPLE recovery model for OLTP databases
100 Chapter 12. 85 Not backing up after changing recovery model
101 Chapter 12. 86 Scheduling log backups immediately after a full backup
102 Chapter 12. 87 Not using COPY ONLY backups for ad hoc backups
103 Chapter 12. 88 Forgetting that backups are part of our security posture
104 Chapter 12. Summary
105 Chapter 13. Availability
106 Chapter 13. 90 Failing to architect for the requirements
107 Chapter 13. 91 Not testing the DR strategy
108 Chapter 13. 92 Assuming availability groups are always the right answer
109 Chapter 13. 93 Overloading a cluster
110 Chapter 13. Summary
111 Chapter 14. Security
112 Chapter 14. 95 Not disabling the sa account
113 Chapter 14. 96 Using the wrong granularity of a service account
114 Chapter 14. 97 Enabling xp cmdshell
115 Chapter 14. 98 Failing to audit administrative activity
116 Chapter 14. 99 Exposing the business to whole-value substitution attacks
117 Chapter 14. 100 Exposing the business to SQL injection attacks
118 Chapter 14. Summary
Resolve the captcha to access the links!