Table of Contents
1. Database design and architecture
Chapter 1. Louis and Paul’s 10 key relational database design ideas
1. Denormalization is for wimps
8. Extensibility through encapsulation
Chapter 2. SQL Server tools for maintaining data integrity
Chapter 3. Finding functional dependencies
First step: finding single-attribute dependencies
Second step: finding two-attribute dependencies
Chapter 4. Set-based iteration, the third alternative
The common methods and their shortcomings
Description of gaps and islands problems
Sample data and desired results
Gaps—solution 1 using subqueries
Gaps—solution 2 using subqueries
Islands—solution 1 using subqueries and ranking calculations
Islands—solution 2 using group identifier based on subqueries
Islands—solution 3 using group identifier based on ranking calculations
Islands—solution 4 using cursors
Chapter 6. Error handling in SQL Server and applications
Handling errors inside SQL Server
Returning information about the error
Handling SQL Server errors on the client
Chapter 7. Pulling apart the FROM clause
The appearance of most queries
Chapter 8. What makes a bulk insert a minimally logged operation?
Chapter 9. Avoiding three common query mistakes
Chapter 10. Introduction to XQuery on SQL Server
Chapter 11. SQL Server XML frequently asked questions
Why does SQL Server remove the DTD from my XML data?
How do I preserve whitespace in my XML?
Why am I getting strange characters in my XML?
How do I specify an XML namespace in my XQuery queries?
How do I get all element names and values from my XML document?
Chapter 12. Using XML to transport relational data
Enabling and maintaining the data flow
Chapter 13. Full-text searching
Foundations of full-text searching
Creating and maintaining catalogs
Creating and maintaining full-text indexes
Custom thesaurus and stopwords
Basic queries to discover what catalogs, indexes, and columns exist
Chapter 14. Simil: an algorithm to look for similar strings
Chapter 15. LINQ to SQL and ADO.NET Entity Framework
Generating SQL that uses projection
Optimizing the number of database round trips
LINQ to SQL and stored procedures
Chapter 16. Table-valued parameters
Table-valued parameters to the rescue!
Using TVPs from client applications
Chapter 17. Build your own index
Plain search and introducing tester_sp
Chapter 18. Getting and staying connected—or not
Understanding the SQL Server Browser service
Diagnosing a connectivity problem
Testing for network availability
To connect or not to connect...
Trusted or untrusted security?
Chapter 19. Extending your productivity in SSMS and Query Analyzer
Creating your custom utility to use with keyboard shortcuts
Chapter 20. Why every SQL developer needs a tools database
What belongs in the tools database?
Using an auxiliary table of numbers
Placing common code in the tools database
Chapter 21. Deprecation feature
Chapter 22. Placing SQL Server in your pocket
Chapter 23. Mobile data strategies
Microsoft Sync Framework (MSF)
Client synchronization provider
Chapter 24. What does it mean to be a DBA?
Business Intelligence and Data Warehousing
Data Modeling and Database Design
Database Application Development
Developing and Maintaining Best Practices
Hardware Setup and Configuration
Installing, Configuring, and Upgrading SQL Server Software
Managing SQL Server–Based Applications
Needs and Requirements Analysis
Negotiating Service Level Agreements
DBA High Availability and Disaster Recovery Specialist
Chapter 25. Working with maintenance plans
What is a maintenance plan, and how do I create one?
One or several schedules per plan?
Chapter 26. PowerShell in SQL Server
PowerShell with SQL Server 2000 and 2005
PowerShell with SQL Server 2008
Chapter 27. Automating SQL Server Management using SMO
Chapter 28. Practical auditing in SQL Server 2008
Overview of audit infrastructure
Configuring the Windows Security Log target
Creating a server audit using the Windows Security Log target
Creating a security audit using the Windows Application Log target
Chapter 29. My favorite DMVs, and why
What is so great about DMVs, anyway?
A brief list of my favorite DMVs and DMFs
sys.dm_exec_sessions, sys.dm_exec_requests, and sys.dm_exec_connections
sys.dm_os_performance_counters
Some interesting applications of my favorite DMVs
A more refined sp_who or sp_who2
Getting statistics for stored procedures (SQL Server 2008 only)
Finding unused stored procedures (SQL Server 2008 only)
Chapter 30. Reusing space in a table
Understanding how SQL Server automatically reuses table space
Recognizing when SQL Server does not reclaim space
Chapter 31. Some practical issues in table partitioning
Table partitioning dependencies
How the partition function works
Chapter 32. Partitioning for manageability (and maybe performance)
Planning and design considerations
Chapter 33. Efficient backups without indexes
It’s OK to not back up nonclustered indexes!
Default table and index storage behavior
Backing up only the PRIMARY filegroup
Restoring the PRIMARY filegroup backup
Planning before moving NCIXs into a dedicated filegroup
Chapter 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
Chapter 35. The poor man’s SQL Server log shipping
Chapter 36. Understated changes in SQL Server 2005 replication
Undocumented or partially documented changes in behavior
Reading the text of hidden replication stored procedures
Chapter 37. High-performance transactional replication
Performance kiss of death factors in transactional replication
Optimal settings for replication
Chapter 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
Downloading and installing DelegConfig
Chapter 39. Running SQL Server on Hyper-V
Advantages of physical to virtual migration
4. Performance Tuning and Optimization
Chapter 40. When is an unused index not an unused index?
Unused indexes that are actually used
Chapter 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
Chapter 42. Tracing the deadlock
Chapter 43. How to optimize tempdb performance
How DBAs can help optimize tempdb
Preallocating tempdb space and avoiding use of autogrowth
Don’t shrink tempdb if you don’t need to
Dividing tempdb among multiple physical files
Moving tempdb to a disk separate from your other databases
Locating tempdb on a fast I/O subsystem
Adding RAM to your SQL server instance
Using SQL Server 2008 transparent data encryption
Leaving auto create statistics and auto update statistics on
Chapter 44. Does the order of columns in an index matter?
Chapter 45. Correlating SQL Profiler with PerfMon
Getting started with PerfMon and Profiler
Chapter 46. Using correlation to improve query performance
Correlation with the clustered index
When the optimizer does it right
When the optimizer does it right again
Chapter 47. How to use Dynamic Management Views
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
Chapter 48. Query performance and disk I/O counters
Expensive I/Os and very expensive I/Os
Random or sequential I/Os and disk performance counters
SQL Server operations and I/O sizes
How expensive are small random I/Os, anyway?
Scenario 1: constant checkpoints
Chapter 49. XEVENT: the next event infrastructure
Extended Events infrastructure characteristics
Chapter 50. BI for the relational guy
Chapter 51. Unlocking the secrets of SQL Server 2008 Reporting Services
Why should developers care about Reporting Services?
Using Visual Studio to create an RDL report
Using the Visual Studio 2008 Report Designer
Using the Visual Studio MicrosoftReportViewer control
What’s in Reporting Services 2008 for developers?
Chapter 52. Reporting Services tips and tricks
Filters versus query parameters
Chapter 53. SQL Server Audit, change tracking, and change data capture
What are these solutions used for?
How does SQL Server 2008 solve these problems?
Chapter 54. Introduction to SSAS 2008 data mining
Chapter 55. To aggregate or not to aggregate—is there really a question?
Chapter 56. Incorporating data profiling in the ETL process
Introduction to the Data Profiling task
Making the Data Profiling task dynamic
Making data-quality decisions in the ETL
Chapter 57. Expressions in SQL Server Integration Services
Expressions in the control flow
Chapter 58. SSIS performance tips
Source acquisition performance
Data transformation performance
Chapter 59. Incremental loads using T-SQL and SSIS