Programming Microsoft SQL Server 2012 (Developer Reference)

Your essential guide to key programming features in Microsoft® SQL Server® 2012 Take your database programming skills to a new level—and build customized applications using the developer tools introduced with SQL Server 2012. This hands-on reference shows you how to design, test, and deploy SQL Server databases through tutorials, practical examples, and code samples. If you’re an experienced SQL Server developer, this book is a must-read for learning how to design and build effective SQL Server 2012 applications. Discover how to: Build and deploy databases using the SQL Server Data Tools IDE Query and manipulate complex data with powerful Transact-SQL enhancements Integrate non-relational features, including native file streaming and geospatial data types Consume data with Microsoft ADO.NET, LINQ, and Entity Framework Deliver data using Windows® Communication Foundation (WCF) Data Services and WCF RIA Services Move your database to the cloud with Windows Azure™ SQL Database Develop Windows Phone cloud applications using SQL Data Sync Use SQL Server BI components, including xVelocity in-memory technologies 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

Your essential guide to key programming features in Microsoft® SQL Server® 2012

Take your database programming skills to a new level—and build customized applications using the developer tools introduced with SQL Server 2012. This hands-on reference shows you how to design, test, and deploy SQL Server databases through tutorials, practical examples, and code samples. If you’re an experienced SQL Server developer, this book is a must-read for learning how to design and build effective SQL Server 2012 applications.

Discover how to:

• Build and deploy databases using the SQL Server Data Tools IDE
• Query and manipulate complex data with powerful Transact-SQL enhancements
• Integrate non-relational features, including native file streaming and geospatial data types
• Consume data with Microsoft ADO.NET, LINQ, and Entity Framework
• Deliver data using Windows® Communication Foundation (WCF) Data Services and WCF RIA Services
• Move your database to the cloud with Windows AzureTM SQL Database
• Develop Windows Phone cloud applications using SQL Data Sync
• Use SQL Server BI components, including xVelocity in-memory technologies