Databases and Data Access

9:00am – 5:00pm
D1 - A Day of SQL Server 2005 and ADO.NET 2.0
Bob Beauchemin

In the upcoming year, Microsoft will enhance its database engine, its common language runtime and its major developer tool simultaneously. The new features in SQL Server 2005 and Visual Studio 2005/.NET 2.0 are far-reaching in their effect on how programmers write data-centric applications. This all day-long tutorial will give you a head start to understanding the changes, but also understanding how they might change the way you program day-to-day. This tutorial is meant to give you a firm grounding; breakout sessions will drill down into the details.

   Back to Top   

Tuesday, Oct 26

9:00am – 10:30am
Keynote

10:45am – 12:15pm
D2 - Locking or Versioning? Snapshot Isolation in SQL Server 2005
Bob Beauchemin

SQL Server 2005 adds support for new implementations of the serializable and read committed isolation levels. This new implementation is based on optimistic locking though versioning rather than the pessimist locking used in SQL Server 2000 and before. A clear understanding of these new isolation implementations will allow you to apply them appropriately. These isolation levels also mimic the isolation implementation in Oracle database, and can be a big help when porting Oracle databases to SQL Server. This session will cover using snapshot isolation, its implications on performance, and the details of it operation.

1:45pm – 3:15pm
D3 - SQL Server 2005 Security Enhancements
Girish Chander

Security has been enhanced in SQL Server 2005. Schemas and synonyms will make it much harder for the bad guys to profile a database to plan an attack. SQL server password can now take part in the password management system provided by Windows Server 2005. The execution context of stored procedures can be controlled more closely. This session will cover these new security features including their impact on ownership chaining.

3:30pm – 5:00pm
D4 - XQuery in SQL Server 2005 and System.Xml
Mark Fussell

XML brings with it some powerful query languages for dealing with the hierarchical format that it typical of an XML document. The newest and richest query language is known as XQuery. This talk provides an overview of the XQuery language and explores XQuery functions and XQuery DML inside SQL Server 2005. It also details the XQuery support in the .NET Framework as part of System.Xml namespace that enables the querying of XML sources stored outside of the database such as locally stored files or XML received via webservices. By combining queries over SQL Server 2005 and other XML sources XQuery can be used as a data integration language.

   Back to Top   

Wednesday, Oct 27

9:00am – 10:30am
D5 - XML data type
Bob Beauchemin

SQL Server 2005 now supports XML as a native data type. This means that XML documents can be efficiently stored and queried in a column of as table, and passed in an out of SQL Server 2005 via user defined functions or stored procedures. Constraints can be added to XML columns just as with columns of other data types, but by using XML Schema collections. This talk will cover the use of the XML data type and using schemas to constrain them. It will also show techniques for using an XML document as an index or primary key, indexing an XML data type column, and applying arbitrary constraints to an XML column using XQuery.

In this session we will discuss adjacency list model, nested sets model, and nested interval model.

1:45pm – 3:15pm
D7 - User-defined types and aggregates in SQL Server 2005
Dan Sullivan

SQL Server 2005 allows you to extend the built-in scalar type system. Using any .NET language you can add a scalar type that will perform on a par with the built in type system. This session will deal with the concept, detail implementation, including controlling the sort order, of User Defined Types.

An aggregate is an operation that converts a set into a scalar. For example the SUM or COUNT of the numbers selected. The built-in aggregates in SQL Server cannot be used with User Define Types, but SQL Server 2005 lets you create your own aggregates to do this. You can also create User Defined Aggregates for built-in types. In either case User Defined Aggregates perform on par with the built-in aggregates in SQL Server 2005. This session will the cover the implementation of User Defined Aggregates for both User Defined Types and built-in SQL Server types.

3:30pm – 5:00pm
D8 - .NET Coding Drilldown in SQL Server 2005
Niels Berglund

SQL Server allows stored procedures, user defined function and triggers to be written in any .NET language. On of the implications of this is that in SQL Server 2005 you can write a stored procedure in COBOL! If you have ever written extended stored procedures or COM objects in SQL Server you may be taken aback by this. The exciting thing about this capability is that stored procedures, user defined functions, and triggers written in a .NET language are just as secure, reliable, and scalable as anything written in T-SQL itself. This session will cover using .NET languages this way and the SQLTypes that have semantics similar to the types in SQL Server itself. Note that examples will be in C# and VB.NET, not COBOL.

