Part 1 Database design and architecture

1 Louis and Paul’s 10 key relational database design ideas: 1. Denormalization is for wimps; 2. Keys are key; 3. Generalize, man!; 4. Class <> table; 5. Data drives design; 6. Sets good, cursors bad; 7. Properly type data; 8. Extensibility through encapsulation; 9. Spaghetti is food, not code; 10. NOLOCK = no consistenc; Summary
2 SQL Server tools for maintaining data integrity: Protection tools; When and why to use what tool; Summary
3 Finding functional dependencies: Interview method; Modeling the sales order; Summary

Part 2 Database Development

4 Set-based iteration, the third alternative: The common methods and their shortcomings; Set-based iteration; Summary
5 Gaps and islands: Description of gaps and islands problems; Sample data and desired results; Solutions to gaps problem; Solutions to islands problem; Summary
6 Error handling in SQL Server and applications: Handling errors inside SQL Server; Handling SQL Server errors on the client; Summary
7 Pulling apart the FROM clause: JOIN basics; Formatting your FROM clause; Filtering with the ON clause; JOIN uses and simplification; The four uses of JOINs; Simplification using views; How JOIN uses affect you; Summary
8 What makes a bulk insert a minimally logged operation?: Recovery and locking; Creating the file to import; Creating the tables to store the data; Importing the data; Summary
9 Avoiding three common query mistakes: NULL comparisons; Multiple OUTER JOINS; Incorrect GROUP BY clauses; Summary
10 Introduction to XQuery on SQL Server: What is XQuery?; How XQuery sees your XML; Querying XML; FLWOR expressions; XQuery comparison operators; XML indexes and XQuery performance; Summary
11 SQL Server XML frequently asked questions: XML basics; The xml data type; Advanced query topics; Summary
12 Using XML to transport relational data: Understanding before coding; Enabling and maintaining the data flow; Preparing the sample data; Homework; Summary
13 Full-text searching: Foundations of full-text searching; Creating and maintaining catalogs; Creating and maintaining full-text indexes; Querying full-text indexes; Custom thesaurus and stopwords; Useful system queries; Summary
14 Simil: an algorithm to look for similar strings: Equals (=) and LIKE; SOUNDEX and DIFFERENCE; CONTAINS and FREETEXT; Simil; Summary
15 LINQ to SQL and ADO.NET Entity Framework: LINQ to SQL and performance; Generating SQL that uses projection; Updating in the middle tier; Optimizing the number of database round trips; LINQ to SQL and stored procedures; Tuning and LINQ to SQL queries; Summary
16 Table-valued parameters: What’s the problem?; Table-valued parameters to the rescue!; Another TVP example; Using TVPs from client applications; Summary
17 Build your own index: The database and the table; Plain search and introducing tester_sp; Fragments and persons; Fragments and lists; Using bitmasks; Summary
18 Getting and staying connected—or not: What is SQL Server?; Understanding the SQL Server Browser service; Diagnosing a connectivity problem; What is a connection?; To connect or not to connect...; Connection management; Connection strategies; Establishing a connection; The server key; Trusted or untrusted security?; Accepting user login credentials—or not; Accessing user instances; Connection pooling; Closing the connection; Summary
19 Extending your productivity in SSMS and Query Analyzer: Custom keyboard shortcuts; Creating your custom utility to use with keyboard shortcuts; Some ideas for utilities to implement; Summary
20 Why every SQL developer needs a tools database: What belongs in the tools database?; Creating the tools database; Using an auxiliary table of numbers; Placing common code in the tools database; Formatting; Calling code from a different database; Summary
21 Deprecation feature: A simple usage example; Methods of tracking deprecated features; Summary
22 Placing SQL Server in your pocket: Design goals; Architecture; Deployment; Tool support; Programming support; Summary
23 Mobile data strategies: Microsoft Sync Framework (MSF); Comparison; Summary

Part 3 Database Administration

