Programming Microsoft SQL Server 2008

Table of Contents

Acknowledgments; Introduction; Who This Book Is For; How This Book Is Organized; Code Samples and the Book’s Companion Web Site; System Requirements; Support for This Book; Part I: Core Fundamentals; Chapter 1: Overview; 1.1 Just How Big Is It?; 1.2 A Book for Developers; 1.3 A Book by Developers; 1.4 A Book to Show You the Way; 1.5 Summary; Chapter 2: T-SQL Enhancements; 2.1 Common Table Expressions; 2.2 The PIVOT and UNPIVOT Operators; 2.3 TOP Enhancements; 2.4 Ranking Functions; 2.5 Exception Handling in Transactions; 2.6 The varchar(max) Data Type; 2.7 The WAITFOR Statement; 2.8 DDL Triggers; 2.9 SNAPSHOT Isolation; 2.10 Table-Valued Parameters; 2.11 New Date and Time Data Types; 2.12 The MERGE Statement; 2.13 The INSERT OVER DML Syntax; 2.14 The GROUPING SETS Operator; 2.15 New T-SQL Shorthand Syntax; 2.16 Summary; Chapter 3: Exploring SQL CLR; 3.1 Getting Started: Enabling CLR Integration; 3.2 Visual Studio/SQL Server Integration; 3.3 Your First SQL CLR Stored Procedure; 3.4 CLR Stored Procedures and Server-Side Data Access; 3.5 Deployment; 3.6 CLR Functions; 3.7 CLR Triggers; 3.8 CLR Aggregates; 3.9 SQL CLR Types; 3.10 Security; 3.11 Examining and Managing SQL CLR Types in a Database; 3.12 Best Practices for SQL CLR Usage; 3.13 Summary; Chapter 4: Server Management; 4.1 What Is SMO?; 4.2 Working with SMO in Microsoft Visual Studio; 4.3 Policy-Based Management; 4.4 Summary; Chapter 5: Security in SQL Server 2008; 5.1 Four Themes of the Security Framework; 5.2 SQL Server 2008 Security Overview; 5.3 Authentication and Authorization; 5.4 Encryption Support in SQL Server; 5.5 Transparent Data Encryption in SQL Server 2008; 5.6 SQL Server Audit; 5.7 How Hackers Attack SQL Server; 5.8 Summary; Part II: Beyond Relational; Chapter 6: XML and the Relational Database; 6.1 XML in SQL Server 2000; 6.2 XML in SQL Server 2008—the xml Data Type; 6.3 FOR XML Commands; 6.4 Querying XML Data Using XQuery; 6.5 Summary; Chapter 7: Hierarchical Data and the Relational Database; 7.1 The hierarchyid Data Type; 7.2 Creating a Hierarchical Table; 7.3 Populating the Hierarchy; 7.4 Hierarchical Table Indexing Strategies; 7.5 Querying Hierarchical Tables; 7.6 Reordering Nodes Within the Hierarchy; 7.7 More hierarchyid Methods; 7.8 Summary; Chapter 8: Using FILESTREAM for Unstructured Data Storage; 8.1 BLOBs in the Database; 8.2 BLOBs in the File System; 8.3 What’s in an Attribute?; 8.4 Enabling FILESTREAM; 8.5 The OpenSqlFilestream Native Client API; 8.6 Summary; Chapter 9: Geospatial Data Types; 9.1 SQL Server 2008 Spaces Out; 9.2 Spatial Models; 9.3 Spatial Data Types; 9.4 Defining Space with Well-Known Text; 9.5 Working with geometry; 9.6 Working with geography; 9.7 Summary; Part III: Reach Technologies; Chapter 10: The Microsoft Data Access Machine; 10.1 ADO.NET and Typed DataSets; 10.2 "Pure" ADO.NET: Working in Code; 10.3 LINQ: A New Syntactic Approach to Data Access; 10.4 LINQ to SQL and the ADO.NET Entity Framework: ORM Comes to .NET; 10.5 Web Services for Data: Using ADO.NET Data Services Against EF Models; 10.6 Data as a Hosted Service: SQL Server Data Services; 10.7 Summary: So Many Tools, So Little Time; Chapter 11: The Many Facets of .NET Data Binding; 11.1 Windows Forms Data Binding: The Gold Standard; 11.2 Data Binding on the Web with ASP.NET; 11.3 Data Binding for Windows Presentation Foundation; 11.4 Grand Finale: Silverlight; 11.5 Summary; Chapter 12: Transactions; 12.1 What Is a Transaction?; 12.2 Local Transaction Support in SQL Server 2008; 12.3 Transaction Terminology; 12.4 Isolation Levels; 12.5 Distributed Transactions; 12.6 Transactions in SQL CLR (CLR Integration); 12.7 Putting It All Together; 12.8 Summary; Chapter 13: Developing Occasionally Connected Systems; 13.1 Comparing Sync Services with Merge Replication; 13.2 Components of an Occasionally Connected System; 13.3 Merge Replication; 13.4 Sync Services for ADO.NET; 13.5 Summary; Part IV: Business Intelligence; Chapter 14: Data Warehousing; 14.1 Data Warehousing Defined; 14.2 The Importance of Data Warehousing; 14.3 What Preceded Data Warehousing; 14.4 Data Warehouse Design; 14.5 What Data Warehousing Is Not; 14.6 Practical Advice About Data Warehousing; 14.7 SQL Server 2008 and Data Warehousing; 14.8 Summary; Chapter 15: Basic OLAP; 15.1 Wherefore BI?; 15.2 OLAP 101; 15.3 Building Your First Cube; 15.4 Running Queries; 15.5 Summary; Chapter 16: Advanced OLAP; 16.1 What We’ll Cover in This Chapter; 16.2 Advanced Dimensions and Measures; 16.3 Server Time Dimensionssssss; 16.4 Calculations; 16.5 Key Performance Indicators; 16.6 Actions; 16.7 Partitions, Storage Settings, and Proactive Caching; 16.8 Aggregations; 16.9 Perspectives; 16.10 Translations; 16.11 Roles; 16.12 Summary; Chapter 17: OLAP Queries, Tools, and Application Development; 17.1 Using Excel; 17.2 Beyond Excel: Custom OLAP Development with .NET; 17.3 Summary; Chapter 18: Expanding Your Business Intelligence with Data Mining; 18.1 Why Mine Your Data?; 18.2 SQL Server 2008 Data Mining Enhancements; 18.3 Getting Started; 18.4 Using the Data Mining Wizard and Data Mining Structure Designer; 18.5 Using Data Mining Extensions; 18.6 Data Mining Applied; 18.7 Summary; Chapter 19: Reporting Services; 19.1 Using the Report Designer; 19.2 Delivering Reports; 19.3 Administering Reporting Services; 19.4 Summary; About the Authors; Contributing Authors; Additional Resources for Developers from Microsoft Press; Visual Basic; Visual C#; Web Development; Data Access; SQL Server 2005; Other Developer Topics; More Great Developer Resources; Developer Step by Step; Developer Reference; Focused Topics;