Cheet Sheets: Administering Microsoft SQL 2000 (70-228)

WordPad/Word95 Edition
Copyright Keen Interactive 2001
Last Updated: 8/31/01

**The following questions have appeared on actual exams. Situational details (ie, names, locations, and
quantities) have been changed to avoid copyright infringement. Memorize as many of these questions as
possible in the 5 nights prior to your exam. Some questions will refer to exhibits that are not available. Be
sure to memorize every detail of these questions…we have given you enough information that they are
easily recognizable on the actual exam. If you cannot memorize all these questions, memorize them from
end to beginning-the higher numbered questions are the most recent. Good luck. -Keen Interactive

1. You are the administrator of a SQL Server 2000 computer. Your company uses the server to store
service contract information for its customers. You are also the administrator of an Oracle
relational database management system (ROEMS) server. This server is used to store your
company's financial information. The financial information is updated frequently throughout the
day. You need to create a series of reports that combine the service contract information and the
financial information. These reports will be updated several times a day. You want to create
reports on the SQL Server computer by using the minimum amount of disk space. What should
you do?

A. Set up SQL server replication to replicate the data from the Oracle server to the SQL server
computer.
B. Set up the Oracle server as a linked server.
Create a view that joins the service contract information and the financial information.
C. Set up a data transformation services (DTS) package that imports and transforms the database
from the Oracle server to the SQL server computer. Use SQL Server Agent to execute the DTS
package throughout the day as needed.
D. Set up an Microsoft ActiveX script that connects to the Oracle server and imports the financial
information into an SQL Server temporary table.
Create a view that joins the service contract information and the temporary table.

2. You are the administrator of two SQL Server computers. One server is named SQL7, and other is
named SQL2000. SQL7 is running SQL server 7.0 and SQL2000 is running SQL server 2000.
The net-libraries on SQL2000 are configured as shown in the exhibit (exhibit not available).
SQL7 is configured so that it has the Named Pipes, TCP/IP, NWLink, IPX/SPX and
Multiprotocol net libraries. SQL2000 and SQL7 exchange confidential company information. You
need to ensure that unauthorized users cannot access this information. Which two actions should
you take? (Each correct answer presents part of the solution. Choose two)

A. On SQL2000, enable the Multiprotocol net library.

B. On SQL2000, select the ‘Force protocol encryption’ check box.
C. On SQL7, select the ‘Force protocol encryption’ check box.
D. On SQL2000, install a secure sockets layer (SSL) encryption certificate.
E. On SQL2000 and SQL7, enable multipoint encryption.

3. You are the administrator of Microsoft Windows 2000 computer. You are preparing to install
SQL Server 2000 on the computer. Your company contains a variety of client computers that will
connect to the SQL server 2000 computer by using a specific net-library, as shown in the
following table.

Client computer Net-Library

Microsoft Windows 98 Named Pipes
Novell NetWare IPX/SPX
Apple Macintosh TCP/IP

You need to allow the client computers to connect to the SQL Server computer. You also want to minimize
the number of configuration changes required on the client computers. Which three actions should you
take? (Each correct answer presents part of the solution. Choose three)

A. Install SQL server 2000 as a named instance.

B. Install SQL server 2000 as the default instance.
C. Configure the new instance for Mixed Mode Authentication.
D. Configure the new instance for Windows authentication.
E. Configure the server to use the named piped, IPX/SPX, and TCP/IP Net-Libraries.
F. Configure the server to use the Multiprotocol Net-Library.

4. You are the administrator of a SQL server 2000 computer. The server contains a database named
inventory. Developers at your company upgrade an inventory tracking application. Users report
that when they insert new information in the Locations table, the upgrade application returns the
following error message:

‘String or binary data would be truncated. The statement has been terminated.’

When you use SQL Profiler to trace the activity of the application, you receive the results as shown below:

Event Class Test Date Application Name

SQL:BatchCompleted Set implicit_transactions off MS SQLEM
SQL:BatchCompleted Use inventory MS SQLEM
Audit Login Network protocol : LPC set quoted Visual Basic
SQL:BatchCompleted Insert into locations Visual Basic

You examine the design of the locations table as shown in the table design below:

Table Schema

Customers Orders Employees

CustomerID OrderID EmployeeID
CompanyName CustomerID LastName
ContactName EmployeeID FirstName
ContactTitle OrderDate Title
Address RequiredDate TitleofCourtsey
City ShippedDate BirthDate
Region ShipVia HireDate
PostalCode Freight Address
Country ShipName City
Phone ShipAddress Region
Fax ShipCity PostalCode
ShipRegion Country

Suppliers Products Order Details

SupplierID ProductID OrderID
CompanyName ProductName ProductID
ContactName SupplierID UnitPrice
ContactTitle CategoryID Quantity
Address QuantityPerUnit Discount
City UnitPrice
Region UnitsInStock
PostalCode UnitsOnOrder
Country ReorderLevel
Phone Discontinued
Fax
HomePage
Categories
CategoryID
CategoryName
Description
Picture

You need to configure the database to support both versions of the application without affecting server
performance. What should you do?

A. Alter the data type of the description field to varchar(50).

B. Alter the data type of the Special field to varchar(50).
C. Alter the data type of the CubicFeet field to float.
D. In the locations table, create an INSTEAD OF trigger that truncates the description field at 10
characters when the record is updated.
E. In the locations table, create an INSTEAD OF trigger that truncates the Special field at 10
characters when the record is updated.

5. You are the administrator of an SQL Server 2000 computer. You configure a set of alerts on the
server to notify you whenever certain operations fail or encounter errors. The notifications are
sent to your Microsoft Windows 2000 Professional computer by using the ‘net send’ command.
You successfully test the alerts. After several days, however, you stop receiving notifications
when operations fail or encounter an error. You verify that the SQLServerAgent service is started.
You need to make sure that you continue to receive alert notifications. What should you do?

A. Stop and restart the SQLServerAgent service.

B. Clear the application log on the SQL server computer.
C. Clear the system log on your Windows 2000 Professional computer.
D. Install the SQL Server 2000 administrative tools on your Windows 2000 Professional computer.

6. You are the administrator of a SQL Server 2000 computer that contains a database. Users report
that queries to this database respond slowly. You use System Monitor to examine the subsystems
on your server and receive the results shown in the exhibit (in the exhibit, the % processor time
remains at 100 all the time). You need to modify the server to accelerate query response time.
What should you do?

A. Increase the amount of RAM.

B. Upgrade to a faster disk subsystem.
C. Add a faster network adapter.
D. Add an additional processor.

7. You are the administrator of an SQL Server 2000 computer. You create a job that performs
several maintenance tasks on the server’s databases. You want the job to run whenever the
server’s processor utilization fails below 5 percent. You create a new schedule for the job and
specify the ‘start whenever the CPU(s) become idle’ option. After several days, you notice that the
job has never executed, although the server’s processor utilization has fallen below 5 percent
several times. What should you do?

A. Modify SQL Server Agent properties and specify a smaller idle time.
B. Modify SQL Server Agent properties and specify a larger idle time.
C. Write a stored procedure that executes the job whenever the GGIDLE system variable is less than
5.
D. Write a stored procedure that executes the job whenever the GGIDLE system variable is greater
than 1.
 8. You are the administrator of a SQL Server 2000 computer. Your company modifies a sales
application it uses so that it can access data from a SQL Server database rather than a Microsoft
Access database. You configure the new SQL server database. The tables are configured as shown
in the table schema exhibit (exhibit not available). Users report that queries to this database
respond slowly. You use System Manager to analyze database activity and receive the results
shown in the exhibit (exhibit not available). You need to modify the database to accelerate query
response time. What should you do?

A. Place primary keys and foreign key indexes on the table.

B. Remove all indexes from the tables.
C. Replace the table joins with correlated subqueries.
D. Use a server-side cursor to retrieve data.
 9. You are the administrator of a Microsoft Windows 2000 Advanced Server computer. The server is
also running SQL Server 2000 and contains a database named sales, as shown in the exhibit
below:

See http://www.cheet-sheets.com/228/image1.jpg

The sales database is configured by using the default options and increases by 50MB per day. You want an
operator to be notified automatically before the database runs out of disk space. You also want to minimize
the amount of administrative overhead necessary for this database. What should you do?

A. Configure System Monitor to log an alert if the hard disk database contains less than 600MB of
disk space. Configure SQL Server Agent to send an e-mail message notifying the operator of the
System Monitor alert.
B. Create a SQL Server Agent job that uses the sp_spaceused stored procedure each morning. Place
the results of the stored procedure in the e-mail message and set SQL mail to send the results to
the operator.
C. Set the Maxsize property of sales_data.mdb to 7.5GB and the Maxsize property of log file to
1.5GB. Configure SQL Server Agent to send an e-mail message to an operator when the data file
and the transaction log have reached these limits.
D. Use Microsoft Windows Explorer to verify the available space on the hard disk each morning.
Execute the sp_spaceused stored procedure each morning.

10. You are the administrator of a Microsoft Windows NT Server 4.0 computer. The server is the
PDC in your domain and also runs SQL Server 2000. The server has four processors and 1GB of
RAM. Your network consists of 600 client computers that are running Microsoft Windows
Millennium edition. Users report that when they log on in the morning their computers respond
slowly and occasionally return error messages. You use System Monitor to monitor processor
performance and RAM utilization. You discover that the %Total Time and %User Time counters
average 90% and the %privileged time counter averages 20 percent. The available Mbytes counter
averages 600MB. You need to improve server performance for user logons. What should you do?

A. Increase the physical RAM on the computer to 2GB.

B. Increase the virtual RAM on the computer to 4GB.
C. Configure SQL server to use only processors 1, 2, and 3.
D. Configure SQL server to use only processors 0, 1 and 2.

11. You are the administrator of an SQL Server 2000 computer. Each night, you need to send a copy
of the server’s MSDB database as an e-mail message to the administrator of another SQL server
computer.

You create a job that contains the following steps:

o Detach the MSDB database.
o Send the database to the administrator in an e-mail message.
o Re-attach the MSDB database.

You test the job, and it fails on the first step. You need to ensure that the MSDB database is mailed to the
administrator every night. What should you do?

A. Ensure that the SQLServerAgent service is running under a user account that has database owner
access to the MSDB database.
B. Delete the first and last steps in the job.
C. Configure the job to back up the MSDB database to a temporary file. Send the file to the
administrator in an e-mail message.
D. Insert a new job step before the first step. Configure the new step to terminate all processes that
use the MSDB database.

12. You are the administrator of a SQL Server 2000 computer. The server contains two databases. A
client/server application accesses one of the databases. A different client/server application
accesses the other database. Each application uses several stored procedures to query and update
its database.

Users report that both applications respond slowly. You use SQL Profiler to monitor activity on the server.
You receive results of the trace as shown below:

Event Class Test Date

SQL:BatchCompleted If @@TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update Customer Set CustomerZip = ……
SQL:BatchCompleted Update Products Set SKU = ‘23232323…..
SQL:BatchCompleted If @@TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update Customers Set CustomerZip=

You need to improve the performance of the applications. What should you do?

A. Modify the applications so that they use views to query data.

B. Modify the stored procedures so that they select data into temporary tables.
C. Re-create the stored procedures so that they do not contain the WITH RECOMPILE option.
D. Disable the ‘Autoupdate statistics’ option in both databases.

13. You are the administrator of a database that contains 64 lookup tables. These tables store static
data that should not change. However, users report that some of this data is being changed. You
need to prevent users from modifying the data. You want to minimize changes to your security
model and to your database applications. How should you modify the database?

A. Create a filegroup named LOOKUP. Move the lookup tables to this filegroup. Select the read-
only check box for the filegroup.
B. Create a database role named datamodifier. Grant SELECT permissions to the datamodifier role.
Add all users to the role.
C. Deny INSERT, UPDATE, and DELETE permissions for all users. Create stored procedures that
modify data in all tables except lookup tables. Require users to modify data through these stored
procedures.
D. Create a view of the lookup tables. Use the view to allow users access to the lookup tables.

14. You are the administrator of an SQL Server 2000 computer. The server contains a database named
Inventory. The database has a parts table that has a field named InStock. When parts are shipped,
a table named PartsShipped is updated. When parts are received, a table names PartsReceived is
updated. The relationship of these tables is shown below:

See http://www.cheet-sheets.com/228/image2.jpg

You want the database to update the Instock field automatically. What should you do?

A. Add triggers to the PartsShipped and the PartsReceived tables that update the InStock field in the
parts table.
B. Create a user-defined function that calculates current inventory by running aggregate queries on
the PartsShipped and PartsReceived tables.
C. Use a view that creates InStock as a part of an aggregate query.
D. Create stored procedures for modifying the PartsReceived and the PartsShipped tables that also
modify the InStock field in the Parts table. Use these procedures exclusively when modifying data
in the PartsReceived and the PartsShipped tables.
 15. You are the administrator of a SQL Server 2000 computer. You are creating a database named
RetailImport to use as an intermediate data store for a data warehouse. Each night you must
import daily sales data into the database from SQL Server 2000 computers in 120 locations. After
the data is moved into the data warehouse, the tables are truncated. The database schema is shown
below:

See http://www.cheet-sheets.com/228/image3.jpg

You want to configure the data import processor so that you minimize the time needed to import the sales
data and administer the database. What should you do?

A. Use the simple recovery model and the FOR LOAD option to create the database. Create a data
transformation services package that uses the BULK INSERT statement to copy the sales data.
B. Index the foreign key fields in the child tables.
C. Create a data transformation services (DTS) package that uses a Data Driven Query task to copy
the sales data.
D. Collect the sales data by using a distributed transaction that inserts the data from all 120 retail
locations into the RetailImport database in a single transaction.
E. On the servers in the retail locations, create stored procedures that submit the updates to the
RetailImport database each night as SQLXML updategrams.

16. You are the administrator of a SQL Server 2000 computer. You have two new hard disks on
which you will create a database named Inventory. You want to insert, update, and delete data as
quickly as possible.

Which two actions should you take? (Each correct answer presents parts of the solution. Choose two)

A. Configure the hard disks as two mirrored NTFS volumes.

B. Configure the hard disks as one mirrored NTFS volume.
C. Configure the hard disks as two independent NTFS volumes.
D. Configure the hard disks as one extended NTFS volumes.
E. Place inventory_data.mdf on the first volume and inventory_log.idf on the second volume.
F. Place inventory_data.mdf on the first volume and inventory_data2.ndf and inventory_log.idf on
the second volume.
G. Place inventory_data.mdf and inventory_log.idf on the same volume.

17. You are the administrator of a SQL Server 2000 database. You import a table of geographic
information from a Microsoft Access database into a SQL Server 2000 database. The table has
12,000 rows. Each row averages 5,000 bytes. The table contains lookup data that does not change.
You want to minimize the size of the data file and the time required to back up the data. Which
two actions should you take? (Each correct answer presents part of the solution. Choose two)

A. Create a 60-MB data file named geography.ndf

B. Create a 95-MB data file named geography.ndf
C. Create a 60-MB data file named geography.mdf
D. Create a 95-MB data file named geography.mdf
E. Place the table in the PRIMARY firegroup.
F. Place the table in a new firegroup named LOCATION.

18. You are the administrator of a SQL Server 2000 computer. The server contains a database named
sales. You need to change the way customer IDs appear in the Customers table. The database
schema is shown below:

See http://www.cheet-sheets.com/228/image4.jpg
You need to automate the process of updating the primary key tools. You also want to minimize record
locks and administration within the database during the update process. What should you do?

A. Add an ON UPDATE CASCADE constraint to the CustomerID field in the Customers table.
Modify the values in the CustomerID field in the Customers table.
B. Create a duplicate record that has a new CustomerID value. Update the foreign key fields in the
invoices, contacts, and quotes tables with the new value.
C. Disable the FOREIGN KEY constraints. Within a transaction, modify the values in the
CustomerID field in the Customers table and all related foreign key values in the invoices,
contacts, and quotes tables. Re-enable the FOREIGN KEY constraints after the keys are changed.
D. Create a data transformation services package. Use the package to transform the CustomerID
value and the values of the related foreign keys in the invoices, contacts, and quotes tables.

19. You are the administrator of two SQL Server 2000 computers. One of these servers contains a 4-
GB database named Marketing. You want to remove the Marketing database from one server and
add it to the other as quickly as possible. What should you do?

A. Detach the database from the original server by using the sp_detach_db stored procedure. Copy
the database and the transaction log files to the new server, and attach them by using the
sp_attach_db stored procedure.
B. Use the DTS export wizard to transfer all database objects from the original server to the new
server. Drop the database from the original server.
C. Run a full backup of the database on the original server. Create a new database named Marketing
on the new server. Restore the backup in the new Marketing database. Drop the database from the
original server.
D. Shut down the original server. Copy the database and the transaction log files to the new server.
Use the DISK INIT and DISK REFIT statements to attach the data file to the new server. Drop the
database from the original server.

20. You are the administrator of a SQL Server 2000 computer. The server contains a database that
stores financial data. You want to use data transformation services packages to import numeric
data from other SQL server computers. The precision and scale values of this data are not defined
consistently on the other servers. You want to prevent any loss of data during the import
operations. What should you do?

A. Use the ALTER COLUMN clause of the ALTER TABLE statement to change data types in the
source tables. Change the data types so that they will use the lowest precision and scale values of
the data that will be transferred.
B. Use the ALTER COLUMN clause of the ALTER TABLE statement to change data types in the
destination tables. Change the data types to reflect the highest precision and scale values involved
in data transfer.
C. Set a flag on each DTS transformation to require an exact match between source and destination
columns.
D. Set the maximum error count for each DTS transformation task equal to the number of rows of
data you are importing. Use an exception file to store any rows of data that generate errors.
E. Write Microsoft ActiveX scripts for each DTS transformation. Use the scripts to recast data types
to the destinations precision and scale values.

21. You are the administrator of a SQL Server 2000 computer. Each evening after business hours, you
perform database maintenance tasks. You create a data transformation services package to
populate the data warehouse. MAPI is not installed on the server. You want to schedule the DTS
package to run each night. You want to attend to other duties while the DTS package is executing.
You also want to be notified if the DTS package fails. What should you do?
A. Select the ‘DTS packages log package execution to SQL Server’ check box. Create a SQL Server
event alert to notify you if the package fails.
B. Select the ‘DTS packages write completion status to event log’ check box. Create a SQL Server
event alert to notify you if the package fails.
C. Configure the DTS package to include an on failure precedence constraint and a send mail task.
Configure the send mail task to notify you if the package fails.
D. Configure the DTS package to include an on failure precedence constraint and a send mail task.
Configure the execute process task to execute a not send command that will notify you if the
package fails.

