MANNING
MVP C
ONTRIBUTORS
John Baird
●
Bob Beauchemin
●
Itzik Ben-Gan
●
Glenn Berry
●
Aaron Bertrand
●
Phil Brammer
●
Robert C. Cain
●
Michael Coles
●
John Paul Cook
●
Hilary Cotter
●

Louis Davidson
●
Christopher Fairbairn
●
Rob Farley
●
Denis Gobo

●
Bill Graziano
●
Dan Guzman
●
Paul Ibison
●
Tibor Karaszi
●
Kathi Kellenberger
●
Don Kiely
●

Kevin Kline
●
Hugo Kornelis
●
Alex Kuznetsov
●
Matija Lah
●
Cristian Lefter
●
Andy Leonard
●
Greg Linwood
●
Bruce Loehle-Conger
●

Brad McGehee
●
Paul Nielsen
●

Pawel Potasinski
●
Matthew Roche
●
Dejan Sarka
●
Edwin Sarmiento
●
Gail Shaw
●
Linchi Shea
●
Richard Siddaway
●
Jasper Smith
●
Erland Sommarskog
●

Scott Stauffer
●
Tom van Stiphout
●
Gert-Jan Strik
●

Ron Talmage
●
William R. Vaughn
●
Joe Webb
●
John Welch
●
Erin Welker
●
Allen White
DEEP DIVES
E
DITED BY
Paul Nielsen Kalen Delaney Greg Low Adam Machanic Paul S. Randal Kimberly L. Tripp
Author royalties go to support War Child International
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
SQL Server MVP
Deep Dives
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
SQL Server MVP
Deep Dives
Edited by Paul Nielsen  Kalen Delaney  Greg Low
Adam Machanic  Paul S. Randal  Kimberly L. Tripp

MANNING
Greenwich
(74° w. long.)
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
For online information and ordering of this and other Manning books, please visit
www.manning.com. The publisher offers discounts on this book when ordered in quantity.
For more information, please contact
Special Sales Department
Manning Publications Co.
Sound View Court 3B
Greenwich, CT 06830
Email:
©2010 by Manning Publications Co. All rights reserved.
No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in
any form or by means electronic, mechanical, photocopying, or otherwise, without prior written
permission of the publisher.
Many of the designations used by manufacturers and sellers to distinguish their products are
claimed as trademarks. Where those designations appear in the book, and Manning
Publications was aware of a trademark claim, the designations have been printed in initial caps
or all caps.
Recognizing the importance of preserving what has been written, it is Manning’s policy to have
the books we publish printed on acid-free paper, and we exert our best efforts to that end.
Recognizing also our responsibility to conserve the resources of our planet, Manning books
are printed on paper that is at least 15 percent recycled and processed without the use of
elemental chlorine.
Development editor: Jeff Bleiel
Manning Publications Co. Lead copyeditor: Andy Carroll
Sound View Court 3B Typesetter: Dottie Marsico

Greenwich, CT 06830 Cover designer: Marija Tudor
ISBN 978-1-935182-04-7
Printed in the United States of America
12345678910–MAL–141312111009
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
v
MVP contributors and their chapters
John Baird 23
Bob Beauchemin 15
Itzik Ben-Gan 5
Glenn Berry 34, 47
Aaron Bertrand 29, 53
Phil Brammer 58
Robert C. Cain 13
Michael Coles 10, 11
John Paul Cook 39
Hilary Cotter 37
Louis Davidson 1, 2
Christopher Fairbairn 22
Rob Farley 7, 40
Denis Gobo 8, 20
Bill Graziano 6
Dan Guzman 32
Paul Ibison 36
Tibor Karaszi 25
Kathi Kellenberger 9
Don Kiely 16
Kevin Kline 45

Hugo Kornelis 3, 4
Alex Kuznetsov 41
Matija Lah 12
Cristian Lefter 21, 49
Andy Leonard 59
Greg Linwood 33
Bruce Loehle-Conger 52
Brad McGehee 24, 43
Paul Nielsen 1
Pawel Potasinski 19
Matthew Roche 57
Dejan Sarka 54
Edwin Sarmiento 35
Gail Shaw 42
Linchi Shea 48
Richard Siddaway 26
Jasper Smith 28
Erland Sommarskog 17
Scott Stauffer 38
Tom van Stiphout 14
Gert-Jan Strik 46
Ron Talmage 31
William R. Vaughn 18, 51
Joe Webb 30, 44
John Welch 56
Erin Welker 55
Allen White 27

