SQL Server MVP deep dives

[Pages:22]SQL Server MVP Deep Dives

Edited by Paul Nielsen Kalen Delaney Greg Low Adam Machanic Paul S. Randal Kimberly L. Tripp

ii

MANNING Greenwich

(74? w. long.)

contents

preface xxxv acknowledgments xxxviii

about War Child xxxix about this book xli

about SQL Server MVPs xliii

:;W,\i % Database design and architecture

1

Eihtkd by Paul Nielsen

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

xiii

xiv

CONTENTS

SQL Server toolsfor maintaining data integrity 11

Louis Davidson

Protection tools 12

Data types

constraints

constraints

13 ? NULL specification 13 * Uniqueness 14 * Filtered unique indexes 15 ? Foreign key 16 Check constraints 17 * Triggers 20

When and why to use what tool 24

Summary 26

Findingfunctional 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* Wtiat if I have some independent attributes left? 39

Summary 40

art 2

Database Development KiaBBoeiatsaBBSiaaniaiaiJraBranHia m a m n n n

Edited by Adam Machanic

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

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 rankingfunctions 64 Gaps--solution 4 using cursors 64 ? Performance summary for gaps solutions 65

CONTENTS

xv

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

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 usingRAISERROR 76 ? Nesting TRY...CATCH

blocks 77 ? TRY... CATCH and transactions 79

Handling SQL Server errors on the client 80 Handling SQJ. Serve)- messages on the client 82

Summaiy 84

|> Pulling apart the FROM clause 86

f

Rob Farley

JOIN basics 86

The INNER fOIN JOIN 88

86 * The OUTER JOIN

87 * The CROSS

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

the matches 93

93* Filtering out

JOIN uses and simplification 94 The four uses of JOINs 94 Simplification using views 96 How JOIN uses affect you 100 Summary 101

CONTENTS

What makes a bulk insert a minimally logged operation?

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

102

Avoiding three common query mistakes

Kathi Kellenberger

NULL comparisons 111 Multiple OUTER JOINS 113

Incorrect GROUP BY clauses 116

Summary 117

111

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

SQL Server XMLfrequently asked questions 133

Michael Coles

XML basics 133

What's XML? 133 * What's "well-formed"XML? Wliat's the prolog? 136 What's an entity? 137 Wlial's a DTD? 137

135

The xml data type 138

Why does SQL Saver remove the DTD from my XML da ta ? 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 valuefrom my XML

data ? 141 How do I shred XML data ? 142

CONTENTS

xvii

Advanced query topics 144

How do I specify an XML namespace in my XQuery queries'? How do I get all element names and values from my XML document? 146' How do I load XML documents from the filesystem? 147

145

Summary 149

JJP Using XML to transport relational data 150

i?j

, Ma- tija,

_

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

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 * Maintainingfull-text indexes 180

Querying full-text indexes 181

Basic searches

prefixed terms

181 - FORMSOF 182

183 ? Ranking 184

Phrases, NEAR, OR, and

Custom thesaurus and stopwords 186

Custom thesaurus 186 * Stopwords and stofAists 189

Useful system queries 192

Basic queries to discover what catalogs, indexes, and columns exist 192 * Advanced queries 194 ? The keywords 196

Summary 199

CONTENTS

Simil: an algorithm to lookfor similar strings

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

200

LINQ to SQL and 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 LINQ to SQL and stored procedures 217 Tuning and LINQ to SQL queries 218

215

Summary 219

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

Build your own index

Erland Sommarskog

234

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

CONTENTS

Fragments and persons 237 Thefragments^persons table 237 Writing the search procedure 238 Keeping the index and the statistics updated 241' Wiat 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

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

Testingfor 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 ' considerations 270 ' Using SQL Server authentication

271

Accepting user login credentials--or not Accessing user instances 272 Connection pooling 273 Closing the connection 275 Summary 275

272

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download