Extend your programming skills with a comprehensive study of the key features of SQL Server 2008. Delve into the new core capabilities, get practical guidance from expert developers, and put their code samples to work. This is a must-read for Microsoft .NET and SQL Server developers who work with data access—at the database, business logic, or presentation levels.
Discover how to:
Query complex data with powerful Transact-SQL enhancements
Use new, non-relational features: hierarchical tables, native file streaming, and geospatial capabilities
Exploit XML inside the database to design XML-aware applications
Consume and deliver your data using Microsoft LINQ, Entity Framework, and data binding
Implement database-level encryption and server auditing
Build and maintain data warehouses
Use Microsoft Excel to build front ends for OLAP cubes, and MDX to query them
Integrate data mining into applications quickly and effectively
Get code samples on the Web.
Core Fundamentals
Chapter 1 Overview
Just How Big Is It?
A Book for Developers
A Book by Developers
A Book to Show You the Way
Summary
Chapter 2 T-SQL Enhancements
Common Table Expressions
The PIVOT and UNPIVOT Operators
TOP Enhancements
Ranking Functions
Exception Handling in Transactions
The varchar(max) Data Type
The WAITFOR Statement
DDL Triggers
SNAPSHOT Isolation
Table-Valued Parameters
New Date and Time Data Types
The MERGE Statement
The INSERT OVER DML Syntax
The GROUPING SETS Operator
New T-SQL Shorthand Syntax
Summary
Chapter 3 Exploring SQL CLR
Getting Started: Enabling CLR Integration
Visual Studio/SQL Server Integration
Your First SQL CLR Stored Procedure
CLR Stored Procedures and Server-Side Data Access
Deployment
CLR Functions
CLR Triggers
CLR Aggregates
SQL CLR Types
Security
Examining and Managing SQL CLR Types in a Database
Best Practices for SQL CLR Usage
Summary
Chapter 4 Server Management
What Is SMO?
Working with SMO in Microsoft Visual Studio
Policy-Based Management
Summary
Chapter 5 Security in SQL Server 2008
Four Themes of the Security Framework
SQL Server 2008 Security Overview
Authentication and Authorization
Encryption Support in SQL Server
Transparent Data Encryption in SQL Server 2008
SQL Server Audit
How Hackers Attack SQL Server
Summary
Beyond Relational
Chapter 6 XML and the Relational Database
XML in SQL Server 2000
XML in SQL Server 2008—the xml Data Type
FOR XML Commands
Querying XML Data Using XQuery
Summary
Chapter 7 Hierarchical Data and the Relational Database
The hierarchyid Data Type
Creating a Hierarchical Table
Populating the Hierarchy
Hierarchical Table Indexing Strategies
Querying Hierarchical Tables
Reordering Nodes Within the Hierarchy
More hierarchyid Methods
Summary
Chapter 8 Using FILESTREAM for Unstructured Data Storage
BLOBs in the Database
BLOBs in the File System
What’s in an Attribute?
Enabling FILESTREAM
The OpenSqlFilestream Native Client API
Summary
Chapter 9 Geospatial Data Types
SQL Server 2008 Spaces Out
Spatial Models
Spatial Data Types
Defining Space with Well-Known Text
Working with geometry
Working with geography
Summary
Reach Technologies
Chapter 10 The Microsoft Data Access Machine
ADO.NET and Typed DataSets
"Pure" ADO.NET: Working in Code
LINQ: A New Syntactic Approach to Data Access
LINQ to SQL and the ADO.NET Entity Framework: ORM Comes to .NET
Web Services for Data: Using ADO.NET Data Services Against EF Models
Data as a Hosted Service: SQL Server Data Services
Summary: So Many Tools, So Little Time
Chapter 11 The Many Facets of .NET Data Binding
Windows Forms Data Binding: The Gold Standard
Data Binding on the Web with ASP.NET
Data Binding for Windows Presentation Foundation
Grand Finale: Silverlight
Summary
Chapter 12 Transactions
What Is a Transaction?
Local Transaction Support in SQL Server 2008
Transaction Terminology
Isolation Levels
Distributed Transactions
Transactions in SQL CLR (CLR Integration)
Putting It All Together
Summary
Chapter 13 Developing Occasionally Connected Systems
Comparing Sync Services with Merge Replication
Components of an Occasionally Connected System
Merge Replication
Sync Services for ADO.NET
Summary
Business Intelligence
Chapter 14 Data Warehousing
Data Warehousing Defined
The Importance of Data Warehousing
What Preceded Data Warehousing
Data Warehouse Design
What Data Warehousing Is Not
Practical Advice About Data Warehousing
SQL Server 2008 and Data Warehousing
Summary
Chapter 15 Basic OLAP
Wherefore BI?
OLAP 101
Building Your First Cube
Running Queries
Summary
Chapter 16 Advanced OLAP
What We’ll Cover in This Chapter
Advanced Dimensions and Measures
Server Time Dimensions
Calculations
Key Performance Indicators
Actions
Partitions, Storage Settings, and Proactive Caching
Aggregations
Perspectives
Translations
Roles
Summary
Chapter 17 OLAP Queries, Tools, and Application Development
Using Excel
Beyond Excel: Custom OLAP Development with .NET
Summary
Chapter 18 Expanding Your Business Intelligence with Data Mining
Why Mine Your Data?
SQL Server 2008 Data Mining Enhancements
Getting Started
Using the Data Mining Wizard and Data Mining Structure Designer
Using Data Mining Extensions
Data Mining Applied
Summary
Chapter 19 Reporting Services
Using the Report Designer
Delivering Reports
Administering Reporting Services
Summary
Appendix About the Authors
Contributing Authors
Appendix Additional Resources for Developers from Microsoft Press
Visual Basic
Visual C#
Web Development
Data Access
SQL Server 2005
Other Developer Topics
Appendix More Great Developer Resources
Developer Step by Step
Developer Reference
Focused Topics
Title:
Programming Microsoft® SQL Server® 2008
By:
Leonard Lobel, Andrew Brust, Stephen Forte
Publisher:
Microsoft Press
Formats:
Print
Ebook
Safari Books Online
Print:
October 2008
Ebook:
November 2009
Pages:
1024
Print ISBN:
978-0-7356-2599-0
| ISBN 10:
0-7356-2599-9
Ebook ISBN:
978-0-7356-3042-0
| ISBN 10:
0-7356-3042-9
Leonard Lobel
Leonard Lobel is a principal consultant at Tallan, a Microsoft Gold Certified Partner.
Stephen Forte
Stephen Forte is the cofounder and CTO of Corzen, Inc. He is also a Microsoft Regional Director for New York and a Microsoft MVP.