22. You are the administrator of a SQL Server 2000 computer. Your company purchased an
accounting application from a vendor. The application stores its data in a database named
Accounting on the server. The tables in this database contain columns that function as primary
keys, but PRIMARY KEY and FOREIGN KEY constraints are not used.

You need to replicate data from this database to another SQL Server computer. This server will use the
replicated data to generate reports. Most reports will run each month, but the accounting department needs
to have the ability to run reports at any time. Reports should be accurate through the last full working day.

You cannot make any changes to the database, but you need to implement replication. Which two actions
should you take? (Each correct answer presents part of the solution. Choose two)

A. Implement merge replication.

B. Implement snapshot replication.
C. Implement transactional replication.
D. Schedule replication to run continuously.
E. Schedule replication to run during off-peak hours.
 23. You are the administrator of a SQL Server 2000 computer named FABSQL2K01. You create a
data transformation services package that contains definitions for two transform data tasks. The
tasks get data from two text files named customers and sales. The DTS package is configured as
shown below:

See http://www.cheet-sheets.com/228/image5.jpg

You do not want the DTS package to add any data to the server unless both transform data tasks complete
successfully. You select the ‘Use transactions’ check box and the ‘Join transaction of present’ check box of
both transform data tasks. You them select the ‘Fail package on step failure’ check box of both transform
data tasks. You want to use the DTS package to import data from the text files to the server. You want to
ensure that changes are committed if all imported data is successfully transformed. What should you do?

A. Select the ‘Commit on successful completion’ check box of the DTS package.
B. Select the ‘Commit transaction on successful completion of this step’ check box of the customers
transform data task.
C. Select the ‘Commit transaction on successful completion of this step’ check box of the sales
transform data task.
D. Select the ‘Commit transaction on successful completion of this step’ check box of both transform
data tasks.

24. You are the administrator of a SQL Server 2000 computer. The server is a member of a Windows
NT domain and is configured for Windows authentication. The server contains a database that
stores contact information for public officials in your region. These officials need to access the
database by means of the Internet by using the guest login. However, some users report that they
cannot connect to the server by means of the Internet. You need to allow anyone access to the
server by means of the Internet. What should you do?

A. Assign the guest login a blank password.

B. Delete the guest login and create a new login that is mapped to the guest domain user account.
C. Create a database user named Anonymous and assign the user the appropriate database
permissions.
D. Configure the server for mixed mode authentication.

25. You are the administrator of a SQL Server 2000 computer named corpsql. The server is a member
of a Microsoft Windows NT domain named CORPDOMAIN. Corpsql is configured for Windows
authentication and contains three databases named sales, finance, and research.

Andrea is an administrative assistant in your company. She uses a suite of client/server applications to
access all three databases on corpsql. Andrea transfers to a different department within your company and
requires access only to the research database. Her former manager requests that you remove Sophia’s
access to the sales and finance databases. You need to configure the appropriate permissions for Andrea.
Which batch of transact-SQL statements should you execute?

A. USE finance
GO
EXEC sp_revokedbaccess Andrea
USE sales
GO
EXEC sp_revokedbaccess Andrea
B. USE finance
GO
EXEC sp_revokedbaccess Andrea ‘db_denydatareader’, ‘Andrea’
USE sales
 GO
EXEC sp_revokedbaccess Andrea ‘db_denydatareader’, ‘Andrea’
C. USE Master
GO
EXEC sp_droplogin ‘Copdomain\Andrea’
GO
EXEC sp_droplogin ‘Copdomain\Andrea’
D. USE Finance
GO
EXEC sp_denylogin ‘Andrea’
USE sales
GO
EXEC sp_denylogin ‘Andrea’

26. You are the Administrator of a SQL Server 2000 computer. The server is configured for Windows
authentication. You add an integrated logon for the domain users group. However, 20 of the users
in the domain users group should not have access to the server. You need to prevent the
unauthorized users from accessing the server. You need to accomplish this goal by making the
least number of changes to the server. What should you do?

A. Add a SQL server login for the unauthorized users, and then deny access to the login.
B. Add the unauthorized users to a domain user group, add a login for the group and then deny
access to the login.
C. Add a login for each authorized user, and then deny access to each login.
D. Remove each unauthorized user from the domain users group.

27. You are the administrator of a SQL Server 2000 computer. Your company has 150 client
computers that are running UNIX. This client computer requires access to the server to use a
database named Techinfo. The server is configured with the TCP/IP and Named Pipes libraries.
You need to configure the server to allow the client computers to use the Techinfo database. You
also want to minimize the amount of time it takes to configure the server and allow for the
tracking of individual user actions. Which two actions should you take? (Each correct answer
presents part of the solution. Choose two)

A. Configure Windows authentication.

B. Configure mixed mode authentication.
C. Create a SQL Server login for each user.
D. Create a domain user group login for all of the users.
E. Create a single SQL server login for all of the users.
F. Enable the multiprotocol Net-Library.
G. Disable the Named Pipes Net-library.

28. You are the administrator of a SQL Server 2000 computer. The server is used to store confidential
company information. Company policy requires that every action and change of permission on the
server be logged. Policy also requires that the server can run only when logging in enabled. You
need to configure the server to comply with this policy. What should you do?

A. Use SQL Profiler to capture security events and audit events. Make sure that a rollover is enabled.
B. On the Security tab of the Server Properties dialog box, set the Audit Level option to All.
C. Configure the server to use Windows authentication. Make sure that Windows security log does
not overwrite events.
D. Set the c2 Audit mode option to 1. Restart the MSSQLServer service.
 29. You are the administrator of a SQL Server 2000 computer. The server is used to store information
for your company's finance department. The permissions on the financeinfo database are
configured as shown below:

User/Role Create Table Create View CreateSP

Accountants Deny Blank Allow
Administrators Allow Blank Blank
Public Blank Blank Blank
AppRole1 Blank Blank Blank
Managers Allow Allow Allow
Brian Blank Deny Blank

You need to configure the database to allow Brian to add a new view. Which two actions should you take?
(Each correct answer presents part of the solution. Choose two)

A. Add Brian to the Managers database role.

B. Add Brian to the Administrators database role.
C. Revoke CREATE TABLE permissions for Brian.
D. Revoke CREATE PROCEDURE permissions for Brian.
E. Revoke CREATE VIEW permissions for Brian.

30. You are the administrator of a SQL Server 2000 computer. One of the databases on the server
contains a table named complaints. This table is used to store information about customer
complaints. The customer service representatives in your company add and edit the information in
the Complaints table. They work with this table by using a number of client applications,
including a Web-based application and a Microsoft Windows 32-bit application.

The customer service manager discovers that some customer complaints are marked as closed before they
are resolved to the customer’s satisfaction. You need to notify the customer service manager whenever a
compliant is marked as closed. You do not want to make any changes to the client applications. What
should you do?

A. Create an UPDATE trigger that sends an e-mail message.

B. Create a stored procedure that sends an e-mail message. Use the stored procedures to make
changes to the data.
C. Create a user-defined function that sends an e-mail message. Use the function to make changes to
the data.
D. Create a rule that validates data entry. Bind the rule to the CompliantClosed column.

31. You are the administrator of a SQL Server 2000 computer. The server is used to store sales
information for your company. Your company hires a new sales manager named Paulo. You
create a user account named Paulo in your company's Microsoft Windows NT domain, which is
named CORPORATE. You create a Windows authenticated login for Paul on the server. The
permissions on the sales table are configured as shown below:

User/Role Select Insert Create View CreateSP

Accountants Allow Allow Allow Deny
Administrators Allow Blank Blank Blank
Reporters Allow Deny Deny Deny
Sales Managers Allow Allow Allow Allow
Salespeople Allow Allow Deny Deny
Guest Blank Blank Blank Blank

Paulo needs permission to view, add, edit, and remove information in the database tables. Which transact-
SQL statement should you execute?

A. EXEC sp_addrolemember ‘SalesManagers’, ‘Corporate\Paul’

B. GRANT ALL ON sales TO ‘Corportae\Paul’
C. EXEC sp_addrolemember ‘SalesManagers’, ‘Paul’
D. EXEC sp_grantdbaccess ‘Corporate\Paulo’, ‘Paul’
EXEC sp_addrolemember ‘SalesManagers’, ‘Paul’

32. You are the administrator of a SQL Server 2000 computer. The server contains a database named
FinanceData that is used by a client/server application. Each employee has a SQL server login that
the application uses to access the server. Your company hires a new employee named Andrew.
You create a SQL server login named Andrew. You also create a database user named Andrew in
the database and grant the database user full permissions in the database. Whenever Andrew runs
the client/server application, he receives the error message ‘Invalid object name’. Which transact-
SQL statement should you execute?

A. EXEC sp_grantdbaccess ‘Financedata’, ‘Andrew’

GO
Sp_addrolemember ‘db_datareader’, ‘Andrew’
B. GRANT ALL ON Financedata TO Andrew.
C. EXEC sp_defaultdb ‘Andrew’, ‘Financedata’
D. EXEC sp_addlogin ‘Andrew’

33. You are the administrator of a SQL Server 2000 computer. The server is used to store information
for your company's sales department.

The permissions on the SalesFigures table are configured as shown below:

User/Role Select Insert Update Delete

Accountants Allow Allow Deny Deny
Managers Allow Blank Deny Deny
Salespeople Allow Blank Blank Blank
Guest Blank Blank Blank Blank

Lisa is a user in the sales department. She needs to review the data in the SalesFigures table. She also needs
to add new items to the table. You need to allow Lisa to perform these tasks without giving her additional
permissions in the database. What should you do? (Each correct answer presents part of the solution.
Choose all that apply)

A. Add Lisa to the Managers database role.

B. Add Lisa to the Salespeople database role.
C. Grant Lisa INSERT permissions on the Sales Table.
D. Grant Lisa UPDATE permissions on the Sales Table.
E. Revoke UPDATE permissions on the table for Lisa.
F. Revoke DELETE permissions on the table for Lisa.
 34. You are the administrator of three SQL Server 2000 computers at Parker publishing. One server,
FLPSQL01, stores other data. You want to be able to use the other two servers, FLPSQL02 and
FLPSQL03, to answer queries and run reports. The planned network configuration is shown in the
exhibit below:

See http://www.cheet-sheets.com/228/image6.jpg

You want to use the Database Maintenance Plan wizard to configure log shipping from FLPSQL01 to
FLPSQL02 and FLPSQL03. You do not want users to add any new data on FLPSQL02 and FLPSQL03,
but they must be able to run queries and reports. Which two actions should you take? (Each correct answer
presents part of the solution. Choose two)

A. Set the database load state for each destination server to No Recovery mode.
B. Set the database load state for each destination server to Standby mode.
C. Enable the ‘Allow database to assume primary role’ option for both destination servers.
D. Enable the ‘Allow database to assume primary role’ option for one destination server and then
disable this option for other destination server.
E. Disable the ‘Allow database to assume primary role’ option for both destination servers.

35. You are the administrator of an SQL Server 2000 computer. The server contains a database named
Sales. Pete reports that he cannot modify data in the database. You use SQL Profiler to capture his
activities shown below:

Event Class Test Date

TraceStart
Existing Connections --network protocol:LPC set qualid_identifier on set implicit_transaction off…
Existing Connections --network protocol:LPC set qualid_identifier on set implicit_transaction off…
SQL:Batch Completed set transaction isolation level serializable begin transaction select * from….

You want Pete to be able to modify data. You also want the Transact-SQL statement to execute
without compromising data integrity. What are two possible ways to achieve this goal? (Each correct
answer presents a complete solution. Choose two)

A. Change the isolation level of the database connection.

B. Add indexes to the product table.
C. Remove the explicit transaction from the Transact-SQL batch.
D. Create a stored procedure to execute the Transact-SQL batch.
E. Add a cascading UPDATE trigger to the Products table.

36. You are the administrator of two SQL Server 2000 computers. Each server contains data for one
of your company's two regional divisions. Company employees currently use a single data entry
application on client computers. After the new application is installed, query response times slow.
You want to monitor server traffic during business hours to help diagnose this problem. What
should you do?

A. Run SQL Profiler, and create one trace to monitor both servers. Configure the trace to include
data from the new data entry application only.
B. Run two instances of SQL Profiler, and create a trace to monitor each server. Configure the trace
to include data from both data entry applications.
C. Run the sqldrag utility from a command prompt.
Write diagnostic data to a text file.
D. Execute the sp_monitor stored procedure.
Save the output to a text file.
 37. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Inventory. Users report that several storage locations in the UnitsStored field contain negative
numbers. You examine the database’s table structure. The table properties are configured as
shown in the exhibit (exhibit not available).

You correct all the negative number numbers in the table. You must prevent the database from storing
negative numbers. You also want to minimize use of server resources and physical I/O. Which transact-
SQL statement should you execute?

A. ALTER TABLE dbo.storagelocations ADD CONSTRAINS

CK_storagelocations_UnitsStored
CHECK (UnitsStored>= 0)

B. CREATE TRIGGER CK_UnitsStored On StorageLocations

FOR INSERT, UPDATE AS
IF INSERTED, UnitsStored < 0 ROLLBACK TRAN

C. CREATE TABLE ck_unitsstored As @Units >= 0

GO
Sp_bindrule ‘OK_UnitsStored’,
‘StorageLocations, UnitsStored’
GO

D. CREATE PROC UpdateUnitsStored

(0StorageLocationID int, @UnitsStored bigint)AS
IF @UnitsStored < 0
DATSERROR (50099, 17)
ELSE
UPDATE storaheLocations
SET UnitsStored = @UnitsStored
WHERE StorageLocationID = @storageLocationID

38. You are the database administrator of a SQL Server 2000 computer. The server contains your
company's Accounts database. Hundreds of users access the database each day. Because you have
had power interruptions in the past, you want to perfect the physical integrity of the Accounts
database. You do not want to slow down server operations. What should you do?

A. Enable the ‘Torn page detection database’ option for each database.
B. Disable ‘Write caching’ on all disk controllers.
C. Ensure that write caching disk controllers have better backups.
D. Create a database maintenance plan to check database integrity and make repairs each night.

39. You are the administrator of an SQL Server 2000 computer. The server contains a database named
Inventory. Users report that the following query responds slowly:

SELECT parts.SKU AS SKS, parts, inscription AS Part Description), locations, inscription AS location
FROM partslocations INSERT JOIN parts (G PartsLocations.Parts )

You examine the indexes in the PartsLocations table. The indexes are configured as shown below:
index_name index_description index_keys
1x_PartsLocaion_LocationsID_PK nonclustered, located on PRIMARY Location ID
1x_PartsLocation_PartsID_PK nonclustered, located on PRIMARY PartsID
PK_PartsLocations clustered, unique, primary… PartsID, LocationID
You need to improve the performance of the query. What should you do?

A. Rebuild the PK_PartsLoactions index.

B. Rebuild the IX_PartsLoactions_PartsID index.
C. Rebuild the IX_PartsLoactions_LocationId index.
D. Create the IX_PartsLocations_PartsID_LocationID index.

40. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Sales. You perform full database backups every two days. You also run regular database
consistency checks on the server. The most recent check of the Sales database returns the
following message:

HECKDB found 0 allocations errors and 3 consistency errors in table ‘Orders’ (object ID 214575782).

You want to correct the data integrity errors while minimizing the amount of data lost. What should you
do?

A. Disconnect users from the Sales database.

Enable the single user database option.
Execute the DBCC CHECKTABLE statement for the Orders table, and specify the
REPAIR_REBUILD option.
B. Disconnect users from the Sales database.
Enable the ‘dbo use only database’ option.
Execute the DBCC CHECKALLOC statement for the Orders table, and specify the
REPAIR_REBUILD option.
C. Disconnect users from the Sales database.
Execute the RESTORES DATABASE statement for the Sales database.
D. Execute the DBCC CLEANTABLE statement for the Orders table.
E. Execute the sp_table_validation stored procedure for the Orders table.

41. You are the administrator of an SQL Server 2000 computer. The server contains a database named
ResearchRecords. This database contains research records and administrative records. The
database consumes about 30GB of disk space. The data files are configured as shown below:

Database files
File Name Location Space Allocated (MB) Filegroup
MedicalRecords_data E:\Program Files\Micro… 10000 PRIMARY
MedicalRecords_data F:\data\MedicalRecords 10000 PRIMARY
MedicalRecords_data G:\ProgramFiles\Micro… 20000 PRIMARY

You run full database backups each night after business hours. You notice that the backup is not complete
by the time the morning shift begins to use the database. You need to minimize the time needed to restore
data in the event of a system failure. You also want to reconfigure the database to allow the backups to
complete during the evening hours. Which two actions should you take? (Each correct answer presents part
of the solution. Choose two)

A. Reorganize the data files into two groups.

Place the system tables in the PRIMARY filegroup and the user-defined tables in the other
filegroup.
B. Reorganize the data files into three groups.
Place the system tables and shared objects in the PRIMARY filegroup, the clinical records in a
filegroup, and the administrative records in a filegroup, and the administrative records in a
filegroup.
C. Reorganize the data files into three groups.
Place the system, tables in the PRIMARY filegroup, the indexes in a filegroup, and the tables in
the other filegroup.
D. Back up the transaction log each night.
Run a filegroup backup on a different filegroup each night.
E. Back up the transaction log each night.
Run a filegroup backup on the database each weekend.
F. Back up the transaction log each night.
Run a differential backup each Sunday, and run a full backup the first day of each month.

42. You are the administrator of several SQL Server 2000 computers. You want to retrieve
information from an archived inventory database. You have a full tape backup of the database.
The backup’s header information shows that the backup uses the
SQL_Latin1_General_CR437_BIN collation. However, the existing SQL server computers in
your office are configured to use the SQL_Latin1_General_CP1_CI_AS collation.

You do not want to join tables in the inventory database with tables in other databases. You need to restore
the inventory database to a SQL Server 2000 computer by using the least amount of administrative effort.
What should you do?

A. Use the rebuildm utility to rebuild the system database on an existing SQL server computer.
Configure all the databases on that server to use the SQL_Latin1_General_CR437_BIN collation.
Restore the inventory database to the server.
B. Restore the inventory database to an existing SQL server computer. Accept the
SQL_Latin1_General_CR437_BIN collation for that database.
C. Install a new named instance of SQL Server 2000 on an existing SQL Server 2000 computer.
Configure the named instance to use the SQL_Latin1_General_CR437_BIN collation.
Restore the inventory database to the named instance.
D. Install SQL Server 2000 on a new computer.
Configure the new server to use the SQL_Latin1_General_CR437_BIN collation.
Restore the inventory database to the new server.
 43. You are the administrator of an SQL Server 2000 computer. The server contains a database named
Sales. The database is configured as shown below:

