1、微软认证 70-228模拟试卷 1及答案与解析 1 You are the administrator of a 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 companys warehouses. The StorageLocations table is configur
2、ed as shown in the exhibit. 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 tables schema to save space. You cannot lose any existing data. You want to do this by
3、using the minimum amount of administrative time and server resources.Which Transact-SQL statement should you execute? （ A） ALTER TABLE dbo.StorageLocations ALTER COLUMN UnitsStored int NOT NULL （ B） ALTER TABLE dbo.StorageLocations LocationDescription char (25) COLLATE SQL_Latin1_General_CP1_CI_AS N
4、OT NULL （ C） ALTER TABLE dbo.StorageLocations ALTER COLUMN UnitsStored smallint NOT NULL （ D） ALTER TABLE dbo.StorageLocations LocationDescription nvarchar (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 2 You are the administrator of a SQL Server 2000 computer. The server contains a database nam
5、ed sales. You need to change the way customer Ids appear in the Customers table. The database schema is shown in the exhibit. You need to automate the process of updating the primary key tools. You also want to minimize records locks and administration within the database during the update process.W
6、hat 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 q
7、uotes 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
8、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. 3 You are the database administrator for a financial services company. Employees enter data 24 hours
9、 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 di
10、bulkaccts143_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 t
11、hen 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 t
12、he BULK INSERT statement and then set the option equal to 10 percent of the number of rows to be loaded. 4 You are the database administrator for a retail company. The company owns 270 stores. Every month, each store submits approximately 2,000 sales records, which are loaded into a SQL Server 2000
13、database at the 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
14、 key value to each transformed sales record. After loading this months 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 reco
15、rds 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
16、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 tables IDENTITY property. （ D） Set the Sales tables IDENTITY_INSERT property to ON.
17、Add new sales records that have the desired key values. 5 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 respon
18、ds 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 in the following screen shot.
19、 You need to improve the applications performance. What should you do? （ A） Create additional nonclustered 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
20、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. 6 You are the administrator of a SQL Server 2000 computer. The server contains a database named Contracts. The server is configured as shown in the server co
21、nfiguration exhibit. The database files are configured as shown in the database properties exhibit. 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 o
22、bjects, 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-cre
23、ate 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. 7 You are the administrator of a SQL Server 2000 computer. The server contains a Dat
24、a 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
25、 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 a user password. （ C） Encrypt the DTS
26、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. 8 You are the administrator of several SQL Server 2000 computers. A data Transformation Services (DTS) package uses
27、 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 con
28、nection 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 re
29、ad 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 t
30、he .udl files available on a network share. 9 You are the administrator of SQL Server computers. One server is named SQL7, and other is named SQL2000. SQL7 is running SQL Server 7.O and SQL2000 is running SQL Server 2000. The net-libraries on SQL2000 are configured as shown in the exhibit. SQL7 is c
31、onfigured 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 p
32、art 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
33、 SQL2000 and SQL7, enable multiprotocol encryption. 10 You are the administrator of SQL Server 2000 computer. The server contains a database named Medical Records. This database contains clinical records and administrative records. The database consumes about 30GB of disk space. The data files are c
34、onfigured as shown in the exhibit. 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
35、to reconfigure the database to allow the backups to complete during the evening hours.Which two actions should you take? (Each correct answer represents 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-define
36、d 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.C. Reorganize the data files into three groups. Place the system tab
37、les 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
38、 up the transaction log each night. Run a differential backup each Sunday, and run a full backup the first day of each month. 微软认证 70-228模拟试卷 1答案与解析 1 【正确答案】 A 【试题解析】 Explanation: In this scenario there are two considerations that must go into the table schema modfication. The space allocated to the
39、 LocationDescription and the use of data types. The scenario states that 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. In the table the parts is represented by the PartID column; the
40、 amount of units by UnitsStored Storage column; the locations by the LocationID column and the LocationDescription field by the LocationDescription column. Because the LocationDescription field is usually described with a name, 10 to 25 characters in length, does not mean that the character length o
41、f this field is never longer than 25 characters. We thus cannot reduce the character length of this column. We must thus turn our attention to the UnitsStored column. SQL Server 2000 has four integer data types. These are bigint, which has a storage size of 8 bytes and is whole numbers that range fr
42、om -9223372036854775808 through 9223372036854775807; int, which has a storage size of 4 bytes and ranges from -2,147,483,648 through 2,147,483,647; smallint, which has a storage size of 2 bytes and ranges from -32,768 through 32,767; and tinyint, which has a storage size of 1 byte and ranges from 0
43、through 255. As no more than 100,000 units of any given part will be held in storage and int ranges from -2,147,483,648 through 2,147,483,647, this data field can be represented by the int data type. ALTER COLUMN is used in conjunction with ALTER TABLE. The latter modifies a table definition by alte
44、ring, adding, or dropping columns. Through ALTER COLUMN a specified columns data type length can be altered. Incorrect Answers: B: The scenario states that the LocationDescription field is usually described with a name, 10 to 25 characters in length. This does not mean that the character length of t
45、his field is never longer than 25 characters. If we ALTER COLUMN to a char data type field with a field length of only 25, truncation of the LocationDescription will occur if the description exceeds 25 characters. We thus cannot reduce the character length of this column. C: As no more than 100,000
46、units of any given part will be held in storage and int ranges from -2,147,483,648 through 2,147,483,647, this data field can be represented by the int data type. smallint, however, does not provide a sufficient range as it only ranges up to 32,767 while we require a range of up to at least 100,000.
47、 Note: SQL Server 2000 uses four integer data types, namely, bigint, which has a storage size of 8 bytes and is whole numbers that range from -9223372036854775808 through 9223372036854775807; int, which has a storage size of 4 bytes and ranges from -2,147,483,648 through 2,147,483,647; smallint, whi
48、ch has a storage size of 2 bytes and ranges from -32,768 through 32,767; and tinyint, which has a storage size of 1 byte and ranges from 0 through 255 D: While the char data type is a fixed-length character data with a specified length has a storage size in bytes that is equal to the specified data
49、length, varchar is a variable-length character data with a specified length but has a storage size in bytes that is equal to the actual length of the entered data and not the specified length of the data. The varchar data type should be used when it is expected that the data values in a column will vary considerably in size. The use of this data type can reduce database size but this solution restricts the data length to 25 characters. The scenario states that the LocationDescription field is usually described with a name, 10 to 25 characters