Technical Editor
Rod Colledge


Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
To all children traumatized by the horrors of war
and War Child’s efforts to help children be children again
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
ix
brief contents
PART 1DATABASE DESIGN AND ARCHITECTURE 1
1  Louis and Paul’s 10 key relational database design ideas 3
2  SQL Server tools for maintaining data integrity 11
3  Finding functional dependencies 28
PART 2DATABASE DEVELOPMENT 41
4  Set-based iteration, the third alternative 43
5  Gaps and islands 59
6  Error handling in SQL Server and applications 73
7  Pulling apart the FROM clause 86
8  What makes a bulk insert a minimally logged operation? 102
9  Avoiding three common query mistakes 111
10  Introduction to XQuery on SQL Server 119
11  SQL Server XML frequently asked questions 133

12  Using XML to transport relational data 150
13  Full-text searching 176
14  Simil: an algorithm to look for similar strings 200
15  LINQ to SQL and ADO.NET Entity Framework 210
16  Table-valued parameters 221
17  Build your own index 234
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
x
BRIEF CONTENTS
18  Getting and staying connected—or not 255
19  Extending your productivity in SSMS and Query Analyzer 277
20  Why every SQL developer needs a tools database 283
21  Deprecation feature 291
22  Placing SQL Server in your pocket 297
23  Mobile data strategies 305
PART 3DATABASE ADMINISTRATION 319
24  What does it mean to be a DBA? 321
25  Working with maintenance plans 330
26  PowerShell in SQL Server 344
27  Automating SQL Server Management using SMO 353
28  Practical auditing in SQL Server 2008 365
29  My favorite DMVs, and why 381
30  Reusing space in a table 403
31  Some practical issues in table partitioning 413
32  Partitioning for manageability (and maybe performance) 421
33  Efficient backups without indexes 432
34  Using database mirroring to become a superhero! 449
35  The poor man’s SQL Server log shipping 463

36  Understated changes in SQL Server 2005 replication 475
37  High-performance transactional replication 484
38  Successfully implementing Kerberos delegation 496
39  Running SQL Server on Hyper-V 518
PART 4PERFORMANCE TUNING AND OPTIMIZATION 529
40  When is an unused index not an unused index? 531
41  Speeding up your queries with index covering 541
42  Tracing the deadlock 549
43  How to optimize tempdb performance 558
44  Does the order of columns in an index matter? 566
45  Correlating SQL Profiler with PerfMon 575
46  Using correlation to improve query performance 583
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
xi
BRIEF CONTENTS
47  How to use Dynamic Management Views 590
48  Query performance and disk I/O counters 606
49  XEVENT: the next event infrastructure 619
PART 5BUSINESS INTELLIGENCE 631
50  BI for the relational guy 633
51  Unlocking the secrets of SQL Server 2008 Reporting
Services 642
52  Reporting Services tips and tricks 660
53  SQL Server Audit, change tracking, and change
data capture 670
54  Introduction to SSAS 2008 data mining 687
55  To aggregate or not to aggregate—is there really a question? 700
56  Incorporating data profiling in the ETL process 709

57  Expressions in SQL Server Integration Services 726
58  SSIS performance tips 743
59  Incremental loads using T-SQL and SSIS 750
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
xiii
contents
preface xxxv
acknowledgments xxxviii
about War Child xxxix
about this book xli
about SQL Server MVPs xliii
P
ART
1D
ATABASE

DESIGN

AND

ARCHITECTURE
1
EDITED BY PAUL NIELSEN
1
Louis and Paul’s 10 key relational database design ideas 3

PAUL NIELSEN AND LOUIS DAVIDSON
1. Denormalization is for wimps 3
2. Keys are key 4
3. Generalize, man! 5
4. Class <> table 5
5. Data drives design 6
6. Sets good, cursors bad 6
7. Properly type data 7
8. Extensibility through encapsulation 7
9. Spaghetti is food, not code 7
10. NOLOCK = no consistency 8
Summary 9
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
xiv
CONTENTS
2
SQL Server tools for maintaining data integrity 11
LOUIS DAVIDSON
Protection tools 12
Data types 13
■
NULL specification 13
■
Uniqueness
constraints 14
■
Filtered unique indexes 15
■

Foreign key
constraints 16
■
Check constraints 17
■
Triggers 20
When and why to use what tool 24
Summary 26
3
Finding functional dependencies 28
HUGO KORNELIS
Interview method 28
Modeling the sales order 29
First step: finding single-attribute dependencies 31
■
Second step: finding
two-attribute dependencies 36
■
Further steps: three-and-more-attribute
dependencies 39
■
What if I have some independent attributes left? 39
Summary 40
P
ART
2D
ATABASE
D
EVELOPMENT
41

