Table Of ContentM A N N I N G
DEEP DIVES
E
DITED BY
Paul Nielsen Kalen Delaney Greg Low Adam Machanic Paul S. Randal Kimberly L. Tripp
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
● ● ● ● ● ● ● ●
Author royalties go to support War Child International
SQL Server MVP
Deep Dives
SQL Server MVP
Deep Dives
Edited by Paul Nielsen (cid:131) Kalen Delaney (cid:131) Greg Low
Adam Machanic (cid:131) Paul S. Randal (cid:131) Kimberly L. Tripp
MANNING
Greenwich
(74° w. long.)
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: [email protected]
©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
1 2 3 4 5 6 7 8 9 10 – MAL – 14 13 12 11 10 09
MVP contributors and their chapters
John Baird 23 Cristian Lefter 21, 49
Bob Beauchemin 15 Andy Leonard 59
Itzik Ben-Gan 5 Greg Linwood 33
Glenn Berry 34, 47 Bruce Loehle-Conger 52
Aaron Bertrand 29, 53 Brad McGehee 24, 43
Phil Brammer 58 Paul Nielsen 1
Robert C. Cain 13 Pawel Potasinski 19
Michael Coles 10, 11 Matthew Roche 57
John Paul Cook 39 Dejan Sarka 54
Hilary Cotter 37 Edwin Sarmiento 35
Louis Davidson 1, 2 Gail Shaw 42
Christopher Fairbairn 22 Linchi Shea 48
Rob Farley 7, 40 Richard Siddaway 26
Denis Gobo 8, 20 Jasper Smith 28
Bill Graziano 6 Erland Sommarskog 17
Dan Guzman 32 Scott Stauffer 38
Paul Ibison 36 Tom van Stiphout 14
Tibor Karaszi 25 Gert-Jan Strik 46
Kathi Kellenberger 9 Ron Talmage 31
Don Kiely 16 William R. Vaughn 18, 51
Kevin Kline 45 Joe Webb 30, 44
Hugo Kornelis 3, 4 John Welch 56
Alex Kuznetsov 41 Erin Welker 55
Matija Lah 12 Allen White 27
Technical Editor
Rod Colledge
v
To all children traumatized by the horrors of war
and War Child’s efforts to help children be children again
brief contents
PART 1 DATABASE DESIGN AND ARCHITECTURE ..............1
1 (cid:131) Louis and Paul’s 10 key relational database design ideas 3
2 (cid:131) SQL Server tools for maintaining data integrity 11
3 (cid:131) Finding functional dependencies 28
PART 2 DATABASE DEVELOPMENT ..............................41
4 (cid:131) Set-based iteration, the third alternative 43
5 (cid:131) Gaps and islands 59
6 (cid:131) Error handling in SQL Server and applications 73
7 (cid:131) Pulling apart the FROM clause 86
8 (cid:131) What makes a bulk insert a minimally logged operation? 102
9 (cid:131) Avoiding three common query mistakes 111
10 (cid:131) Introduction to XQuery on SQL Server 119
11 (cid:131) SQL Server XML frequently asked questions 133
12 (cid:131) Using XML to transport relational data 150
13 (cid:131) Full-text searching 176
14 (cid:131) Simil: an algorithm to look for similar strings 200
15 (cid:131) LINQ to SQL and ADO.NET Entity Framework 210
16 (cid:131) Table-valued parameters 221
17 (cid:131) Build your own index 234
ix