See http://www.cheet-sheets.com/228/image7.jpg

In the last six months, the database has grown by 3 GB. Users report that query response time has slowed.
The options set the database are shown in the database options exhibit. Click the exhibit button (exhibit not
available). You want to accelerate query response time. What should you do?

A. Update the database statistics.

B. Add indexes to the foreign key fields.
C. Truncate the transaction log.
D. Run the Database Maintenance Plan Wizard.
E. Drop primary keys from all tables.

44. You are the administrator of an SQL Server 2000 computer. The server contains a database named
Sales that has two database files and one transaction log file. Each data file is located on its own
hard disk and exists in its own filegroup. You perform full database, differential, and transaction
log backups on a regular basis. All backups made during a single week are striped across three
disk backup devices. A portion of the header information for the current week’s backups is shown
in the following table:

Backup Name Backup Type BackupFinishDate

sales_db_10000625 1 2000-06-25 11:57:04.000
sales_t1_10000626_1 2 2000-06-26 11:04:12.000
sales_t1_10000626_2 2 2000-06-26 15:04:23.000
sales_df_20000426 5 2000-06-26 21:15:41.000

On June 28, 2000, at 1:47 P.M, the hard disk that contains the PRIMARY filegroup fails. You want to
recover as much data as possible. What should you do? (Each correct answer presents part of solution.
Choose two)

A. Backup the transaction log by using the NO_LOG option.

B. Backup the transaction log by using the NO_TRUNCATE option.
C. Restore the most recent full database backup.
D. Restore the most recent differential backup.
E. Restore all differential backups in sequence.
F. Restore all transaction log backups in sequence.
G. Restore all transaction logs once the most recent differential backup.

45. You are the database administrator for a legal services company. Employees enter data 24 hours a
day into a SQL server 2000 database. These employees report slower response times when new
account information is gathered from branch offices and added to the database. You currently use
the following BULK INSERT statement to and the account information:

BULK INSERT finance.dbo.customers

FROM ‘di\bulk\accts143_10141000.txt’
WITH DATAFILETYPE = ‘char’,
FIELD/TERMINATOR = ‘\t’,
ROWTERMINATOR = ‘\n,’
TABLOCK

You want to ensure that response times do not slow when new account information is added to the
database. What should you do?
A. Drop the indexes for the Customers table before the data load, and then re-create the indexes after
the data load is complete.
B. Remove the TABLOCK option from the BULK INSERT statement.
C. Add the BATCHSIZE option to the BULK INSERT statement and then set the option equal to 10
percent of the number of rows to be loaded.
D. Add the ROWS_PER_BATCH option to the BULK INSERT statement and then set the option
equal to 10 percent of the number of rows to be loaded.

46. You are the administrator of a SQL Server 2000 computer. The server contains a database named
sales. Users report that they cannot add new data to the database. You examine the properties of
the database. The database is configured as shown in the sales properties exhibit (exhibit not
available). You examine drive E. The hard disk is configured as shown in the local disk properties
exhibit (exhibit not available). You want the users to be able to add data, and you want to
minimize administrative overhead. What should you do?

A. Increase the maximum file size of Sales_Data to 1,500MB.

B. Use the DBCC SHRINKDATABASE statement.
C. Set automatic file growth to 10 percent.
D. Create another data file named Sales_Data2 in a new SECONDARY filegroup.

47. You are the administrator of a SQL server 2000 computer that contains a database named Acct.
the database contains 1.5 GB of data. The server has one 9-GB hard disk that is configured as
shown in the exhibit (exhibit not available). You need to import data into the database without
adversely affecting database performance. The data will require an additional 2GB of storage
space. What should you do?

A. Add another data file on drive E, and then add the file to the PRIMARY filegroup.
B. Move the transaction log file to drive E, and set the file growth of Acc_Data.mdf by selecting the
‘Unrestricted file growth’ option.
C. Rebuild all clustered indexes so that they have a fill factor of 100.
D. Compress drive D.

48. You are the administrator of an SQL server 2000 computer. The server contains a database named
MedicalRecords. Users access medical records by using the PatientID field. This field is the
clustered primary key for the Patients table. When users try to access medical records, the
database responds slowly. You examine the database options as shown in the exhibit (exhibit not
available). You want to accelerate query response time and minimize administrative overhead.
How should you reconfigure the database?

A. Create a SQL Server agent job to execute the UPDATE STATISTICS statement, and schedule the
job to run weekly.
B. Select the ‘Auto update statistics’ check box.
C. Run the Database Maintenance Plan Wizard, and accept the default settings.
D. Rebuild the primary key as a non-clustered primary key.
E. Clear the ‘Auto create statistics’ check box.

49. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Sales. Your company uses the database to store the sales department’s responses to requests for
price quotations.

Developers in your company create an application used for saving quotations. The application executes the
following transact-SQL statement:

UPDATE QuotationSummary
SET CustomerName = ‘Jean-Paul Deloria’
WHERE QuotationID = 12

When the application executes the statement, the developers receives the following error:

Server: Mrh4402, Level 16, Stats 1, Line 1

View or function ‘Quotation/Summary’ is not updateable because it contains aggregates:

You want developers to be able to use this UPDATE statement without generating errors. What should you
do?

A. Create an INSTEAD OF trigger on the view to update the composite tables.

B. Grant the developers UPDATE permissions on each base table.
C. Add a cascading update trigger on each base table.
D. Create a QuotationSummary table and populate the table with data from the base tables.

50. You are the administrator of a SQL Server 2000 computer. The server contains a database that is
heavily indexed and that company users query extensively. The database has grown and query
response time has slowed. The database is stored in a single data file. You want to accelerate
query response time. What should you do?

A. On a new hard disk, create a new filegroup.

Drop the existing nonclustered indexes, and then re-create them on the new filegroup.
B. On a new hard disk, add a new file to the PRIMARY filegroup.
Drop the existing nonclustered indexes, and then re-create them on the PRIMARY filegroup.
C. On the existing hard disk, create a new filegroup.
Drop the existing nonclustered indexes, and then re-create them on the new filegroup.
D. On the existing hard disk, add a new file to the primary filegroup.
Drop the existing nonclustered indexes, and then re-create them on the PRIMARY filegroup.

51. You are the administrator of a SQL Server 2000 computer. You want to set up snapshot
replication on the server. The server will serve as Publisher and Distributor for a minimum of 40
Subscribers. Currently, you want to publish 3GB of data, but the data is expected to grow over
time. Subscribers will receive a new snapshot each month. You want to minimize the workload on
the Publisher/Distributor. Which two actions should you take to configure snapshot replication?
(Each correct answer presents part of the solution. Choose two)

A. Store the snapshot in the default folder on the Publisher/Distributor.

B. Store the snapshot in an alternative folder on the Publisher/Distributor.
C. Store the snapshot in a shared folder on a file server.
D. Create pull subscriptions.
E. Create push subscriptions.

52. You are the administrator of a SQL Server 2000 computer named SQL1. You want to perform
adhoc distribution queries against a database that is stored on a SQL Server 2000 computer named
SQL2. SQL2 contains several databases, and each of these databases uses a different collation.
You want to ensure that comparisons in distributed queries are evaluated correctly. You also want
to minimize administrative overhead. How should you configure SQL1?

A. Use the ALTER DATABASE statement to change the collation of the databases on SQL1.
B. Add SQL2 as remote server.
C. Add SQL2 as a linked server.
Select the Use Remote Collation check box, and do not specify a collation name.
D. Add SQL2 as a linked server.
Select the Use Remote Collation check box, and specify a collation name.
 Repeat this process once for each database on SQL2.

53. You are the administrator of a SQL Server 2000 computer in your company's HR department.
Employee data is stored in a SQL Server 2000 database. A portion of the database schema is
shown in the exhibit (exhibit not available).

You want to create a text file that lists these data columns:

FirstName, LastName, WorkPhone, PositionName, DepartmentName.

You want to create the text file as quickly as possible. You do not expect to recreate this file, and you want
to avoid creating new database objects if possible. What should you do?

A. Use the bcp utility to export data from each table to a separate text file. Use format files to select
the appropriate columns. Merge the data from each text file into a single text file.
B. Create a view that joins data from all three tables include only the columns you want to appear in
the text file. Use the bcp utility to export data from the view.
C. Create a SELECT query that joins the data from the appropriate columns in the three tables. Add
an INTO clause to the query to create a local temporary table. Use the bcp utility to export data
from the local temporary table to a text file.
D. Create a SELECT query that joins the data from the appropriate columns in the three tables. Add
an INTO clause to the query to create a global temporary table. Use the bcp utility to export data
from the global temporary table to a text file.

54. You are the administrator of several SQL Server 2000 computers. A data Transformation Services
(DTS) package uses native OLE DB providers to transfer data between the servers. Connection
details for the servers are specified in .udl files. The .udl files are frequently updated as connection
details change.

You want to distribute the DTS package as a file to developers in your company. You want to make sure
connection details are available to developers who receive the DTS package. Which two tasks should you
perform? (Each correct answer presents part of the solution. Choose two)

A. Enable the ‘Always read properties from UDL file option’ in the Connection Properties dialog
box.
B. Disable the ‘Always read properties from UDL file option’ in the Connection Properties dialog
box.
C. Delete the .udl files and store connection details in the registry by using system data source
names.
D. Delete the .udl files, and store connection details in the registry by using user data source names.
E. Make the .udl files available on a network share.

55. You are the administrator of a SQL Server 2000 computer named DataSvr. The server is a
member of a Microsoft Windows NT domain named RESEARCH. Several users need access to
the database stored on the server. These users all belong to a domain user group named domain
scientists. They also belong to a local user group named Local Techs on the DataSvr computer.

Your company will be hiring 30 new employees who will be added to the domain scientists group by
domain administrators. These employees will also need access to DataSvr. You need to allow all of the
appropriate users to access DataSvr. You also want to minimize the amount of time you have spend
maintaining security on DataSvr. Which Transact-SQL statement would you execute?

A. EXEC sp_grant login BUILTIN\Local Techs

B. EXEC sp_grant login ‘Local Techs’
C. EXEC sp_grant login [Datasvr\Domain Scientists]
D. EXEC sp_grant login [Research\Domain scientists]

56. On the server, you create a new database named EmployeeData. The EmployeeData database will
store confidential information about company employees. You need to allow only authenticated
users to access the EmployeeData database. Which Transact SQL statement should you execute?

A. EXEC sp_revokelogin ‘guest’

B. EXEC sp_droplogin ‘guest’
C. EXEC sp_revokedbaccess ‘guest’
D. EXEC sp_addrolereader ‘db_denydatareader’, ‘guest’

57. You are the administrator of a SQL Server 2000 computer. You configure SQL Server to perform
auditing. Audit logs are saved in the C:\MSSQL\Audit folder. The server functions normally for
several days, but then the MSSQL Server Service shuts down without warning.

You cannot restart the service. You need to restart the MSSQL Server service. What should you do?

A. Set the SQLServerAgent service’s startup method to disabled.

Start the MSSQLServer service.
B. Start the MSSQLServer service manually by using the -s startup option.
C. Start the MSSQLServer service manually by using the -m startup option.
D. Delete the audit logs from the C:\MSSQL\Audit folder.
Start the MSSQLServer service.

58. You are the administrator of a SQL Server 2000 computer. The server contains confidential
information about contracts on which your company has placed bids. Company policy requires
that bid information be removed permanently from the database one year after the bid closes. You
need to comply with this policy and minimize server overhead. What should you do?

A. Create a data Transformation Services (DTS) package to remove bids that have a closing date
older than one year.
B. Create a trigger to delete any bids that have a closing data older than one year.
Bind the trigger to the bids table for all INSERT, UPDATE, and DELETE events.
C. Create a stored procedure to delete any bids that have a closing date older than one year.
Use SQL server agent to schedule the stored procedure to run every night.
D. Create a view that contains a WHERE clause to exclude bids that have a closing date older than
one year.

59. You are the administrator of a SQL Server 2000 computer. The server is a member of a Microsoft
Windows NT domain named CORP. The server is configured for Windows Authentication.
Several users can connect to the server by using SQL Query Analyzer. These users belong to a
domain user group named DataUsers, and the server contains a login for CORP\DataUsers.

Your company purchases a client/server application that implements its own user security and will access
the server by using a login named Csapp1. You create a domain user account named Csapp1 and add it to
the DataUsers domain user group. However, when you try to run the client/server application, you receive
the error message, “The required SQL Server database cannot be accessed.” You need to ensure that the
application runs properly. What should you do?

A. Remove the Csapp1 domain user account from the DataUsers domain user group. Create a login
for CORP\Csapp1.
B. Configure the server for Mixed Mode authentication. Create a login named Csapp1, and configure
it to access the application’s database.
C. Create a local user account on the server, and name it Csapp1. Add a login for BUILTIN\C sapp1.
D. Configure an application role in the application’s database, and name the role Csapp1. Set the
application role password so that it is the same as the Csapp1 user account password.

60. You are the administrator of a SQL Server 2000 computer. The server is used to store information
for your company's accounting department.

Database Role Select Insert Update Delete

Accountants Allow Allow Deny Deny
Brian Deny Blank Blank Blank
Managers Allow Blank Allow Allow
Salespeople Allow Blank Blank Blank

Brian is a member of the Managers and Salespeople database roles. While working in the table, he reports
that he can edit and remove information. However, he cannot view any of the information in the table.
You need to allow Brian to view the information in the Accounting table. Which Transact-SQL statement
should you execute?

A. GRANT PRIVILEGES ON Accounting TO Brian

B. GRANT SELECT ON Accounting TO Brian WITH GRANT OPTION
C. REVOKE GRANT OPTION FOR SELECT ON Accounting TO Brian
D. REVOKE SELECT OR Accounting FROM Brian
 61. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Inventory. The database is configured as shown below:

See http://www.cheet-sheets.com/228/image8.jpg

Users frequently need details about parts. Users search for data by using the following query:

SELECT Parts.SKU, Locations.Description, Manufacturer.Name, PartsLocations.Qty,

PartsLocations.LastInventoried
FROM Parts
INNER JOINT Manufacturer ON Parts.ManufacturerID= Manufacturer.ManufacturerID
INNER JOINT Locations ON Parts.LocationID= Loactions.LocationID
WHERE SKU?

You need to accelerate query response time. What should you do?

A. Create a parameterised stored procedure to retrieve the data.

B. Create a denormalized table that is maintained by triggers.
C. Use ad hoc queries to retrieve the data.
D. Create a nonclustered index on the primary key of each table.
 62. You are the administrator of a SQL Server 2000 computer. The server contains a database named
MedicalRecords. The database tables are configured as shown below:

Column Name Data Type Length Allow Nulls

PatientID int 4
Title char 4 Yes
FirstName char 25
LastName char 35
Address varchar 50
Address2 varchar 50 Yes
City char 25
State char 2
Zip char 10
Phone char 15 Yes

The existing PatientID field is an identity field. According to a new government regulation, the patient IDs
must include a three-character prefix before the numeric portion of the ID. The patient’s home address
defines the prefix. You want to comply with the regulation while minimizing changes to the database.
Which two actions should you take? (Each correct answer presents part of the solution. Choose Two)

A. Drop the FOREIGN KEY constraints. In each table, change the data type of the PatientID field to
char.
B. Add an on update constraint to the patients table, and link it to the PatientID field. Update the
field in the Patients table.
C. In the Patients table, add a new field named StatePatientID that has a data type of char. Populate
the field with the prefix and the existing PatientID.
D. In each table, update the PatientID field.
Re-create the FOREIGN KEY constraints.
E. In the Patients table, update the PatientID field.
F. Create a trigger that populates the StatePatientID field when a new patient is created.

63. You are the administrator of a SQL Server 2000 computer. The server contains a database named
MedicalRecords. This database contains clinical records and administrative records. The database
consumes about 30 GB of disk space. The data files are configured as shown in the exhibit
(exhibit not available). You run full database backups each night after business hours. You notice
that the backup is not complete by the time the morning shift begins to use the database. You need
to minimize the time needed to restore data in the event of a system failure. You also want to
reconfigure the database to allow the backups to complete during the evening hours. Which two
actions should you take? (Each correct answer presents part of the solution. Choose two)

A. Reorganize the data files into two filegroups.

Place the system tables in the PRIMARY filegroup and the user-defined tables in the other
filegroup.
B. Reorganize the data files into three filegroups.
Place the system tables and shared objects in the PRIMARY filegroup, the clinical records in a
filegroup, and the administrative records in a filegroup.
C. Reorganize the data into three filegroups.
Place the system tables in the PRIMARY filegroup, the indexes in a filegroup, and the tables in a
filegroup.
D. Back up the transaction log each night.
Run a filegroup backup on a different filegroup each night.
E. Back up the transaction log each night.
Run a full backup of the database each weekend.
F. Back up the transaction log each night.
Run the differential backup each Sunday, and run a full backup the first day of each month.
 64. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Accounting. The data files are configured as shown below:

File Name Location Disk Space (MB) Filegroup

Account_data E:\Program Files\ 8000 Primary

The database has grown to 7 GB. The server has no more available disk space. You need to make more
disk space available in the accounting database without affecting system performance. What should you
do?

A. Compress drive E.
B. Add a new hard disk to the server.
Create a new file on the new hard disk as part of the PRIMARY filegroup.
C. Add a new hard disk to the server.
Create a new file on the new hard disk as part of a new filegroup.
D. Map a network drive to the server.
Create a new data file on the network drive as part of the PRIMARY filegroup.
E. Map a network drive on the server.
Create a new data file on the network drive as part of a new filegroup.
 65. You are the administrator of an SQL Server 2000 computer. The server contains a database named
Sales. The database stored information that employees use to analyze sales data. You configure
the database by using the default options. You create the database tables as shown below:

See http://www.cheet-sheets.com/228/image9.jpg

You import data as a batch process each evening. Users report that the database responds slowly when they
run the following query:

SELECT Customers.CustomerName, Invoices.InvoiceID, Invoices.InvoiceDate, OrderDetail.Quantity,

Products.Description, Products.SKU, SalesPersons.FirstName, SalesPerson.LastName
FROM Invoices
INNER JOIN Customers ON Invoices.Customer ID = Customer.CustomerID
INNER JOIN OrderDetail ON Invoices.Invoice ID = OrderDetail.InvoiceID
INNER JOIN Products ON OrderDetail.ProductID = Products.ProductID
INNNER JOIN SalesPerson ON Invoices.SalesPersonID = SalesPerson.SalesPersonID

You want to improve the performance of this query. What should you do?

A. Create indexes on the foreign key fields.

B. Create a view to provide the data that the query will use.
C. Create a stored procedure that executes the query.
D. Create cascading UPDATE and DELET triggers to the table.
 66. You are the administrator of a SQL Server 2000 computer. The server contains a database named