EDITED BY ADAM MACHANIC
4
Set-based iteration, the third alternative 43
HUGO KORNELIS
The common methods and their shortcomings 43
Declarative (set-based) code 44
■
Iterative (cursor-based) code 45
Set-based iteration 46
The most basic form 46
■
Running totals 47
■
Bin packing 51
Summary 58
5
Gaps and islands 59
ITZIK BEN-GAN
Description of gaps and islands problems 59
Sample data and desired results 60
Solutions to gaps problem 61
Gaps—solution 1 using subqueries 62
■
Gaps—solution 2 using
subqueries 63
■
Gaps—solution 3 using ranking functions 64
Gaps—solution 4 using cursors 64
■
Performance summary for

gaps solutions 65
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
xv
CONTENTS
Solutions to islands problem 66
Islands—solution 1 using subqueries and ranking calculations 66
Islands—solution 2 using group identifier based on subqueries 67
Islands—solution 3 using group identifier based on ranking
calculations 68
■
Islands—solution 4 using cursors 69
Variation on the islands problem 69
■
Performance summary
for islands solutions 71
Summary 71
6
Error handling in SQL Server and applications 73
BILL GRAZIANO
Handling errors inside SQL Server 73
Returning information about the error 74
■
Generate your own
errors using RAISERROR 76
■
Nesting TRY CATCH
blocks 77
■

TRY CATCH and transactions 79
Handling SQL Server errors on the client 80
Handling SQL Server messages on the client 82
Summary 84
7
Pulling apart the FROM clause 86
ROB FARLEY
JOIN basics 86
The INNER JOIN 86
■
The OUTER JOIN 87
■
The CROSS
JOIN 88
Formatting your FROM clause 89
A sample query 89
■
The appearance of most queries 90
When the pattern doesn’t apply 90
■
How to read a FROM
clause 91
■
When the pattern can’t apply 91
■
Writing the FROM
clause clearly the first time 92
Filtering with the ON clause 92
The different filters of the SELECT statement 93
■

Filtering out
the matches 93
JOIN uses and simplification 94
The four uses of JOINs 94
Simplification using views 96
How JOIN uses affect you 100
Summary 101
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
xvi
CONTENTS
8
What makes a bulk insert a minimally logged operation? 102
DENIS GOBO
Recovery and locking 102
Creating the file to import 104
Creating the tables to store the data 105
Importing the data 106
Summary 109
9
Avoiding three common query mistakes 111
KATHI KELLENBERGER
NULL comparisons 111
Multiple OUTER JOINS 113
Incorrect GROUP BY clauses 116
Summary 117
10
Introduction to XQuery on SQL Server 119
MICHAEL COLES

What is XQuery? 119
How XQuery sees your XML 122
Querying XML 124
FLWOR expressions 127
XQuery comparison operators 129
XML indexes and XQuery performance 132
Summary 132
11
SQL Server XML frequently asked questions 133
MICHAEL COLES
XML basics 133
What’s XML? 133
■
What’s “well-formed” XML? 135
What’s the prolog? 136
■
What’s an entity? 137
What’s a DTD? 137
The xml data type 138
Why does SQL Server remove the DTD from my XML data? 138
How do I preserve whitespace in my XML? 139
■
Why am I getting
strange characters in my XML? 139
■
How do I query XML
data? 141
■
How do I query a single value from my XML
data? 141

■
How do I shred XML data? 142
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
xvii
CONTENTS
Advanced query topics 144
How do I specify an XML namespace in my XQuery queries? 145
How do I get all element names and values from my XML
document? 146
■
How do I load XML documents from the
filesystem? 147
Summary 149
12
Using XML to transport relational data 150
MATIJA LAH
Understanding before coding 150
The concept 150
■
The logical model 152
■
The physical
model 154
■
The database 155
■
The XML Schema 155
Enabling and maintaining the data flow 160

Preparing the inbound data flow 160
■
Importing the data 163
Exporting the data 171
Preparing the sample data 173
Homework 174
Summary 175
13
Full-text searching 176
ROBERT C. CAIN
Foundations of full-text searching 176
Creating and maintaining catalogs 177
Creating and maintaining full-text indexes 178
Creating the full-text index 178
■
Maintaining full-text indexes 180
Querying full-text indexes 181
Basic searches 181
■
FORMSOF 182
■
Phrases, NEAR, OR, and
prefixed terms 183
■
Ranking 184
Custom thesaurus and stopwords 186
Custom thesaurus 186
■
Stopwords and stoplists 189
Useful system queries 192

