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 AzureTM SQL Database * Develop Windows Phone cloud applications using SQL Data Sync * Use SQL Server BI components, including xVelocity in-memory technologies Introduction......Page 23 Acknowledgements......Page 39 Part I: Core SQL Server Development......Page 41 Chapter 1: Introducing SQL Server Data Tools......Page 43 Database Tooling Designed for Developers......Page 44 Declarative, Model-Based Development......Page 45 Connected Development......Page 46 Disconnected Development......Page 47 Versioning and Snapshots......Page 48 Working with SSDT......Page 49 Connecting with SQL Server Object Explorer......Page 50 Gathering New Requirements......Page 56 Using the Table Designer (Connected)......Page 57 Working Offline with a SQL Server Database Project......Page 62 Using the Table Designer (Offline Database Project)......Page 65 Introducing LocalDB......Page 67 Refactoring the Database......Page 71 Testing and Debugging......Page 73 Comparing Schemas......Page 75 Publishing to SQL Azure......Page 79 Adopting SSDT......Page 82 Summary......Page 83 Chapter 2: T-SQL Enhancements......Page 85 More Than Just Another Temporary Table Solution......Page 86 Submitting Orders......Page 87 Using TVPs for Bulk Inserts and Updates......Page 89 Passing TVPs Using ADO.NET......Page 92 Passing Collections to TVPs Using Custom Iterators......Page 94 TVP Limitations......Page 97 More Portable Dates and Times......Page 98 Time Zone Awareness......Page 99 Date and Time Accuracy, Storage, and Format......Page 100 Date and Time Functions......Page 102 The MERGE Statement......Page 105 Defining the Merge Source and Target......Page 107 The WHEN MATCHED Clause......Page 108 The WHEN NOT MATCHED BY TARGET Clause......Page 109 Using MERGE for Table Replication......Page 110 The WHEN NOT MATCHED BY SOURCE Clause......Page 111 MERGE Output......Page 113 Choosing a Join Method......Page 114 MERGE DML Behavior......Page 115 The INSERT OVER DML Syntax......Page 116 A Filterable Alternative to OUTPUT...INTO......Page 117 Consuming CHANGES......Page 120 The GROUPING SETS Operator......Page 123 Rolling Up by Level......Page 125 Rolling Up All Level Combinations......Page 126 Returning Just the Top Level......Page 128 Mixing and Matching......Page 129 Handling NULL Values......Page 130 Windowing (OVER Clause) Enhancements......Page 133 Sliding Aggregations......Page 136 New T-SQL Functions in SQL Server 2012......Page 137 New Analytic Functions......Page 138 New Conversion Functions......Page 143 New Date and Time Functions......Page 144 New Logical Functions......Page 146 New String Functions......Page 147 The THROW Statement......Page 149 Re-Throwing Exceptions......Page 150 Comparing THROW and RAISERROR......Page 151 Using ROW_NUMBER......Page 153 Using OFFSET/FETCH NEXT......Page 154 The SEQUENCE Object......Page 155 Sequence Limitations......Page 157 Metadata Discovery......Page 158 Summary......Page 162 Chapter 3: Exploring SQL CLR......Page 165 Visual Studio/SQL Server Integration......Page 166 SQL Server Database Projects in Visual Studio......Page 167 SQL CLR Code Attributes......Page 169 Your First SQL CLR Stored Procedure......Page 170 CLR Stored Procedures and Server-Side Data Access......Page 172 Piping Data with SqlDataRecord and SqlMetaData......Page 174 Deployment......Page 176 Getting Ready......Page 177 Deploying Your Assembly......Page 178 Deploying Your Stored Procedures......Page 181 Testing Your Stored Procedures......Page 182 CLR Functions......Page 183 CLR Triggers......Page 188 CLR Aggregates......Page 191 SQL CLR Types......Page 196 Security......Page 201 Examining and Managing CLR Types in a Database......Page 202 Summary......Page 208 Chapter 4: Working with Transactions......Page 209 Understanding the ACID Properties......Page 210 Local Transaction Support in SQL Server......Page 212 Explicit Transaction Mode......Page 213 Batch-Scoped Transaction Mode......Page 216 Read Uncommitted Isolation Level......Page 219 Read Committed Isolation Level......Page 221 Snapshot Isolation Level......Page 222 Read Committed Snapshot Isolation Level......Page 223 Isolation Levels in ADO.NET......Page 224 Distributed Transaction Terminology......Page 226 Rules and Methods of Enlistment......Page 227 Distributed Transactions in SQL Server......Page 229 Distributed Transactions in the .NET Framework......Page 230 Using a Resource Manager in a Successful Transaction......Page 238 Transactions in SQL CLR (CLR Integration)......Page 241 Putting It All Together......Page 244 Summary......Page 246 Chapter 5: SQL Server Security......Page 247 Secure Communications......Page 248 SQL Server Security Overview......Page 249 SQL Server Logins......Page 250 Database Users......Page 251 The guest User Account......Page 252 How Clients Establish a Connection......Page 253 Password Policies......Page 255 User-Schema Separation......Page 256 Execution Context......Page 258 Encryption Support......Page 262 Encrypting Data on the Move......Page 263 Encrypting Data at Rest......Page 264 Transparent Data Encryption......Page 269 SQL Server Audit......Page 274 Creating an Audit Object......Page 275 Auditing Options......Page 276 Recording Audits to the File System......Page 278 Auditing Server Events......Page 279 Auditing Database Events......Page 280 Viewing Audited Events......Page 282 Partially Contained Databases......Page 284 Creating a Contained User......Page 285 Other Partially Contained Database Features......Page 286 SQL Server Browser Service......Page 289 Intelligent Observation......Page 290 Summary......Page 291 Part II: Going Beyond Relational......Page 293 Chapter 6: XML and the Relational Database......Page 295 Character Data as XML......Page 296 Working with the xml Data Type as a Variable......Page 297 Working with XML in Tables......Page 298 XML Schema Definitions (XSDs)......Page 299 XML Indexes......Page 306 FOR XML Commands......Page 308 FOR XML AUTO......Page 309 FOR XML EXPLICIT......Page 311 The TYPE Option......Page 316 FOR XML PATH......Page 317 Emitting a ROOT Element......Page 320 Producing an Inline XSD Schema......Page 321 Producing Element-Based XML......Page 322 Shredding XML Using OPENXML......Page 324 Understanding XQuery Expressions and XPath......Page 325 SQL Server XQuery in Action......Page 328 XML DML......Page 336 Summary......Page 338 Chapter 7: Hierarchical Data and the Relational Database......Page 339 The hierarchyid Data Type......Page 340 Creating a Hierarchical Table......Page 341 The GetLevel Method......Page 342 The GetRoot Method......Page 343 The GetDescendant Method......Page 344 The ToString Method......Page 345 The GetAncestor Method......Page 350 Hierarchical Table Indexing Strategies......Page 353 Breadth-First Indexing......Page 354 The IsDescendantOf Method......Page 355 Reordering Nodes within the Hierarchy......Page 357 The GetReparentedValue Method......Page 358 Transplanting Subtrees......Page 359 More hierarchyid Methods......Page 361 Summary......Page 362 Traditional BLOB Strategies......Page 363 BLOBs in the File System......Page 364 Introducing FILESTREAM......Page 365 Enabling FILESTREAM for the Machine......Page 366 Enabling FILESTREAM for the Server Instance......Page 368 Creating a FILESTREAM-Enabled Database......Page 369 Creating a Table with FILESTREAM Columns......Page 370 Storing and Retrieving FILESTREAM Data......Page 371 Deleting FILESTREAM Data......Page 374 Understanding SqlFileStream......Page 375 Building the Windows Forms Client......Page 377 Programming SqlFileStream Data Access......Page 378 Creating a Streaming HTTP Service......Page 388 Building a WPF Client......Page 392 FILESTREAM Limitations and Considerations......Page 395 Introducing FileTable......Page 397 Creating a FileTable......Page 400 Manipulating a FileTable......Page 402 Searching Documents......Page 405 Summary......Page 406 SQL Server Spaces Out......Page 407 Geodetic (Ellipsoidal Sphere) Model......Page 408 Importing Well-Known Text (WKT)......Page 410 Importing WKB......Page 413 Spatial Data Types......Page 414 Working with geometry......Page 415 Working with geography......Page 428 Spatial Enhancements in SQL Server 2012......Page 440 New Spatial Data Classes......Page 441 New Spatial Methods......Page 445 Other Enhancements......Page 451 Integrating with Microsoft Bing Maps......Page 453 Summary......Page 463 Part III: Applied SQL......Page 465 .NET Data Access Evolution......Page 467 Preparing the Sample Database......Page 470 Monitoring Database Activity with SQL Server Profiler......Page 475 Using the Raw Data Access Objects......Page 476 Working with DataSets......Page 495 Language-Integrated Query (LINQ)......Page 512 LINQ to DataSet......Page 513 Object Relational Modeling (ORM) Comes to .NET......Page 517 LINQ to SQL: Then and Now......Page 519 Entity Framework: Now and in the Future......Page 522 Summary......Page 548 Defining Services......Page 549 WCF Data Access Options......Page 550 WCF Data Services......Page 551 Building a WCF Data Service......Page 552 Creating the Entity Data Model......Page 553 Testing WCF Data Services with Internet Explorer......Page 555 Building Client Applications for WCF Data Services......Page 558 Extending WCF Data Services......Page 584 WCF RIA Services......Page 588 Establishing a WCF RIA Services Link......Page 589 Creating the Entity Data Model......Page 591 Building the Domain Service and Metadata Classes......Page 592 Building the Silverlight Client......Page 601 Testing the Complete WCF RIA Services Solution......Page 609 Making the Right WCF Data Access Choice......Page 617 Summary......Page 618 Chapter 12: Moving to the Cloud with......Page 619 But What Is SQL Azure?......Page 621 Why the Limitations?......Page 622 The First One’s Free......Page 623 Getting Set Up......Page 624 Beyond the Prerequisites......Page 625 Provisioning Your Server......Page 626 Managing Your Database......Page 629 Creating Tables and Entering Data......Page 630 Index Design......Page 632 Management and Visualizations......Page 633 Connecting from Down Below......Page 636 Migrating and Syncing Between Earth and Cloud......Page 639 Extract, Deploy, Export, and Import DAC files......Page 640 Scenarios......Page 642 A SQL Azure Federations Lexicon......Page 647 Creating a Federation......Page 648 Federated Tables......Page 649 Central Tables and Reference Tables......Page 650 Federations Support in SSMS and SSDT......Page 651 SQL Azure Reporting......Page 652 Provisioning......Page 653 Report Authoring......Page 654 Deploying Reports......Page 655 Summary......Page 657 Chapter 13: SQL Azure Data Sync and......Page 659 Data Management......Page 660 Capabilities and Features......Page 661 Data Sync Terminology......Page 662 Sync Groups......Page 663 The Client Sync Agent......Page 664 SQL Azure Data Sync Considerations......Page 665 Creating an Occasionally Connected System......Page 666 Prerequisites......Page 669 Provisioning the SQL Azure Data Sync Server......Page 670 Creating the Sync Group......Page 671 About Windows Azure......Page 681 Creating the FlixPoll Solution......Page 682 Adding the FlixPoll Data Service......Page 683 Adding the Entity Data Model......Page 684 Creating the FlixPoll Client......Page 687 Consuming OData on Windows Phone......Page 702 SQL Server on the Phone......Page 706 Deploying to Windows Azure......Page 712 Summary......Page 714 Chapter 14: Pervasive Insight......Page 715 The Microsoft BI Stack: What’s It All About?......Page 716 Master Data Services......Page 717 Data Quality Services......Page 720 Integration Services......Page 721 Data Marts and Data Warehouses......Page 723 SQL Server Data Warehouse Appliances......Page 724 The Multidimensional Engine......Page 726 PowerPivot and SSAS Tabular Mode......Page 727 Data Mining......Page 730 Power View......Page 731 Reporting Services......Page 732 Alerting......Page 733 Using Excel Services......Page 734 PerformancePoint Services......Page 736 SQL Server Editions and SharePoint Version Requirements......Page 737 Summary......Page 739 Chapter 15: xVelocity In-Memory Technologies......Page 741 Column Store Databases......Page 742 Column Store Tech in the BI Industry......Page 743 What You Can’t Do......Page 744 How Columnstore Indexes Work......Page 746 xVelocity for Analysis: PowerPivot and SSAS Tabular Models......Page 749 Clearing Up the Analysis Services Vocabulary......Page 750 Friends, Countrymen, Bring Me Your Data......Page 751 Building the BISM......Page 752 Dial M for Modeling......Page 755 Modeling, Part Deux......Page 758 Querying in Excel......Page 764 PowerPivot for SharePoint......Page 766 Moving to SSAS Tabular......Page 767 Power View Here We Come......Page 772 Welcome Back to VertiPaq......Page 774 Summary......Page 775 Index......Page 777 About the Authors......Page 813

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