Beginning Microsoft SQL Server 2012 Programming

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