MedicalRecords. You have recently added a hard disk to the server and configured the database.

The database is configured as shown in the database schema below:

See http://www.cheet-sheets.com/228/image10.jpg

Each primary key is clustered. All foreign keys contain nonclustered indexes. You want to use the new
hard disk to make the database respond more quickly to queries.

What should you do?

A. Re-create the Notes table on the SECONDARY filegroup.

B. Re-create the nonclustered indexes on the SEONDARY filegroup.
C. Re-create the Patients and Doctors tables and their indexes on the SECONDARY filegroup.
D. Re-create the Cases and Notes tables and their indexes on the SECONDARY filegroup.

67. You are the administrator of a SQL Server 2000 computer at a bank’s headquarters. Customers
can access their accounts from headquarters or from branch offices. The server at headquarters
maintains a master copy of all account data for all customers. When a customer deposits or
withdraws funds at a branch office, the customer’s new balance must be reflected on the
computers at that office and at headquarters. You want to minimize latency between the
headquarters and branch offices. You want to copy account data between the server at
headquarters and the servers at the branch offices. What should you do?

A. Implement merge replication. Use the DATETIME (Later Wins) conflict resolver. Schedule
replication for off-peak hours.
B. Implement transactional replication. Allow immediate-updating subscribers. Schedule replication
to run continuously.
C. Create a data transformation services package on the server at headquarters. Configure the DTS
package to import new account data from each of the servers at the branch offices and write it to
all other servers. Schedule the DTS package to run during off-peak hours.
D. Add all branch office servers to the headquarters server as linked servers. Create a job on the
headquarters server. Configure the job to query each linked server for new account data and write
this data to the other servers. Schedule the job to run every three minutes.
E. Configure log shipping between the headquarters server and the branch office servers. Configure
the headquarters server as the primary server and the branch office servers as secondary servers.
Schedule log backups and log shipping to run every 10 minutes.

68. You are the administrator of a SQL Server 2000 computer. You are creating a data transformation
services package. As the first step in this process, you need to load data from text files into a
database table. These text files contain data on new stores that join your franchise. The text files
list the data columns in the following format:

StoreID, storeName, address, city, state, postalCode, managerID, storeTypeID, facilityID.

The destination table is configured as shown in the exhibit (exhibit not available). You want to load the
data into the table as quickly as possible. What should you do?

A. Use a Bulk Insert Task to read the data into a temporary table. Use an Execute SQL task to import
the appropriate data into the destination table.
B. Create and edit a format file to select the columns you want to import. Use a bulk insert task, and
then specify the format file to import the appropriate data into the destination table.
C. Use a transform data task to import the data. Use Microsoft ActiveX transformation scripts to
write the appropriate data to the appropriate columns in the destination table.
D. Create and edit a format file to select the columns you want to import. Use a transform data task,
and then specify the format file to import the appropriate data into the destination table.

69. You are the administrator of an SQL Server computer. The server is running SQL Server 6.5 and
SQL server 7.0. You install a named instance of SQL Server 2000, and then run the SQL Server
Upgrade Wizard. On the database selection screen, some of the SQL server 6.5 databases are not
listed. You cancel the SQL Server Upgrade Wizard. You need to ensure that the SQL Server 6.5
databases are listed in the wizard. What should you do?

A. Uninstall SQL Server 7.0, and then rerun the SQL Server Upgrade Wizard.
B. Run the Microsoft SQL Server-switch application, and then rerun the SQL Server Upgrade
Wizard.
C. Create a data transformation services package that imports the databases from SQL server 6.5 to
SQL server 2000, and then execute the package.
D. Uninstall SQL Server 2000, and then reinstall SQL Server 2000 as the default instance.

70. You are the administrator of a new Microsoft Windows 2000 Server computer named CPSRV1.
CPSRV1 is a member server in your company's Windows NT 4.0 domain, which is named
CPDOMAIN. A separate Windows NT domain named CPUSERS contains all of your company's
user accounts. A trust relationship exists between the CPDOMAIN domain and the CPUSERS
domain. You are installing SQL Server 2000 on CPSRV1. You specify a service account for each
SQL Server service as shown in the following table:

Service Service Account Account Type

MSSQLServer SPUSERS\sqlsvr Domain administrator
SQLServerAgent CPUSERS\sqlagent Domain administrator

After the installation is complete, the MSSQLServer service and the SQLServerAgent service do not start.
You need to configure the services to start without changing any security settings in either domain.
What should you do?

A. Configure the services to run under the local system account

B. Add CPSRV1 to the CPUSERS domain
C. Add CPUSERS\sqlsvr and CPUSERS\sqlagent to the local administrators group on CPSRV1
D. Add CPUSERS\sqlsvr and CPUSERS\sqlagent to the CPUSERS\Domain Admins groups.
E. Add CPUSERS\sqlsvr and CPUSERS\sqlagent to the CPUSERS\Domain users groups.

71. You are the administrator of 12 SQL Server 2000 computers. These servers are located in
company offices throughout the world. You configure a multiserver job on the server in your
company's New York City office. You configure this server as a master server, and designate the
remaining 11 servers as target servers.

After the job completes successfully, you decide to set up a new SQL Server 2000 computer as the master
server. The new server will be named JobMaster and the other 12 servers will be target servers. You need
to make the New York City server a target server instead of a master server. What should you do?

A. On the New York City server, defect all 11 target servers.

On the JobMaster server, enlist the 12 servers as target servers.
B. On the JobMaster server, enlist all other servers as target servers.
C. On the New York City server enlist the JobMaster server as a target server.
Transfer the multiserver jobs from the New York City server to the JobMaster server.
D. On the JobMaster server, enlist the New York City server as a target server.
On the JobMaster server, enlist the other 11 servers as target servers.
 On the JobMaster server, defect the New York City server.

72. You are the administrator of 20 SQL Server 2000 computers. The servers are located in 10
company offices around the world. You work in the New York City office. You need to configure
several dozen alert responses on each server. In the future, you will need to modify these alert
responses periodically. You want to reduce the amount of time you spend managing alert
responses. You also want to manage the alert responses from the New York City office. What
should you do?

A. Use SQL Enterprise Manager to manage the alert responses on all company servers.
B. Configure alert forwarding on the remote servers.
C. Write Transact-SQL scripts that can be used to set up alert responses on the remote servers.
D. Write stored procedures that can be used to set up alert responses on the remote servers.

73. You are the administrator of a SQL Server 2000 computer that contains a database. Users report
that the database responds slowly when they insert data. You use System Monitor to examine the
server’s performance and receive the results shown below:

Memory
Pages/sec 244. 443

PhysicalDisk _total
%Disk Time 240. 198

Processor _total
% Processor Time 2,000

You need to modify the server to improve its performance. What should you do?

A. Install a larger hard disk.

B. Increase the amount of RAM.
C. Increase the size of the virtual memory paging file.
D. Add an additional processor.

74. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Customers, which is used by a custom client/server application. The database contains more than
1 million rows. Users report that the database responds slowly when they search for customers by
using the application. Users search for customers by scrolling through an alphabetical list of
customer names.

You use SQL profiles to capture information about how the application uses the database. You receive
results as shown below:

Event Class Text Data

SQL: Batch completed Select * from customers

You need to improve the application’s performance. What should you do?

A. Create additional non-clustered indexes on the CustomerInformation table.

B. Rewrite the application so that it uses a user-defined function to return the customer list.
C. Rewrite the application so that it uses a query with a WHERE clause to search for customer
names.
D. Rewrite the application so that is uses a stored procedure to return the customer list.

75. You are the administrator of a Microsoft Windows 2000 computer. The computer is also running
SQL Server 2000 and contains a database named Sales. The sales department of your company
uses a custom application that has just been upgraded.
A user at your company named Pedro reports that when he attempts to run the weekly analysis report, he
receives the following error message:

“Server.Msg3704, Level 16, State 1 , Line 1 User dos not have permission to perform this operation on
table ‘Report’.”

You log on to Pedro’s computer, run the program, and receive no error message. You use SQL Profiler to
trace database activity while you run the report. You receive the results shown below:

Event Class Text Data

