Design and Implementation

Pro

_SQL

Server 2012

Relational

Database

Design

and

Implementation

Louis Davidson

with

_Jessica

M. Moss

Apress-Contents

Foreword xix

Aboutthe Author xxii

About the Technical Reviewer xxiii

Acknowledgments

xxv

Introduction xxvii

Chapter

1: The Fundamentals... 1

Taking

aBrief Jaunt

_{Through History}

2 IntroducingCodd's Rules foran RDBMS 3

NoddingatSQL Standards 8

Recognizing

Relational Data Structures 9 Introducing Databases and Schemas 10

Understanding Tables,Rows,and Columns 10

Workingwith_MissingValues_(NULLs) 15

DefiningDomains 16

Storing Metadata 17

Assigning UniquenessConstraints_(Keys) 18

Understanding Relationships

24

Workingwith _{Binary Relationships} 26

Workingwith _{Nonbinary Relationships} 29

Understanding

Dependencies

30

Workingwith FunctionalDependencies 30

Workingwith Determinants 31

:CONTENTS

Relational

_Programming

31

Outlining

the

_{Database-Specific Project}

Phases 32

Conceptual Phase 33

LogicalPhase 34

Physical 35

Storage Phase 35

Summary

, 35

Chapter 2: Introductionto

_Requirements

37

Documenting Requirements

39

Gathering Requirements

41

Interviewing

Clients 42

Asking

the

_Right

Questions 43

What Data Is Needed? 43

How Will the Data Be Used? 44

What Rules Govern the Use of the Data? 44

What Data Is_Reported On? 45

Where Is the Data Now? 46

Will the Data Need to Be _Integratedwith Other_Systems? 47 How Much Is This Data Worth? 47

Who Will Use the Data? 47

Working

with

_{Existing Systems}

and

_Prototypes

48

Utilizing

Other

_Types

of Documentation 48

EarlyProjectDocumentation 49

ContractsorClientWork Orders 49

Level ofService_Agreement 49

AuditPlans 50

Following

Best Practices 50

Summary

50

CONTENTS

Chapter

3: The

_Language

of Data

_Modeling

53

Introducing

Data

_Modeling

54

Entities 55 Attributes 58 PrimaryKeys 59 Alternate _Keys 61 Foreign Keys 62 Domains 63 Naming 65

Relationships

67 Identifying Relationships 68 Nonidentifying Relationships 69 Role Names 71 Relationship Cardinality 72

Verb Phrases_{(Relationship Names)} 79

Descriptive Information 81

Alternative

_Modeling

Methodologies 82

Information Engineering 83

Chen ERD 85

Visio 86

ManagementStudio Database Diagrams 87

Best Practices ...88

Summary

89

Chapter

4: Initial Data Model Production 91

Example

Scenario 92

Identifying

Entities 93

People 94

Places 95

CONTENTS Ideas 96 Documents 97 Groups 98 Other Entities 99 Entity Recap 100

Relationships

betweenEntities 102

One-to-N Relationships 102

Many-to-ManyRelationships 106

Listing Relationships 107

Identifying

Attributes and Domains 109

Identifiers m

Descriptive Information 113

Locators 113

Values 115

RelationshipAttributes 116

A List of_{Entities, Attributes,}and Domains 117

Identifying

Business Rules 120

Identifying

Fundamental Processes 122

The IntermediateVersion ofthe

_Logical

Model 124

IdentifyingObviousAdditional Data Needs 124

Review with the Client 125

RepeatUntil the Customer_Agreeswith YourModel 126

Best Practices 126

Summary

127

Chapter

5: Normalization , 129

The Process of Normalization 130

Table and Column

_Shape

131

All Columns Must Be Atomic 131

All Rows Must Contain the Same Number of Values 139

jCONTENTS

All Rows Must Be Different 141

CluesThatan _{Existing Design}Is Not in First Normal Form 143

Relationships

BetweenColumns 144

BCNF Defined 144

PartialKey Dependency 146 EntireKey Dependency 147

Surrogate Keys

Effecton

_Dependency

149

DependencyBetween Rows 151

Clues That Your Database Is Not inBCNF 152 Positional Meaning 155 Tables with

_{Multiple Meanings}

, 156

Fourth Normal Form: _IndependentMultivalued _Dependencies 157 Fifth Normal Form 159

Denormalization 162

Best Practices 165

Summary

165

The

_Story

of the Book So Far 167

Chapter

6:

_Physical

Model

_{Implementation}

Case

_Study

169

Choosing

Names 172

Table Naming 173

Naming Columns 175

Model NameAdjustments 176

Choosing Key

Implementation

177

PrimaryKey ....177

AlternateKeys 182

Determining

Domain

_{Implementation}

.• 184

Implementas aColumnorTable? 186

wiCONTENTS

Choosing Nullability 198

Choosinga Collation 199

Setting Up

Schemas 200

Adding Implementation

Columns 201

Using

DDL to Create the Database 202

Creatingthe BasicTable Structures 204

Adding UniquenessConstraints 214

BuildingDefault Constraints 218

Adding Relationships (ForeignKeys) 219

Adding Basic Check Constraints 224 Triggersto Maintain Automatic Values 226

DocumentingYour Database 230

