Programming Microsoft SQL Server 2012

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