There is a new server side data provide in SQL Server 2005 that complements the ability to implement stored procedures, user defined functions, and triggers in any .NET language. It allows these implementations to access SQL Server with about the same level performance as T-SQL itself. This provider uses the SQL Server Programming Model, which is very similar to the programming model provided by ADO.NET. Note that this is a huge improvement over the way that extended stored procedures must access SQL Server by making actual database connections even though they are running inside of SQL Server. This session will cover the classes provided by the Server Side Data Provider and how to use them, including error reporting.

   Back to Top   

Thursday, Oct 28

9:00am – 10:30am
D9 - T-SQL New Features in SQL Server 2005
Dan Sullivan

Common Table Expressions, or CTE's, are part of the SQL Standard and SQL Server 2005 provides them. With CTE's you can write cleaner looking queries when the same subexpression is used in more than one place. CTE's also can be used to create true hierarchical queries, even when the hierarchy is unbalanced or of unknown depth. This session will show using basic CTE's and creating hierarchical queries.

T-SQL has been enhanced in SQL Server 2005. It includes a PIVOT clause that can be used to create open schemas or period based aggregation of temporal data. Row numbers and rank give you better control over order and finding of data. Data definition command can now have triggers associated with them. This session will cover using these enhancements.

In this session we will discuss tricks for CHECK() constraints, overlapping UNIQUE constraints, compound keys, and DRI actions.

1:45pm – 3:15pm
D11 - Understanding and Optimizing Performance with ADO.NET
Rob Steward

Pre-requisites: attendee needs to know before attending - Basic SQL, RDBMS concepts, ADO.NET:

This session covers a broad selection of tips and tricks for how to code applications that are high-performing when using ADO.NET. Many of the tips are database agnostic while some are database specific for various different DBMSs including: SQL Server, Oracle, DB2, Sybase, Informix. Code samples in C# will be included. Specific topics covered include: avoiding network and disk I/O, using connection pooling, appropriate use of local and distributed transactions, prepared statements, reducing the size of result sets using data source specific features, understanding the DBMS datatypes and their impact on performance, SQL tricks, avoiding long data, and batched requests

3:30pm – 5:00pm
D12 - Web Services in SQL Server 2005
Dan Sullivan

SQL Server 2005 can be used to host web services, with no dependencies on IIS or ASP.NET. All metadata for the web service is stored in SQL Server itself. Stored procedures or user defined functions, written in either T-SQL or any .NET language, can be used as the operations of the web service and SQL Sever 2005 generates the WSDL file automatically. This session will cover creating a Web Service in SQL Server 2005 and consuming it with a WinForms client and an Acrobat client.

   Back to Top   

Friday, Oct 29

9:00am – 10:30am
D13 - ADO 2.0 classes for providers
Bob Beauchemin

ADO.NET 2.0 enhances the data provider model to include base classes in addition to the traditional interfaces. Provider writers and consumers can use these classes to enhance the portability of there applications. In addition, ADO.NET 2.0 includes a standard configuration section to store provider information, as well as a standard factory model, data source enumerator model and a graphic connection string builder. This session will look at these enhancements from a perspective of data source independence.

Pre-requisites: attendee needs to know before attending: Basic SQL, basic ADO.NET:

This session covers a broad selection of tips and tricks for how to code applications whose SQL will work across DBMSs when using ADO.NET. Code samples included written in C#. Discussion of specific variances in SQL constructs supported by various different DBMSs including: SQL Server, Oracle, DB2, Sybase, Informix. Specific topics covered by this session include: parameter markers for dynamic SQL statements, representation of date and time literals, syntax for outer joins, syntax for scalar functions, invocation of stored procedures, retrieval of results from stored procedures, empty string and null conversions, long character string insertion, batch (compound) SQL statements, and data provider differences in handling SQL.

1:45pm – 3:15pm
D15 - SQL Server Reporting Services Overview
Jason Carlson

Reporting Services is a web service that serves up reports. Report formats are defined in SQL Server using the XML based Report Definition Language. Reports may be output in the form of simple text, an Excel file, a PDF file or other custom formats. Report delivery can be as straightforward as a client pulling a report on demand or event driven with email delivery. This session will cover using the Report Definition Language and creating clients and web pages that consume the reports.

3:30pm – 5:00pm
D16 - SQL Server Service Broker
Niels Berglund

Service Broker is a framework built into SQL Service 2005 that greatly simplifies the creation of reliable, scalable, message-based, asynchronous, distributed database application. A service broker can manage business transactions, which in practice can last for hours, days or indefinitely and span databases. A Service Broker application consists of a set of services, queues, message formats, and dynamically created conversation. Any application that can make a connection to SQL Server or a web service can make use of a Service Broker application. This session will cover the basics of Service Broker and show the implementation of a Service Broker application.

   Back to Top