Basic queries to discover what catalogs, indexes, and columns
exist 192
■
Advanced queries 194
■
The keywords 196
Summary 199
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
xviii
CONTENTS
14
Simil: an algorithm to look for similar strings 200
TOM VAN STIPHOUT
Equals (=) and LIKE 201
SOUNDEX and DIFFERENCE 201
CONTAINS and FREETEXT 202
Simil 204
Algorithm 205
■
Implementation in .NET 206
Installation 206
■
Usage 207
■
Testing 208
Summary 209
15
LINQ to SQL and ADO.NET Entity Framework 210

BOB BEAUCHEMIN
LINQ to SQL and performance 211
Generating SQL that uses projection 212
Updating in the middle tier 214
Optimizing the number of database round trips 215
LINQ to SQL and stored procedures 217
Tuning and LINQ to SQL queries 218
Summary 219
16
Table-valued parameters 221
DON KIELY
What’s the problem? 221
Table-valued parameters to the rescue! 222
Another TVP example 225
Using TVPs from client applications 226
Using a DataTable 227
■
Using a DbDataReader 228
Using TVPs to enter orders 229
Summary 232
17
Build your own index 234
ERLAND SOMMARSKOG
The database and the table 234
Plain search and introducing tester_sp 235
Using the LIKE operator—an important observation 236
Using a binary collation 237
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -

xix
CONTENTS
Fragments and persons 237
The fragments_persons table 237
■
Writing the search
procedure 238
■
Keeping the index and the statistics
updated 241
■
What is the overhead? 242
Fragments and lists 243
Building the lists 244
■
Unwrapping the lists 244
The fragments_personlists table 245
■
Loading the table 245
A search procedure 248
■
Keeping the lists updated 249
Using bitmasks 250
The initial setup 250
■
Searching with the bitmask 251
Adapting the bitmask to the data 251
■
Performance and
overhead 252

■
The big bitmask 253
Summary 253
18
Getting and staying connected—or not 255
WILLIAM VAUGHN
What is SQL Server? 255
Understanding the SQL Server Browser service 256
Diagnosing a connectivity problem 257
Testing for network availability 257
■
Managing the SQL Server
instance state 258
■
Finding visible SQL Server instances 259
What is a connection? 261
To connect or not to connect 263
Connection management 264
Connection strategies 265
Establishing a connection 266
The server key 268
Trusted or untrusted security? 269
Using trusted or integrated security 270
■
ASP.NET
considerations 270
■
Using SQL Server authentication 271
Accepting user login credentials—or not 272
Accessing user instances 272

Connection pooling 273
Closing the connection 275
Summary 275
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
xx
CONTENTS
19
Extending your productivity in SSMS and Query Analyzer 277
PAWEL POTASINSKI
Custom keyboard shortcuts 277
Creating your custom utility to use with
keyboard shortcuts 279
Some ideas for utilities to implement 282
Summary 282
20
Why every SQL developer needs a tools database 283
DENIS GOBO
What belongs in the tools database? 283
Creating the tools database 284
Using an auxiliary table of numbers 284
Generating a calendar on the fly 284
■
Splitting strings with a numbers
table 286
Placing common code in the tools database 288
Formatting 289
Calling code from a different database 290
Summary 290

21
Deprecation feature 291
CRISTIAN LEFTER
A simple usage example 292
Methods of tracking deprecated features 293
Summary 296
22
Placing SQL Server in your pocket 297
CHRISTOPHER FAIRBAIRN
Design goals 297
Architecture 298
Deployment 299
Deploying on a desktop 299
■
Deploying on a device 300
XCOPY deployment 300
Tool support 300
Programming support 302
Summary 303
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
xxi
CONTENTS
23
Mobile data strategies 305
JOHN BAIRD
Microsoft Sync Framework (MSF) 306
Client synchronization provider 306
■

Server synchronization
provider 306
■
Synchronization adapter 307
Synchronization agent 308
■
Using MSF 310
Comparison 316
Summary 316
P
ART
3D
ATABASE
A
DMINISTRATION
319
EDITED BY PAUL S. RANDAL
AND KIMBERLY L. TRIPP
24
What does it mean to be a DBA? 321
BRAD M. MCGEHEE
Typical DBA tasks: A to Z 321
DBA specialties 327
Summary 328
25
Working with maintenance plans 330
TIBOR KARASZI
What is a maintenance plan, and how do I create one? 330
Versions and service packs 331
■

