SQL Server MVP Deep Dives
Contributions from 53 SQL Server MVPs, Edited by Paul Nielsen, Kalen Delaney, Greg Low, Adam Machanic, Paul S. Randal, and Kimberly L. Tripp
  • November 2009
  • ISBN 9781935182047
  • 848 pages
  • printed in black & white

This is no ordinary SQL Server book. In SQL Server MVP Deep Dives, the world's leading experts and practitioners offer a masterful collection of techniques and best practices for SQL Server development and administration. 53 MVPs each pick an area of passionate interest to them and then share their insights and practical know-how with you.

Table of Contents detailed table of contents



about War Child

about this book

about SQL Server MVPs

Part 1 Database design and architecture

Edited by Paul Nielsen

1. Louis and Paul’s 10 key relational database design ideas

by Paul Nielsen and Louis Davidson

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


2. SQL Server tools for maintaining data integrity

by Louis Davidson

Protection tools

When and why to use what tool


3. Finding functional dependencies

by Hugo Kornelis

Interview method

Modeling the sales order


Part 2 Database Development

Edited by Adam Machanic

4. Set-based iteration, the third alternative

by Hugo Kornelis

The common methods and their shortcomings

Set-based iteration


5. Gaps and islands

by Itzik Ben-Gan

Description of gaps and islands problems

Sample data and desired results

Solutions to gaps problem

Solutions to islands problem


6. Error handling in SQL Server and applications

by Bill Graziano

Handling errors inside SQL Server

Handling SQL Server errors on the client


7. Pulling apart the FROM clause

by Rob Farley

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


8. What makes a bulk insert a minimally logged operation?

by Denis Gobo

Recovery and locking

Creating the file to import

Creating the tables to store the data

Importing the data


9. Avoiding three common query mistakes

by Kathi Kellenberger

NULL comparisons


Incorrect GROUP BY clauses


10. Introduction to XQuery on SQL Server

by Michael Coles

What is XQuery?

How XQuery sees your XML

Querying XML

FLWOR expressions

XQuery comparison operators

XML indexes and XQuery performance


11. SQL Server XML frequently asked questions

by Michael Coles

XML basics

The xml data type

Advanced query topics


12. Using XML to transport relational data

by Matija Lah

Understanding before coding

Enabling and maintaining the data flow

Preparing the sample data



13. Full-text searching

by Robert C. Cain

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


14. Simil: an algorithm to look for similar strings

by Tom van Stiphout

Equals (=) and LIKE





15. LINQ to SQL and ADO.NET Entity Framework

by Bob Beauchemin

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


16. Table-valued parameters

by Don Kiely

What’s the problem?

Table-valued parameters to the rescue!

Another TVP example

Using TVPs from client applications


17. Build your own index

by Erland Sommarskog

The database and the table

Plain search and introducing tester_sp

Fragments and persons

Fragments and lists

Using bitmasks


18. Getting and staying connected—or not

by William Vaughn

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


19. Extending your productivity in SSMS and Query Analyzer

by Pawel Potasinski

Custom keyboard shortcuts

Creating your custom utility to use with keyboard shortcuts

Some ideas for utilities to implement


20. Why every SQL developer needs a tools database

by Denis Gobo

What belongs in the tools database?

Creating the tools database

Using an auxiliary table of numbers

Placing common code in the tools database


Calling code from a different database


21. Deprecation feature

by Cristian Lefter

A simple usage example

Methods of tracking deprecated features


22. Placing SQL Server in your pocket

by Christopher Fairbairn

Design goals



Tool support

Programming support


23. Mobile data strategies

by John Baird

Microsoft Sync Framework (MSF)



Part 3 Database Administration

Edited by Paul S. Randal and Kimberly L. Tripp

24. What does it mean to be a DBA?

by Brad M. McGehee

Typical DBA tasks: A to Z

DBA specialties


25. Working with maintenance plans

by Tibor Karaszi

What is a maintenance plan, and how do I create one?

Executing and monitoring the plan


26. PowerShell in SQL Server

by Richard Siddaway

PowerShell overview

PowerShell with SQL Server 2000 and 2005

PowerShell with SQL Server 2008


27. Automating SQL Server Management using SMO

by Allen White

Loading required libraries



Creating a database



28. Practical auditing in SQL Server 2008

by Jasper Smith

Overview of audit infrastructure

Server audits

Server audit specifications

Database audit specifications


29. My favorite DMVs, and why

by Aaron Bertrand

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


30. Reusing space in a table

by Joe Webb

Understanding how SQL Server automatically reuses table space

Recognizing when SQL Server does not reclaim space

Using DBCC CLEANTABLE to reclaim unused table space


31. Some practical issues in table partitioning

by Ron Talmage

