Introduction
Acknowledgements
Part I: Core SQL Server Development
Chapter 1: Introducing SQL Server Data Tools
Introducing SSDT
Database Tooling Designed for Developers
Declarative, Model-Based Development
Connected Development
Disconnected Development
Versioning and Snapshots
Targeting Different Platforms
Working with SSDT
Connecting with SQL Server Object Explorer
Gathering New Requirements
Using the Table Designer (Connected)
Working Offline with a SQL Server Database Project
Taking a Snapshot
Using the Table Designer (Offline Database Project)
Introducing LocalDB
Refactoring the Database
Testing and Debugging
Comparing Schemas
Publishing to SQL Azure
Adopting SSDT
Summary
Chapter 2: T-SQL Enhancements
Table-Valued Parameters
More Than Just Another Temporary Table Solution
Submitting Orders
Using TVPs for Bulk Inserts and Updates
Passing TVPs Using ADO.NET
Passing Collections to TVPs Using Custom Iterators
TVP Limitations
Date and Time Data Types
Separation of Dates and Times
More Portable Dates and Times
Time Zone Awareness
Date and Time Accuracy, Storage, and Format
Date and Time Functions
The MERGE Statement
Defining the Merge Source and Target
The WHEN MATCHED Clause
The WHEN NOT MATCHED BY TARGET Clause
Using MERGE for Table Replication
The WHEN NOT MATCHED BY SOURCE Clause
MERGE Output
Choosing a Join Method
MERGE DML Behavior
The INSERT OVER DML Syntax
A Filterable Alternative to OUTPUTโฆINTO
Consuming CHANGES
The GROUPING SETS Operator
Rolling Up by Level
Rolling Up All Level Combinations
Returning Just the Top Level
Mixing and Matching
Handling NULL Values
Windowing (OVER Clause) Enhancements
Sliding Aggregations
Using RANGE versus ROWS
New T-SQL Functions in SQL Server 2012
New Analytic Functions
New Conversion Functions
New Date and Time Functions
New Logical Functions
New String Functions
Changed Mathematical Function
The THROW Statement
Re-Throwing Exceptions
Comparing THROW and RAISERROR
Server-Side Paging
Using ROW_NUMBER
Using OFFSET/FETCH NEXT
The SEQUENCE Object
Sequence Limitations
Metadata Discovery
Summary
Chapter 3: Exploring SQL CLR
Getting Started: Enabling CLR Integration
Visual Studio/SQL Server Integration
SQL Server Database Projects in Visual Studio
Automated Deployment
SQL CLR Code Attributes
Your First SQL CLR Stored Procedure
CLR Stored Procedures and Server-Side Data Access
Piping Data with SqlDataRecord and SqlMetaData
Deployment
Getting Ready
Deploying Your Assembly
Deploying Your Stored Procedures
Testing Your Stored Procedures
CLR Functions
CLR Triggers
CLR Aggregates
SQL CLR Types
Security
Examining and Managing CLR Types in a Database
Best Practices for SQL CLR Usage
Summary
Chapter 4: Working with Transactions
What Is a Transaction?
Understanding the ACID Properties
Local Transaction Support in SQL Server
Autocommit Transaction Mode
Explicit Transaction Mode
Implicit Transaction Mode
Batch-Scoped Transaction Mode
Isolation Levels
Read Uncommitted Isolation Level
Read Committed Isolation Level
Repeatable Read Isolation Level
Serializable Isolation Level
Snapshot Isolation Level
Read Committed Snapshot Isolation Level
Isolation Levels in ADO.NET
Distributed Transactions
Distributed Transaction Terminology
Rules and Methods of Enlistment
Distributed Transactions in SQL Server
Distributed Transactions in the .NET Framework
Using a Resource Manager in a Successful Transaction
Transactions in SQL CLR (CLR Integration)
Putting It All Together
Summary
Chapter 5: SQL Server Security
Four Themes of the Security Framework
Secure by Design
Secure by Default
Secure by Deployment
Secure Communications
SQL Server Security Overview
SQL Server Logins
Database Users
The guest User Account
Authentication and Authorization
How Clients Establish a Connection
Password Policies
User-Schema Separation
Execution Context
Encryption Support
Encrypting Data on the Move
Encrypting Data at Rest
Transparent Data Encryption
SQL Server Audit
Creating an Audit Object
Auditing Options
Recording Audits to the File System
Recording Audits to the Windows Event Log
Auditing Server Events
Auditing Database Events
Viewing Audited Events
Querying Audit Catalog Views
Partially Contained Databases
Creating a Partially Contained Database
Creating a Contained User
Other Partially Contained Database Features
How Hackers Attack SQL Server
Direct Connection to the Internet
Weak System Administrator Account Passwords
SQL Server Browser Service
SQL Injection
Intelligent Observation
Summary
Part II: Going Beyond Relational
Chapter 6: XML and the Relational Database
Character Data as XML
The xml Data Type
Working with the xml Data Type as a Variable
Working with XML in Tables
XML Schema Definitions (XSDs)
XML Indexes
FOR XML Commands
FOR XML RAW
FOR XML AUTO
FOR XML EXPLICIT
Additional FOR XML Features
The TYPE Option
FOR XML PATH
Emitting a ROOT Element
Producing an Inline XSD Schema
Producing Element-Based XML
Shredding XML Using OPENXML
Querying XML Data Using XQuery
Understanding XQuery Expressions and XPath
SQL Server XQuery in Action
XML DML
Summary
Chapter 7: Hierarchical Data and the Relational Database
The hierarchyid Data Type
Creating a Hierarchical Table
The GetLevel Method
Populating the Hierarchy
The GetRoot Method
The GetDescendant Method
The ToString Method
The GetAncestor Method
Hierarchical Table Indexing Strategies
Depth-First Indexing
Breadth-First Indexing
Querying Hierarchical Tables
The IsDescendantOf Method
Reordering Nodes within the Hierarchy
The GetReparentedValue Method
Transplanting Subtrees
More hierarchyid Methods
Summary
Chapter 8: Native File Streaming
Traditional BLOB Strategies
BLOBs in the Database
BLOBs in the File System
Introducing FILESTREAM
Enabling FILESTREAM
Enabling FILESTREAM for the Machine
Enabling FILESTREAM for the Server Instance
Creating a FILESTREAM-Enabled Database
Creating a Table with FILESTREAM Columns
Storing and Retrieving FILESTREAM Data
Deleting FILESTREAM Data
Direct Streaming in .NET with SqlFileStream
Understanding SqlFileStream
Building the Windows Forms Client
Programming SqlFileStream Data Access
Creating a Streaming HTTP Service
Building a WPF Client
FILESTREAM Limitations and Considerations
Introducing FileTable
Creating a FileTable
Manipulating a FileTable
Searching Documents
Summary
Chapter 9: Geospatial Support
SQL Server Spaces Out
Spatial Models
Planar (Flat-Earth) Model
Geodetic (Ellipsoidal Sphere) Model
Spatial Data Standards
Importing Well-Known Text (WKT)
Importing WKB
Importing Geography Markup Language (GML)
Spatial Data Types
Working with geometry
Working with geography
Spatial Enhancements in SQL Server 2012
New Spatial Data Classes
New Spatial Methods
Other Enhancements
Integrating with Microsoft Bing Maps
Summary
Part III: Applied SQL
Chapter 10: The Microsoft Data Access Juggernaut
.NET Data Access Evolution
Preparing the Sample Database
Monitoring Database Activity with SQL Server Profiler
Conventional ADO.NET
Using the Raw Data Access Objects
Working with DataSets
Language-Integrated Query (LINQ)
LINQ to DataSet
Object Relational Modeling (ORM) Comes to .NET
Multiple ORM Offerings from Redmond
LINQ to SQL: Then and Now
Entity Framework: Now and in the Future
Summary
Chapter 11: WCF Data Access Technologies
Defining Services
WCF Data Access Options
WCF Data Services
Building a WCF Data Service
Creating the Entity Data Model
Testing WCF Data Services with Internet Explorer
Building Client Applications for WCF Data Services
Extending WCF Data Services
WCF RIA Services
Establishing a WCF RIA Services Link
Creating the Entity Data Model
Building the Domain Service and Metadata Classes
Building the Silverlight Client
Inspecting the .NET Framing Protocol with Fiddler
Testing the Complete WCF RIA Services Solution
Making the Right WCF Data Access Choice
Summary
Chapter 12: Moving to the Cloud with
History
But What Is SQL Azure?
Why the Limitations?
Pricing
The First Oneโs Free
Getting Set Up
Beyond the Prerequisites
Provisioning Your Server
Provisioning Your Database
Managing Your Database
Creating Tables and Entering Data
Querying in the Browser
Index Design
Management and Visualizations
Connecting from Down Below
Migrating and Syncing Between Earth and Cloud
DACPACs to the Rescue
Extract, Deploy, Export, and Import DAC files
Scenarios
SQL Azure Federations
A SQL Azure Federations Lexicon
Creating a Federation
Federated Tables
Using a Federation Member
Splitting and Dropping Federation Members
Central Tables and Reference Tables
Fan-Out Queries and Multi-Tenancy
Federations Support in SSMS and SSDT
Federations Make Sense in the Cloud
SQL Azure Reporting
Provisioning
Report Authoring
Deploying Reports
Getting Your Bearings
Summary
Chapter 13: SQL Azure Data Sync and
Characteristics of an Occasionally Connected System
Data Management
Getting to Know SQL Azure Data Sync
Capabilities and Features
Data Sync Terminology
Sync Groups
The Client Sync Agent
SQL Azure Data Sync Considerations
Creating an Occasionally Connected System
Prerequisites
Configuring SQL Azure Data Sync
Provisioning the SQL Azure Data Sync Server
Creating the Sync Group
Hosting WCF Data Services in Windows Azure
About Windows Azure
Creating the FlixPoll Solution
Adding the FlixPoll Data Service
Adding the Entity Data Model
Creating the FlixPoll Client
Consuming OData on Windows Phone
SQL Server on the Phone
Deploying to Windows Azure
Summary
Chapter 14: Pervasive Insight
The Microsoft BI Stack: Whatโs It All About?
Master Data Services
Data Quality Services
Integration Services
SQL Server RDBMS, Fast Track DW, and SQL Server PDW
Data Marts and Data Warehouses
The Star Schema
SQL Server Data Warehouse Appliances
Analysis Services
The Multidimensional Engine
PowerPivot and SSAS Tabular Mode
Data Mining
Power View
Reporting Services
Report Parts
Alerting
Dashboard Components
Excel and Excel Services
Using Excel Services
PerformancePoint Services
StreamInsight
SQL Server Editions and SharePoint Version Requirements
Summary
Chapter 15: xVelocity In-Memory Technologies
Column Store Databases
Column Store Tech in the BI Industry
xVelocity in the RDBMS: Columnstore Indexes
Building a Columnstore Index
What You Canโt Do
How Columnstore Indexes Work
xVelocity for Analysis: PowerPivot and SSAS Tabular Models
Clearing Up the Analysis Services Vocabulary
The Lowdown on BISM
Friends, Countrymen, Bring Me Your Data
Building the BISM
Dial M for Modeling
Modeling, Part Deux
Querying in Excel
PowerPivot for SharePoint
Moving to SSAS Tabular
Power View Here We Come
Welcome Back to VertiPaq
Summary
Index
About the Authors