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 xxvCHAPTER 1: RDBMS BASICS: WHAT MAKES UP A SQL SERVER DATABASE? 1An Overview of Database Objects 2SQL Server Data Types 13SQL Server Identifiers for Objects 19Summary 21CHAPTER 2: LEARNING THE TOOLS OF THE TRADE 23Getting Help with Books Online 24SQL Server Configuration Manager 25SQL Server Management Studio 31SQL Server Data Tools (formerly BIDS) 43SQL Server Integration Services (SSIS) 44SQL Server Reporting Services (SSRS) 45SQL Server Analysis Services (SSAS) 45Bulk Copy Program (BCP) 46SQL Server Profiler 46sqlcmd 46PowerShell 47Summary 47CHAPTER 3: THE FOUNDATION STATEMENTS OF T-SQL 49Getting Started with a Basic SELECT Statement 50Adding Data with the INSERT Statement 76Changing What You’ve Got with the UPDATE Statement 84The DELETE Statement 87Summary 88CHAPTER 4: JOINS 91Combining Table Data with JOINs 92Selecting Matching Rows with INNER JOIN 93Retrieving More Data with OUTER JOIN 102Seeing Both Sides with FULL JOINs 115Understanding CROSS JOINs 117Exploring Alternative Syntax for Joins 118Stacking Results with UNION 121Summary 125CHAPTER 5: CREATING AND ALTERING TABLES 129Object Names in SQL Server 130The CREATE Statement 134The ALTER Statement 155The DROP Statement 164Using the GUI Tool 165Summary 171CHAPTER 6: KEYS AND CONSTRAINTS 175Types of Constraints 176Constraint Naming 178Key Constraints 179CHECK Constraints 197DEFAULT Constraints 199Disabling Constraints 201Rules and Defaults — Cousins of Constraints 206Triggers for Data Integrity 210Choosing What to Use 210Summary 211CHAPTER 7: ADDING MORE TO YOUR QUERIES 213What Is a Subquery? 214Building Correlated Subqueries 218Derived Tables 225Using Common Table Expressions (CTEs) 228Using the EXISTS Operator 232Mixing Data Types: CAST and CONVERT 236Synchronizing Data with the MERGE Command 239Gathering Affected Rows with the OUTPUT Clause 243Through the Looking Glass: Windowing Functions 246One Chunk at a Time: Ad Hoc Query Paging 251Performance Considerations 252CHAPTER 8: BEING NORMAL: NORMALIZATION AND OTHER BASIC DESIGN ISSUES 257Understanding Tables 258Keeping Your Data “Normal” 258Understanding Relationships 270Diagramming Databases 279Denormalization 291Beyond Normalization 291Drawing Up a Quick Example 293Summary 302CHAPTER 9: SQL SERVER STORAGE AND INDEX STRUCTURES 305SQL Server Storage 306Understanding Indexes 308Creating, Altering, and Dropping Indexes 323Choosing Wisely: Deciding Which Index Goes Where and When 331Maintaining Your Indexes 345Summary 350CHAPTER 10: VIEWS 353Creating Simple Views 354Editing Views with T-SQL 367Dropping Views 367Creating and Editing Views in the Management Studio 367Auditing: Displaying Existing Code 371Protecting Code: Encrypting Views 373About Schema Binding 374Making Your View Look like a Table with VIEW_METADATA 375Indexed (Materialized) Views 375Indexing an Aggregate View 378Summary 381CHAPTER 11: WRITING SCRIPTS AND BATCHES 383Understanding Script Basics 384Grouping Statements into Batches 399Running from the Command Prompt: sqlcmd 405Dynamic SQL: Using the EXEC Command 409Using Control-of-Flow Statements 415Summary 431CHAPTER 12: STORED PROCEDURES 435Creating the Sproc: Basic Syntax 436Changing Stored Procedures with ALTER 438Dropping Sprocs 438Parameterizing Sprocs 438More on Dealing with Errors 448What a Sproc Offers 468Extended Stored Procedures (XPs) 472A Brief Look at Recursion 472Debugging 475Understanding .NET Assemblies 484When to Use Stored Procedures 485Summary 486CHAPTER 13: USER-DEFINED FUNCTIONS 489What a UDF Is 489UDFs Returning a Scalar Value 491UDFs That Return a Table 496Debugging User-Defined Functions 506Using .NET in a Database World 507Summary 507CHAPTER 14: TRANSACTIONS AND LOCKS 509Understanding Transactions 509How the SQL Server Log Works 514Understanding Locks and Concurrency 518Setting the Isolation Level 527Dealing with Deadlocks (aka “a 1205”) 531Summary 534CHAPTER 15: TRIGGERS 537What Is a Trigger? 538ON 540WITH ENCRYPTION 540FOR|AFTER 540The FOR|AFTER versus the INSTEAD OF Clause 541NOT FOR REPLICATION 543AS 543Using Triggers for Data Integrity Rules 543Dealing with Requirements Sourced from Other Tables 544Using Triggers to Check the Delta of an Update 545Using Triggers for Custom Error Messages 547Other Common Uses for Triggers 548Other Trigger Issues 548Triggers Can Be Nested 548Triggers Can Be Recursive 549Triggers Don’t Prevent Architectural Changes 549Triggers Can Be Turned Off without Being Removed 550Trigger Firing Order 550INSTEAD OF Triggers 552Performance Considerations 552Triggers Are Reactive Rather Than Proactive 552Triggers Don’t Have Concurrency Issues with the Process That Fires Them 553Using IF UPDATE() and COLUMNS_UPDATED 553Keep It Short and Sweet 556Don’t Forget Triggers When Choosing Indexes 556Try Not to Roll Back within Triggers 556Dropping Triggers 556Debugging Triggers 557Summary 558CHAPTER 16: A BRIEF XML PRIMER 561XML Basics 562What SQL Server Brings to the Party 577A Brief Word on XSLT 610Summary 613CHAPTER 17: BUSINESS INTELLIGENCE FUNDAMENTALS 615What Is Business Intelligence? 616Those Who Forget History: The Data Warehouse 619Dimensional Modeling: Why Be Normal? 624ETLs 637Making Your Data Actionable: BI Reporting Techniques 642Summary 647CHAPTER 18: BI STORAGE AND REPORTING RESOURCES 649SQL Server Analysis Services, or How I Learned to Stop Worrying and Love the Cube 661Building Your First Cube 663Self-Service BI: User Tools 675Summary 678CHAPTER 19: REPORTING FOR DUTY, SIR! A LOOK AT REPORTING SERVICES 681Reporting Services 101 682Understanding the SSRS Report Lifecycle 683Understanding the Reporting Services Architecture 684Building Simple Report Models 686Report Server Projects 711Summary 717CHAPTER 20: GETTING INTEGRATED WITH INTEGRATION SERVICES 719Understanding the Problem 720Using the Import/Export Wizard to Generate Basic Packages 720Examining Package Basics 727Executing Packages 738A Final Word on Packages 745Summary 745CHAPTER 21: PLAYING ADMINISTRATOR 747Scheduling Jobs 748Logins and Users 760Backup and Recovery 764Index Maintenance 770Policy Based Management 774Automating Administration Tasks with PowerShell 775Summary 779APPENDIX: ANSWERS TO EXERCISES 783INDEX 807ONLINE APPENDICESBONUS APPENDIX 1: SYSTEM FUNCTIONS 1BONUS APPENDIX 2: VERY SIMPLE CONNECTIVITY EXAMPLES 69