Microsoft Transition Your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE: Data Platform Sample Questions:

1. DRAG DROP
You are designing a database for a university.
The database will contain two tables named Classes and StudentGrades that have the following specifications:
- Classes will store brochures in the XPS format. The brochures must be structured in folders and must be accessible by using UNC paths.
- StudentGrades must be backed up on a separate schedule than the rest of the database.
You need to identify which SQL Server technology meets the specifications of each table.
Which technologies should you identify?
To answer, drag the appropriate technology to the correct table in the answer area.

2. You have a database hosted on SQL Azure.
You are developing a script to create a view that will be used to update the data in a table. The following is the relevant portion of the script. (Line numbers are included for reference only.)

You need to ensure that the view can update the data in the table, except for the data in Columnl.
Which code segment should you add at line 06?

A) WITH ENCRYPTION
B) WITH CHECK OPTION
C) WITH VIEW_METADATA
D) WITH SCHEMABINDING

3. You have a SQL Azure database.
You execute the following script:

You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2.
You plan to deploy an application that will search Column2.
You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements.
Which code segment should you execute?

A) CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NOT NULL
B) CREATE INDEX IX_Table1 ON Table1 (Column1) INCLUDE (Column2)
C) CREATE INDEX IX_Table1 ON Table1 (Column2) WITH FILLFACTOR=0
D) CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NULL

4. You need to recommend a change to sp3 to ensure that the procedure continues to execute even if one of the UPDATE statements fails.
Which change should you recommend?

A) Set the XACT_ABORT option to off.
B) Set the IMPLICIT_TRANSACTIONS option to off.
C) Set the XACT_ABORT option to on.
D) Set the IMPLICIT_TRANSACTIONS option to on.

5. You need to recommend a solution that addresses the concurrency requirement.
What should you recommend?

A) Make calls to Sales.Proc1 and Sales.Proc2 synchronously.
B) Break each stored procedure into two separate procedures, one that changes Sales.Table1 and one that changes Sales.Table2.
C) Call the stored procedures in a Distributed Transaction Coordinator (DTC) transaction.
D) Modify the stored procedures to update tables in the same order for all of the stored procedures.

Solutions: