Beginning Microsoft SQL Server 2012 Programming

Paul Atkinson works for Huron Healthcare as a BI Architect and Team Lead developing both traditional and real-time BI solutions. His training classes in high-performance TSQL programming are among the most popular course offerings available at Huron. Robert Vieira is a Software Architect with Huron Consulting Group and is considered one of the leading authorities on Microsoft SQL Server. He speaks at conferences nationally and is well known for his unique tutorial approach in his teaching and writing. Wrox Beginning guides are crafted to make learning programming languages and technologies easier than you think, providing a structured, tutorial format that guides you through all the techniques involved.

INTRODUCTION xxv

CHAPTER 1: RDBMS BASICS: WHAT MAKES UP A SQL SERVER DATABASE? 1

An Overview of Database Objects 2

SQL Server Data Types 13

SQL Server Identifiers for Objects 19

Summary 21

CHAPTER 2: LEARNING THE TOOLS OF THE TRADE 23

Getting Help with Books Online 24

SQL Server Configuration Manager 25

SQL Server Management Studio 31

SQL Server Data Tools (formerly BIDS) 43

SQL Server Integration Services (SSIS) 44

SQL Server Reporting Services (SSRS) 45

SQL Server Analysis Services (SSAS) 45

Bulk Copy Program (BCP) 46

SQL Server Profiler 46

sqlcmd 46

PowerShell 47

Summary 47

CHAPTER 3: THE FOUNDATION STATEMENTS OF T-SQL 49

Getting Started with a Basic SELECT Statement 50

Adding Data with the INSERT Statement 76

Changing What You’ve Got with the UPDATE Statement 84

The DELETE Statement 87

Summary 88

CHAPTER 4: JOINS 91

Combining Table Data with JOINs 92

Selecting Matching Rows with INNER JOIN 93

Retrieving More Data with OUTER JOIN 102

Seeing Both Sides with FULL JOINs 115

Understanding CROSS JOINs 117

Exploring Alternative Syntax for Joins 118

Stacking Results with UNION 121

Summary 125

CHAPTER 5: CREATING AND ALTERING TABLES 129

Object Names in SQL Server 130

The CREATE Statement 134

The ALTER Statement 155

The DROP Statement 164

Using the GUI Tool 165

Summary 171

CHAPTER 6: KEYS AND CONSTRAINTS 175

Types of Constraints 176

Constraint Naming 178

Key Constraints 179

CHECK Constraints 197

DEFAULT Constraints 199

Disabling Constraints 201

Rules and Defaults — Cousins of Constraints 206

Triggers for Data Integrity 210

Choosing What to Use 210

Summary 211

CHAPTER 7: ADDING MORE TO YOUR QUERIES 213

What Is a Subquery? 214

Building Correlated Subqueries 218

Derived Tables 225

Using Common Table Expressions (CTEs) 228

Using the EXISTS Operator 232

Mixing Data Types: CAST and CONVERT 236

Synchronizing Data with the MERGE Command 239

Gathering Affected Rows with the OUTPUT Clause 243

Through the Looking Glass: Windowing Functions 246

One Chunk at a Time: Ad Hoc Query Paging 251

Performance Considerations 252

CHAPTER 8: BEING NORMAL: NORMALIZATION AND OTHER BASIC DESIGN ISSUES 257

Understanding Tables 258

Keeping Your Data “Normal” 258

Understanding Relationships 270

Diagramming Databases 279

Denormalization 291

Beyond Normalization 291

Drawing Up a Quick Example 293

Summary 302

CHAPTER 9: SQL SERVER STORAGE AND INDEX STRUCTURES 305

SQL Server Storage 306

Understanding Indexes 308

Creating, Altering, and Dropping Indexes 323

Choosing Wisely: Deciding Which Index Goes Where and When 331

Maintaining Your Indexes 345

Summary 350

CHAPTER 10: VIEWS 353

Creating Simple Views 354

Editing Views with T-SQL 367

Dropping Views 367

Creating and Editing Views in the Management Studio 367

Auditing: Displaying Existing Code 371

Protecting Code: Encrypting Views 373

About Schema Binding 374

Making Your View Look like a Table with VIEW_METADATA 375

Indexed (Materialized) Views 375

Indexing an Aggregate View 378

Summary 381

CHAPTER 11: WRITING SCRIPTS AND BATCHES 383

Understanding Script Basics 384

Grouping Statements into Batches 399

Running from the Command Prompt: sqlcmd 405

Dynamic SQL: Using the EXEC Command 409

Using Control-of-Flow Statements 415

Summary 431

CHAPTER 12: STORED PROCEDURES 435

Creating the Sproc: Basic Syntax 436

Changing Stored Procedures with ALTER 438

Dropping Sprocs 438

Parameterizing Sprocs 438

More on Dealing with Errors 448

What a Sproc Offers 468

Extended Stored Procedures (XPs) 472

A Brief Look at Recursion 472

Debugging 475

Understanding .NET Assemblies 484

When to Use Stored Procedures 485

Summary 486

CHAPTER 13: USER-DEFINED FUNCTIONS 489

What a UDF Is 489

UDFs Returning a Scalar Value 491

UDFs That Return a Table 496

Debugging User-Defined Functions 506

Using .NET in a Database World 507

Summary 507

CHAPTER 14: TRANSACTIONS AND LOCKS 509

Understanding Transactions 509

How the SQL Server Log Works 514

Understanding Locks and Concurrency 518

Setting the Isolation Level 527

Dealing with Deadlocks (aka “a 1205”) 531

Summary 534

CHAPTER 15: TRIGGERS 537

What Is a Trigger? 538

ON 540

WITH ENCRYPTION 540

FOR|AFTER 540

The FOR|AFTER versus the INSTEAD OF Clause 541

NOT FOR REPLICATION 543

AS 543

Using Triggers for Data Integrity Rules 543

Dealing with Requirements Sourced from Other Tables 544

Using Triggers to Check the Delta of an Update 545

Using Triggers for Custom Error Messages 547

Other Common Uses for Triggers 548

Other Trigger Issues 548

Triggers Can Be Nested 548

Triggers Can Be Recursive 549

Triggers Don’t Prevent Architectural Changes 549

Triggers Can Be Turned Off without Being Removed 550

Trigger Firing Order 550

INSTEAD OF Triggers 552

Performance Considerations 552

Triggers Are Reactive Rather Than Proactive 552

Triggers Don’t Have Concurrency Issues with the Process That Fires Them 553

Using IF UPDATE() and COLUMNS_UPDATED 553

Keep It Short and Sweet 556

Don’t Forget Triggers When Choosing Indexes 556

Try Not to Roll Back within Triggers 556

Dropping Triggers 556

Debugging Triggers 557

Summary 558

CHAPTER 16: A BRIEF XML PRIMER 561

XML Basics 562

What SQL Server Brings to the Party 577

A Brief Word on XSLT 610

Summary 613

CHAPTER 17: BUSINESS INTELLIGENCE FUNDAMENTALS 615

What Is Business Intelligence? 616

Those Who Forget History: The Data Warehouse 619

Dimensional Modeling: Why Be Normal? 624

ETLs 637

Making Your Data Actionable: BI Reporting Techniques 642

Summary 647

CHAPTER 18: BI STORAGE AND REPORTING RESOURCES 649

SQL Server Analysis Services, or How I Learned to Stop Worrying and Love the Cube 661

Building Your First Cube 663

Self-Service BI: User Tools 675

Summary 678

CHAPTER 19: REPORTING FOR DUTY, SIR! A LOOK AT REPORTING SERVICES 681

Reporting Services 101 682

Understanding the SSRS Report Lifecycle 683

Understanding the Reporting Services Architecture 684

Building Simple Report Models 686

Report Server Projects 711

Summary 717

CHAPTER 20: GETTING INTEGRATED WITH INTEGRATION SERVICES 719

Understanding the Problem 720

Using the Import/Export Wizard to Generate Basic Packages 720

Examining Package Basics 727

Executing Packages 738

A Final Word on Packages 745

Summary 745

CHAPTER 21: PLAYING ADMINISTRATOR 747

Scheduling Jobs 748

Logins and Users 760

Backup and Recovery 764

Index Maintenance 770

Policy Based Management 774

Automating Administration Tasks with PowerShell 775

Summary 779

APPENDIX: ANSWERS TO EXERCISES 783

INDEX 807

ONLINE APPENDICES

BONUS APPENDIX 1: SYSTEM FUNCTIONS 1

BONUS APPENDIX 2: VERY SIMPLE CONNECTIVITY EXAMPLES 69