MSCE Interview Questions and Answers

QUESTION 31
You work as the database administrator at Certkiller .com. The Certkiller .com
network consists of a single Active Directory domain named Certkiller .com. All
servers on the Certkiller .com network run Windows Server 2000. You install SQL
Server 2000 on a server named Certkiller -DB01 that runs on a Windows Server
2000. Certkiller -DB01 will contain a database named CK_Sales that will be used
to store Internet sales transactions from the company's e-commerce Web site.
Certkiller -DB01 has seven 120 GB hard disk drives. Two of the hard disks are
configured as a mirrored volume and contains the operating system and SQL
Server 2000 is installation. Another two disks are configured as a RAID-1 volume
and the rest are configured as a RAID-5 volume.

You to need to plan the placement of the database and the transaction logs. You
want to implement fault tolerance and best performance for the CK_Sales database.
What should you do?

A. Place the database on the RAID-5 array and the transaction log on the RAID-1 array.
B. Place the database and the transaction log on the RAID-5 array.
C. Place the database on the mirrored volume and the transaction log on the RAID-5
array.
D. Place the database on the RAID-1 array and the transaction log on the RAID-5 array.

Answer: A

Explanation: The RAID-5 array provides striping with parity. Striping improves
read/write operations across multiple disks while parity provides fault tolerance.
Placing the database on the RAID-5 array would thus provide best performance
and fault tolerance. Performance can be improved by placing the transaction logs
on a separate hard disk. The RAID-1 array provides striping which improves
read/write operations across multiple disks.

Incorrect answers:
B: RAID-5 array provides striping with parity. Striping improves read/write
operations across multiple disks while parity provides fault tolerance. Performance
can be improved even further by placing the transaction logs on a separate hard disk.
C: Mirrored volumes write the same data to two physical disks. This provides fault
tolerance but does not provide improved performance. Fault tolerance and improved
performance can be gained by placing the database on the RAID-5 volume.
D: The RAID-1 array provides striping which improves read/write operations across
multiple disks but it does not provide fault tolerance.

QUESTION 32
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The
Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01.

You are currently planning to implement a SQL Server 2000 database to store the
Certkiller .com business information. You estimate that you will require
approximately 8 GB of disk space to the database and approximately 3 GB of disk
space for the transaction log file. The computer that you intend using to host the
database contains two disks: Disk 0 and Disk 1.
1. Disk 0 is configured with two partitions
Each partition has 7 GB of free disk space.
Drive C and D is located on Disk 0.
Drive C hosts the Windows 2000 Server operating system files and the SQL Server
program files.
1. Disk 1 is configured with two partitions.
Each partition has 7 GB of free disk space.
Drive E and F is located on Disk 1.
You need to ensure that you get the best performance and minimal administration
and thus need a strategy to place the data files and the transactions log files
accordingly.
What should you do? (Each correct answer presents part of the solution. Choose
TWO.)

A. Create two transaction log files.
B. Create two data files.
C. Place one transaction log file on drive D and the other on drive E.
Then place the data file on drive F.
D. Place one transaction log file on drive E and the other on drive F.
Then place the data file on drive D.
E. Place one data file on drive D and the other on drive E.
Then place the transaction log on drive F.
F. Place one data file on drive E and the other on drive F.
Then place the transaction log file on drive D.

Answer: B, E

Explanation: For optimal write performance, a database's transaction log is best
placed on a separate physical disk. The transaction log is written to sequentially,
isolating the transaction log from any other data or programs improves
performance by allowing the disk read/write head to remain at the point it last
wrote to. The next record will then be written from that point. There are no large
enough partitions to accommodate the data, thus you will need to create two data files in a single file group as this will also minimize administrative overhead.

Incorrect answers:
A, C, D: There is no sense in creating two transaction log files since there is no
benefit to be had in terms of performance. Only one transaction log can be written
to at a time. Only when the first one is full, will a second log file be written to.
Furthermore, by the time the second transaction log file becomes filled with data, the log will have to be truncated and then the first transaction log file will be written to etc. F: The best write performance in this case can be obtained by placing one data file on drive D and the other on drive E and then placing the transaction log file on drive F.

