New book: MCTS Self-Paced Training Kit (Exam 70-433): Microsoft SQL Server 2008—Database Development

If you’re planning to get certified as a SQL Server 2008 database developer, the arrival of the new MCTS Self-Paced Training Kit (Exam 70-433): Microsoft SQL Server 2008—Database Development book is welcome news.


The new Training Kit helps you prepare for the 70-433 exam, which earns you the MCTS: Microsoft SQL Server 2008 – Database Development certification. The book covers topics such as storing, retrieving, and modifying data; creating T-SQL stored procedures, user-defined functions, and triggers; working with XML; creating SQLCLR objects; and, building applications using Service Broker.

We’re fortunate to have a very SQL-smart and experienced team of authors on this one: Tobias Thernstrom, Ann Weber, and Mike Hotek. In fact, while writing the book, Tobias was recruited by Microsoft’s SQL Server product group, where he’s now a program manager on the SQL Engine Platform team. Ann and Mike are still holding out. But I try to tell them that resistance is futile.

As a sampling of what you’ll find inside, we offer the Contents at a Glance, a Real World sidebar, and a Case Scenario, along with the answer to the Case Scenario (don’t peek).

Contents at a Glance



Real World

Ann Weber

I was working with a small group within a major university that offered technical classes to faculty, staff, and sometimes students of the university. They purchased a database application to manage their class schedule, including student enrollments, instructors, classrooms, and resources. The database created by the application was fully normalized and was locked so that modifications to the schema could not be made. The training coordinator wanted to write a query that would return the course number, course title, classroom assigned, instructor assigned, and the number of seats still available. To accomplish this, she needed to join 11 tables because of the structure of the database. Because she had the permissions required, she developed a Web-based application that included her SELECT statement joining 11 tables. Because of many factors, including limited resources on the server, additional overhead caused by the Web-based application, and locking contention that her query caused, there were many problems with several databases housed on this server while she ran her query. After this situation, she temporarily lost her privileges to write new queries directly against the production server. Luckily, shortly after this time, they upgraded their database to SQL Server 2005 and the company from which they purchased the database added permissions for them to create indexed views, which helped solve the 11-table join problem.

Case Scenarios

In the following case scenarios, you apply what you have learned in this chapter. You can find answers to these questions in the “Answers” section at the end of this book.

Case Scenario 1: Retrieving Data

You are a database developer for Wide World Importers. You are responsible for a sales database for the company. This database is normalized to third normal form.

An application developer who is fairly new to SQL Server is developing a Web application that retrieves information from the database. Although he feels confident in his ability to use his reference tools to develop the required queries, he has asked for a set of sample queries from which he can extrapolate and build the required queries.

You need to provide a Web developer with a sampling of queries demonstrating the required functionality. The application needs to include queries that return information on products (including their names and descriptions) that were imported based on the date the order was placed or completed. The sales department would like to provide an option in the application to display dates in different formats. In addition, the salespeople would like to be able to sort data based on the number of days between when the order was placed and when it was completed. Finally, users should be able to return data from different time periods, such as the past month, quarter, or year.

Answer the following question for your manager:

What functions and clauses should you include in the sample queries to provide the application developer with samples to help him with his application development?

Case Scenario 1 Answer

  • Include a sample of an ORDER BY clause that references either the order date or completion date.

  • Include several queries and sample results that use the CONVERT function to display the dates in a variety of formats. Include the names of the format styles in the result set to help the application developer make date formatting decisions.

  • Include a query that uses the DATEDIFF function in both the SELECT and the ORDER BY clauses that determines the number of days between the order date and the completed date.

  • Include a query that uses the DATEDIFF function to find orders that were placed 1 month (MONTH), 1 quarter (QUARTER), and 1 year (YEAR) from the current date.

  • Provide information on any JOIN operations that may be required due to the database normalization level.