24 What does it mean to be a DBA?: Typical DBA tasks: A to Z; DBA specialties; Summary
25 Working with maintenance plans: What is a maintenance plan, and how do I create one?; Executing and monitoring the plan; Summary
26 PowerShell in SQL Server: PowerShell overview; PowerShell with SQL Server 2000 and 2005; PowerShell with SQL Server 2008; Summary
27 Automating SQL Server Management using SMO: Loading required libraries; Backup; Restore; Creating a database; Scripting; Summary
28 Practical auditing in SQL Server 2008: Overview of audit infrastructure; Server audits; Server audit specifications; Database audit specifications; Summary
29 My favorite DMVs, and why: What is so great about DMVs, anyway?; A brief list of my favorite DMVs and DMFs; Honorable mentions; Setting up a utility database; Some interesting applications of my favorite DMVs; DMV categories in SQL Server; Summary
30 Reusing space in a table: Understanding how SQL Server automatically reuses table space; Recognizing when SQL Server does not reclaim space; Using DBCC CLEANTABLE to reclaim unused table space; Summary
31 Some practical issues in table partitioning: Table partitioning dependencies; Manipulating partitioned data; How the partition function works; The key: avoiding data movement; Sources for more information; Summary
32 Partitioning for manageability (and maybe performance): Overview; How to partition; Planning and design considerations; Gotchas and tips; Summary
33 Efficient backups without indexes: It’s OK to not back up nonclustered indexes!; A simple example; Backing up only the PRIMARY filegroup; Restoring the PRIMARY filegroup backup; usp_Manage_NCIX_Filegroup; Planning before moving NCIXs into a dedicated filegroup; Log shipping; Summary
34 Using database mirroring to become a superhero!: Why should I use database mirroring?; How does database mirroring work?; How do you set up database mirroring?; How do you prepare the mirror?; Using database mirroring for routine maintenance; Using database mirroring to upgrade to SQL Server 2008; Using database mirroring to move data seamlessly; Summary
35 The poor man’s SQL Server log shipping: Creating the T-SQL script; Creating a cleanup script; Creating a batch file; Improving the log shipping process; Summary
36 Understated changes in SQL Server 2005 replication: Undocumented or partially documented changes in behavior; More efficient methodologies; Summary
37 High-performance transactional replication: Performance kiss of death factors in transactional replication; Optimal settings for replication; Summary
38 Successfully implementing Kerberos delegation: Understanding the issues that Kerberos delegation resolves; Understanding Kerberos delegation; Implementing Kerberos delegation step by step; Validating delegation from end to end; Resources to assist in more complex infrastructures; Summary
39 Running SQL Server on Hyper-V: Virtualization architecture; Benefits of isolation; Configuring virtual machines; Addressing clock drift issues; Backup considerations; Advantages of physical to virtual migration; Test environments and virtualization; Summary

Part 4 Performance Tuning and Optimization

Overview of indexing; Unused indexes; Unused indexes that are actually used; How is the unused index being used?; How does this affect me?; Summary
41 Speeding up your queries with index covering: Index covering speeds up selects; Some rules of thumb about indexes aren’t true for covering indexes; Covering indexes usually slow down modifications; One index should cover many queries; One index can both cover queries and implement uniqueness; Summary
42 Tracing the deadlock: What’s a deadlock?; Causes of deadlocks; Deadlock graph; The big picture; Summary
43 How to optimize tempdb performance: What is tempdb used for?; Tempdb internals; How DBAs can help optimize tempdb; Minimizing the use of tempdb; Summary
44 Does the order of columns in an index matter?: Understanding the basics of composite indexes; Finding a specific row; Finding a last name; Finding a first name; Summary
45 Correlating SQL Profiler with PerfMon: What vexes you?; Getting started with PerfMon and Profiler; Summary
46 Using correlation to improve query performance: The purpose of the optimizer; Correlation with the clustered index; When the optimizer does it right; When the optimizer does it right again; When the optimizer gets it wrong; Correcting the optimizer; When to expect correlation; Determining correlation; Summary
47 How to use Dynamic Management Views: Why should I use DMV queries?; Setting up security to run DMV queries; Looking at top waits at the instance level; Looking for CPU pressure and what’s causing it; Finding I/O pressure in SQL Server; SQL Server memory pressure; SQL Server index usage; Detecting blocking in SQL Server; Summary
48 Query performance and disk I/O counters: Expensive I/Os and very expensive I/Os; Disk performance counters; Random or sequential I/Os and disk performance counters; SQL Server operations and I/O sizes; How expensive are small random I/Os, anyway?; Performance scenarios; Summary
49 XEVENT: the next event infrastructure: Extended Events infrastructure characteristics; XEVENT architecture; XEVENT in action; Summary

Part 5 Business intelligence

50 BI for the relational guy: Business intelligence overview; Terminology; Really, what is so different?; Approach; Dimensional modeling; Cubes, anyone?; Microsoft BI stack; How do I get started?; Summary
51 Unlocking the secrets of SQL Server 2008 Reporting Services: Why should developers care about Reporting Services?; What is Reporting Services?; Using Visual Studio to create an RDL report; Using the Visual Studio 2008 Report Designer; Using the Report Manager; Using the Visual Studio MicrosoftReportViewer control; What’s in Reporting Services 2008 for developers?; Working with the Report Designer; Summary
52 Reporting Services tips and tricks: Performance tips; Design tips; Summary
53 SQL Server Audit, change tracking, and change data capture: What are these solutions used for?; What do people do now?; How does SQL Server 2008 solve these problems?; Comparison of features; Summary
54 Introduction to SSAS 2008 data mining: Data mining basics; Creating mining models; Harvesting the results; Sources for more information; Summary
55 To aggregate or not to aggregate—is there really a question?: What are aggregations?; Designing aggregations; Influencing aggregations; Attribute relationships; Usage-based optimization; High-level approach; Other considerations; Summary
56 Incorporating data profiling in the ETL process: Why profile data?; Introduction to the Data Profiling task; Making the Data Profiling task dynamic; Making data-quality decisions in the ETL; Consuming the task output; Summary
57 Expressions in SQL Server Integration Services: SSIS packages: a brief review; Expressions: a quick tour; Expressions in the control flow; Expressions and variables; Expressions in the data flow; Expressions and connection managers; Summary
58 SSIS performance tips: SSIS overview; Control flow performance; Data flow performance; Summary
59 Incremental loads using T-SQL and SSIS: Some definitions0; A T-SQL incremental load; Incremental loads in SSIS; Summary