Chapter 4: Advanced Query Topics

Overview

Microsoft SQL Server 2005 offers many ways to administer a SQL Server service. Lesson 1 will examine SQL Server Management Objects (SMO), which is a replacement for SQL Distributed Management Objects (SQL-DMO) and enables programmatic access to any object in the SQL Server object model. Replication Management Objects (RMO) is used to programmatically control a replication topology by administrating the objects associated with replication. Analysis Management Objects (AMO) is a library that enables you to manage all the objects associated with your SQL Server Analysis Services (SSAS) objects.

Lesson 2 will move on to a new feature introduced with SQL Server 2005 called Multiple Active Result Sets (MARS). This offers developers an alternative way of executing multiple statements. With MARS, statements can all be processed within a single connection. Finally, Lesson 3 will focus on asynchronous processing, which enables you to perform database operations in parallel.

Exam objectives in this chapter:

• Design client libraries to write applications that administer a SQL Server service.

  • Design server management objects (SMO) applications.

  • Design replication management objects (RMO) applications.

  • Design automation management objects (AMO) applications.

  • Design SQL Server Networking Interface (SNI) for asynchronous queries.

• Design queries that use multiple active result sets (MARS).

  • Decide when MARS queries are appropriate.

  • Choose an appropriate transaction isolation level when you use MARS.

  • Choose when to use Asynchronous queries.