Exam Prep Questions


An existing sales catalog database structure exists on a system in your company. The company sells inventory from a single warehouse location that is across town from where the computer systems are located. The product table has been created with a nonclustered index, based on the product ID, which is also the primary key. Nonclustered indexes exist on the product category column and also the storage location column. Most of the reporting done is ordered by storage location. How would you change the existing index structure?

  • A. Change the definition of the primary key so that it is a clustered index.

  • B. Create a new clustered index, based on the combination of storage location and product category.

  • C. Change the definition of the product category so that it is a clustered index.

  • D. Change the definition of the storage location so that it is a clustered index.


You are designing an application that will provide data entry clerks the capability of updating the data in several tables. You would like to ease entry and provide common input so the clerks do not need to enter data into all fields or enter redundant values. What types of technologies could you use to minimize the amount of input needed? (Select all that apply.)

  • A. Foreign key

  • B. Cascading update

  • C. Identity column

  • D. Default

  • E. NULL

  • F. Primary key

  • G. Unique index


You are the database developer for a leasing company. Your database includes a table that is defined as shown here:

CREATE TABLE Lease (Id Int IDENTITY NOT NULL   CONSTRAINT pk_lease_id PRIMARY KEY NONCLUSTERED, Lastname varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, SSNo char(9) NOT NULL, Rating char(10) NULL, Limit money NULL) 

Each SSNo must be unique. You want the data to be physically stored in SSNo sequence. Which constraint should you add to the SSNo column on the Lease table?

  • A. The UNIQUE CLUSTERED constraint

  • B. The UNIQUE UNCLUSTERED constraint

  • C. The PRIMARY KEY CLUSTERED constraint



You are preparing a new index on a table that has 1,500 rows. 10 rows are added to this table every day. The table already has a primary key, and the new index does not represent the order in which data in the table is to be stored. Updates to the table occur periodically but are infrequent. Which type of index would you create under this situation?

  • A. Use a clustered index with a high FILLFACTOR setting

  • B. Use a clustered index with a low FILLFACTOR setting

  • C. Use a nonclustered index with a high FILLFACTOR setting

  • D. Use a nonclustered index with a low FILLFACTOR setting


You have a database that contains several FOREIGN KEY and CHECK constraints. Users are having problems with data entry on the database because the data they are adding is constantly in violation of the CHECK constraints. Corporate policy regarding database design prevents you from modifying the current constraints, so you decide to implement your changes via a trigger. Which types of triggers would be best suited for this task?

  • A. UPDATE, DELETE, and INSERT TRiggers

  • B. Just UPDATE and INSERT triggers

  • C. INSTEAD OF TRiggers

  • D. Triggers cannot be used in this circumstance.


You have an accounting SQL Server database application that is accessed by 50 users on your company network. When a user inserts or updates a record, you want to make sure that all the required columns have appropriate values. Which of the following would be best for this situation?

  • A. A stored procedure and a trigger

  • B. A batch and a trigger

  • C. An UPDATE TRigger and an INSERT trigger

  • D. One trigger by itself


You have a development environment in which a number of individuals create databases regularly. You would like to log the database creation activity so that the username, time, date, and details of the creation are recorded. How would you implement this?

  • A. Use a DDL trigger.

  • B. Use a DML trigger.

  • C. Use a constraint.

  • D. Use a UDF.

MCTS 70-431(c) Implementing and Maintaining Microsoft SQL Server 2005
MCTS 70-431 Exam Cram: Implementing and Maintaining Microsoft SQL Server 2005 Exam
ISBN: 0789735881
EAN: 2147483647
Year: 2006
Pages: 200
Authors: Thomas Moore

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net