SQL:Batch Completed Drop table report
SQL:Batch Completed Drop table report (Productname C…

You want to allow Pedro to run the report. You also want to minimize administrative overhead. What
should you do?

A. Add Pedro’s login to the db_ddladmin role in the Sales database.

B. Create an application role for the sales department’s application. Grant the role database owner
rights.
C. Add Pedro to the db_datawriter role in the Sales database.
D. Add Pedro to the local administrators group on the server.

76. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Sales. Your company deploys an upgraded version of a customer relationship management
application, which accesses the sales database.

The sales database contains a table named Customer, which stores information about your company’s
customers. Users report that they cannot delete information in the CustomerAddress2 column. The
Customer table is configured as shown below:

Table Design

Name Customers
Owner dbo
CreateDate 8/25/2000 10:42:06 a.m
Filegroup PRIMARY
Rows 1

Columns

Key ID Name Data Type Size Nulls

CustomerID int 4
CustomerName varchar 50
CustomerAddress varchar 50
CustomerAddress2 varchar 50
CustomerCity varchar 50 Y
CustomerState char 2
CustomerZip char 10

You use SQL Profiler to capture the activity of the application and receive the results shown below:

Event Class Text Data Application Name

Audit Login ----Nework protocol---- Visual Basic
SQL:Batch Completed Update Customer se… Visual Basic

You want to configure the Customer table to support the upgraded application. What should you do?
A. Alter the table to allow null values in the CustomerAddress2 column.
B. Alter the data type of the CustomerAddress2 column to sql_varient.
C. Create an UPDATE trigger that changes a null value in the CustomerAddress2 column to an
empty string.
D. Use an INSTEAD OF trigger to change the value of the CustomerAddress2 column to an empty
string whenever the column’s value is null.

77. You are the administrator of a SQL Server 2000 computer. You import a large amount of data into
the contracts database. After the import, you perform a full database backup. You examine the
hard disk and discover that the log file has grown from 200MB to 800MB. You want to recover
disk space as quickly as possible without losing backup information. Which two Transact-SQL
statements should you execute? (Each correct answer present s part of the solution. Choose two)

A. BACKUP LOG Contracts WITH no_log

B. BACKUP LOG Contracts WITH truncate_only
C. BACKUP LOG Contracts TO disk= ‘E: \SQLBackup\Contracts_Log.bkp’
D. ALTER DATABASE Contracts
(Name= Contract_Log.ldf,
FILENAME= ‘E: \Data\Contracts_ldf’
SIZE= 200)
E. DBCC ShrinkDatabase (Contracts, 200)
F. DBCC ShrinkFile (Contracts_Log, 200)

78. You are the administrator of two Microsoft Windows 2000 Advanced Server computers. On these
servers, you are configuring a database that will store accounting information for your company.
The data must be available at all times. Interruptions in data connectivity should not last longer
than five minutes. Any changes to the database should not require you to reconfigure the client
computers. How should you configure the database?

A. Configure the database on the two servers as a SQL Server 2000 cluster.
B. Configure the database on one server, and then configure a standby database on the second server.
C. Configure the database on each server.
Use Microsoft Distributed Transaction Coordinator to keep the two servers perfectly
synchronized.
D. Configure the database as a federated database, and then partition half the data on each server.

79. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Contracts. The server is configured as shown below:

See http://www.cheet-sheets.com/228/image11.jpg

The database files in Contracts are configured as shown below:

File Name Location Initial Size (MB) File group

Contracts_data E:\data\contracts_data 2000 PRIMARY
Contracts_data2 E:\data\contracts_data2 2000 SECONDARY

The database developers have been creating new tables and indexes without specifying a filegroup. The
primary filegroup is reaching its maximum capacity. You want the developers to continue adding new
objects, but you do not want then to change the way they create objects. You do not want to run out of disk
space. You also want to minimize the time it takes to administer the database. What should you do?

A. Back up the existing objects on the PRIMARY filegroup. Drop them from the database. Re-create
them on the SECONDARY filegroup.
B. Set the file growth on the PRIMARY filegroup to UNLIMITED.
C. Set the PRIMARY filegroup so that it is read-only.
D. Set the SECONDARY filegroup as the default filegroup.

80. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Sales. The database will store sales transactions. Retail locations must be able to add transactions
to the database 24 hours a day, every day. The server is configured as shown below:

See http://www.cheet-sheets.com/228/image12.jpg

You need to configure the data files and transaction log for the Sales database. What should you do?

A. Place the transaction log on physical disk 0 and the data file on the RAID-5 disk array.
B. Place the transaction log on the RAID-5 disk array and the data file on physical disk 0.
C. Place the transaction log and the data file on physical disk 0.
D. Place the transaction log and the data file on the RAID-5 disk array.

81. You are the administrator of an SQL Server 2000 computer. The server contains a database named
Acct. You purge the Acct database of old records and perform a full backup. The database now
uses 4 GB of space. The database files are configured as shown below:

File Name Location Space Allocated Filegroup

Acct_Data C:\data\acct_data.mdf 4000 PRIMARY
Acct_Data1 D:\data\Acct_Data1_D… 4000 PRIMARY
Acct_Data2 E:\data\Acct_Data2_D… 4000 PRIMARY

The server has two hard disks that are configured as shown below:

See http://www.cheet-sheets.com/228/image13.jpg

The 3 GB transaction log file for the Acct database is stored on drive C. You need to make room for a new
database that has a 3 GB data file and a 1 GB transaction log file. You want to optimize database
performance on both databases. You also want to minimize administrative overhead. What should you do?

A. Shrink the empty data file on drive E.

Place the new data file and the new log file on drive E.
B. Shrink the empty data file on drive E.
Backup and shrink the log file on drive C to 2 GB.
Place the new data file on drive E and the new log file on drive C.
C. Shrink the data file on drive D to 1 GB, and then shrink the data file on drive E to 2 GB.
Place the new data file on drive D and the new log file on drive E.
D. Shrink and delete the data file on drive C, and then shrink the database files on drives D
and E so that they are both 2 GB.
Place the new data file and the new log file on drive C.

82. You are the administrator of a SQL Server 2000 computer. The server contains your company's
Accounts database. Hundreds of users access the database each day. Because you have had power
interruptions in the past, you want to protect the physical integrity of the Accounts database. You
do not want to slow down server operations. What should you do?

A. Enable the ‘torn page detection’ database option for each database.
B. Disable write caching on all disk controllers.
C. Ensure that the write caching disk controllers have battery backups.
D. Create a database maintenance plan to check database integrity and make repairs each
night.
 83. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Sales. Gus reports that he cannot modify data in the database. You use SQL Profiler to capture his
activities as shown below:

EventClass TextData
TraceStart
ExistingConnection -- network protocol: LPC set quoted identifier on set implicit transactions off…
ExistingConnection -- network protocol: LPC set quoted identifier on set implicit transactions off…
SQL:BatchCompleted set transaction isolation level serializable begin transaction select * from…

You want Gus to be able to modify data. You also want the Transact-SQL statement to execute without
compromising data integrity. What are two possible ways to achieve this goal? (Each correct answer
represents a complete solution. Choose two.)

A. Change the isolation level of the database connection.

B. Add indexes to the Sales table.
C. Remove the explicit transaction from the Transact-SQL batch.
D. Create a stored procedure to execute the Transact-SQL batch.
E. Add a cascading UPDATE trigger to the Products table.

84. You are the database administrator for a retail company. The company owns 250 stores. Every
month, each store submits approximately 3,000 sales records, which are loaded into a SQL Server
2000 database at corporate headquarters. A Data Transformation Services (DTS) package
transforms the sales records as they are loaded. The package writes the transformed sales records
to the Sales table, which has a column for integer primary key values. The IDENTITY property
automatically assigns a key value to each transformed sales record.

After loading this month's sales data, you discover that a portion of the data contains errors. You stop
loading data, identify the problem records, and delete those records from the database. You want to reuse
the key values that were assigned to the records that you deleted. You want to assign the deleted key values
to the next sales records you load. You also want to disrupt users' work as little as possible. What should
you do?

A. Export all records from the Sales table to a temporary table.

Truncate the Sales table, and then reload the records from the temporary table.
B. Export all records from the Sales table to a text file.
Drop the Sales table, and then reload the records from the text file.
C. Use the DBCC CHECKIDENT statement to reseed the Sales table's IDENTITY
property.
D. Set the Sales table's IDENTITY_INSERT property to ON.
Add new sales records that have the desired key values.

85. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Inventory. The database contains a table that is used to store information about equipment
scheduling. The EquipmentSchedules table is configured as shown below:

Column_name Type ComputedLength Prec Scale Nullable

EquipmentID int no 4 10 0 no
CustomerID int no 4 10 0 no
Location varchar no 50 yes
StartDate datetime no 8 no
EndDate datetime no 8 no
Rate money no 8 19 4 no

Identity Seed Increment

EquipmentID 1 1

RowGuidCol
No rowguidcoi column defined

Data_located_on_filegroup
PRIMARY

Index_name index_description index_keys

PK_EquipimentSchedules clusterd, unique, primary… EquipmentID

Contraint_type contraint_name delete_action update_action

PRIMARY KEY (clustered) PK_EquipmentSchedules na na

Users report that some equipment schedules have an end date that is earlier than the start date. You need to
ensure that the start date is always earlier than or equal to the end date. You also want to minimize physical
I/O. You do not want users to change the Transact-SQL statements they use to modify data within the
database. What should you do?

A. Create a constraint that compares the start date to the end date.
B. Create a trigger that compares the start date to the end date.
C. Create a rule that compares the start date to the end date.
D. Create a stored procedure that tests the start and end dates before inserting the row into
the database.

86. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Inventory. Users report that several storage locations in the UnitsStored field contain negative
numbers. You examine the database's table structure. The table properties are configured as shown
below:

Key ID Name Data Type Size

Y SotrageLocationID int 4
LocationDescription varchar 50
PartID int 4
UnitsStored bigint 8
DateStored datetime 8
TagID int 4

You correct all the negative numbers in the table. You must prevent the database from storing negative
numbers. You also want to minimize use of server resources and physical I/O. Which Transact-SQL
statement should you execute?

A. ALTER TABLE dbo.StorageLocations ADD CONSTRAINT

CK_StorageLocations_UnitsStored
CHECK (UnitsStored >= 0)
B. CREATE TRIGGER CK_UnitsStored On StorageLocations
FOR INSERT, UPDATE AS
IF INSERTED.UnitsStored < 0 ROLLBACK TRAN
C. CREATE RULE CK_UnitsStored As @Units >= 0
GO
sp_bindrule 'CK_UnitsStored'
'StorageLocations.UnitsStored'
GO
D. CREATE PROC UpdateUnitsStored
(@StorageLocationID int, @UnitsStored bigint) AS
IF @UnitsStored < 0
RAISERROR (50099, 17)
ELSE
UPDATE StorageLocations
 SET UnitsStored = @UnitsStored
WHERE StorageLocationID = @StorageLocationID

87. You are the administrator of a SQL Server 2000 computer. You have two new hard disks on
which you will create a database named Inventory. You want to insert, update, and delete data as
quickly as possible. Which two actions should you take? (Each correct answer represents part of
the solution. Choose two.)

A. Configure the hard disks as two mirrored NTFS volumes.

B. Configure the hard disks as one mirrored NTFS volume.
C. Configure the hard disks as two independent NTFS volumes.
D. Configure the hard disks as one extended NTFS volume.
E. Place Inventory_data.mdf on the first volume and Inventory_log.ldf on the second
volume.
F. Place Inventory_data.mdf and Inventory_log.ldf on the first volume and
Inventory_data2.ndf and Inventory_log2.ldf on the second volume
G. Place Inventory_data.mdf and Inventory_log.ldf on the same volume.

88. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Inventory. The database has a Parts table that has a field named InStock. When the parts have
shipped, a table named PartsShipped is updated. When the parts are received, a table named
PartsReceived is updated. The relationship of these tables is shown below:

See http://www.cheet-sheets.com/228/image14.jpg

You want the database to update the InStock field automatically. What should you do?

A. Add triggers to the PartsShipped and the PartsReceived tables that update the InStock
field in the Parts table.
B. Create a user-defined function that calculates current inventory by running aggregate
queries on the PartsShipped and PartsReceived tables.
C. Use a view that creates an InStock field as part of an aggregate query.
D. Create stored procedures for modifying the PartsShipped and PartsReceived tables that
also modify the InStock field in the Parts table.
Use these procedures exclusively when modifying data in the PartsShipped and
PartsReceived tables.

89. You are the administrator of a SQL Server 2000 computer. The server is configured as shown
below:

See http://www.cheet-sheets.com/228/image15.jpg

You need to create a new database named Inventory. Employees in your company will use the Inventory
database to track inventory data. Users will require immediate responses to queries that help them locate
where parts are stored. The tables in the database will be configured as shown below:

See http://www.cheet-sheets.com/228/image16.jpg

The database will consume 14 GB of disk space. You must configure the data files and transaction log to
accelerate query response time. Which two courses of action should you take? (Each correct answer
represents part of the solution. Choose two.)

A. On drive C, create a transaction log.

On drive D, create a data file in the PRIMARY filegroup.
On drive E, create a data file in the SECONDARY filegroup.
B. On each drive, create a transaction log.
On each drive, create a data file in the PRIMARY filegroup.
C. On drive D, create a transaction log.
On drive E, create a data file in the PRIMARY filegroup.
D. On the PRIMARY filegroup, create all tables and all indexes.
E. On the PRIMARY filegroup, create all tables. On the SECONDARY filegroup, create all
indexes.
F. On the PRIMARY filegroup, create the Parts table and its indexes.
On the SECONDARY filegroup, create all other tables and their indexes.

90. You are the administrator of a database that contains 64 lookup tables. These tables store static
data that should not change. However, users report that some of this data is being changed. You
need to prevent users from modifying the data. You want to minimize changes to your security
model and to your database applications. How should you modify the database?

A. Create a filegroup named LOOKUP.

Move the lookup tables to this filegroup.
Select the read only check box for the filegroup.
B. Create a database role named DataModifier.
Grant the SELECT permissions to the DataModifier role.
Add all users to the role.
C. Deny INSERT, UPDATE and DELETE permissions for all users.
Create stored procedures that modify data in all tables except lookup tables.
Require users to modify data through these stored procedures.
D. Create a view of the lookup tables.
Use the view to allow users access to the lookup tables.

91. You are the administrator of a SQL Server 2000 computer. One of these servers contains a 4-GB
database named Marketing. You want to remove the Marketing database from one server and add
it to the other as quickly as possible. What should you do?

A. Detach the database from the original server by using the sp_detach_db stored procedure.
Copy the database and the transaction log files to the new server, and attach them by
using the sp_attach_db stored procedure.
B. Use the DTS Export Wizard to transfer all database objects from the original server to
the new server.
Drop the existing database from the original server.
C. Run a full backup of the database on the original server.
Create a new database named Marketing on the new server.
Restore the backup in the new Marketing database.
Drop the database from the original server.
D. Shut down the original server.
Copy the database and the transaction log files to the new server.
Use the DISK INIT and DISK REFIT statements to attach the data file to the new server.
Drop the database from the original server.

92. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Inventory. In this database, the Parts table has a primary key that is used to identify each part
stored in the company's warehouse. Each part has a unique UPC code that your company's
accounting department uses to identify it. The database is configured as shown below:

See http://www.cheet-sheets.com/228/image17.jpg
You want to maintain the referential integrity between the Parts table and the OrderDetails table. You want
to minimize the amount of physical I/O that is used within the database. Which two Transact-SQL
statements should you execute? (Each correct answer represents part of the solution. Choose two.)

A. CREATE UNIQUE INDEX IX_UPC On Parts(UPC)

B. CREATE UNIQUE INDEX IX_UPC On OrderDetails(UPC)
C. CREATE TRIGGER UPCRI On OrderDetails
FOR INSERT, UPDATE As
If Not Exists (Select UPC From Parts
Where Parts.UPC = inserted.UPC) BEGIN
ROLLBACK TRAN
END
D. CREATE TRIGGER UPCRI On Parts
FOR INSERT, UPDATE As
If Not Exists (Select UPC From Parts
Where OrderDetails.UPC = inserted.UPC) BEGIN
ROLLBACK TRAN
END
E. ALTER TABLE dbo.OrderDetails ADD CONSTRAINT
FK_OrderDetails_Parts FOREIGN KEY(UPC)
REFERENCES dbo.Parts(UPC)
F. ALTER TABLE dbo.Parts ADD CONSTRAINT
FK_Parts_OrderDetails FOREIGN KEY (UPC)
REFERENCES dbo.Parts(UPC)

93. You are the administrator of a SQL Server 2000 computer named SQL1. You want to perform ad
hoc distributed queries against a database that is stored on a SQL Server 2000 computer named
SQL2. SQL2 contains several databases, and each of these databases uses a different collation.
You want to ensure that comparisons in distributed queries are evaluated correctly. You also want
to minimize administrative overhead. How should you configure SQL1?

A. Use the ALTER DATABASE statement to change the collation of the database SQL1.
B. Add SQL2 as a remote server.
C. Add SQL2 as a linked server.
Select the Use Remote Collation check box, and do not specify a collation name.
D. Add SQL2 as a linked server.
Select the Use Remote Collation check box, and specify a collation name.
Repeat this process once for each database on SQL2.

94. You are the administrator of a SQL Server 2000 computer. The server contains seven databases
that provide data to several company client/server applications. A different user maintains each
database. You need to configure server and database permissions so that each user who maintains
a database has full permissions on that database. What should you do?

A. Create a domain user group for all users.

Add a login for the domain user group.
Add the login to the sysadmin server role.
B. Create a domain user group for all users.
Add a login for the domain user group.
Map the login to a database user in each database.
Add all database users to the db_owner database role in each database.
C. Create a login for each user's domain user account.
Map each login to a database user in the appropriate databases.
Add the database users to the db_owner database role in each database.
D. Create a login for each user's domain user account.
Add the logins to the dbcreator server role.

95. You are the administrator of a SQL Server 2000 computer. The server contains a Data
Transformation Services (DTS) package that queries multiple databases and writes the results to a
text file. You run this package by using a Microsoft Windows batch file. The batch file uses the
dtsrun utility to execute the DTS package.

You want to ensure that connection properties, such as login names and passwords, cannot be read or
modified by users. Which two actions should you take? (Each correct answer represents part of the
solution. Choose two.)

A. Save the DTS package so that it has an owner password.

B. Save the DTS package so that it has an user password.
C. Encrypt the DTS package details in the command line of the dtsrun utility.
D. Store the DTS package in the Meta Data Services repository.
E. Store the DTS package as a Microsoft Visual Basic file.

96. You are the administrator of a SQL Server 2000 computer. The server is used to store information
for your company's finance department. The permissions on the FinanceData table are configured
as shown below:

Users/Database Role Select Insert Update Delete

Accountants Grant Grant Grant Deny
Administrators Grant Grant Grant Grant
Reporters Grant Deny Deny Deny
Sales Managers Grant Blank Blank Grant
Guest Blank Blank Blank Blank

Ryan is a member of the Accountants, Administrators, and public groups.

He reports that he is unable to delete information from the FinanceData table. You need to allow him to
delete information from the table. Which Transact-SQL statement should you execute?

A. EXEC sp_droprolemember 'Accountants', 'Ryan'

B. EXEC sp_droprolemember 'Administrators', 'Ryan'
C. EXEC sp_addrolemember 'db_datawriter', 'Ryan'
D. EXEC sp_addrolemember 'db_owner', 'Ryan'

97. You are the administrator of a SQL Server 2000 computer. Peter is an employee in your
company's marketing department. Peter has a SQL Server login named Peter, and he has access to
a database named MarketingData.

Peter has been assigned to a research project and needs to view and edit information stored in a database
named ResearchData. The database is contained on the same server as the MarketingData database.
You need to configure the appropriate permissions for Peter on the ResearchData database. Which
Transact-SQL statement should you execute?

A. GRANT ALL ON ResearchData TO 'Peter'

B. GRANT SELECT ON ResearchData TO 'Peter'
GRANT INSERT ON ResearchData TO 'Peter'
GRANT UPDATE ON ResearchData TO 'Peter'
C. EXEC sp_addrolemember 'db_datareader','Peter'
EXEC sp_addrolemember 'db_datawriter','Peter'
D. EXEC sp_grantdbaccess 'Peter',' PeterU'
GO
 EXEC sp_addrolemember 'db_datareader',' PeterU'
EXEC sp_addrolemember 'db_datawriter',' PeterU'

98. You are the administrator of two Microsoft Windows 2000 computers. One computer is running
Internet Information Services (IIS), and the other is running SQL Server 2000. Company partners
need to connect by means of the Internet and query data stored on the SQL Server computer.
Some of these partners have computers that do not use Microsoft operating systems or Web
browsers.

You need to configure the IIS and SQL Server 2000 computers to allow access to data by means of the IIS
virtual directory. IIS uses Basic Authentication to allow access to the virtual directory. Only company
partners should have access to the SQL Server computer by means of the Internet. The partners should be
allowed to make ad hoc queries. You want to be able to audit successful and failed logins to the SQL
Server computer. You want to allow the partners access to the SQL Server computer while keeping your
security scheme as simple as possible. Which two actions should you take? (Each correct answer represents
part of the solution. Choose two.)

A. Configure the IIS virtual directory to allow URL queries.

B. Configure the IIS virtual directory to allow template queries.
C. Create a new Windows user account.
Create a corresponding Windows Authenticated login on the SQL Server computer.
Configure the IIS virtual directory to always use this login when connecting to the SQL
Server computer.
D. Create a Windows Authenticated login on the SQL Server computer for the IIS Internet
Guest Account.
Configure the IIS virtual directory to always use the Internet Guest Account when
connecting to the SQL Server computer.
E. Create a Windows Authenticated login for each company partner on the SQL Server
computer.
Configure the IIS virtual directory to use Windows Integrated Authentication when
connecting to the SQL Server computer.

99. You are the administrator of a SQL Server 2000 computer. The server is used to store information
for your company's sales department. The permissions on the SalesFigures table are configured as
shown below:

Users/Database Role Select Insert Update Delete

Accountants Grant Grant Deny Deny
Managers Grant Blank Grant Grant
Salespeople Grant Blank Blank Blank
Guest Blank Blank Blank Blank

Lilly is a user in the sales department. She needs to review the data in the SalesFigures table. She also
needs to add new items to the table. You need to allow Lilly to perform these tasks without giving her
additional permissions to the database. What should you do? (Each correct answer represents part of the
solution. Choose all that apply.)

A. Add Lilly to the Managers database role.

B. Add Lilly to the Salespeople database role.
C. Grant Lilly INSERT permissions on the table.
D. Grant Lilly UPDATE permissions on the table.
E. Revoke UPDATE permissions on the table for Lilly.
F. Revoke DELETE permissions on the table for Lilly.

100. You are the administrator of a SQL Server 2000 computer. The server will be used to contain data
from your company's accounting, engineering, and production departments. Each department is
 contained in a Microsoft Windows domain users group. The groups are named Accounting,
Engineering, and Production. Members of each department need to read and modify their data.
However, users in one department should not have access to data in other departments. You want
to configure the database so that it meets your company's security requirements. You also want to
minimize administration time and the consumption of server resources. Which three actions
should you take? (Each correct answer represents part of the solution. Choose three.)

A. Create a database for each department.

B. Create a database for all departments.
C. Create a Windows-Authenticated login for each department.
D. Create a named instance of SQL Server 2000 for each department.
E. Map each domain users group to the processadmin role.
F. Map each domain users group to the db_datareader and db_datawriter database roles.
G. Grant each domain users group SELECT, INSERT, UPDATE, and DELETE permissions
on the database objects that are used by that department.

101. You are the administrator of a SQL Server 2000 computer. The server contains a database named
EmployeeData. Your company's human resources department uses this database, which contains
several tables as shown in the following table.

Table name Used for

Employees Employee names, addresses, departments, and base salaries
Bonuses Bonuses that have been paid to employees
Awards Awards that have been presented to employees

The human resources department wants to make employee names, addresses, and awards available to
anyone who has permission to access the server. However, the human resources department wants to allow
only users in the human resources department to access salary and bonus information. You need to allow
company users to access only the appropriate employee information. What should you do?

A. Create a trigger on the Salary column of the Employees table.

B. Create a stored procedure that retrieves all of the data from the Employees and Awards
tables, inserts the data into a temporary table, and then grants the current user SELECT
permissions on the temporary table.
Grant the Guest database user EXECUTE permissions on the stored procedure.
C. Move the sensitive information out of the Employees table into a new table.
Grant the Guest database user SELECT permission on the Employees table.
D. Create a view that contains the name, address, and award information.
Grant the Guest database user SELECT permissions on the view.

102. You are the administrator of a SQL Server 2000 computer. The server is used to store sales and
product information for your company. A client/server application accesses the server by
activating an application role named SalesAppRole. Several users report that they are unable to
use the client/server application. You test the application and discover that it cannot log on to the
server. You examine your security audit logs, which contain the information below:

EventClass TextData LoginName

Audit Object Permission Select * FROM [customers] Martha
Audit Object Permission Select [productname] * [productname] FR… Martha
Audit Object Permission Byron
Audit Object Permission -- sp_Mshasdbaccess select name as ‘… Byron
Audit Object Permission UPDATE [Customers] SET [CompanyName] =… Martha
Audit Object Permission UPDATE [Customers] SET [CompanyName] =… Martha
Audit Object Permission -- ‘sp_approlepassword’ was found in… Byron
Audit App Role Change Pa… Byron
Audit Object Permission -- ‘sp_approlepassword’ was found in… Byron
Audit Object Permission -- ‘sp_approlepassword’ was found in… Byron
Audit Object Permission -- ‘sp_approlepassword’ was found in… Byron
Audit Object Permission UPDATE [Customers] SET [CompanyName] =… Maria
Audit Object Permission UPDATE [Customers] SET [CompanyName] =… Maria
Audit Object Permission -- ‘sp_setapprole’ was found in the t… Byron

You need to enable the application to log on to the server. What should you do?

A. Configure the database permissions to deny Martha access to the database.

B. Reset the password on the SalesAppRole application role.
C. Reset the permissions on the SalesAppRole application role.
D. Add the SalesAppRole application role to the db_owner database role.
E. Remove Byron’s database user account.
F. Enable encryption on the password for the SalesAppRole application role.

103. You are the administrator of a SQL Server 2000 computer. The server is used to store confidential
company information. Company policy requires that every action and change of permission on the
server be logged. Policy also requires that the server can run only when logging is enabled. You
need to configure the server to comply with this policy. What should you do?

A. Use SQL Profiler to capture security events and audit events.

Make sure that file rollover is enabled.
B. On the Security tab of the Server Properties dialog box, set the audit level to All.
C. Configure the server to use Windows Authentication.
Make sure the Windows security log does not overwrite events.
D. Set the c2 audit mode option to 1.
Restart the MSSQLServer service.

104. You are the administrator of a SQL Server 2000 computer. The server is a member of a Windows
NT domain and is configured for Windows Authentication. The server contains a database that
stores contact information for public officials in your region. These officials need to access the
database by means of the Internet by using the Guest login. However, some users report that they
cannot connect to the server by means of the Internet. You need to allow anyone access to the
server by means of the Internet. What should you do?

A. Assign the Guest login a blank password.

B. Delete the Guest login and create a new login that is mapped to the Guest domain user
account.
C. Create a database user named Anonymous and assign the user the appropriate database
permissions.
D. Configure the server for Mixed Mode authentication.

105. You are the administrator of a SQL Server 2000 computer at a doctor's office. The server contains
a database named Medical. The database stores patient and treatment information and is
configured as shown below:

See http://www.cheet-sheets.com/228/image18.jpg

The office accountant needs access to the total number of treatments that have been performed in the
office. The accountant should not have access to any other patient data and should not be able to change
any information in the database. The accountant has a SQL Server Authenticated login and database user
account named AccountUser. You need to make the appropriate information available to the accountant,
while protecting the other information in the database. Which batch of Transact-SQL statements should
you execute?

A. CREATE VIEW TotalTreatments AS

SELECT COUNT(PatientID) AS Total FROM dbo.Patients
 GO
GRANT ALL ON TotalTreatments TO AccountUser
B. CREATE VIEW TotalTreatments AS
SELECT COUNT(*) AS Total FROM dbo.Patients
GO
GRANT ALL ON TotalTreatments TO AccountUser
C. CREATE PROCEDURE TotalTreatments AS
SELECT COUNT(*) AS Total FROM dbo.Patients
GO
GRANT ALL ON TotalTreatments TO AccountUser
D. CREATE PROCEDURE TotalTreatments AS
SELECT COUNT(PatientID) AS Total FROM dbo.Patients
GO
GRANT ALL ON TotalTreatments TO AccountUser

106. You are the administrator of a SQL Server 2000 computer. One of the databases on the server
contains a table named SalesData. This table stores information about sales orders that is used to
create sales reports and to calculate commissions for sales personnel. The permissions on the
SalesData table are configured as shown below:

Users/Database Role Select Insert Update Delete

Reporters Grant Blank Blank Blank
SalesManagers Grant Grant Grant Grant
Salespeople Blank Grant Blank Blank
Guest Blank Blank Blank Blank

Sales managers monitor the entries made by sales personnel and correct any errors. Because monitoring is
time-consuming, the sales department will hire several assistants. The sales managers will make necessary
corrections on hard copy, and then the assistants will enter the changes in the table. However, the assistants
must not be allowed to delete any data from the table. You need to configure permissions on the table to
support these requirements. You also want to reduce the amount of time needed to manage these
permissions. What should you do?

A. Create a database user account for each assistant.

Add each database user account to the SalesManagers database role.
Revoke DELETE permissions for the SalesManagers database role.
B. Create a single database user account for all assistants.
Add the database user account to the SalesManagers database role.
Revoke DELETE permissions for the SalesManagers database role.
C. Create a database user account for each assistant.
Create a database role named SalesAssistants. Grant SELECT, UPDATE, and INSERT
permissions to the SalesAssistants database role.
Add each database user account to the SalesAssistants database role.
D. Create a database user account for each assistant.
Create a database role named SalesAssistants. Add the role to the SalesManagers
database role.
Add each database user account to the SalesAssistants database role.
Revoke DELETE permissions for the SalesAssistants database role.

107. You are the administrator of a SQL Server 2000 computer. The server belongs to a Microsoft
Windows NT domain named DOMAIN1. The server contains a database named SalesData. Julia
is a sales representative who uses the database. You create a login named DOMAIN1\Julia and
manually configure the permissions necessary for her to perform her job.

Julia is temporarily reassigned to the marketing department. During her assignment in the marketing
department Julia must not be able to access the server. However, you must restore her access when she
returns to the sales department. You need to configure the appropriate database permissions for Julia.
Which Transact-SQL statement should you execute?

A. REVOKE ALL TO SalesData FROM 'Julia'

B. EXEC sp_revokeaccess 'Julia'
C. EXEC sp_denylogin 'Julia'
D. EXEC sp_droplogin 'Julia'

108. You are the administrator of a SQL Server 2000 computer named ProdSql1. The server is a
member of a Microsoft Windows NT domain named CORP. ProdSql1 contains a database named
Finance. Your company purchases a client/server application that will use a database named
Marketing. This database will be stored on ProdSql1. Maria, Bruno, and Stefan will use the new
application. These users belong to a domain user group named Marketers. In the future, several
additional users who will also use the new application will be added to the Marketers group. The
logins on ProdSql1 are configured as shown below:

Name Type Server Access

BUILTIN\Administrators Windows Permit
Sa Standard Permit
CORP\Martha Windows Permit
CORP\Byron Windows Permit
CORP\Sal Windows Permit
Marketers Standard Permit

You need to allow Martha, Byron, and Sal access to the Marketing database as quickly as possible. Which
batch of Transact-SQL statements should you execute?

A. USE Marketing
GO
EXEC sp_grantdbaccess 'CORP\Martha'
EXEC sp_grantdbaccess 'CORP\Byron'
EXEC sp_grantdbaccess 'CORP\Sal'
B. EXEC sp_grantlogin 'CORP\Marketers'
GO
USE Marketing
EXEC sp_grantdbaccess 'CORP\Marketers'
C. USE Marketing
GO
EXEC sp_grantdbaccess 'Marketers'
D. EXEC sp_addlogin 'CORP\Marketers'
GO
USE Marketing
EXEC sp_grantdbaccess 'CORP\Marketers'

109. You are the administrator of a SQL Server 2000 computer. Your company has client computers
that are running Microsoft Windows 2000 Professional and UNIX. All client computers require
access to the server. Users with domain accounts will access the server by means of the company
LAN, and external users will access the server by means of the Internet. You need to configure
the server so that all authorized users can connect to it. What should you do? (Each correct answer
presents part of the solution. Choose all that apply.)

A. Configure Windows Authentication.

B. Configure Mixed Mode Authentication.
C. Create a login for the Domain Users group.
D. Create a login for the Guest domain user account.
E. Create a login for each UNIX user, and set a password for each login.
F. Create a login for each external user, and set a blank password for each login.
G. Create a login named Guest, and set a blank password for the login.

110. You are the administrator of a SQL Server 2000 computer. One of the databases on the server
contains a table named CustomerInfo. This table is used to store information about your
company's customers. You want some users to be allowed to modify only the table's Address
column. Other users should be allowed to modify only the table's CreditCard column. Neither
group of users has permission on the CustomerInfo table. You need to make sure that only
authorized users make changes to the Address and CreditCard columns. What should you do?

A. Create a stored procedure and require users to enter information by means of the stored
procedure.
B. Create a user-defined function and require users to enter information by means of the
function.
C. Create a rule and apply it to the Address and CreditCard columns.
D. Create a constraint and apply it to the Address and CreditCard columns.

111. You are the administrator of a SQL Server 2000 computer named DataSvr. The server is a
member of a Microsoft Windows NT domain. DataSvr is currently configured for Windows
Authentication and contains logins for several domain user groups. The server is configured to use
the Named Pipes Net-Library. Your company purchases five UNIX client computers. You want
to allow employees who will be using these computers to access DataSvr. What should you do?

A. Configure DataSvr for Mixed Mode authentication.

Add a login for a domain user group that includes the five UNIX computers.
B. Configure DataSvr for Mixed Mode authentication.
Add a login for each of the five UNIX users.
C. Install the TCP/IP Net-Library.
Add the five UNIX users to a domain user group that has access to DataSvr.
D. Install the TCP/IP Net-Library.
Add a login for each of the five UNIX users.

112. You are the administrator of an SQL Server 2000 computer named CorpSQL. The server is a
member of a Microsoft Windows NT domain named CORPORATE. Franz maintains a local user
account on CorpSQL and is a member of the local Power Users group. He requests access to the
SQL Server databases that are stored on CorpSQL. You need to allow Franz to access CorpSQL.
Which Transact-SQL statement should you execute?

A. EXEC sp_grantlogin Franz

B. EXEC sp_grantlogin CORPORATE\Franz
C. EXEC sp_grantlogin Power Users\Franz
D. EXEC sp_grantlogin CorpSQL\Franz

113. You are the administrator of a SQL Server 2000 computer. The server is a member of a Microsoft
Windows NT domain named CORPHQ. Several users need access to the server. These users are
members of a domain user group named Receptionists. The Receptionists group also contains
users who should not have access to the server. These users are also members of a domain user
group named TempEmployees. You need to allow only the appropriate users to access the server.
Which Transact-SQL statement should you execute?

A. EXEC sp_addlogin 'CORPHQ\Receptionists'

EXEC sp_droplogin 'CORPHQ\TempEmployees'
B. EXEC sp_addlogin 'CORPHQ\TempEmployees'
EXEC sp_addlogin 'CORPHQ\Receptionists'
REVOKE ALL ON Master FROM CORPHQ\TempEmployees
C. EXEC sp_addlogin 'CORPHQ\Receptionists'
EXEC sp_denylogin 'CORPHQ\TempEmployees'
D. EXEC sp_addlogin 'CORPHQ\TempEmployees'
EXEC sp_addlogin 'CORPHQ\Receptionists'
GRANT ALL ON Master TO CORPHQ\Receptionists

114. You are the administrator of a SQL Server 2000 computer. The server is used to store confidential
information about company employees. You suspect that at least one user is accessing the
employee information database without company approval.

You believe the access is occurring after business hours. You need to discover whether users are accessing
the database without approval and which users are accessing the data. You also want to minimize any
additional overhead on the server during business hours. What should you do?

A. Use SQL Profiler to capture security events and audit events.

B. On the Security tab of the Server Properties dialog box, set the audit level option to All.
C. Configure the server to use Windows Authentication.
Make sure the Windows security log does not overwrite events.
D. Set the c2 audit mode option to 1.
Restart the MSSQLServer service.

115. You are the administrator of a SQL Server 2000 computer. The server is used to store information
for your company's finance department. The permissions on the FinanceInfo database are
configured as shown below:

Users/Database Role Create Table Create View Create SP

Accountants Deny Blank Deny
Administrators Grant Blank Blank
Public Blank Blank Blank
AppRole1 Blank Blank Blank
Managers Grant Grant Grant
Salespeople Blank Blank Blank
Byron Blank Deny Blank
Lilly Grant Blank Blank

Byron is a member of the Accountants database role. He needs to add a new view to the database. You
need to configure the database to allow Byron to add a new view. Which two actions should you take?
(Each correct answer represents part of the solution. Choose two.)

A. Add Byron to the Managers database role.

B. Add Byron to the Administrators database role.
C. Revoke the CREATE TABLE permissions for Byron.
D. Revoke the CREATE PROCEDURE permissions for Byron.
E. Revoke the CREATE VIEW permissions for Byron.

116. You are the administrator of a SQL Server 2000 computer that contains multiple databases. One
of these databases is named CorpSales. Your company hires a new employee named Helene. Part
of Helene's job will be to create new tables in the CorpSales database. However she should not
have access to the other databases on the server. You create a SQL Server login named Helene.
Helene reports that she can log on to the server but is unable to access the CorpSales database.
You need to allow Helene access to the CorpSales database. What should you do?

A. Create a new database user in the CorpSales database.

Map the database user to Helene's login.
Grant CREATE TABLE permissions to the database user.
B. Create a new database role in the CorpSales database.
Add Helene's login as a member of the role.
Grant CREATE TABLE permissions to the database role.
C. Change the server's authentication mode to Windows Authentication.
D. Add Helene's login to the processadmin server role on the server.

117. You are the administrator of a new multiprocessor SQL Server 2000 computer. You are migrating
data from other database management systems to this server. You need to load data from two data
files into a new table on the server. The table is empty and has no indexes. You want to complete
the data load as quickly as possible. What should you do? (Each correct answer represents part of
the solution. Choose all that apply.)

A. Apply a bulk update lock to the table.

B. Use separate client connections to load the files into the table simultaneously.
C. Create a clustered index on the table.
D. Specify the Full Recovery model for the database.
E. Specify the Bulk-Logged Recovery model for the database.

118. You are the administrator of a SQL Server 2000 computer in your company's personnel
department. Employee data is stored in a SQL Server 2000 database. A portion of the database
schema is shown below:

See http://www.cheet-sheets.com/228/image19.jpg

You want to create a text file that lists these data columns in the following format:
title, firstName, lastName, workPhone, positionName, departmentName.

You want to create the text file as quickly as possible. You do not expect to re-create this file, and you
want to avoid creating new database objects if possible. What should you do?

A. Use the bcp utility to export the data from each table to a separate text file.
Use ‘format files’ to select the appropriate columns.
Merge the data from each text file into a single text file.
B. Create a view that joins data from all three tables.
Include only the columns you want to appear in the text file.
Use the bcp utility to export the data from the view.
C. Create a SELECT query that joins the data from the appropriate columns in the three
tables.
Add an INTO clause to the query to create a local temporary table.
Use the bcp utility to export the data from the local temporary table to a text file.
D. Create a SELECT query that joins the data from the appropriate columns in the three
tables.
Add an INTO clause to the query to create a global temporary table.
Use the bcp utility to export the data from the global temporary table to a text file.

119. You are the administrator of an SQL Server 2000 computer. The server contains a database that
stores financial data. You want to use Data Transformation Services (DTS) packages to import
numeric data from other SQL Server computers. The precision and scale values of this data are
not defined consistently on the other servers. You want to prevent any loss of data during the
import operations. What should you do?

A. Use the ALTER COLUMN clause of the ALTER TABLE statement to change data types
in the source tables. Change the data types so that they will use the lowest precision and
scale values of the data that will be transferred.
B. Use the ALTER COLUMN clause of the ALTER TABLE statement to change the data
types in the destination tables. Change the data types to reflect the highest precision and
scale values involved in the data transfer.
C. Set a flag on each DTS transformation to require an exact match between source and
destination columns.
D. Set the maximum error count for each DTS transformation task equal to the number of
rows of data you are importing.
Use an exception file to store any rows of data that generate errors.
E. Write a Microsoft ActiveX script for each DTS transformation.
Use the script to recast data types to the destination's precision and scale values.

120. You are the administrator of a SQL Server 2000 computer. You are creating a database named
RetailImport to use as an intermediate data store for a data warehouse. Each night you must
import daily data into the database from SQL Server 2000 computers in 120 retail locations. After
the data is moved into the data warehouse, the tables are truncated.

You want to configure the data import process so that you minimize the time needed to import the sales
data and administer the database. What should you do?

A. Use the Simple Recovery model and the FOR LOAD option to create the database.
Create a Data Transformation Services (DTS) package that uses the BULK INSERT
statement to copy the sales data.
B. Index the foreign key fields in the child tables.
C. Create a Data Transformation Services (DTS) package that uses a Data Driven Query
task to copy the sales data.
D. Collect the sales data by using a distributed transaction that inserts the data from all 120
retail locations into the RetailImport database as a single transaction.
E. On the servers in the retail locations, create stored procedures that submit the updates to
the RetailImport database each night as SQL-XML updategrams.

121. You are the database administrator for a financial services company. Employees enter data 24
hours a day into a SQL Server 2000 database. These employees report slower response times
when new account information is gathered from branch offices and added to the database. You
currently use the following BULK INSERT statement to add the account information.

BULK INSERT finance.dbo.customers

FROM 'd:\bulk\accts143_10142000.txt'
WITH DATAFILETYPE = 'char',
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
TABLOCK

You want to ensure that response time does not slow when new account information is added to the
database. What should you do?

A. Drop the indexes on the Customers table before the data load, and then re-create the
indexes after the data load is complete.
B. Remove the TABLOCK option from the BULK INSERT statement.
C. Add the BATCHSIZE option to the BULK INSERT statement, and then set the option
equal to 10 percent of the number of rows to be loaded.
D. Add the ROWS_PER_BATCH option to the BULK INSERT statement, and then set the
option equal to 10 percent of the number of rows to be loaded.

122. You are the administrator of an SQL Server 2000 computer. You create a Data Transformation
Services (DTS) package. The package is stored as a local package on the server. The package
 exports data from an online transaction processing (OLTP) database system to an online analytical
processing (OLAP) database system that is located on a second SQL Server 2000 computer. You
want the package to execute automatically each evening at 8:00 P.M. What should you do?

A. Use SQL Server Enterprise Manager to create a new job

Create a CmdExec job step that runs the dtsrun utility
Schedule the job to run each night at 8:00 P.M.
B. Use SQL Server Enterprise Manager to create a new job
Create a Transact-SQL job step to query data from the OLTP database
Create a second Transact-SQL job step to launch the DTS package
Schedule the job to run each night at 8:00 P.M.
C. Create a batch file that runs the dtsrun utility
Use the Microsoft Windows Task Scheduler to run the job each night at 8:00 P.M.
Configure the Windows Task Scheduler to use the local system account
D. Move the DTS package to the repository
Configure the SQLServerAgent service on the OLAP database server to update the
package each night at 8:00 P.M.

123. You are a database administrator in the Los Angeles branch office of a specialty foods supplier. A
mainframe database at the headquarters contains all company data. Each branch office contains a
SQL Server 2000 computer that imports regional data from the mainframe database. The server in
Los Angeles contains a Data Transformation Services (DTS) package that uses OLE DB to
connect to the company's mainframe database. The DTS package extracts and transforms data
about buyers and products for that region. The DTS package then writes the data to the SQL
Server database in Los Angeles.

You need to configure a SQL Server computer for a new branch office in Sydney. You want to copy the
Los Angeles package and modify it so that it writes data to the SQL Server database in Sydney. You use
the DTS Designer to modify the DTS package so that it imports regional data from the mainframe database
to the server in Sydney. The modified DTS package runs successfully on the server in Los Angeles. You
save this DTS package to the server in Sydney, but the server in Sydney cannot connect to the mainframe
database. You want to enable the server in Sydney to connect to the mainframe database. What should you
do?

A. Change the connection properties in the DTS package so that the package uses new login
credentials to connect to the mainframe database.
B. Modify the workflow in the DTS package so that the server in Sydney is included.
C. On the server in Sydney, install an OLE DB provider for the mainframe database.
D. On the server in Sydney, delete and then re-create the DTS package.

124. You are the administrator of a SQL Server 2000 computer. The server contains your company's
order processing database. Two-hundred operators take orders by telephone 24 hours a day.
Three-hundred data entry personnel enter data from orders received by mail. To ensure that order
data will not be lost, your company's disaster recovery policy requires that backups be written to
tape. Copies of these tapes must be stored at an off-site company location. Orders must be entered
into the database before they can be filled. If the server fails, you must be able to recover the order
data as quickly as possible. You need to create a backup strategy that meets the company
requirements and minimizes server workload. Which two actions should you take? (Each correct
answer represents part of the solution. Choose two.)

A. Perform a combination of full database and filegroup backups.

B. Perform a combination of full database and file backups.
C. Perform a combination of full database, differential, and transaction log backups.
D. Back up the data to a local tape drive.
E. Back up the data to a network share, and then use enterprise backup software to write the
disk backups to tape.

125. You are the administrator of a SQL Server 2000 computer. The server contains a database named
CustomerManagement that tracks customer requests for product information. The
CustomerManagement database has two filegroups. The PRIMARY filegroup stores current
transactional data, and the SECONDARY filegroup stores historical data. Your backup strategy
includes full database and transactional log backups. A portion of the header information for the
current week's backups is shown below:

BackupName BackupType BackupFinishDate

---------------------------- --------------- ---------------------------
custmgmt_full_20000625 1 2000-06-25 21:57:03.000
custmgmt_tlog_20000626_1 2 2000-06-26 10:34:21.000
custmgmt_tlog_20000626_2 2 2000-06-26 14:36:32.000
custmgmt_tlog_20000626_3 2 2000-06-26 18:42:11.000
custmgmt_tlog_20000627_1 2 2000-06-27 10:33:37.000
custmgmt_tlog_20000627_2 2 2000-06-27 14:35:18.000

On June 27, 2000, at 4.18 PM, an application developer accidentally deletes the current customer requests
and immediately notifies you of the problem. You want to recover as much data as you can as quickly as
possible to minimize server downtime. What should you do?

A. Back up the transaction log.

Restore the PRIMARY filegroup to a new location.
Restore subsequent transaction logs in sequence.
Use the STOPAT option to restore the final transaction log backup.
Import the deleted data from the new location.
B. Back up the transaction log.
Restore the PRIMARY filegroup to the existing database.
Restore subsequent transaction logs in sequence.
Use the STOPAT option to restore the final transaction log backup.
C. Back up the transaction log.
Restore the entire database to a new location.
Restore subsequent transaction logs in sequence.
Use the STOPAT option to restore the final transaction log backup.
Import the deleted data from the new location.
D. Restore the PRIMARY filegroup to the existing database.
Restore subsequent transaction logs in sequence.

126. You are the administrator of a SQL Server 2000 computer. The server contains a database that has
the torn page detection database option enabled. Backups of the database are made daily. The
server loses power for one minute. When power is restored, torn pages are detected. You notice in
SQL Server Enterprise Manager that the database is marked suspect. You need to correct the
problem. What should you do?

A. Execute the DBCC CHECKDB statement, and then specify the PHYSICAL_ONLY
option.
B. Execute the DBCC CHECKDB statement, and then specify the REPAIR_REBUILD
option.
C. Execute the sp_resetstatus stored procedure.
D. Restore the suspect database from backups.

127. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Sales. You perform full database backups every two days. You also run regular database
 consistency checks on the server. The most recent check of the Sales database returns the
following message:

CHECKDB found 0 allocation errors and 9 consistency errors in the table 'Orders'
(object ID 214575782).

You want to correct the data integrity errors while minimizing the amount of data lost. What should you
do?

A. Disconnect users from the Sales database.

Enable the ‘single user’ database option.
Execute the DBCC CHECKTABLE statement for the Orders table, and specify the
REPAIR_REBUILD option.
B. Disconnect users from the Sales database.
Enable the ‘DBO use only’ database option.
Execute the DBCC CHECKTABLE statement for the Orders table, and specify the
REPAIR_REBUILD option.
C. Disconnect users from the Sales database.
Execute the RESTORE DATABASE statement for the Sales database
D. Execute the DBCC CLEANTABLE statement for the Orders table.
E. Execute the sp_table_validation stored procedure for the Orders table.

128. You are the administrator of two SQL Server 2000 computers for an online retailer. One of the
SQL Servers records customer buying preferences and demographic data. A DTS package
populates a data warehouse with this information and a second SQL server uses Analysis Services
to create multidimensional cubes.

The Data Transformation Services (DTS) package is stored in the Meta Data Services repository on the
second server. You want to maximize the amount of lineage data that can be recovered if a data file is
damaged or lost. Which two actions should you take? (Each correct answer represents part of the solution.
Choose two.)

A. Use the Full Recovery model for the staging database.

B. Use the Full Recovery model for the msdb database.
C. Back up the transaction log in the staging database by using the NO_TRUNCATE
option.
D. Back up the transaction log in the msdb database by using the NO_TRUNCATE option.
E. Back up the multidimensional data cube.
F. Save the DTS package as a file.

129. You are the administrator of a SQL Server 2000 computer at your company's warehouse. All
product orders are shipped from this warehouse. Orders are received at 30 sales offices. Each
sales office offers a range of products specific to its region. Each sales office contains one SQL
Server 2000 computer. These servers connect to the warehouse through dial-up connections as
needed, typically once a day. Each sales office needs data pertaining only to its region. You need
to replicate inventory data from the server at the warehouse to the servers at the sales offices. You
want to minimize the amount of time needed to replicate the data. Which three actions should you
take? (Each correct answer represents part of the solution. Choose three.)

A. Create one publication for each Subscriber.

B. Create one publication for all Subscribers.
C. Enable horizontal filtering.
D. Enable vertical filtering.
E. Use pull subscriptions.
F. Use push subscriptions.
 130. You are the administrator of a SQL Server 2000 computer. Your company purchases an
accounting application from a vendor. The application stores its data in a database named
Accounting on the server. The tables in this database contain columns that function as primary
keys, but PRIMARY KEY and FOREIGN KEY constraints are not used. You need to replicate
data from this database to another SQL Server computer. This server will use the replicated data
to generate reports. Most reports will run each month, but the accounting department needs to
have the ability to run reports at any time. Reports should be accurate through the last full
working day. You cannot make any changes to the database, but you need to implement
replication. Which two actions should you take? (Each correct answer represents part of the
solution. Choose two.)

A. Implement merge replication.

B. Implement snapshot replication.
C. Implement transactional replication.
D. Schedule replication to run continuously.
E. Schedule replication to run during off-peak hours.

131. You are the administrator of an SQL Server 2000 computer. The server contains a database named
Sales. The database is configured as shown below:

See http://www.cheet-sheets.com/228/image20.jpg

In the last six months, the database has grown by 4 GB. Users report that query response time has slowed.
The options set up on the database are configured as follows:

Restrict Access Read-only Recovery Model ANSI NULL default Recursive triggers
Unchecked Unchecked Full Unchecked Unchecked

Auto update statistics Torn page detection Auto close Auto shrink
Checked Checked Unchecked Unchecked

Auto create statistics Use quoted identifiers Compatibility Level

Checked Unchecked 80

You want to accelerate query response time. What should you do?

A. Update the database statistics

B. Add indexes to the foreign key fields
C. Truncate the transaction log
D. Run the Database Maintenance Plan Wizard
E. Drop primary keys from all tables

132. You are the administrator of a SQL Server 2000 computer. The server contains a database that is
heavily indexed and that the company users query extensively. The database has grown and query
response time has slowed. The database is stored in a single data file. You want to accelerate
query response time. What should you do?

A. On a new hard disk, create a new filegroup.

Drop the existing nonclustered indexes, and then re-create them on the new filegroup.
B. On a new hard disk, add a new file to the PRIMARY filegroup.
Drop the existing nonclustered indexes, and then re-create them on the PRIMARY
filegroup.
C. On the existing hard disk, create a new filegroup.
Drop the existing nonclustered indexes, and then re-create them on the new filegroup.
D. On the existing hard disk, add a new file to the PRIMARY filegroup.
Drop the existing nonclustered indexes, and then re-create them on the PRIMARY
filegroup.

133. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Sales. The database has a group of tables that analysts use to examine sales trends. The database
options are set to their default values, and the tables are configured as shown below:

See http://www.cheet-sheets.com/228/image21.jpg

Analysts who use the database report that query performance has become slow. You analyze the clustered
primary key on the Invoices table and receive the following results:

DBCC SHOWCONTIG scanning 'Invoices' table...

Table: 'Invoices' (21575115); index ID: 1, database ID: 6
TABLE level scan performed.

- Pages Scanned................................: 200

- Extents Scanned..............................: 50
- Extent Switches..............................: 40
- Avg. Pages per Extent........................: 4.0
- Scan Density [Best Count:Actual Count].......: 60.00% [3:5]
- Logical Scan Fragmentation...................: 0.00%
- Extent Scan Fragmentation....................: 40.00%
- Avg. Bytes Free per Page.....................: 146.5
- Avg. Page Density (full).....................: 98.19%

- DBCC execution completed. If DBCC printed error messages, contact your system
administrator.

You want to improve performance of queries that join tables to the Invoices table. What are three possible
Transact-SQL statements you can execute to achieve this goal? (Each correct answer represents a complete
solution. Choose three.)

A. DBCC UPDATEUSEAGE ('Sales','Invoices','PK_Invoices')

B. CREATE UNIQUE CLUSTERED INDEX PK_Invoices On
Invoices(InvoiceID) WITH DROP_EXISTING
C. DBCC INDEXDEFRAG ('Sales','Invoices','PK_Invoices')
D. DBCC DBREINDEX (Sales.dbo.Invoices, 'PK_Invoices')
E. UPDATE STATISTICS 'Invoices'
F. DBCC CHECKALLOC (Sales, REPAIR_FAST)

134. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Sales. The Sales database is configured as shown below:

See http://www.cheet-sheets.com/228/image22.jpg

The Sales database stores point-of-sale transaction data. The data is later imported into another database for
analysis. Recently, users have reported slow response times whenever they enter point-of-sale information.
You examine the indexes on the Invoices table that are configured as shown in the Index Description
exhibit.

index_name index_description index_keys

IX Customers PK nonclustered located on PRIMARY CustomerID
IX SalesPerson PK nonclustered located on PRIMARY SalesPersonID
PK Invoices clustered, unique, primary key located on PRIMARY InvoiceID
You want to accelerate database response time when users insert new point-of-sale information. What
should you do?

A. Drop IX_Customers_FK and IX_SalesPerson_FK.

B. Change IX_Customers_FK to a clustered index.
C. Change IX_SalesPerson_FK to a clustered index.
D. Change PK_Invoices to a nonclustered index.

135. You are the administrator of a SQL Server 2000 database. The database was configured with all
default options. You drop indexes to facilitate a large import of data, and rebuild them after the
import is complete. Users report that response times from a stored procedure have become slower.
In the stored procedure, you examine the following code.

CREATE PROCEDURE GetCustomer

( @CustomerID Int )
AS
SELECT FirstName, LastName, Address1, Address2, City,
State, Zip, MAX(PurchaseDate) AS LastPurchase
FROM Customers C
JOIN Addresses A ON A.AddressID = C.AddressID
JOIN Purchases P ON P.CustomerID = C.CustomerID
WHERE C.CustomerID = @CustomerID
GROUP BY FirstName, LastName, Address1, Address2, City, State, Zip

You want to improve the performance of the procedure by using the least amount of administrative effort.
What should you do?

A. Recompile the stored procedure.

B. Update the database statistics.
C. Check the consistency of the database and repair errors.
D. Create a view and modify the stored procedure to use the view.

136. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Inventory. Users report that the following query responds slowly:

Select Parts.SKU AS SKU, Parts.Description as [Part Description],

Locations.Description AS Location
FROM PartsLocations INNER JOIN Parts ON PartsLocations.LocationID = Locations.LocationID AND
Parts.LocationID = Locations.LocationID

You examine the indexes in the PartsLocations table. The indexes are configured as shown below:

Index_name index_description index_keys

IX_PartsLocations_LocationID_PK nonclustered located on PRIMARY LocationID
IX_PartsLocations_PartID_PK nonclustered located on PRIMARY PartID
PK_PartsLocations clustered, unique, primary… PartID, LocationID

You also examine the contiguity of the table and receive the following results:

DBCC SHOWCONTIG scanning 'PartsLocations' table...

Table: 'PartsLocations' (1); index ID: 1, database ID: 6
TABLE level scan performed.

- Pages Scanned................................: 3000

- Extents Scanned..............................: 750
- Extent Switches..............................: 502
- Avg. Pages per Extent........................: 4.0
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation...................: 33.33%
- Extent Scan Fragmentation....................: 33.33%
- Avg. Bytes Free per Page.....................: 1166.0
- Avg. Page Density (full).....................: 85.77%

You need to improve the performance of the query. What should you do?

A. Rebuild the PK_PartsLocations index.

B. Rebuild the IX_PartsLocations_PartsID index.
C. Rebuild the IX_PartsLocations_LocationID index.
D. Rebuild the IX_PartsLocations_PartsID_LocationID index.

137. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Sales. The database is configured as shown below:

See http://www.cheet-sheets.com/228/image23.jpg

Analysts examine customer buying patterns and frequently use the following query:

SELECT Customers.CustomerID,
Invoices.InvoiceID,
Invoices.InvoiceDate,
OrderDetail.Quantity, Products.BKU
FROM Customers
INNER JOIN Invoices ON Customers.CustomerID = Invoices.CustomerID
INNER JOIN OrderDetail ON Invoices.InvoiceID = OrderDetail.InvoiceID
INNER JOIN Products ON OrderDetail.ProductID = Products.ProductID
WHERE CustomerID = ?

You analyze the indexes in the Invoices table. The indexes are configured as shown below:

Index_name index_description index_keys

IX_Customers_PK nonclustered located on PRIMARY CustomerID
IX_SalesPerson_PK nonclustered located on PRIMARY SalesPersonID
PK_Invoices clustered, unique, primary key located on PRIMARY InvoiceID

You want to optimize the database schema so that it responds quickly to the analysts' queries. Which two
actions should you take? (Each correct answer represents part of the solution. Choose two.)

A. Re-create the PK_Invoices index as a nonclustered index

B. Re-create the IX_Customers_FK index as a clustered index
C. Re-create the IX_SalesPerson_FK index as a clustered index
D. Create a composite index on CustomerID fields and SalesPersonID fields
E. Create a composite index on InvoiceID fields and CustomerID fields

138. You are the administrator of a SQL Server 2000 computer. The server contains a database named
MedicalRecords. Users access medical records by using the PatientID field. This field is the
clustered primary key for the Patients table. When users try to access medical records, the
database responds slowly. The database options are configured as follows:

Restrict Access Read-only Recovery Model ANSI NULL default Recursive triggers
Unchecked Unchecked Full Unchecked Unchecked
Auto update statistics Torn page detection Auto close Auto shrink
Unchecked Checked Unchecked Unchecked

Auto create statistics Use quoted identifiers Compatibility Level

Checked Unchecked 80

You want to accelerate query response time and minimize administrative overhead. How should you
reconfigure the database?

A. Create a SQL Server Agent job to execute the UPDATE STATISTICS statement, and
schedule the job to run weekly.
B. Select the ‘Auto update statistics’ check box.
C. Run the Database Maintenance Plan Wizard, and accept the default settings.
D. Rebuild the primary key as a nonclustered primary key.
E. Clear the Auto create statistics check box.

139. You are the administrator of a SQL Server 2000 database named Articles. A large city newspaper
uses this database to store its archived newspaper articles. Journalists query the database as part of
their research. You have created full-text indexes so that users can query the articles quickly. A
hardware failure forces you to restore the entire database from backup. After you restore the
database, the journalists report that they can no longer run queries without receiving errors. You
want to ensure that the journalists can query the database. What should you do?

A. Create a new full-text catalog for the Articles database

B. Rebuild the full-text catalog for the Articles database
C. Create a new full-text index on the table that holds the text for the articles
D. Repopulate the full-text index on the table that holds the text for the articles

140. You are the administrator of a SQL Server 2000 computer. The server contains your company's
investment-tracking database. Each day, 100 operators make approximately 5,000 changes to
customer investments. In addition, daily and monthly reports are created from the investment data.
Developers at your company need to optimize the database application. They need a sample of
database query activity to discover if they can speed up the transactions. The developers also want
to replay the sample on another SQL Server computer. You need to capture the sample, but you
want to minimize any increase to the workload of the server. What should you do?

A. Run SQL Profiler on a client computer.

Configure SQL Profiler to monitor database activity, and log data to a .trc file.
B. Run SQL Profiler on the server.
Configure SQL Profiler to monitor database activity, and log data to a database table.
C. Run System Monitor on a client computer.
Configure System Monitor to monitor database activity, and log data to a .blg file.
D. Start SQL Server from a command prompt.
Specify trace flag 1204 to enable verbose logging.

141. You are the administrator of a SQL Server 2000 computer. You are configuring a database for an
inventory application. The hard drive has two physical hard disks. Physical disk 0 has two drives,
C and D-both 4.5 GB in size. Physical disk 1 has two drives, E and F-both 4.5 GB in size.

The operating system files are located on drive C. Your database will store a maximum of 6 GB of data and
requires a maximum of 2 GB of disk space for the transaction log. You want to optimize database
performance. What should you do?

A. Add a 2-GB transaction log to drive D, a 3-GB data file to drive E, and a 3-GB data file
to drive F
B. Add a 1-GB transaction log to drive C, a 1-GB transaction log to drive D, a 3-GB data
file to drive E, and a 3-GB data file to drive F
C. Add a 1-GB transaction log to drive E, a 1-GB transaction log to drive F, a 3-GB data
file to drive E, and a 3-GB data file to drive F
D. Add a 2-GB transaction log to drive F, a 3-GB data file to drive D, and a 3-GB data file
to drive E

142. You are the administrator of an SQL Server 2000 computer. The server contains a database named
Inventory. The Inventory database has a table named StorageLocations that stores the location of
parts contained in your company's warehouses. The StorageLocations table is configured as
shown below:

Column Name Data Type Length

StorageLocationID (key) int 4
LocationDescription varchar 50
PartID int 4
UnitsStored bigint 8
DateStored datetime 8
TagID int 4

The LocationDescription field is usually described with a name 10 to 25 characters in length. The locations
never store more than 100,000 units of any given part. You want to modify the table's schema to save
space. You cannot lose any existing data. You want to do this by using the minimum amount of
administrative time and server resources. Which Transact-SQL statement should you execute?

A. ALTER TABLE [dbo].[StorageLocations]

ALTER FIELD [UnitsStored] [int] NOT NULL
B. ALTER TABLE [dbo].[StorageLocations]
[LocationDescription] [char] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
C. ALTER TABLE [dbo].[StorageLocations]
ALTER FIELD [UnitsStored] [smallint] NOT NULL
D. ALTER TABLE [dbo].[StorageLocations]
[LocationDescription] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL

143. You are the administrator of a SQL Server 2000 computer. The server contains a database that
stores inventory data. Another database administrator has created a number of scheduled jobs to
maintain the inventory database. Each weeknight the following jobs occur:

• A BULK INSERT job imports data at 10:00 P.M.

• Indexes are rebuilt at 10:15 P.M.
• Data integrity checks are performed at 10:30 P.M.
• A differential backup is performed at 10:45 P.M.
• A DBCC SHRINKDATABASE job runs at 11:00 P.M.

You notice that the final job often fails and returns the following error message:

"Server Msg 3140, Level 16, State 3. Could not adjust the space allocation for file 'inventory_data'."

You need to ensure that the final job runs without errors. What should you do?

A. Create a new DBCC SHRINKFILE job to shrink the inventory_data file. Schedule the
new job to run at the same time as the DBCC SHRINKDATABASE job.
B. Modify the DBCC SHRINKDATABASE job so that it uses the DBCC SHRINKFILE
statement to shrink each file individually.
C. Increase the time between the data integrity checks and the differential backup.
D. Increase the time between the differential backup and the DBCC SHRINKDATABASE
job.

144. You are the administrator of an SQL Server computer. Users report that the database times out
when they attempt to modify data. You use the Current Activity window to examine locks held in
the database:

Spid 41: Locked

Spid 42: Blocking
Spid 53: Blocked by 42

You need to discover why users cannot modify data in the database, but you do not want to disrupt normal
database activities. What should you do?

A. Use the spid 42 icon in the Current Activity window to discover which SQL statement is
being executed
B. Use the sp_who stored procedure to discover who is logged in as spid 42
C. Use SQL Profiler to capture the activity of the user who is logged in as spid 42
D. Use System Monitor to log the locks that are granted in the database

145. You are the administrator of a SQL Server 2000 computer. The server contains a database that
stores product data for your company. You need to execute an existing stored procedure that
examines prices for your company's products and can modify them if necessary. You execute the
stored procedure after business hours, but it does not complete. You execute the sp_lock stored
procedure and receive the following output:

spid dbid ObjId IndId Type Mode

------ ------ ----------- ------- ---- --------
61 7 0 0 DB S
64 7 0 0 DB S
72 7 0 0 DB S
72 7 2145623952 1 PAG IS
72 7 2145623952 0 TAB IS
72 7 2145623952 1 KEY S
78 7 0 0 DB S
78 7 2145623952 1 PAG IX
78 7 2145623952 0 TAB IX
78 7 2145623952 1 KEY X

You want the stored procedure to complete successfully. What should you do?

A. Execute the stored procedure, and specify the WITH RECOMPILE option.
B. Execute the DBCC FREEPROCCACHE statement.
C. Release the locks that are held by connections 61 and 64.
D. Release the locks that are held by connections 72 and 78.

146. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Orders, which is used to record customer orders for the products your company sells. Your
company's order volume exceeds 1 million orders per day. Each order uses approximately 100 KB
of space in the database. Users report that the database responds slowly when they enter new
orders. You use SQL Profiler to monitor the activity on the database and receive the data shown
below:

StartTime EventClass
2001-07-22 03:43:13 Data File Auto Grow
2001-07-22 03:43:15 Data File Auto Grow
2001-07-22 03:43:17 Data File Auto Grow
2001-07-22 03:43:19 Data File Auto Grow
2001-07-22 03:43:21 Data File Auto Grow

You need to modify the database to improve performance. What should you do?

A. Double the size of the data file

B. Configure the database to automatically grow by 10 percent
C. Separate the database into two physical files
D. Increase the size of the transaction log file

147. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Sales. Users report that the database times out when they attempt to perform queries. You use
System Monitor to analyze the server and receive the results shown below:

SQLServer:Access Methods
Full Scans/sec 0.000

SQLServer:Latches
Latch Waits/sec 14.241

SQLServer:Locks _Total
Lock Requests/sec 73402.577

SQLServer:SQL Statistics
Batch Requests/sec 61.020

You need to discover why database performance is slow. What should you do?

A. Use the current Activity window to discover who is holding locks in the database.
B. Use SQL Profiler to discover who is scanning tables in the database.
C. Measure disk activity by running the diskperf /y command, restarting, then using the %
Disk Time counter in System Monitor.
D. Use the sp_monitor stored procedure to measure the activity of the server.

148. You are the administrator of a SQL Server 2000 computer. The server contains a database named
MedicalRecords. The data processing department in your company adds new business rules to the
database's stored procedures. After the rules are added, users report that the medical records
application receives time-out errors. You use System Monitor to analyze the server and receive
the results shown below:

SQLServer:Access Methods
Full Scans/sec 0.000
Index Searches/sec 5601.116

SQLServer:Cache Manager Adhoc SQL Plans Procedure Plans

Cache Hit Ratio 99.810 .056

SQLServer:Locks _Total
Lock Requests/sec 9700.577

SQLServer:SQL Statistics
Batch Requests/sec 51.020
SQL Compilations/sec 51.020
SWL Re-Compilations/sec 0.000

You need to tune the stored procedures to improve performance. What should you do?

A. Add indexes to the fields that are used in the joins.

B. Remove the WITH COMPILE option from the stored procedure.
C. Remove the stored procedures and alter the application so that it uses ad hoc queries to
obtain data.
D. Change the transaction isolation level to Repeatable Read.

149. You are the administrator of a SQL Server 2000 computer. The SQLServerAgent service on the
server is configured as shown below:

Service startup account: System account

Mail profile: Empty
Error log file name: E:\Program Files\Microsoft SQL
Include execution trace messages: Unchecked
Write OEM file: Unchecked
Net Send Receipt: Empty

You configure two SQL Server Agent jobs to perform maintenance tasks. Each job copies files from the
server to a file share on a corporate file server. Both jobs fail when you attempt to execute them. You need
both jobs to execute successfully. What should you do?

A. Ensure that the file copy procedures are in an ActiveScripting job step
B. Make a member of the serveradmin server role the owner of both jobs
C. Configure the SQLServerAgent service to use a domain account
D. Grant the necessary permissions in the msdb database to the job owner's SQL Server
login

150. You are the administrator of a Microsoft Windows NT 4.0 computer that is running SQL Server
2000. The computer contains a database named Sales. The data file and transaction log for the
Sales database are located on a 9.1 GB hard disk. Both files are configured for automatic file
growth. The data file occupies 7 GB of disk space. You need to calculate when you will need to
add additional disk space. What should you do?

A. Configure System Monitor to log disk space utilization

B. Configure a database maintenance job to run the DBCC CHECKALLOC statement each
night and then to log the to the job history
C. Configure a SQL Server Agent job to execute the sp_spaceused stored procedure each
day and then to store the results of the procedure to a table in the database
D. Configure a SQL Server Agent job to execute the chkdsk utility each night at midnight
and then to send the results of the utility to the default operator in an e-mail message

151. You are the administrator of an SQL Server 2000 computer. You create a job to perform several
database maintenance tasks. This job must run whenever the server is restarted. You use SQL
Server Enterprise Manager to create a new schedule entry for the job, and specify that the job
should start automatically. You test the job by restarting the server. You check the job's history
and discover that the job did not run. You need the job to run automatically when the server
restarts. What should you do?

A. Create a stored procedure that uses sp_start_job to start the job whenever the
MSSQLService service starts
B. Add another schedule entry to the job, specifiy that the job start hourly, and then disable
the job
C. Change the job's category to Database Maintenance
D. Configure the SQLServerAgent service to start automatically

152. You are the administrator of eight SQL Server 2000 computers. You configure alerts on each
server so that various problem conditions will be reported if they occur. You create 18 operators
on one of the servers. You configure these operators by using the e-mail and pager contact
 information for the employees in your department. You configure the alerts on the server to send
e-mail messages and pager messages to the appropriate operators. You need to configure the same
18 operators on the other seven servers. You want to do this with a minimum amount of
administrative time. What should you do?

A. Detach the msdb database from the first server

Copy the database to the other seven servers
Attach the database on all eight servers
B. Use SQL Server Enterprise Manager on a single client computer to create the operators
on each server
C. Use SQL Server Enterprise Manager to script all of the operators
Use SQL Query Analyzer to run the script on the other seven servers
D. Use snapshot replication to replicate the msdb database from the first server to the other
seven servers

153. You are the administrator of a SQL Server 2000 computer. The server contains a database named
Sales. A company e-commerce application uses the Sales database to store sales transactions. The
company Web site has grown in popularity, and database utilization has increased. You need to
collect data about the utilization of server resources so that you can provide capacity planning.
You want to automate the collection process so that information is gathered as quickly as possible.
What should you do?

A. Configure System Monitor to collect data and store it in a SQL Server table
B. Create a SQL Server Agent job that executes the sp_statistics stored procedure daily and
places the results in a text file
C. Use SQL Profiler to trace server activity and store the results in SQL Server tables
D. Configure SQL Server alerts to store information in the Windows application event log

154. You are the administrator of an SQL Server 2000 computer. You create several jobs on the server
and schedule them to run during the evening. You configure an operator on the server and
configure the jobs to send an e-mail to this operator if a job fails for any reason. While testing the
jobs, you discover that e-mail messages are not being sent to the operator when a job fails. You
use SQL Server Enterprise Manager to confirm that SQL Mail is started. You need to ensure that
e-mail messages are sent to the operator whenever a job fails. What should you do?

A. Configure SQLAgentMail to use a valid MAPI profile

B. Configure SQL Mail to use a mailbox that delegates permissions to the operator
C. Use the net send notification method to send the operator e-mail messages
D. Create a job that executes the xp_startmail stored procedure
Configure the job to run whenever SQL Server 2000 starts

155. You are the administrator of a SQL Server 2000 computer named CorpSQL. You configure
several SQL Server Agent jobs to perform automated maintenance tasks on CorpSQL. The jobs
execute properly for several weeks, and then stop executing. You discover that the
SQLServerAgent service has stopped. You attempt to start the SQLServerAgent service. The
service does not start. In the Microsoft Windows application event log you discover the following
error message:

"SQLServerAgent could not be started (reason: Unable to connect to server 'CORPSQL', SQLServerAgent
cannot start)".

You verify that the MSSQLServer service is running. You need to start the SQLServerAgent service and
execute the maintenance jobs. What should you do?
A. Restore the msdb database from a backup
Restart the MSSQLServer service, and start the SQLServerAgent service
B. Restart the server
Start the MSSQLServer service, and start the SQLServerAgent service
C. Configure the SQLServerAgent service to start by using the local system account
D. Configure the SQLServerAgent service to start by using the same user account as the
MSSQLServer service

156. You are the administrator of a SQL Server 2000 computer. Each night you need to send a copy of
the server's msdb database in an e-mail message to the administrator of another SQL Server
computer.

You create a job that contains the following steps:

• Detach the msdb database

• Send the database to the administrator in an e-mail message
• Reattach the msdb database

You test the job, and it fails on the first step. You need to ensure that the msdb database is mailed to the
administrator every night. What should you do?

A. Ensure that the SQLServerAgent service is running under a user account that has
database owner access to the msdb database
B. Delete the first and last steps in the job
C. Configure the job to back up the msdb database to a temporary file
Send the file to the administrator in an e-mail message
D. Insert a new job step before the first step
Configure the new step to terminate all processes that use the msdb database

157. You are the administrator of a SQL Server 2000 computer. The server contains a database named
MedicalRecords. Users query the database using the following stored procedure:

CREATE PROC GetManyVisits

(@PatientID INT,
@MadeTable TINYINT OUTPUT)
AS
DECLARE @Visits INT
SET @Visits = (SELECT COUNT(*) FROM Cases
WHERE PatientID = @PatientID)
IF @Visits > 5
BEGIN
SELECT P.PatientName, D.PhysicianName,
V.DateVisited, V.VisitDuration,
INTO ##ClientVisits
FROM Patients P
JOIN Physician D ON P.PhysicianID = D.PhysicianID
JOIN Visits V ON P.PatientID = V.PatientID
WHERE P.PatientID = @PatientID
SET @MadeTable = 1
END
ELSE SET @MadeTable = -1

Users report that queries to the database respond slowly. You use System Monitor to measure the system
and discover that SQL Compilations/sec average 50 per minute. You need to modify the server to
accelerate query response time. What should you do?
A. Execute sp_recompile on the stored procedure
B. Replace the stored procedure with a user-defined function
C. Convert the stored procedure into two procedures--one procedure to decide if the
##ClientVisits table should be populated and one to populate the ##ClientVisits table
D. Replace the stored procedure with a temporary stored procedure created by the user

158. You are the administrator of three SQL Server 2000 computers at Five Lakes Publishing. One
server, FLPSQL01, stores order data. You want to be able to use the other two servers,
FLPSQL02 and FLPSQL03, to answer queries and run reports. The planned network
configuration is shown in the exhibit (exhibit not available).

