Physical Database Design and Implementation
Chapter 3: Physical Database Design and Implementation
The second unit of the SQL Server 2000 Design exam is concerned with implementing the physical database design. This chapter covers all of the exam objectives respecting designing and implementing the physical database, specifically including creating and altering databases, creating database objects, and troubleshooting failed object creation.
The specific objectives from the SQL Server 2000 Design exam that will be covered in this chapter are as follows:
Create and alter databases. Considerations include file groups, file placement, growth strategy, and space requirements.
Specify space management parameters. Parameters include autoshrink, growth increment, initial size, and maxsize.
Specify file group and file placement. Considerations include logical and physical file placement.
Specify transaction log placement. Considerations include bulk load operations and performance.
Create and alter database objects. Objects include constraints, indexes, stored procedures, tables, triggers, user-defined functions, and views.
Specify table characteristics. Characteristics include cascading actions, CHECK constraints, clustered, defaults, FILLFACTOR, foreign keys, nonclustered, primary key, and UNIQUE constraints.
Specify schema binding and encryption for stored procedures, triggers, user-defined functions, and views.
Specify recompile settings for stored procedures.
Specify index characteristics. Characteristics include clustered, FILLFACTOR, nonclustered, and uniqueness.
Alter database objects to support replication and partitioned views.
Support merge, snapshot, and transactional replication models.
Design a partitioning strategy.
Design and create constraints and views.
Resolve replication conflicts.
Troubleshoot failed object creation.