1、微软认证 70-228模拟试卷 6及答案与解析 1 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 informatio
2、n 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 custo
3、mers satisfaction. You need to notify the customer service manager whenever a complaint 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-
4、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. 2 You are the administrator
5、of a SQL Server 2000 computer. The server contains 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. You examine drive E. The hard disk is configured as sh
6、own in the Local Disk Properties exhibit. 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 pe
7、rcent. （ D） Create another data file named Sales_Data2 in a new SECONDARY filegroup. 3 You are the administrator of a 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 as shown in
8、the following screenshot. 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 52 icon in the Current Activity window to discover which SQL statement is being executed （ B） Use the sp_who s
9、tored procedure to discover who is logged in as spid 52 （ C） Use SQL Profiler to capture the activity of the user who is logged in as spid 52 （ D） Use System Monitor to log the locks that are granted in the database 4 You are the administrator of a SQL Server 2000 computer. The server contains a dat
10、abase 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 in the Acct Properties exhibit. The server has two hard disks that are configured as shown in the Server Configuration exhibit. The
11、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?
12、 （ 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
13、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 f
14、ile on drive C. 5 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 defi
15、ned 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
16、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 transformati
17、on 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 script for
18、each DTS transformation. Use the script to recast data types to the destinations precision and scale values. 6 You are the administrator of a SQL Server 2000 computer. You are configuring a database for an inventory application. The hard disks on your server are configured as shown in the exhibit. T
19、he 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 dri
20、ve 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 dat
21、a 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 7 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 Ac
22、counting 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. Mos
23、t 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? (Eac
24、h 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. 8 You are the administra
25、tor of a SQL Server 2000 computer. The server contains your companys order processing database. Two-hundred operators take orders by telephone 24 hours a day. Threehundred data entry personnel enter data from orders received by mail. To ensure that order data will not be lost, your companys disaster
26、 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 cre
27、ate 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 databas
28、e 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. 9 You are the administrator of
29、 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
30、disks as two mirrored NTFS volumes. B. Configure the hard disks as one mirrored NTFS volumes. 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 volu
31、me. F. Place inventory_data.mdf on the first volume and inventory_data2.ndf and inventory_log.ldf on the second volume. G. Place inventory_data.mdf and inventory_log.ldf on the same volume. 10 You are the administrator of a SQL Server 2000 computer. The server contains a database named MedicalRecord
32、s.The database tables are configured as shown in the exhibit.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 patients home address defines the prefix.You want
33、to comply 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 constraints
34、 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.
35、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. 11 You are the administrator of a new multiprocessor SQL Server 2000 computer. You are migrating data from other datab
36、ase 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
37、 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.
38、微软认证 70-228模拟试卷 6答案与解析 1 【正确答案】 A 【试题解析】 Explanation: Adding an appropriate UPDATE trigger that notifies the service manager whenever a complaint is marked is closed would accomplish the requirements. The client applications application would not have to be changed. Note: In SQL Server 2000, trigger
39、s are a special type of stored procedure that fire automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. Triggers are powerful tools that can be used to enforce business rules automatically when data is modified and can automate the processing for a company. Li
40、ke stored procedures, triggers contain Transact-SQL statements which can include instructions to send an e-mail message when a particular column is updated Incorrect Answers: B: If a stored procedure is created and used the client application would have to be adjusted accordingly. But we dont want t
41、o make any changes in the client application. Note: A stored procedure is a set of Transact-SQL statements compiled into a single execution plan and can return data as output parameters, which can return either data or a cursor variable; return codes, which are always an integer value; a result set
42、for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure; or as a global cursor that can be referenced outside the stored procedure. Stored procedures assist in achieving a consistent implementation of logic across applications as the
43、SQL statements and logic needed to perform a task can be coded and tested once in a stored procedure. Thereafter each application or user that needs to perform that task can execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for e
44、nsuring that business rules are correctly enforced. Stored procedures can also be used to customize security permissions in much the same way as views. Stored procedure permissions can be used to prevent users from modifying data in certain columns and then grant users permissions to execute the pro
45、cedure. C: If a user defined function is created and used the client application would have to be adjusted accordingly. But we dont want to make any changes in the client application. Note: In programming languages, functions are subroutines used to encapsulate frequently performed logic. Any code t
46、hat must perform the logic incorporated in a function can call the function rather than having to repeat all of the function logic. SQL Server 2000 supports built-in functions and user-defined functions. Built-in functions operate as defined in the Transact-SQL Reference and cannot be modified while
47、 user-defined functions allow the user to define their own Transact-SQL functions using the CREATE FUNCTION statement. User-defined functions take zero or more input parameters, and return a single value. Some user-defined functions return a single, scalar data value, such as an int, char, or decima
48、l value. D: Just creating a rule would only check the input, it would not notify the service manager as required. Note: Rules are a backward-compatibility feature of SQL Server 2000 and performs the same functions as CHECK constraints. CHECK constraints are the preferred, standard way to restrict th
49、e values in a column as they are more concise than rules. 2 【正确答案】 A 【试题解析】 Explanation: By examining the first exhibit we see that the database has reached its maximum size of 1001 MB. The data file is located on drive E and the second exhibit shows us that there is enough free space on that disk to increase the maximum size of the data file to 1,500 MB. Incorrect Answers: B: DBCC SHRINKDATABASE could possibly shrink the database and new records could be added for a while, but it would just be a temporary solution. C: The database is already set to automatic file growth, but it has reac