QUESTION 33
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The
Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01 Certkiller -DB01 hosts a database named CK_Sales that stores
data sales data for the company. The tables in the CK_Sales database are shown in
the following database diagram.



A new Certkiller .com directive essentially decided to switch to commission-based
salaries for the Sales staff. To this end the manager of the Sales department will run queries against the Invoices table to calculate the commission for each sales person. To ensure the success of the queries, you want to enforce the values that the
SalesPersonID column in the Invoices table will accept.
What should you do?

A. A foreign key constraint that references the CK_Sales.SalesPersons table should be
created.
B. A check key constraint that references the CK_Sales.SalesPersons table should be
created.
C. A DML trigger that references the CK_Sales.SalesPersons table should be created.
D. A unique constraint on the SalesPersonID column of the CK_Sales.Invoices table
should be created.

Answer: A

Explanation: A foreign key constraint that references the CK_Sales.SalesPersons
table will ensure that only SalesPersonIDs that exist in the SalesPersons table may
be used in the Invoices table.

Incorrect answers:
B: A check constraint defines the value that can be entered into a column.
C: A DML trigger fires when a UPDATE, INSERT or DELETE statement is run
against a table. This is not what is required.
D: A unique constraint ensures that a value in a column only appears once in that
column. This is not what is required.


QUESTION 34
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01 Certkiller -DB01 hosts a database named CK_Sales that stores
data sales data for the company. The tables in the CK_Sales database are shown in
the following database diagram.



A PRIMARY KEY column has been declared on the ProductID column of the
Products table and the InvoiceID column of the Invoices table. You are creating a
table named Inventory to track stock levels. You need to ensure that combination of
the DateShipped, ProductID and InvoiceID is unique for each row in the Inventory
table.
What should you do?

A. Create a rule.
B. Create a table-level constraint.
C. Create a UNIQUE constraint for each of the three columns.
D. Create a CHECK constraint for each of the three columns.

Answer: B

Explanation: A table-level constraint allows you to ensure that a combination of
columns is unique for each row in the table.

Incorrect answers:
A, D: A rule and CHECK constraints are used to ensure FOREIGN KEY integrity.
The yare not used to ensure that data in a column or combination of columns is unique.
C: UNIQUE constraints do not allow a value to be repeated in the column. This
means that every value in the DateShipped column must be unique; each value in the
ProductID column must be unique; and each value in the InvoiceID column must be
unique.

QUESTION 35
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01 Certkiller -DB01 hosts a database named CK_Sales that stores
data sales data for the company. The tables in the CK_Sales database are shown in
the following database diagram.




Several products listed in the Products table have been discontinued. You want to
delete the data from the Products table that refer to the discontinued products. You
also want to copy data from the Invoices table that are related to the discontinued
products to a separate table named DiscontinuedProductInvoices. You decide to use
triggers to accomplish this task. The first trigger will delete rows from the Products table while the second trigger will to copy data from the Invoices table to the DiscontinuedProductInvoices table. You want the second trigger to execute once the first trigger is executed.
What should you do?

A. The sp_configure 'nested triggers', 0 statement must be executed.
B. The sp_configure 'nested triggers', 1 statement must be executed.
C. The CREATE TRIGGER statement must be executed and create nested triggers.
D. The TRIGGER_NESTLEVEL statement must be executed.

Answer: C, D

Explanation:
You can use the CREATE TRIGGER statement to create nested triggers. A nested
trigger is initiated by another trigger.

Incorrect answers:
A, B: The nested trigger option of the sp_configure command specifies whether
nested triggers are permitted. Setting this option to 0 disables nested triggers while 1 enables nested triggers. By default nested triggers are enabled.
E: The TRIGGER_NESTLEVEL returns the number of triggers executed for the
statement that fired the trigger.

QUESTION 36
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server and all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The
Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Products. The
tables in the CK_Products database are shown in the following database diagram.




When products are shipped, the PrShipped table is updated and when products are
received, the PrReceived table is updated. You want the InStock column in the
Products table to be updated automatically when the PrShipped and PrReceived
tables are updated.
What should you do?

A. Nested triggers should be created on the PrShipped and PrReceived tables.
B. UPDATE triggers should be created on the PrShipped and PrReceived tables.
C. Nested triggers should be created on the Products table.
D. UPDATE triggers should be created on the Products table.