You want to use the Database Maintenance Plan Wizard to configure log shipping from FLPSQL01 to
FLPSQL02 and FLPSQL03. You do not want users to add any new data on FLPSQL02 or FLPSQL03, but
they must be able to run queries and reports.

Which two actions should you take?

A. Set the database load state for each destination server to ‘No recovery’ mode.
B. Set the database load state for each destination server to ‘Standby’ mode.
C. Enable the ‘Allow database to assume primary role’ option for both destination servers.
D. Enable the ‘Allow database to assume primary role option for one destination server,’ and then
disable this option for the other destination server.
E. Disable the ‘Allow database to assume primary role’ option for both destination servers.

159. You are the administrator of a SQL Server 2000 computer. You configure the SQLServerAgent
service as shown below:

Service Startup account

System Account
This Account: CORP\SQLAgent

Mail Session
Mail Profile
Save copies of the sent items in the Sent folder

Error Log
File Name: E:\Program files\MicrosoftSQLService

You configure several SQL Server Agent jobs to perform maintenance tasks. These jobs delete old
database records and copy files from the server to file servers on your network. You discover that none of
the jobs will execute. You use SQL Server Enterprise Manager to check the jobs and discover that the
SQLServerAgent service will not start. You need to allow the SQL Server Agent service to start and
execute the jobs. What should you do?

A. Configure the SQL Server Agent service to start by using the local system account.
B. Grant the SQLAgent domain user account ‘Log on as a service’ rights.
C. Add the SQLAgent domain user account as a Microsoft Windows login account on the server.
D. Configure permissions on the master database to allow full access to the SQLAgent user account.

160. You are the administrator of a SQL server 2000 computer. The server contains a database named
SalesTransactions. The database handles sales transactions for a network of retail outlets. The
primary table is shown below:
Column Name Data Type Length Allow Nulls
SalesTransID int 4 blank
CustomerName varchar 50 checked
CustomerAddress varchar 50 checked
CustomerCity varchar 50 checked
CustomerState char 10 checked
CustomerZip char 10 checked
DateSold datetime 8 checked
PaymentMethod char 10 checked
Item1SKU char 10 checked
Item1Qty int 4 checked
Item2SKU char 10 checked
Item2Qty int 4 checked
Item2Price money 8 checked
Item3SKU char 10 checked
Item3Qty int 4 checked
Item3Price money 8 checked
SalesTax money 8 checked

Users report that when they add new sales, their inserts often time out. You examine the indexes on the
table. The indexes are shown below:

Index_name index_description
IX_CustomerName nonclustered located on PRIMARY
IX_CustomerState nonclustered located on PRIMARY
IX_CustomerZip nonclustered located on PRIMARY
IX_PaymentMethod nonclustered located on PRIMARY
PK_SalesTransactions_1 nonclustered, unique, primary key located on PRIMARY

You want to decrease the time required to store purchases made at the retail outlets. What are two possible
ways to achieve this goal?

A. Normalize the data structure of the table.

B. Drop the nonclustered indexes of the table.
C. Drop the primary key.
D. Add nonclustered indexes to the item 1SKU, item2SKU, and item3SKU fields in the table.
E. Re-create the PK_Sales Transactions index as a clustered index.

161. You are the administrator of a new SQL Server 2000 computer. Your company has client
computers that are running Microsoft Windows 2000 Professional. All client computers require
access to the server.

According to company policy, passwords must be at least eight characters long and change every 30 days.
You need to implement these policies and allow client computers to connect. What should you do? (Each
correct answer presents part of the solution. Choose all that apply.)

A. Configure Mixed Mode authentication.

B. Configure Windows Authentication.
C. Create a SQL Server login for each user who will access the server.
D. Create a login for the Domain Users group.

162. You are the administrator of several SQL Server 2000 computers. You configure these servers to
send e-mail messages to SQL Server operators whenever a SQL server agent job fails or
encounters an error.

Your company's software developers create a set of stored procedures that send query results in e-mail
messages about stored procedures that have not been successful.
By using a MAP1 profile named MS Exchange Settings, you verify that SQLAgentMail is correctly
configured and working properly. You need to configure the servers so that the stored procedures function
correctly. Which two actions should you take?
A. Set the security permissions on your e-mail server to permit multiple connections to the SQL
Server mailbox.
B. Set the security permissions on your e-mail sever to permit connections from the MSSQLServer
service account.
C. Configure the MSSQLServer service to use the same service account as the SQLServerAgent service.
D. Configure the SQLServerAgent service to use the same service account as the MSSQLServer service.
E. Configure SQL Mail to use the MS Exchange Settings MAOI profile.
F. Configure SQL Mail to use the Default Settings MAPI profile.

163. You are the administrator of a new Microsoft Windows 2000 server computer named HQSQL5.
HQSQL5 is a member server in your company's Windows NT 4.0 domain, which is named
HQMAIN. After installing SQL Server 2000 on HQSQL5, you configure the MSSQLServer
service account to use the HQMAIN\sqladmin user account, which is a member of the local
administrators group on HQSQL5.

You can use the HQMAIN\sqladmin user account to log on the HQSQL5. However, the MSSQLServer
service fails to start. You need to start the service. What should you do?

A. Revoke log on interactivity rights for the HQMAIN\sqladmin user account.

B. Add the HQMAIN\sqladmin user account to the HQMAIN\Domain Admins group.
C. Select the Password Never Expires check box for the HQMAIN\sqladmin user account.
D. Grant log on as a service rights to the HQMAIN\sqladmin user account.

164. You are the database administrator for an accounting company. You are installing SQL Server
2000 on a new Microsoft Windows 2000 Server computer. The computer will run two
client/server database applications that your company has purchased. Each application uses a
separate database.

During the installation, you specify SQL_Latin1_General_CP1_CI_AI as the default collation for the SQL
server computer. After completing the installation, you discover that one of the new applications, named
Financials, is sorting information incorrectly.

You contact the application vendor and discover that the Financials application requires the
SQL_Latin1_CP437_BIN collation. You need to configure the correct collation for the applications
without diminishing the performance of the SQL Server computer.

What should you do?

A. Rerun the SQL Server Setup and specify the SQL_Latin1_General_CP437_BIN collation.
B. Run the rebuildm utility and specify the SQL_Latin1_General_CP437_BIN collation.
C. Use the ALTER DATABASE statement to specify the SQL_Latin1_General_CP437_BIN collation
for the Financials database.
D. Use the ALTER DATABASE statement to specify the SQL_Latin1_General_CP437_BIN collation
for each table in the Financials database.
Answer Key:

1. B
2. AB
3. BCF
4. A
5. B
6. D
7. A
8. A
9. A
10. D
11. C
12. B
13. A
14. A
15. D
16. CE
17. AF
18. A
19. A
20. B
21. B
22. BE
23. A
24. D
25. A
26. B
27. BC
28. D
29. AE
30. A
31. D
32. C
33. BC
34. BC
35. AC
36. D
37. A
38. C
39. A
40. A
41. CD
42. B
43. B
44. CD
45. B
46. A
47. B
48. B
49. A
50. A
51. CD
52. C
53. D
54. AE
55. D
56. C
57. D
58. C
59. B
60. D
61. D
62. AD
63. CD
64. B
65. A
66. B
67. B
68. B
69. D
70. C
71. A
72. B
73. B
74. C
75. A
76. A
77. CF
78. A
79. D
80. A
81. B
82. C
83. AC
84. C
85. A
86. A
87. CE
88. A
89. AE
90. A
91. A
92. AE
93. C
94. C
95. AB
96. A
97. D
98. AE
99. BC
100. ACF
101. D
102. B
103. D
104. D
105. C
106. C
107. C
108. B
109. BCE
110. A
111. B
112. D
113. C
114. A
115. AE
116. B
117. BE
118. D
119. B
120. D
121. B
122. C
123. C
124. CE
125. A
126. D
127. B
128. BD
129. BCE
130. BE
131. B
132. A
133. BCD
134. A
135. A
136. A
137. AB
138. B
139. B
140. A
141. A
142. A
143. D
144. A
145. D
146. A
147. A
148. D
149. C
150. A
151. D
152. C
153. C
154. A
155. D
156. C
157. A
158. BC
159. B
160. AE
161. BD
162. CF
163. D
164. C