Introduction

Chapter 1, "Introduction to SQL Server 2000," looked at the modeling considerations and the logical structure of a database design. In moving from an idea to a logical structure to the actual physical elements, you must remember to consider elements that contribute to performance, reliability, and data integrity. Having a model of the system is one thing, but it must be able to meet the demands of an environment where inevitably the system must meet the intended goals of the company and add to the bottom line.

This chapter explores the aspects of the physical database design. It compares and contrasts the physical design and logical data modeling of Chapter 1 and then proceeds towards the implementation of a database system. Included are discussions of the file system, data structure, index structure, SQL Server objects, hardware, and finally, troubleshooting of the database design. Of particular interest to anyone preparing for the exam will be the discussion of schema binding and data partitioning. Because these two topics represent functionality that has been provided over and above the previous version, you can certainly expect questions on these areas when you take the exam.

In any physical design, the goal is to provide an efficient and responsive database system that also lends itself to appropriate maintenance tasks without becoming a database administrator's burden . At this stage of a database implementation, care is taken to provide a system structure that is usable, provides for optimum user response time, can be readily maintained , and above all meets the needs of the business for which it was designed.

As each of the physical design topics are discussed, pay close attention to the options that are available. Many different database technologies can be applied. Selecting the most appropriate technique to apply, based on what the problem warrants , is a very imperfect science. To become proficient in all these technologies, a developer must have a diverse background covering many data solutions. It would be beneficial, therefore, to try to experiment with as many different types of applications as possible. You will find that a manufacturing solution is considerably different from an online sales-oriented solution, which differs as well from a scientific application's solution, and so on.

The exam covers physical elements in numerous areas. The material contained in this chapter hits many of the exam topics. This chapter covers the exam objectives for the physical database design section of the Database Development SQL Server 2000 exam, including the hardware, the operating system, SQL Server, the database, all database objects, and the application. Many of these features are overviewed in this chapter but are further defined in other chapters throughout the book.