Chapter Summary

This chapter has considered a number of different subjects, all pertaining to the physical aspects of a database system. Although the exam concentrates on the new features in SQL Server 2000, you will find that almost all topics are addressed in some manner. In a real-world scenario, you will find that the best solution often involves a compromise, rather than the ideal. Many considerations, such as budget, time constraints, user knowledge, and technology bias might hamper your ability to achieve an optimum environment.

KEY TERMS

• constraint

• collation Sequence

• identity

• indexes

• stored procedures

• triggers

• user-defined functions (UDFs)

• views

• cascading actions

• CHECK constraints

• clustered index

• defaults

• FILLFACTOR

• Foreign Keys

• non-clustered index

• Primary Key

• UNIQUE constraints

• UNIQUE index

• schema binding

• encryption

• recompile

• partitioned views

• merge replication

• RAID

• snapshot replication

• filegroups

• log

• rules

• roles

• UNIQUE constraint

• transactional replication

When in doubt, select the options that provide for the best performance in the system. Next to performance, a provision to minimize administrative effort would probably come in a close second. Performance is gained through the use of the optimum levels of hardware, especially options pertaining to the disk, controller, and volume configuration. Performance is gained in interactions between the database and file system, so appropriate placement of files, filegroups, and database objects becomes very important. Within the databases, selection of appropriate indexing, constraints, triggers, and other related processing all help as well.

To achieve minimal levels of administration, look to set up and utilize existing features that SQL Server can perform automatically. Be careful: Using too many of the features that are automated might detract from the system performance.