SQL Server MVP Deep Dives

SQL Server......Page 1
MVP contributors and their chapters......Page 6
brief contents......Page 10
contents......Page 14
preface......Page 36
acknowledgments......Page 39
about War Child......Page 40
Source code......Page 42
Author Online......Page 43
Interview with Ed Lehman, SQL Server Product Team MVP Liaison......Page 44
Part 1 Database design and architecture......Page 46
1. Denormalization is for wimps......Page 48
2. Keys are key......Page 49
4. Class <> table......Page 50
6. Sets good, cursors bad......Page 51
9. Spaghetti is food, not code......Page 52
10. NOLOCK = no consistency......Page 53
Summary......Page 54
SQL Server tools for maintaining data integrity......Page 56
Protection tools......Page 57
NULL specification......Page 58
Uniqueness constraints......Page 59
Filtered unique indexes......Page 60
Foreign key constraints......Page 61
Check constraints......Page 62
Triggers......Page 65
When and why to use what tool......Page 69
Summary......Page 71
Interview method......Page 73
Modeling the sales order......Page 74
First attribute: OrderNo......Page 76
Second attribute: CustomerName......Page 78
Next attribute: Product......Page 79
The remaining attributes......Page 80
Second step: finding two-attribute dependencies......Page 81
Candidate key and independent attribute......Page 82
Two dependent attributes......Page 83
What if I have some independent attributes left?......Page 84
Summary......Page 85
Part 2 Database Development......Page 86
The common methods and their shortcomings......Page 88
Drawbacks......Page 89
Drawbacks......Page 90
The most basic form......Page 91
Iterative code......Page 92
Set-based iteration......Page 94
Bin packing......Page 96
Iterative code......Page 97
Set-based iteration......Page 100
Summary......Page 103
Description of gaps and islands problems......Page 104
Sample data and desired results......Page 105
Solutions to gaps problem......Page 106
Gaps — solution 1 using subqueries......Page 107
Gaps — solution 2 using subqueries......Page 108
Gaps — solution 4 using cursors......Page 109
Performance summary for gaps solutions......Page 110
Islands — solution 1 using subqueries and ranking calculations......Page 111
Islands — solution 2 using group identifier based on subqueries......Page 112
Islands — solution 3 using group identifier based on ranking calculations......Page 113
Variation on the islands problem......Page 114
Summary......Page 116
Handling errors inside SQL Server......Page 118
Returning information about the error......Page 119
Generate your own errors using RAISERROR......Page 121
Nesting TRY...CATCH blocks......Page 122
TRY...CATCH and transactions......Page 124
Handling SQL Server errors on the client......Page 125
Handling SQL Server messages on the client......Page 127
Summary......Page 129
The INNER JOIN......Page 131
The OUTER JOIN......Page 132
The CROSS JOIN......Page 133
A sample query......Page 134
When the pattern doesn’t apply......Page 135
When the pattern can’t apply......Page 136
Filtering with the ON clause......Page 137
Filtering out the matches......Page 138
The four uses of JOINs......Page 139
Simplification using views......Page 141
How JOIN uses affect you......Page 145
Summary......Page 146
Recovery and locking......Page 147
Creating the file to import......Page 149
Creating the tables to store the data......Page 150
Importing the data......Page 151
Summary......Page 154
NULL comparisons......Page 156
Multiple OUTER JOINS......Page 158
Incorrect GROUP BY clauses......Page 161
Summary......Page 162
What is XQuery?......Page 164
How XQuery sees your XML......Page 167
Querying XML......Page 169
FLWOR expressions......Page 172
XQuery comparison operators......Page 174
Summary......Page 177
What’s XML?......Page 178
What’s “well-formed” XML?......Page 180
What’s the prolog?......Page 181
What’s a DTD?......Page 182
Why does SQL Server remove the DTD from my XML data?......Page 183
Why am I getting strange characters in my XML?......Page 184
How do I query a single value from my XML data?......Page 186
How do I shred XML data?......Page 187
Advanced query topics......Page 189
How do I specify an XML namespace in my XQuery queries?......Page 190
How do I get all element names and values from my XML document?......Page 191
How do I load XML documents from the filesystem?......Page 192
Summary......Page 194
The concept......Page 195
The logical model......Page 197
The physical model......Page 199
The XML Schema......Page 200
Entities of principal importance......Page 201
Preparing the inbound data flow......Page 205
Extracting data from XML using Transact-SQL......Page 206
Extracting Album data......Page 208
Extracting Band data......Page 212
Exporting the data......Page 216
Preparing the sample data......Page 218
Homework......Page 219
Summary......Page 220
Foundations of full-text searching......Page 221
Creating and maintaining catalogs......Page 222
Creating the full-text index......Page 223
Maintaining full-text indexes......Page 225
Basic searches......Page 226
FORMSOF......Page 227
Phrases, NEAR, OR, and prefixed terms......Page 228
Ranking......Page 229
Custom thesaurus......Page 231
Stopwords and stoplists......Page 234
Useful queries pertaining to stopwords and stoplists......Page 236
Basic queries to discover what catalogs, indexes, and columns exist......Page 237
Advanced queries......Page 239
The keywords......Page 241
Summary......Page 244
Simil: an algorithm to look for similar strings......Page 245
SOUNDEX and DIFFERENCE......Page 246
CONTAINS and FREETEXT......Page 247
Simil......Page 249
Algorithm......Page 250
Installation......Page 251
Usage......Page 252
Testing......Page 253
Summary......Page 254
LINQ to SQL and ADO.NET Entity Framework......Page 255
Concern......Page 256
Concern......Page 257
Concern......Page 259
Concern......Page 260
Concern......Page 262
Concern......Page 263
Summary......Page 264
What’s the problem?......Page 266
Table-valued parameters to the rescue!......Page 267
Another TVP example......Page 270
Using TVPs from client applications......Page 271
Using a DataTable......Page 272
Using a DbDataReader......Page 273
Using TVPs to enter orders......Page 274
Summary......Page 277
The database and the table......Page 279
Plain search and introducing tester_sp......Page 280
Using the LIKE operator—an important observation......Page 281
The fragments_persons table......Page 282
Writing the search procedure......Page 283
The final answer......Page 284
Keeping the index and the statistics updated......Page 286
What is the overhead?......Page 287
Fragments and lists......Page 288
Unwrapping the lists......Page 289
Loading the table......Page 290
A search procedure......Page 293
Keeping the lists updated......Page 294
The initial setup......Page 295
Adapting the bitmask to the data......Page 296
Performance and overhead......Page 297
Summary......Page 298
What is SQL Server?......Page 300
Understanding the SQL Server Browser service......Page 301
Testing for network availability......Page 302
Managing the SQL Server instance state......Page 303
Finding visible SQL Server instances......Page 304
What is a connection?......Page 306
To connect or not to connect.........Page 308
Connection management......Page 309
Connection strategies......Page 310
Establishing a connection......Page 311
The server key......Page 313
Trusted or untrusted security?......Page 314
ASP.NET considerations......Page 315
Using SQL Server authentication......Page 316
Accessing user instances......Page 317
Connection pooling......Page 318
Summary......Page 320
Custom keyboard shortcuts......Page 322
Creating your custom utility to use with keyboard shortcuts......Page 324
Summary......Page 327
What belongs in the tools database?......Page 328
Generating a calendar on the fly......Page 329
Splitting strings with a numbers table......Page 331
Placing common code in the tools database......Page 333
Formatting......Page 334
Summary......Page 335
Deprecation feature......Page 336
A simple usage example......Page 337
Methods of tracking deprecated features......Page 338
Summary......Page 341
Design goals......Page 342
Architecture......Page 343
Deploying on a desktop......Page 344
Tool support......Page 345
Programming support......Page 347
Summary......Page 348
Mobile data strategies......Page 350
Server synchronization provider......Page 351
Synchronization adapter......Page 352
Synchronization agent......Page 353
The WCF Service......Page 355
The Mobile Application......Page 359
Summary......Page 361
Part 3 Database Administration......Page 364
Typical DBA tasks: A to Z......Page 366
Business intelligence and data warehousing......Page 367
High availability......Page 368
Managing test environments......Page 369
Project management......Page 370
Working with teammates......Page 371
DBA high availability and disaster recovery specialist......Page 372
Summary......Page 373
What is a maintenance plan, and how do I create one?......Page 375
One or several schedules per plan?......Page 376
Task types......Page 377
Shrink database task......Page 378
Reorganize index task......Page 379
Update statistics task......Page 380
History cleanup task......Page 381
Back up database task......Page 383
Maintenance cleanup task......Page 385
Execute T-SQL statement task......Page 386
Executing and monitoring the plan......Page 387
Summary......Page 388
PowerShell features......Page 389
PowerShell issues and solutions......Page 390
Data access......Page 391
Administration......Page 392
SQLPS......Page 393
Provider......Page 394
Cmdlets......Page 395
Summary......Page 396
Automating SQL Server Management using SMO......Page 398
Loading required libraries......Page 399
Backup......Page 400
Restore......Page 402
Creating a database......Page 404
Scripting......Page 407
Summary......Page 409
Server audit objects......Page 410
Server audits......Page 411
Configuring the Windows Security Log target......Page 412
Creating a server audit using the Windows Security Log target......Page 413
Creating a security audit using the Windows Application Log target......Page 415
Configuring a server audit using the File target......Page 416
Creating server audit specifications......Page 417
Viewing audit events......Page 419
Creating database audit specifications......Page 421
Summary......Page 425
What is so great about DMVs, anyway?......Page 426
sys.dm_exec_sessions, sys.dm_exec_requests, and sys.dm_exec_connections......Page 428
sys.dm_exec_procedure_stats......Page 429
sys.dm_os_performance_counters......Page 430
sys.dm_db_index_physical_stats......Page 431
Setting up a utility database......Page 432
A more refined sp_who or sp_who2......Page 435
Getting statistics for stored procedures (SQL Server 2008 only)......Page 438
Finding unused stored procedures (SQL Server 2008 only)......Page 439
Finding inefficient and unused indexes......Page 441
Finding inefficient queries......Page 443
Finding missing indexes......Page 444
DMV categories in SQL Server......Page 446
Summary......Page 447
Understanding how SQL Server automatically reuses table space......Page 448
Recognizing when SQL Server does not reclaim space......Page 452
Using DBCC CLEANTABLE to reclaim unused table space......Page 455
Summary......Page 456
Table partitioning dependencies......Page 458
Manipulating partitioned data......Page 459
How the partition function works......Page 460
Drilling down: using SPLIT and MERGE......Page 462
Drilling down: using SWITCH......Page 463
Sources for more information......Page 464
Summary......Page 465
Overview......Page 466
How to partition......Page 468
Planning and design considerations......Page 469
Boundary time values......Page 474
Summary......Page 475
It’s OK to not back up nonclustered indexes!......Page 477
Default table and index storage behavior......Page 478
Moving nonclustered indexes into the new filegroup......Page 480
Restoring the PRIMARY filegroup backup......Page 481
Restoring for production use......Page 482
Issue 2: new nonclustered index filegroup must be renamed......Page 483
Step 2—Script out index definitions from primary filegroup......Page 484
Step 3—Restart SQL Server in single-user mode......Page 485
Step 5—Delete nonclustered index definitions......Page 486
usp_Manage_NCIX_Filegroup......Page 487
Moving NCIXs temporarily requires additional disk space......Page 490
Log shipping......Page 491
Summary......Page 492
How does database mirroring work?......Page 494
How do you set up database mirroring?......Page 495
How do you prepare the mirror?......Page 498
Using database mirroring for routine maintenance......Page 501
Using database mirroring to upgrade to SQL Server 2008......Page 503
Case study of moving data with database mirroring......Page 504
Lessons learned from case study......Page 505
Summary......Page 506
The poor man’s SQL Server log shipping......Page 508
Creating a cleanup script......Page 510
Creating a batch file......Page 511
Summary......Page 518
Undocumented or partially documented changes in behavior......Page 520
Creating snapshots without any data—only the schema......Page 521
“Not for Replication” treatment of identity columns......Page 523
Partitioning of snapshot files......Page 524
Remove redundant pre-snapshot and post-snapshot scripts......Page 526
Replace merge -EXCHANGETYPE parameters......Page 527
Summary......Page 528
High-performance transactional replication......Page 531
Batch updates......Page 532
Sp_setsubscriptionxactseqno......Page 534
Replicating text......Page 535
Logging......Page 536
Network latency......Page 537
Subscriber indexes and triggers......Page 538
Optimal settings for replication......Page 539
CommitBatchSize and CommitBatchThreshold......Page 540
Update proc......Page 541
Summary......Page 542
Successfully implementing Kerberos delegation......Page 543
A generic infrastructure—our business challenge......Page 544
Service principle names......Page 545
Creating SPNs......Page 547
Constrained delegation......Page 548
Configuring the Active Directory......Page 550
Set accounts for delegation in the Active Directory Users and Computers management console......Page 551
LAN settings......Page 552
Set Enable Integrated Windows Authentication......Page 553
Set Automatic Logon Only in Intranet Zone......Page 554
Configuring the web tier......Page 555
Configure virtual directory to use application pool......Page 556
Configuring the data tier......Page 557
Resources to assist in more complex infrastructures......Page 559
Summary......Page 564
Running SQL Server on Hyper-V......Page 565
Virtualization architecture......Page 566
Benefits of isolation......Page 567
Configuring disks......Page 568
CPU configuration......Page 570
Configuring networking......Page 571
Backup considerations......Page 572
Test environments and virtualization......Page 573
Summary......Page 574
Part 4 Performance Tuning and Optimization......Page 576
Overview of indexing......Page 578
Unused indexes......Page 579
Unused indexes that are actually used......Page 582
How is the unused index being used?......Page 585
Summary......Page 586
Index covering speeds up selects......Page 588
Covering indexes usually slow down modifications......Page 592
One index should cover many queries......Page 593
Summary......Page 594
What’s a deadlock?......Page 596
Trace flag 1204......Page 597
Reading the deadlock graph......Page 598
The process list......Page 599
The resource list......Page 602
Summary......Page 603
What is tempdb used for?......Page 605
How DBAs can help optimize tempdb......Page 606
Minimizing the use of tempdb......Page 607
Preallocating tempdb space and avoiding use of autogrowth......Page 608
Dividing tempdb among multiple physical files......Page 609
Locating tempdb on a fast I/O subsystem......Page 610
Verifying CHECKSUM for SQL Server 2008......Page 611
Summary......Page 612
Understanding the basics of composite indexes......Page 613
Finding a specific row......Page 614
Finding a last name......Page 616
Finding a first name......Page 617
Summary......Page 621
Correlating SQL Profiler with PerfMon......Page 622
Best practices using PerfMon......Page 623
Best practices using Profiler......Page 625
A correlated view of performance......Page 627
Summary......Page 628
The purpose of the optimizer......Page 630
Low correlation......Page 631
When the optimizer does it right......Page 632
When the optimizer does it right again......Page 633
Correcting the optimizer......Page 634
Determining correlation......Page 635
Summary......Page 636
Setting up security to run DMV queries......Page 637
Looking at top waits at the instance level......Page 638
Looking for CPU pressure and what’s causing it......Page 639
Finding I/O pressure in SQL Server......Page 641
SQL Server memory pressure......Page 644
SQL Server index usage......Page 647
Detecting blocking in SQL Server......Page 650
Summary......Page 651
Expensive I/Os and very expensive I/Os......Page 653
Disk performance counters......Page 654
Random or sequential I/Os and disk performance counters......Page 655
SQL Server operations and I/O sizes......Page 656
How expensive are small random I/Os, anyway?......Page 657
Scenario 1: constant checkpoints......Page 660
Scenario 2: NOLOCK and faster query processing......Page 661
Scenario 3: read-ahead reads......Page 662
Scenario 4: index fragmentation......Page 663
Summary......Page 665
Extended Events infrastructure characteristics......Page 666
SQL Server Extended Events engine......Page 669
Packages......Page 670
Targets......Page 671
Types and maps......Page 672
Usage scenarios......Page 673
System health session......Page 675
Performance considerations......Page 676
Summary......Page 677
Part 5 Business intelligence......Page 678
Business intelligence overview......Page 680
Terminology......Page 681
Really, what is so different?......Page 682
Approach......Page 683
Dimensional modeling......Page 684
Cubes, anyone?......Page 685
How do I get started?......Page 687
Summary......Page 688
Why should developers care about Reporting Services?......Page 689
What is Reporting Services?......Page 690
Using Visual Studio to create an RDL report......Page 692
Using the Visual Studio 2008 Report Designer......Page 696
Managing report parameters......Page 697
Deploying your report......Page 698
Using the Report Manager......Page 699
Using the Visual Studio MicrosoftReportViewer control......Page 700
What’s in Reporting Services 2008 for developers?......Page 701
Using SQL Server Reporting Services Configuration Manager......Page 702
Enabling My Reports......Page 703
Working with the Report Designer......Page 705
Summary......Page 706
Linked servers......Page 707
Drillthrough instead of drill-down......Page 708
Connection pooling......Page 709
Excel merged cell solution......Page 710
Excel web queries and reports......Page 711
Server portability......Page 714
Embedding T-SQL in a report......Page 715
Summary......Page 716
What are these solutions used for?......Page 717
What do people do now?......Page 718
How does SQL Server 2008 solve these problems?......Page 719
SQL Server Audit......Page 720
Change tracking......Page 723
Change data capture......Page 728
Comparison of features......Page 732
Summary......Page 733
Introduction to SSAS 2008 data mining......Page 734
Data mining basics......Page 735
Data mining projects......Page 736
SSAS 2008 data mining algorithms......Page 737
Creating mining models......Page 738
Harvesting the results......Page 740
Viewing the models......Page 741
Evaluating the models......Page 742
Creating prediction queries......Page 744
Summary......Page 745
What are aggregations?......Page 747
Designing aggregations......Page 748
Influencing aggregations......Page 749
Attribute relationships......Page 751
Usage-based optimization......Page 752
Other considerations......Page 754
Summary......Page 755
Why profile data?......Page 756
The Column Value Distribution profile......Page 758
Input to the task......Page 759
Output from the task......Page 760
Constraints of the Data Profiling task......Page 762
Altering the profile requests......Page 763
Setting the ProfileInputXml property......Page 764
Adjusting rules dynamically......Page 766
Capturing the output......Page 767
Using SSIS XML functionality......Page 768
Incorporating the values in the package......Page 770
Summary......Page 771
SSIS packages: a brief review......Page 773
Expressions: a quick tour......Page 774
Expressions in the control flow......Page 776
Expressions and variables......Page 779
Expressions in the data flow......Page 781
Expressions and connection managers......Page 784
Summary......Page 788
Control flow performance......Page 790
Source acquisition performance......Page 791
Data transformation performance......Page 792
Destination performance......Page 793
Lookup transformation performance......Page 794
General data flow performance......Page 795
Summary......Page 796
Some definitions......Page 797
A T-SQL incremental load......Page 798
Creating the new BIDS project......Page 801
Defining the lookup transformation......Page 802
Setting the lookup transformation behavior......Page 804
Summary......Page 807
A......Page 810
B......Page 812
C......Page 813
D......Page 816
E......Page 821
F......Page 822
I......Page 824
L......Page 826
M......Page 828
N......Page 830
O......Page 831
P......Page 832
R......Page 835
S......Page 838
T......Page 845
U......Page 847
V......Page 848
W......Page 849
X......Page 850