Table partitioning dependencies

Manipulating partitioned data

How the partition function works

The key: avoiding data movement

Sources for more information


32. Partitioning for manageability (and maybe performance)

by Dan Guzman


How to partition

Planning and design considerations

Gotchas and tips


33. Efficient backups without indexes

by Greg Linwood

It’s OK to not back up nonclustered indexes!

A simple example

Backing up only the PRIMARY filegroup

Restoring the PRIMARY filegroup backup


Planning before moving NCIXs into a dedicated filegroup

Log shipping


34. Using database mirroring to become a superhero!

by Glenn Berry

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


35. The poor man’s SQL Server log shipping

by Edwin Sarmiento

Creating the T-SQL script

Creating a cleanup script

Creating a batch file

Improving the log shipping process


36. Understated changes in SQL Server 2005 replication

by Paul Ibison

Undocumented or partially documented changes in behavior

More efficient methodologies


37. High-performance transactional replication

by Hilary Cotter

Performance kiss of death factors in transactional replication

Optimal settings for replication


38. Successfully implementing Kerberos delegation

by Scott Stauffer

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


39. Running SQL Server on Hyper-V

by John Paul Cook

Virtualization architecture

Benefits of isolation

Configuring virtual machines

Addressing clock drift issues

Backup considerations

Advantages of physical to virtual migration

Test environments and virtualization


Part 4 Performance Tuning and Optimization

Edited by Kalen Delaney

40. When is an unused index not an unused index?

by Rob Farley

Overview of indexing

Unused indexes

Unused indexes that are actually used

How is the unused index being used?

How does this affect me?


41. Speeding up your queries with index covering

by Alex Kuznetsov

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


42. Tracing the deadlock

by Gail Shaw

What’s a deadlock?

Causes of deadlocks

Deadlock graph

The big picture


43. How to optimize tempdb performance

by Brad M. McGehee

What is tempdb used for?

Tempdb internals

How DBAs can help optimize tempdb

Minimizing the use of tempdb


44. Does the order of columns in an index matter?

by Joe Webb

Understanding the basics of composite indexes

Finding a specific row

Finding a last name

Finding a first name


45. Correlating SQL Profiler with PerfMon

by Kevin Kline

What vexes you?

Getting started with PerfMon and Profiler


46. Using correlation to improve query performance

by Gert-Jan Strik

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


47. How to use Dynamic Management Views

by Glenn Berry

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


48. Query performance and disk I/O counters

by Linchi Shea

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


49. XEVENT: the next event infrastructure

by Cristian Lefter

Extended Events infrastructure characteristics

XEVENT architecture

XEVENT in action


Part 5 Business intelligence

Edited by Greg Low

50. BI for the relational guy

by Erin Welker

Business intelligence overview


Really, what is so different?


Dimensional modeling

Cubes, anyone?

Microsoft BI stack

How do I get started?


51. Unlocking the secrets of SQL Server 2008 Reporting Services

by William Vaughn

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


52. Reporting Services tips and tricks

by Bruce Loehle-Conger

Performance tips

Design tips


53. SQL Server Audit, change tracking, and change data capture

by Aaron Bertrand

What are these solutions used for?

What do people do now?

How does SQL Server 2008 solve these problems?

Comparison of features


54. Introduction to SSAS 2008 data mining

by Dejan Sarka

Data mining basics

Creating mining models

Harvesting the results

Sources for more information


55. To aggregate or not to aggregate—is there really a question?

by Erin Welker

What are aggregations?

Designing aggregations

Influencing aggregations

Attribute relationships

Usage-based optimization

High-level approach

Other considerations


56. Incorporating data profiling in the ETL process

by John Welch

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


57. Expressions in SQL Server Integration Services

by Matthew Roche

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


58. SSIS performance tips

by Phil Brammer

SSIS overview

Control flow performance

Data flow performance


59. Incremental loads using T-SQL and SSIS

by Andy Leonard

Some definitions0

A T-SQL incremental load

Incremental loads in SSIS



About the book

SQL Server MVP Deep Dives is organized into five parts: Design and Architecture, Development, Administration, Performance Tuning and Optimization, and Business Intelligence. In each, you'll find concise, brilliantly clear chapters that take on key topics like mobile data strategies, Dynamic Management Views, or query performance.

What's inside

  • Topics important for SQL Server pros
  • Accessible to readers of all levels
  • New features of SQL Server 2008

About the reader

Whether you're just getting started with SQL Server or you're an old master looking for new tricks, this book belongs on your bookshelf.

About the authors

The authors of this book have generously donated 100% of their royalties to support War Child International.

  • combo $59.99 pBook + eBook
  • eBook $47.99 pdf + ePub + kindle

FREE domestic shipping on three or more pBooks