SQL Server 2005 Architecture

• SQL Server 2005 edition overview
• SQL Server Management Studio

Developing with Transact-SQL

Managing data

• Storing data in varchar (max) and XML columns
• Ordering data with ranking functions
• Developing recursive queries with common table expressions (CTE)
• Producing cross tabulations using PIVOT
• Combining data using INTERSECT and EXCEPT
• Controlling access using EXECUTE AS

Working with metadata

• Managing tables and procedures using schemas
• Hiding the location of base objects using synonyms
• Auditing table changes with DDL triggers
• Tracking data changes using the OUTPUT clause

Handling errors and locking

• Intercepting errors with TRY...CATCH
• Avoiding blocking locks using row versioning
• Contrasting snapshot isolation and read committed snapshot isolation

Encrypting data

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

Querying and Storing XML

Storing XML data

• Creating XML SCHEMA COLLECTIONS
• Defining typed XML variables
• Adding XML columns to tables

Exchanging XML data

• Extending queries with FOR XML
• Converting data with OPENXML

Capitalising on XQuery capabilities

• Filtering rows based on XML elements
• Extracting data from an XML column
• Exploiting XML data type methods

Creating Applications with Service Broker

Designing Asynchronous messaging systems

• Achieving scalability with queuing
• Ensuring reliable message delivery

Sending and receiving messages

• Defining contracts to control conversations
• Associating message types with XML schema collections
• Handling message order and unique delivery with conversation group locks
• Starting conversations with BEGIN DIALOG
• Managing reliability using transactions

Integrating .NET with SQL Server

Integrating CLR

• Exploiting the rich .NET programming model
• Streamlining development through a standardised environment
• Improving safety and security using .NET
• Applying best practices

Developing procedural code in .NET

• Writing stored procedures in a .NET language
• Converting a .NET expression into a function
• Invoking a CLR function from a Transact-SQL SELECT statement
• Loading and unloading assemblies
• Improving code level security using the PERMISSION_SET clause
• Controlling assembly deployment using the TRUSTWORTHY database option

Extending SQL Server with .NET

• Developing aggregates for enhanced functionality
• Augmenting SQL Server with .NET user-defined data types
• Adding customer methods to extend user-defined data types

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