Answer: B

Explanation: You should create UPDATE triggers on the PrShipped and
PrReceived tables. Whenever these tables are updated, the trigger will fire. The
trigger should be configured to update the InStock field in the Products table.

Incorrect answers:
A, C: A nested trigger allows one trigger to initiate another trigger. This is not what is required. What are required are UPDATE triggers on the PrShipped and PrReceived tables that are configured to update the InStock column in the Products table.
D: The UPDATE triggers should be created on the PrShipped and PrReceived tables and
should be configured to update the InStock field in the Products table.

QUESTION 37
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server and all client computers run
Windows 2000 Professional. The Certkiller .com network contains a SQL Server
2000 database server named Certkiller -DB01. Certkiller -DB01 has two RAID-5
arrays used for storing data files. Certkiller -DB01 hosts a database named
CK_Sales that stores data sales data for the company. The CK_Sales database is
also used for online transaction processing (OLTP). The tables in the CK_Sales
database are shown in the following database diagram.




Each of the tables in the CK_Sales database contains in excess of one million rows.
You have created a clustered index on each table. You have also created several
nonclustered indexes on the Invoices and OrderDetail tables as these two tables are
used very frequently. The OrderDetail and Customers tables are used in joins.
You then decide to create two filegroups named Filegroup1 and Filegroup2 so as to
optimize performance for the CK_Sales database. You will place each filegroup on a
separate RAID-5 array. You need to decide where to place the tables and indexes.
What should you do? (Each correct answer presents part of the solution. Choose
THREE.)

A. The nonclustered indexes for the Invoices and OrderDetail tables should be placed on Filegroup1.
B. The Invoices and OrderDetail tables should be placed on FileGroup1.
C. The nonclustered indexes for the Invoices and OrderDetail tables should be placed on Filegroup2.
D. The Customers table should be placed on Filegroup2.
E. The OrderDetail and Customers tables should be placed on Filegroup2.

Answer: B, C, D

Explanation: You can improve database performance by placing the frequently
used tables on one filegroup, and their nonclustered indexes on another. In addition,
you should place the two tables that are used in joins on separate disks. The Invoices and OrderDetail tables are frequently used so they should be place on one filegroup while their nonclustered indexes should be place on another filegroup. The
OrderDetail and Customers tables are used in joins. Therefore these two tablesshould be on different file groups. As the OrderDetail table is on Filegroup1, the
Customers table should be placed on Filegroup2.

Incorrect answers:
A: You can improve database performance by placing the frequently used tables on
one filegroup, and their nonclustered indexes on another filegroup. You only have
the option of placing the Invoices and OrderDetail tables on FileGroup1; therefore the nonclustered indexes for the Invoices and OrderDetail tables should be placed on
Filegroup2.
E: You can improve database performance by placing the two tables that are used
in joins on separate filegroups. The OrderDetail and Customers tables are used in joins; therefore these two tables should be on different file groups.

QUESTION 38
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server and all client computers run
Windows 2000 Professional. The Certkiller .com network contains a SQL Server
2000 database server named Certkiller -DB01. Certkiller -DB01 hosts a database
named CK_Sales that stores data sales data for the company. The tables in the
CK_Sales database are shown in the following database diagram.




The CK_Sales database currently makes use of a single filegroup. A new
Certkiller .com directive essentially decided to switch to commission-based salaries
for the Sales staff. To this end the manager of the Sales department will run queries
against the Invoices table to calculate the commission for each sales person.
You then decide to partition the Invoices table by SalesPersonID and store each
partition in a separate filegroup so as to improve the CK_Sales database
performance. In addition you also want to create a partitioned index for the
Invoices table. There is however some steps that you need to take prior to creating a
partition scheme.
What should you do? (Each correct answer presents part of the solution. Choose
TWO.)

A. The partition function should be created.
B. The index should be created.
C. The filegroups should be created.
D. The table should be created.

Answer: A, C

Explanation: You need to create the partition function and the filegroups that you
want to use in the partition scheme before you can create the partition scheme.

Incorrect answers:
B, D: The partition scheme can be created prior to creating the tables and indexes.