Comments (19)
  1. mike says:

    when does the book actually hit the shelves?

  2. MSDN Archive says:

    The book is officially available from our distributor as of last Wednesday, Mike. But it can take a week or two before it reaches the shelves of your favorite bookstore or online shopping site.

  3. mike says:

    thanks.  i’m looking at the british version of Amazon and they have it, but the US and Canadian versions do not.  so strange…

  4. Robert Chapin says:

    I’m waiting for a copy now too.  Amazon’s pre-order ship date came and went today.

  5. MSDN Archive says:

    Sorry for the wait on this, Robert. The book is published and available through the distributor. So it’s just a matter of a week or two before Amazon has it in stock. Sometimes contacting the retailer can speed things up. Can’t hurt 🙂

  6. mike says:

    I know I can just wait a few extra days, but how many pages does the book have?  I seen the Amazon site saying around 650, but this website and the amazon UK version says around 450.  Which is it?

    BTw, it’s in stock with US version of Amazon as of yesterday – i ordered it of course!

  7. MSDN Archive says:

    Mike, the final page count is 456. Our goal is to cover all exam objectives and include helpful background material as needed. And keep the book as concise as possible. The higher page count figure was based on my original estimate, which Amazon received when we sent out a tip sheet announcing the book several months before publication. Sometimes I’m off by a little 🙂

  8. Mike Clark says:

    Thank you for the ‘conciseness’! 😉  I just finished the mcts 2.0 in web and the asp (70-528) exam book from MS was a mammoth to digest.  Think it was over a 800 pages – seemingly a little bloated.

  9. We posted on the debut of the new MCTS Self-Paced Training Kit (Exam 70-433): Microsoft SQL Server 2008—Database

  10. Robert Chapin says:

    Is it okay to post book errors here?

    On page xx the instructions for installing AdventureWorks2008 from the CD cause corruption errors in the SQL server.  I posted a workaround at

    On page 3, "The most basic SELECT statement must include … a FROM clause."  I consider the most basic SELECT statement to be SELECT 0;

    On page 20, "The top row, where the ProductSubcategoryID is listed as NULL, is the summary row … of products across all subcategories".  The top row of Figure 1-6 is actually the aggregate of products that have no subcategory.

  11. Thanks, Robert!  Also good is sending mail to  Then our support team researches the errors and posts fixes to the book’s KnowledgeBase article.

  12. Matthew Lamb says:

    I’m confused about the following statement on page 146:

    "The batch delimiter for T-SQL is GO […] Even if the GO is within a comment block, SQL Server still interprets the GO as a batch delimiter".

    A quick experiment seems to prove this is not the case – the following code runs:

    select * from

    /* this is a comment



    (select 1 + 3 as something) as mytable

    Is this an error in the text or am I misinterpreting something here?

    For anyone else who finds this page looking for the KB article for the book (comments and corrections), it’s here:

  13. Matthew Lamb says:

    Also, on page 151, the ‘Break and Continue’ note is misleading. It says that "a WHILE loop terminates as soon as the condition for the WHILE loop is no longer true".  This isn’t true, because the evaluation of the condition for the WHILE loop only happens once per loop.  ‘CONTINUE’ can be used to force a re-evaluation of the condition and the text is wrong to imply that it effectively does nothing, and "you should never need to use a CONTINUE statement".

  14. Hi, Matthew.  I’ve sent both of your comments to so that our support team can address these issues.  Emailing that address (for Training Kits) is the only way for fixes to get into the book’s KB article.  Thanks much for your question and comment!

  15. Matthew Lamb says:

    Thanks, devonm! I’ll send any further comments directly to that address rather than continue cluttering up the blog posting 🙂

  16. Cool. Thanks again, Matthew!

  17. Letron says:

    Is there a central site that addresses the errors in this book?  I also stumbled across the error on page 20 and after googling, I landed here.  

    It would be nice not to go into the exam with misleading information.


  18. Letron says:

    Nevermind.  I just found that a link to the corrections site was posted here after re-reading.  Thanks Matthew for providing that link.

Comments are closed.

Skip to main content