One or several schedules per
plan? 331
■
Wizard dialogs 332
■
Task types 332
Check database integrity task 333
■
Shrink database task 333
Reorganize index task 334
■
Rebuild index task 335
Update statistics task 335
■
History cleanup task 336
Execute SQL Server Agent job task 338
■
Back up database
task 338
■
Maintenance cleanup task 340
■
Select reporting
options 341
■
Execute T-SQL statement task 341
Executing and monitoring the plan 342
Summary 343
26
PowerShell in SQL Server 344

RICHARD SIDDAWAY
PowerShell overview 344
PowerShell features 344
■
PowerShell issues and solutions 345
PowerShell with SQL Server 2000 and 2005 346
Data access 346
■
Administration 347
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
xxii
CONTENTS
PowerShell with SQL Server 2008 348
SQLPS 348
■
Provider 349
■
Cmdlets 350
Summary 351
27
Automating SQL Server Management using SMO 353
ALLEN WHITE
Loading required libraries 354
Backup 355
Restore 357
Creating a database 359
Scripting 362
Summary 364

28
Practical auditing in SQL Server 2008 365
JASPER SMITH
Overview of audit infrastructure 365
Server audit objects 365
■
Server audit specification objects 366
Database audit specification objects 366
Server audits 366
Configuring the Windows Security Log target 367
■
Creating a server
audit using the Windows Security Log target 368
Creating a security audit using the Windows Application Log
target 370
■
Configuring a server audit using the File target 371
Server audit specifications 372
Creating server audit specifications 372
■
Viewing
audit events 374
Database audit specifications 376
Creating database audit specifications 376
Summary 380
29
My favorite DMVs, and why 381
AARON BERTRAND
What is so great about DMVs, anyway? 381
A brief list of my favorite DMVs and DMFs 383

sys.dm_os_sys_info 383
■
sys.dm_exec_sessions,
sys.dm_exec_requests, and sys.dm_exec_connections 383
sys.dm_exec_sql_text 384
■
sys.dm_exec_query_stats 384
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
xxiii
CONTENTS
sys.dm_exec_procedure_stats 384
sys.dm_db_index_usage_stats 385
sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups,
and sys.dm_db_missing_index_group_stats 385
Honorable mentions 385
sys.dm_os_performance_counters 385
sys.dm_db_partition_stats 386
sys.dm_db_index_physical_stats 386
sys.dm_sql_referenced_entities 387
Setting up a utility database 387
Some interesting applications of my favorite DMVs 390
A more refined sp_who or sp_who2 390
■
Getting statistics for stored
procedures (SQL Server 2008 only) 393
■
Finding unused stored
procedures (SQL Server 2008 only) 394

■
Finding inefficient and
unused indexes 396
■
Finding inefficient queries 398
■
Finding
missing indexes 399
DMV categories in SQL Server 401
Summary 402
30
Reusing space in a table 403
JOE WEBB
Understanding how SQL Server automatically reuses
table space 403
Recognizing when SQL Server does not reclaim space 407
Using DBCC CLEANTABLE to reclaim unused table
space 410
Summary 411
31
Some practical issues in table partitioning 413
RON TALMAGE
Table partitioning dependencies 413
Manipulating partitioned data 414
How the partition function works 415
Drilling down: using SPLIT and MERGE 417
■
Drilling down: using
SWITCH 418
The key: avoiding data movement 419

Sources for more information 419
Summary 419
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -
xxiv
CONTENTS
32
Partitioning for manageability (and maybe performance) 421
DAN GUZMAN
Overview 421
How to partition 423
Planning and design considerations 424
Gotchas and tips 429
Boundary time values 429
■
SPLIT and MERGE
performance 430
■
Update statistics after SWITCH 430
Shared partition functions and schemes 430
Summary 430
33
Efficient backups without indexes 432
GREG LINWOOD
It’s OK to not back up nonclustered indexes! 432
A simple example 433
Default table and index storage behavior 433
■
Adding a dedicated

filegroup for nonclustered indexes 435
■
Moving nonclustered indexes
into the new filegroup 435
Backing up only the PRIMARY filegroup 436
Restoring the PRIMARY filegroup backup 436
Restoring for extraction only 437
■
Restoring for production
use 437
■
Restoring for production use—step by step 439
usp_Manage_NCIX_Filegroup 442
Planning before moving NCIXs into a dedicated
filegroup 445
Moving NCIXs temporarily requires additional disk space 445
Moving NCIXs creates empty space in PRIMARY filegroup 445
Log shipping 446
Summary 447
34
Using database mirroring to become a superhero! 449
GLENN BERRY
Why should I use database mirroring? 449
How does database mirroring work? 449
How do you set up database mirroring? 450
How do you prepare the mirror? 453
Using database mirroring for routine maintenance 456
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Simpo PDF Merge and Split Unregistered Version -