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).

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.