SQL Server Architecture

• SQL Server edition overview
• SQL Server Management Studio

Working with XML

Storing XML data

• Creating and managing XML SCHEMA COLLECTIONS
• Defining untyped and typed XML columns
• Converting query results for storage in an XML variable
• Loading XML from the file system into a variable

Leveraging XQuery capabilities

• Filtering rows based on XML elements
• Extracting data from an XML column
• Retrieving XML attributes from database columns

Producing XML data

• Extending queries with FOR XML PATH
• Using PATH, TYPE and subqueries to create nested XML
• Controlling XML elements and attributes with column aliases

Receiving XML data

• Converting XML data to a relational form using OPENXML
• Using XPATH at row and column level to transform complex XML structures

Encrypting data

• Creating a MASTER KEY
• Encrypting and decrypting with a pass phrase
• Contrasting asymmetric and symmetric keys
• Utilizing a certificate to encrypt data

Creating Applications with Service Broker

Designing asynchronous messaging systems

• Improving scalability with queuing
• Taking advantage of reliable message delivery
• Offload processing to multiple back-end servers

Sending and receiving messages

• Defining contracts to control conversations
• Handling message order and unique delivery with conversation group locks

Controlling message delivery

• Associating message types with XML schema collections
• Starting conversations with BEGIN DIALOG
• Ensuring messages are safe using encryption
• Designing an effective error-handling strategy

Integrating .NET with SQL Server

Incorporating CLR

• Contrasting Transact-SQL with the rich .NET programming model
• Improving code safety and security using .NET

Developing procedural code in .NET

• Writing stored procedures in a .NET language
• Loading and unloading assemblies
• Carrying out a refresh on an existing assembly
• Improving code level security using the PERMISSION_SET clause
• Controlling assembly deployment using the TRUSTWORTHY database option

Defining scalar- and table-valued functions

• Converting a .NET expression into a function
• Invoking a CLR function from a Transact-SQL SELECT statement
• Taking advantage of asynchronous data access using .NET table-valued functions

Creating CLR routines with Visual Studio

• Developing stored procedures and functions with templates
• Deploying assemblies directly to SQL Server
• Stepping through CLR code in the debugger

Extending SQL Server with .NET

• Developing aggregates for enhanced functionality
• Augmenting SQL Server with .NET user-defined data types
• Adding custom methods to extend user-defined data types
• Contrasting .NET data types and the XML data type for storing complex data structures

Managing Data from .NET Code

Retrieving and storing data

• Taking advantage of the SqlContext object
• Accessing data in memory collections using LINQ to Object
• Assessing the performance implications of LINQ

Working with entities

• Retrieving entities using LINQ to Entity
• Developing queries in Entity SQL
• Persisting .NET objects in the database with an ADO.NET Entity Data Model
• Creating a WCF Data Services server
• Building a WCF Data Services client