QUESTION 39
You work as the database administrator at Certkiller .com. The Certkiller .com
network consists of a single Active Directory domain named Certkiller .com. All
servers on the Certkiller .com network run Windows 2000 Server and all client
computers run Windows 2000 Professional. The Certkiller .com network contains a
SQL Server 2000 database server named Certkiller -DB01. Certkiller -DB01
hosts a database named CK_Sales that stores sales data for the company.
CK_Sales is configured with the default options. You have received instruction to
load a significant amount of new data into the database. You then begin carrying
out the task. Currently the data file and the transaction file reside on the same disk and you thus realize that the transaction log has grown from 200 MB to 1,000 MB.
However, you still need to load more new data into CK-Sales and there is no more
space on the hard disk.

You need to create more space to complete the task. To this end you need to
perform a full database backup. Prior to performing a full database backup you
need to perform preparatory tasks.
What should you do? (Each correct answer presents part of the solution. Choose
TWO.)

A. The BACKUP LOG CK_Sales WITH NO_LOG statement must be executed.
B. The DBCC SHRINKFILE (LogFile, 300) statement must be executed.
C. The BACKUP DATABASE CK_Sales TO Tape1 WITH NO_TRUNCATE statement must
be executed.
D. You must switch to the Full Recovery model.
E. You must switch to the Bulk-Logged Recovery model

Answer: A, B

Explanation: Because CK_Sales was configured with the default options, it makes
use of the Full Recovery model. This means that each transaction will be recorded in
the full database backup that you performed after the bulk-load. Therefore you do
not need that information in the log any longer. Thus to free space, you should
truncate the transaction log and then shrink it. The BACKUP LOG CK_Sales
WITH NO_LOG statement is used to perform the truncating. The shrinking is
performed by executing the DBCC SHRINKFILE (LogFile 300) statement. The
target size of the transaction log can be 300 MB which will provide 700MB of free space.

Incorrect answers:
C: You cannot specify the NO_TRUNCATE option in a BACKUP DATABASE
statement. Specifying this option will result in the transaction log to be backed up
without being truncated.
D: The CK_Sales database is already configured with the default options. Thus it is
not necessary to switch to the Full Recovery model.
E: Switching to the Bulk-Logged Recovery model will improve performance of
future bulk-load operations and prevent the transaction log from growing
excessively. However, you need to provide more space now. Thus you would still
require to truncate the transaction log and shrink the transaction log file.

QUESTION 40
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The
Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01 Certkiller -DB01 hosts a database named CK_Sales that stores
data sales data for the company. The CK_Sales database contains a Customers
table. The Customers table has been created with a statement that is similar to the
following:
CREATE TABLE Customers
(CustomerID int IDENTITY PRIMARY KEY NONCLUSTERED,
CompanyName nvarchar (50),
Address nvarchar (50),
City nvarchar (20),
PostalCode nvarchar (10),
Phone nvarchar (10))
Sales reports are generated on a weekly basis by the Sales Department members.
These reports identify customers who purchased products and the employees who
made the sales. Part of the identification of customers on these reports usually
includes the CompanyName column. You received a complaint from the users
stating that all queries that involve the Customers table executes too slow. You need
to improve the performance of queries on the Customers table.
What should you do?

A. A clustered index should be created on CompanyName.
B. The index on CustomerID should be changed to clustered.
C. A clustered index should be created on Address.
D. A clustered index should be created on City.
E. A clustered index should be created on PostalCode.
F. A clustered index should be created on Phone.

Answer: B

Explanation: Query performance can be improved if one configured a clustered index.
Clustered indexes order the rows in a table at a physical level according to the values in the column or columns on which the clustered index is created. Only one clustered index can be created on a table. For CompanyName to be included in the Sales Department members' reports, the Customer table is likely to be joined on the
CustomerID column to other tables that have foreighn keys that reference the
CustoemrID column. These tables are usually the Invoices and Order tables.
This will speed up queries is a clustered index was created on the CUstomerIF
column in the Customers table. CustomerID is a primary key column. The primary
key column is created on the clustered index by default in SQL Server 2000. Thus if
you change the CustomerID column index to clustered, it will result in the organization of the rows assigned to each customer, improving query performance.

Incorrect answers:
A, C, D, E, F: You can only have one clustered index on a table. Having the clustered
index on the CustomerID column will improve query performance.