Viewingthe Basic Metadata 233

Unit

_Testing

Your Structures 237

Best Practices 241

Summary

242

Chapter

7: Data Protection with CheckConstraints and

_Triggers

245

Check Constraints 247

CHECK Constraints BasedonSimple Expressions 252

CHECK ConstraintsUsingFunctions 254

EnhancingErrors Caused byConstraints 258

DML

_Triggers

260

AFTERTriggers 261 RelationshipsThatSpanDatabases and Servers 279 INSTEAD OFTriggers 283

Dealing

with

Triggers

andConstraints Errors 292

Best Practices 297

Summary

, , 298

CONTENTS

Chapter

8: Patterns and Anti-Patterns 301

Desirable Patterns 302

Uniqueness 302

Data-DrivenDesign 319

Hierarchies 320

Images,

Documents, and OtherFiles,Oh

_My

332

Generalization 340 Storing User-SpecifiedData 345

Anti-Patterns 359

UndecipherableData 360

One-Size-Fits-All_KeyDomain 361

Generic_KeyReferences 364

OverusingUnstructured Data 367

Summary

368

Chapter9: Database

_Security

and

_Security

Patterns 371 DatabaseAccess

_{Prerequisites}

372

Guidelines for Server_Security 373

Principals and Securables 374

Connectingto the Server 375

Using Loginand User 376

Usingthe Contained Database Model 379

Impersonation 383

Database Securables 386

Grantable Permissions 387

ControllingAccess to_Objects 388

Roles 392

Schemas 400

a,CONTENTS

ControllingAccess to Data viaT-SQL-Coded

Objects

402

StoredProceduresand ScalarFunctions 402

Impersonation

within Objects 404 ViewsandTable-Valued Functions

410

Crossing

Database Lines 417 UsingCross-DatabaseChaining 418 UsingImpersonationtoCross Database Lines 423

UsingaCertilicate-BasedTrust

424 DifferentServer(DistributedQueries) 426

Obfuscating

Data 427

Monitoring

and

Auditing

429

Serverand Database Audit

430

WatchingTable History UsingDMLTriggers 434

DDLTriggers 438

Loggingwith Profiler 441

BestPractices 442

Summary 443

Chapter

10: Table Structures and

Indexing

445

Physical

Database Structure 447

FilesandFilegroups 447

Extents andPages 450

Dataon_Pages 453

Partitioning... 456

Indexes Overview 459

Basic Index Structure 460

Index

_Types

462

Clustered Indexes 462 Nonclustered Indexes 463

CONTENTS

Nonclustered Indexeson Clustered Tables 461

Nonclustered Indexeson a_Heap 462

Basicsof Index Creation 464

BasicIndex _UsagePatterns 466

Using Clustered Indexes 467

UsingNonclustered Indexes 471

Using UniqueIndexes 484

Advanced Index

_Usage

Scenarios 484

IndexingForeign Keys 489

IndexingViews 493

Index

_{Dynamic Management}

View Queries 497

MissingIndexes 497

Index Utilization Statistics 500

Fragmentation 501

BestPractices 501

Summary

,...503

Chapter11:

_Coding

for

_Concurrency

505

What Is

_Concurrency?

506

OS and Hardware Concerns 508

Transactions 509

Transaction _Syntax 510

Compiled SQLServer Code 518

Isolating

Sessions 527

Locks 528

Isolation Levels 533

Coding

for

Integrity

and

_Concurrency

546

PessimisticLocking 546 ImplementingaSingle-ThreadedCode Block 549

Optimistic Locking 552

;CONTENTS

Row-BasedLocking 553

LogicalUnitof Work 558

Best Practices 560

Summary

561

Chapter

12: Reusable Standard Database

Components

563

Numbers Table 564

DeterminingtheContents ofa_String 568 Finding GapsinaSequenceof Numbers 570

SeparatingCommaDelimited Items 571

StupidMathematicTricks 573

CalendarTable 576 Utility

Objects

585 Monitoring Objects 586 Extended DDL Utilities 588

Logging

Objects

590 Other Possibilities 592

Summary

593

Chapter

13:

_Considering

Data Access

_{Strategies...}

595

Ad Hoc SQL 597 Advantages 597 Pitfalls 607 Stored Procedures 613 Encapsulation 614 DynamicProcedures 616 Security 619 Performance ₆₂₁ Pitfalls ₆₂₃

AllThingsConsidered...What Do IChoose? 627

CONTENTS

T-SQLandthe CLR 629

Guidelinesfor_ChoosingT-SQL 633 Guidelinesfor_ChoosingaCLRObject 634

CLRObject Types 634

Best Practices 637

Summary

638

Chapter

14:

_{Reporting Design}

639

Reporting Styles

639

Analytical Reporting 640

Aggregation Reporting 641

Requirements-Gathering

Process 641 Dimensional

_Modeling

for

_{Analytical Reporting}

642

Dimensions 644

Facts 655

Analytical Querying

661

Queries 661

Indexing 663

Summary Modeling

for

_{Aggregation Reporting}

664

Initial_SummaryTable 665

Additional_SummaryTables 667

Aggregation

Querying

667 Queries 668 Indexing 669

Summary

670

Appendix

A 671

Appendix

B 707 Index 735 xvii