Programming Microsoft® SQL Server® 2012

Programming Microsoft® SQL Server® 2012

by Leonard Lobel and Andrew Brust
Released July 2012
Publisher(s): Microsoft Press
ISBN: 9780735671706

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

    •

    Table of contents

    1. Programming Microsoft® SQL Server® 2012
    2. Dedication
    3. Introduction
      1. How Significant Is the SQL Server 2012 Release?
      2. Who Should Read This Book
        1. Assumptions
      3. Who Should Not Read This Book
      4. Organization of This Book
        1. Core SQL Server Development
        2. Going Beyond Relational
        3. Applied SQL
      5. Conventions and Features in This Book
      6. System Requirements
      7. Installing SQL Server Data Tools
      8. Using the Book’s Companion Website
        1. Code Samples
        2. Sample AdventureWorks Databases
        3. Previous Edition Chapters
      9. Errata & Book Support
      10. We Want to Hear from You
      11. Stay in Touch
    4. Acknowledgements
    5. I. Core SQL Server Development
      1. 1. Introducing SQL Server Data Tools
        1. Introducing SSDT
          1. Database Tooling Designed for Developers
          2. Declarative, Model-Based Development
          3. Connected Development
          4. Disconnected Development
          5. Versioning and Snapshots
          6. Targeting Different Platforms
        2. Working with SSDT
          1. Connecting with SQL Server Object Explorer
          2. Gathering New Requirements
          3. Using the Table Designer (Connected)
          4. Working Offline with a SQL Server Database Project
          5. Taking a Snapshot
          6. Using the Table Designer (Offline Database Project)
          7. Introducing LocalDB
          8. Refactoring the Database
          9. Testing and Debugging
          10. Comparing Schemas
          11. Publishing to SQL Azure
          12. Adopting SSDT
        3. Summary
      2. 2. T-SQL Enhancements
        1. Table-Valued Parameters
          1. More Than Just Another Temporary Table Solution
          2. Submitting Orders
          3. Using TVPs for Bulk Inserts and Updates
          4. Passing TVPs Using ADO.NET
          5. Passing Collections to TVPs Using Custom Iterators
          6. TVP Limitations
        2. Date and Time Data Types
          1. Separation of Dates and Times
          2. More Portable Dates and Times
          3. Time Zone Awareness
          4. Date and Time Accuracy, Storage, and Format
          5. Date and Time Functions
        3. The MERGE Statement
          1. Defining the Merge Source and Target
          2. The WHEN MATCHED Clause
          3. The WHEN NOT MATCHED BY TARGET Clause
          4. Using MERGE for Table Replication
          5. The WHEN NOT MATCHED BY SOURCE Clause
          6. MERGE Output
          7. Choosing a Join Method
          8. MERGE DML Behavior
        4. The INSERT OVER DML Syntax
          1. A Filterable Alternative to OUTPUT…INTO
          2. Consuming CHANGES
        5. The GROUPING SETS Operator
          1. Rolling Up by Level
          2. Rolling Up All Level Combinations
          3. Returning Just the Top Level
          4. Mixing and Matching
          5. Handling NULL Values
        6. Windowing (OVER Clause) Enhancements
          1. Running Aggregations
          2. Sliding Aggregations
          3. Using RANGE versus ROWS
        7. New T-SQL Functions in SQL Server 2012
          1. New Analytic Functions
          2. New Conversion Functions
          3. New Date and Time Functions
          4. New Logical Functions
          5. New String Functions
          6. Changed Mathematical Function
        8. The THROW Statement
          1. Re-Throwing Exceptions
          2. Comparing THROW and RAISERROR
        9. Server-Side Paging
          1. Using ROW_NUMBER
          2. Using OFFSET/FETCH NEXT
        10. The SEQUENCE Object
          1. Sequence Limitations
        11. Metadata Discovery
        12. Summary
      3. 3. Exploring SQL CLR
        1. Getting Started: Enabling CLR Integration
        2. Visual Studio/SQL Server Integration
          1. SQL Server Database Projects in Visual Studio
          2. Automated Deployment
          3. SQL CLR Code Attributes
        3. Your First SQL CLR Stored Procedure
        4. CLR Stored Procedures and Server-Side Data Access
          1. Piping Data with SqlDataRecord and SqlMetaData
        5. Deployment
          1. Getting Ready
          2. Deploying Your Assembly
          3. Deploying Your Stored Procedures
          4. Testing Your Stored Procedures
        6. CLR Functions
        7. CLR Triggers
        8. CLR Aggregates
        9. SQL CLR Types
        10. Security
        11. Examining and Managing CLR Types in a Database
        12. Best Practices for SQL CLR Usage
        13. Summary
      4. 4. Working with Transactions
        1. What Is a Transaction?
          1. Understanding the ACID Properties
        2. Local Transaction Support in SQL Server
          1. Autocommit Transaction Mode
          2. Explicit Transaction Mode
            1. Nested Transactions
            2. Savepoints
          3. Implicit Transaction Mode
          4. Batch-Scoped Transaction Mode
            1. MARS and Transactions
            2. MARS and Savepoints
        3. Isolation Levels
          1. Read Uncommitted Isolation Level
          2. Read Committed Isolation Level
          3. Repeatable Read Isolation Level
          4. Serializable Isolation Level
          5. Snapshot Isolation Level
          6. Read Committed Snapshot Isolation Level
          7. Isolation Levels in ADO.NET
            1. Setting the Isolation Level for Explicit ADO.NET Transactions
            2. Setting the Isolation Level for Implicit ADO.NET Transactions
        4. Distributed Transactions
          1. Distributed Transaction Terminology
            1. Resource Manager
            2. Transaction Manager or Transaction Coordinator
            3. Two-Phase Commit
          2. Rules and Methods of Enlistment
            1. Volatile Enlistment
            2. Durable Enlistment
            3. Promotable Single-Phase Enlistment
          3. Distributed Transactions in SQL Server
          4. Distributed Transactions in the .NET Framework
            1. Writing Your Own Resource Manager
          5. Using a Resource Manager in a Successful Transaction
            1. Using the Resource Manager When the Caller Issues a Rollback
            2. Using the Resource Manager When It Issues a Rollback
            3. Using the Resource Manager with Another Resource Manager
        5. Transactions in SQL CLR (CLR Integration)
        6. Putting It All Together
        7. Summary
      5. 5. SQL Server Security
        1. Four Themes of the Security Framework
          1. Secure by Design
          2. Secure by Default
          3. Secure by Deployment
          4. Secure Communications
        2. SQL Server Security Overview
          1. SQL Server Logins
          2. Database Users
          3. The guest User Account
        3. Authentication and Authorization
          1. How Clients Establish a Connection
          2. Password Policies
          3. User-Schema Separation
          4. Execution Context
        4. Encryption Support
          1. Encrypting Data on the Move
          2. Encrypting Data at Rest
          3. Transparent Data Encryption
            1. Creating Keys and Certificates for TDE
            2. Enabling TDE
            3. Backing Up the Certificate
            4. Restoring an Encrypted Database
        5. SQL Server Audit
          1. Creating an Audit Object
          2. Auditing Options
            1. QUEUE_DELAY
            2. ON_FAILURE
            3. AUDIT_GUID
            4. STATE
          3. Recording Audits to the File System
            1. FILEPATH
            2. MAXSIZE
            3. MAX_ROLLOVER_FILES
            4. MAX_FILES
            5. RESERVE_DISK_SPACE
          4. Recording Audits to the Windows Event Log
          5. Auditing Server Events
          6. Auditing Database Events
          7. Viewing Audited Events
          8. Querying Audit Catalog Views
        6. Partially Contained Databases
          1. Creating a Partially Contained Database
          2. Creating a Contained User
          3. Other Partially Contained Database Features
            1. Uncontained Entities View
            2. Collations and tempdb
        7. How Hackers Attack SQL Server
          1. Direct Connection to the Internet
          2. Weak System Administrator Account Passwords
          3. SQL Server Browser Service
          4. SQL Injection
          5. Intelligent Observation
        8. Summary
    6. II. Going Beyond Relational
      1. 6. XML and the Relational Database
        1. Character Data as XML
        2. The xml Data Type
          1. Working with the xml Data Type as a Variable
          2. Working with XML in Tables
          3. XML Schema Definitions (XSDs)
            1. SQL Server Schema Collections
            2. Lax Validation
            3. Union and List Types
          4. XML Indexes
        3. FOR XML Commands
          1. FOR XML RAW
          2. FOR XML AUTO
          3. FOR XML EXPLICIT
        4. Additional FOR XML Features
          1. The TYPE Option
          2. FOR XML PATH
          3. Emitting a ROOT Element
          4. Producing an Inline XSD Schema
          5. Producing Element-Based XML
        5. Shredding XML Using OPENXML
        6. Querying XML Data Using XQuery
          1. Understanding XQuery Expressions and XPath
            1. XPath Expressions
            2. FLWOR Expressions
          2. SQL Server XQuery in Action
            1. xml.exist
            2. xml.value
            3. xml.query
          3. XML DML
            1. xml.modify(insert)
            2. xml.modify(delete)
            3. xml.modify(replace)
        7. Summary
      2. 7. Hierarchical Data and the Relational Database
        1. The hierarchyid Data Type
        2. Creating a Hierarchical Table
          1. The GetLevel Method
        3. Populating the Hierarchy
          1. The GetRoot Method
          2. The GetDescendant Method
          3. The ToString Method
          4. The GetAncestor Method
        4. Hierarchical Table Indexing Strategies
          1. Depth-First Indexing
          2. Breadth-First Indexing
        5. Querying Hierarchical Tables
          1. The IsDescendantOf Method
        6. Reordering Nodes within the Hierarchy
          1. The GetReparentedValue Method
          2. Transplanting Subtrees
        7. More hierarchyid Methods
        8. Summary
      3. 8. Native File Streaming
        1. Traditional BLOB Strategies
          1. BLOBs in the Database
          2. BLOBs in the File System
        2. Introducing FILESTREAM
        3. Enabling FILESTREAM
          1. Enabling FILESTREAM for the Machine
          2. Enabling FILESTREAM for the Server Instance
        4. Creating a FILESTREAM-Enabled Database
          1. Creating a Table with FILESTREAM Columns
        5. Storing and Retrieving FILESTREAM Data
        6. Deleting FILESTREAM Data
        7. Direct Streaming in .NET with SqlFileStream
          1. Understanding SqlFileStream
          2. Building the Windows Forms Client
          3. Programming SqlFileStream Data Access
          4. Creating a Streaming HTTP Service
          5. Building a WPF Client
        8. FILESTREAM Limitations and Considerations
        9. Introducing FileTable
          1. Creating a FileTable
          2. Manipulating a FileTable
        10. Searching Documents
        11. Summary
      4. 9. Geospatial Support
        1. SQL Server Spaces Out
        2. Spatial Models
          1. Planar (Flat-Earth) Model
          2. Geodetic (Ellipsoidal Sphere) Model
        3. Spatial Data Standards
          1. Importing Well-Known Text (WKT)
            1. The STGeomFromText and STxxxFromText Methods
          2. Importing WKB
            1. The STGeomFromWKB and STxxxFromWKB Methods
          3. Importing Geography Markup Language (GML)
            1. The GeomFromGml Method
        4. Spatial Data Types
          1. Working with geometry
            1. The STBuffer Method
            2. The STCentroid and STEnvelope Methods
            3. The STIntersects and STIntersection Methods
            4. The STDimension Method
            5. The STUnion, STDifference, and STSymDifference Methods
          2. Working with geography
            1. On Your Mark …
            2. The STArea and STLength Methods
            3. Spatial Reference IDs
            4. Building Out the EventLibrary Database
            5. Creating the Event Media Client Application
            6. The STDistance Method
        5. Spatial Enhancements in SQL Server 2012
          1. New Spatial Data Classes
            1. Circular Strings
            2. Compound Curves
            3. Curve Polygons
          2. New Spatial Methods
            1. The STNumCurves and STCurveN Methods
            2. The BufferWithCurves Method
            3. The ShortestLineTo Method
            4. The MinDbCompatibilityLevel Method
            5. The STCurveToLine and CurveToLineWithTolerance Methods
            6. The STIsValid, IsValidDetailed, and MakeValid Methods
          3. Other Enhancements
            1. Support for geography Instances Exceeding a Logical Hemisphere
            2. Full Globe Support
            3. New “Unit Sphere” Spatial Reference ID
            4. Better Precision
        6. Integrating with Microsoft Bing Maps
        7. Summary
    7. III. Applied SQL
      1. 10. The Microsoft Data Access Juggernaut
        1. .NET Data Access Evolution
        2. Preparing the Sample Database
        3. Monitoring Database Activity with SQL Server Profiler
        4. Conventional ADO.NET
          1. Using the Raw Data Access Objects
            1. Creating Connections and Commands
            2. Using Parameters
            3. Calling Stored Procedures
            4. Iterating Data Readers
            5. Returning Scalar Values
            6. Batching Updates with Transactions
          2. Working with DataSets
            1. Generic Versus Strongly Typed DataSets
            2. Filling and Updating a Generic DataSet
            3. Building Strongly Typed DataSets
            4. Mapping Stored Procedures to the Typed DataSet
        5. Language-Integrated Query (LINQ)
          1. LINQ to DataSet
            1. Querying a Generic DataSet
            2. Querying a Strongly Typed DataSet
        6. Object Relational Modeling (ORM) Comes to .NET
          1. Multiple ORM Offerings from Redmond
          2. LINQ to SQL: Then and Now
          3. Entity Framework: Now and in the Future
            1. Building an Entity Data Model (EDM)
            2. Using LINQ to Entities
            3. Mapping Stored Procedures to the EDM
            4. Saving Entity Changes
            5. Object Context Lazy Loading and Change Tracking
            6. The EF n-Tier Challenge
            7. Resolving Impedance Mismatch
            8. Working with Many-to-Many Relationships
            9. Exploring the Entity SQL Alternative
            10. Working with EntityClient
        7. Summary
      2. 11. WCF Data Access Technologies
        1. Defining Services
        2. WCF Data Access Options
        3. WCF Data Services
          1. Building a WCF Data Service
          2. Creating the Entity Data Model
          3. Testing WCF Data Services with Internet Explorer
          4. Building Client Applications for WCF Data Services
            1. Building a Test Client
            2. Building a Data Entry Client
          5. Extending WCF Data Services
            1. Creating Custom Service Operations
            2. Overriding Service Methods
            3. Writing Interceptors
        4. WCF RIA Services
          1. Establishing a WCF RIA Services Link
          2. Creating the Entity Data Model
          3. Building the Domain Service and Metadata Classes
            1. The Metadata Classes
            2. The Domain Service Class
          4. Building the Silverlight Client
          5. Inspecting the .NET Framing Protocol with Fiddler
          6. Testing the Complete WCF RIA Services Solution
        5. Making the Right WCF Data Access Choice
        6. Summary
      3. 12. Moving to the Cloud with SQL Azure
        1. History
        2. But What Is SQL Azure?
          1. Why the Limitations?
          2. Pricing
          3. The First One’s Free
        3. Getting Set Up
          1. Beyond the Prerequisites
          2. Provisioning Your Server
            1. Configuring the Administrative User and Firewall
          3. Provisioning Your Database
        4. Managing Your Database
          1. Creating Tables and Entering Data
          2. Querying in the Browser
          3. Index Design
          4. Management and Visualizations
          5. Connecting from Down Below
        5. Migrating and Syncing Between Earth and Cloud
          1. DACPACs to the Rescue
          2. Extract, Deploy, Export, and Import DAC files
            1. SQL Server Management Studio
            2. SQL Server Data Tools
            3. Windows Azure Management Portal
            4. SQL Azure Management Portal
          3. Scenarios
            1. New Deployment to SQL Azure
              1. From SSDT
              2. From SSMS
              3. From the SQL Azure Management Portal
            2. Deploy Update to SQL Azure
            3. Migration from SQL Server to SQL Azure
              1. SSDT
              2. SSMS
            4. Migration from SQL Azure to SQL Server
        6. SQL Azure Federations
          1. A SQL Azure Federations Lexicon
          2. Creating a Federation
          3. Federated Tables
          4. Using a Federation Member
          5. Splitting and Dropping Federation Members
          6. Central Tables and Reference Tables
          7. Fan-Out Queries and Multi-Tenancy
          8. Federations Support in SSMS and SSDT
          9. Federations Make Sense in the Cloud
        7. SQL Azure Reporting
          1. Provisioning
          2. Report Authoring
          3. Deploying Reports
          4. Getting Your Bearings
        8. Summary
      4. 13. SQL Azure Data Sync and Windows Phone 7 Development
        1. Characteristics of an Occasionally Connected System
          1. Data Management
        2. Getting to Know SQL Azure Data Sync
          1. Capabilities and Features
          2. Data Sync Terminology
          3. Sync Groups
            1. Synchronization
            2. Conflict Resolution
          4. The Client Sync Agent
          5. SQL Azure Data Sync Considerations
            1. Data Sync Security
            2. Performance and Costs
        3. Creating an Occasionally Connected System
          1. The FlixPoll Application
            1. The FlixPoll Application
            2. Creating the FlixPoll Databases
        4. Prerequisites
        5. Configuring SQL Azure Data Sync
          1. Provisioning the SQL Azure Data Sync Server
          2. Creating the Sync Group
            1. Step 1: Name the Sync Group
            2. Step 2: Add the On-Premise Database
            3. Step 3: Select the SQL Azure Hub Database
            4. Step 4: Configuring Sync Schedule and Conflict Resolution
            5. Step 5: Define the Dataset
            6. Step 6: Deploying the Sync Group
        6. Hosting WCF Data Services in Windows Azure
          1. About Windows Azure
          2. Creating the FlixPoll Solution
          3. Adding the FlixPoll Data Service
          4. Adding the Entity Data Model
          5. Creating the FlixPoll Client
            1. Creating the View
            2. Understanding the Model-View-ViewModel (MVVM) Pattern
            3. Modifying the App Class
        7. Consuming OData on Windows Phone
          1. SQL Server on the Phone
            1. LINQ to SQL Strikes Back
            2. Securing Local Data
            3. Create the SQL CE Database
            4. Issuing LINQ to SQL Queries Against SQL CE
            5. Running Against a Local WCF Data Service
          2. Deploying to Windows Azure
        8. Summary
      5. 14. Pervasive Insight
        1. The Microsoft BI Stack: What’s It All About?
        2. Master Data Services
        3. Data Quality Services
        4. Integration Services
        5. SQL Server RDBMS, Fast Track DW, and SQL Server PDW
          1. Data Marts and Data Warehouses
          2. The Star Schema
          3. SQL Server Data Warehouse Appliances
        6. Analysis Services
          1. The Multidimensional Engine
          2. PowerPivot and SSAS Tabular Mode
          3. Data Mining
        7. Power View
        8. Reporting Services
          1. Report Parts
          2. Alerting
          3. Dashboard Components
        9. Excel and Excel Services
          1. Using Excel Services
        10. PerformancePoint Services
        11. StreamInsight
        12. SQL Server Editions and SharePoint Version Requirements
        13. Summary
      6. 15. xVelocity In-Memory Technologies
        1. Column Store Databases
          1. Column Store Tech in the BI Industry
        2. xVelocity in the RDBMS: Columnstore Indexes
          1. Building a Columnstore Index
          2. What You Can’t Do
          3. How Columnstore Indexes Work
        3. xVelocity for Analysis: PowerPivot and SSAS Tabular Models
          1. Clearing Up the Analysis Services Vocabulary
          2. The Lowdown on BISM
          3. Friends, Countrymen, Bring Me Your Data
          4. Building the BISM
          5. Dial M for Modeling
            1. Calculated Columns
            2. Analysis in Excel, Using Data from Excel
            3. It’s All about Relationships
          6. Modeling, Part Deux
            1. Creating Measures
            2. Building KPIs
            3. Hierarchies Come to BISM
            4. Finishing Touches
            5. Exploring Advanced Mode
          7. Querying in Excel
          8. PowerPivot for SharePoint
          9. Moving to SSAS Tabular
            1. Role-Based Security
            2. Partitions
            3. Moving to DirectQuery
            4. SSAS Deployment
            5. Making the Connection
          10. Power View Here We Come
            1. Building the Report
        4. Welcome Back to VertiPaq
        5. Summary
    8. A. About the Authors
    9. Index
    10. About the Authors
    11. Copyright

    Product information

    • Title: Programming Microsoft® SQL Server® 2012
    • Author(s): Leonard Lobel and Andrew Brust
    • Release date: July 2012
    • Publisher(s): Microsoft Press
    • ISBN: 9780735671706