You are the administrator of a SQL Server 2000 database. Using the default options configures the database. 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

题目

You are the administrator of a SQL Server 2000 database. Using the default options configures the database. 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.

相似考题

1.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.customersFROM ‘di\bulk\accts143_10141000.txt’WITH DATAFILETYPE = ‘char’,FIELD/TERMINATOR = ‘\t’,ROWTERMINATOR = ‘\n,’TABLOCKYou 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.

2.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 lockup 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.ndfB. Create a 95-MB data file named geography.ndfC. Create a 60-MB data file named geography.mdfD. Create a 95-MB data file named geography.mdfE. Place the table in the PRIMARY filegroup.F. Place the table in a new filegroup named LOCATION.

3.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.

4.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 databaseB．Rebuild the full-text catalog for the Articles databaseC．Create a new full-text index on the table that holds the text for the articlesD．Repopulate the full-text index on the table that holds the text for the articles

更多“You are the administrator of a SQL Server 2000 database. Using the default options configures the database. 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”相关问题

  • 第1题：

    You are the administrator 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 shown 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 percent.

    D．Create another data file named Sales_Data2 in a new SECONDARY filegroup.


    正确答案：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 reached the maximum size.

    D: The primary file has reached it maximum size. A secondary filegroup would help if it was set to the default file group. New data would still be added to the primary filegroup which is at its maximum size.

  • 第2题：

    You work in Dublin at the main office of TestKing.com. You are responsible for managing a SQL Server 2005 database. The sales department wants a report that compares customer activity in the previous quarter between the main office in Dublin and the branch office in Buenos Aires. They want the data sorted by surname and first name.You restore a recent backup of the Buenos Aires database onto your server. You write queries to build the report， ordering the data by the Surname and FirstName columns. You review the data and notice that the customer list from the Buenos Aires database is sorted differently. The sales department needs the revised data within 15 minutes for a presentation.  You need to implement the fastest possible solution that ensures that the data from both databases is sorted identically. What should you do？（）

    • A、Use the Copy Database Wizard to copy the data in the Buenos Aires database to a new database with the same collation as the Dublin database.
    • B、Use the SQL Server Import and Export Wizard to copy the data from the Buenos Airesdatabase into new tables， specifying the same collation as the Dublin database.
    • C、Modify the format file to specify the same collation as the Dublin database. Import the table again.
    • D、Modify the query on the Buenos Aires database to use the COLLATE setting in the ORDER BY clause. In the query， specify the same collation as the Dublin database.

    正确答案:D

  • 第3题：

    You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. The company wants to export data from SQL Server to a Microsoft Excel file. The company assigns this task to you. You perform this by using the SQL Server Import and Export Wizard. After this， you save the package definition to a file. Now you have to reexecute the package from the command line.  In the options below， which utility should you choose to use？（）

    • A、bcp.exe 
    • B、dta.exe 
    • C、dtexec.exe 
    • D、sqlmaint.exe

    正确答案:C

  • 第4题：

    You are a database administrator for your company. The company uses a vendor company’s application that is based on a proprietary database. You query data from the application database and import the data into a SQL Server 2005 database. The application vendor provides you with a new OLE DB driver to be used when querying the application database. Company policy prohibits connections between the SQL Server database and the application database， except when querying this data. You need to provide a way to query data from the application database. What should you？（）

    • A、Configure a Linked Server connection to the application database.
    • B、Write a query that uses an OPENDATASOURCE command.
    • C、Write a query that uses an OPENQUERY command.
    • D、Configure a Remote Server connection to the application database.

    正确答案:B

  • 第5题：

    You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. You intend to configure FILESTREAM data, enabling FILESTREAM for file I/O streaming access and allowing remote client computers to have streaming access to FILESTREAM data. You must make sure that FILESTREAM data is enabled. In the options below, which service should you configure? （） 

    • A、You should configure Distributed File System
    • B、You should configure SQL Server
    • C、You should configure SQL Server Full Text
    • D、You should configure SQL Server VSS Writer

    正确答案:B

  • 第6题：

    You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a remote SQL Server 2008 instance. You notice that the application has a poor performance. On the remote SQL Server instance， you capture a workload of the remote instance to a trace table by using SQL Profiler. Now you have to use the Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local SQL Server instance.  So what action should you perform to achieve this goal？（）

    • A、You should recapture the workload by using the data collector.
    • B、You should enable the XP_MSVER stored procedure on the local server.
    • C、You should enable the XP_MSVER stored procedure on the remote server
    • D、You should recapture the workload to a trace file by using SQL Profiler.

    正确答案:D

  • 第7题：

    You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions， monitoring the database server， and design deployment. In the company， you major job is the deployment， maintenance and support of Microsoft SQL Server 2005. You work as a database administrator for your company. There is a database named Inventory on a SQL Server 2005 computer， and the computer is named SQL01. SQL01 is utilized to aggregate manufacturing part numbers from your company’s trading partners. And the company stores the manufacturing part numbers in the Product table in the Inventory database. The company requests the users to deliver data as text files from each trading partner to SQL1 every night. The data should be imported， and no duplicate manufacturing part numbers should exist in the data which is imported from the text files， and the text files are stored in the Inventory database on SQL01. You should cut the effect on your company’s trading partners and your IT department to the least.  Which action should you perform？（）

    • A、First， you should import the data from each text file into a staging table. Second， you should write a query to have a HAVING clause contained so as to migrate replicate values before results are inserted into the Product table.
    • B、First， you should make sure that each text file is stored in an Extensible Markup Language （XML） file. Second， you should utilize Extensible Stylesheet Language Transformations （XSLT） to automatically migrate replicates before data is imported into the Product table.
    • C、First， you should make sure that the unique key values for manufacturing part numbers are utilized by each of your company’s trading partners utilizes. Second， you should import the text files into the Product table.
    • D、You should place a unique index on the PartNum column.

    正确答案:A

  • 第8题：

    TestKing.com uses a SQL Server 2005 database. This database contains a trigger named trg InsertOrders， which fires when order data is inserted into the Orders table. The trigger is responsible for ensuring that a customer exists in the Customers table before data is inserted into the Orders table. You need to configure the trigger to prevent it from firing during the data import process. You must accomplish this goal while using the least amount of administrative effort. Which two Transact-SQL statements can you use to achieve this goal？（）

    • A、ALTER TABLE OrdersDISABLE TRIGGER trg InsertOrders
    • B、DROP TRIGGER trg InsertOrders
    • C、DISABLE TRIGGER trg InsertOrders ON Orders
    • D、ALTER TRIGGER trg InsertOrders ON Orders NOT FOR REPLICATION

    正确答案:A,C

  • 第9题：

    You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. There is a report which is often executed during business time. There is a stored procedure that is used as the data source for the report. You get a report from users saying that they receive the data returned by the report but the data is inconsistent. As the technical support, you check and find that phantom reads cause this problem. You must make sure that consistent data is returned by the report while not affecting other users. So what action should you perform to achieve this goal?（）  

    • A、You should configure the database for Read Committed Snapshot isolation. 
    • B、You should modify the stored procedure to use the Snapshot isolation level 
    • C、You should configure the database for Auto Update Statistics asynchronously 
    • D、You should modify the stored procedure to use the Repeatable Read isolation level

    正确答案:B

  • 第10题：

    单选题
    You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. You intend to configure FILESTREAM data, enabling FILESTREAM for file I/O streaming access and allowing remote client computers to have streaming access to FILESTREAM data. You must make sure that FILESTREAM data is enabled. In the options below, which service should you configure? （）
    A

    You should configure Distributed File System

    B

    You should configure SQL Server

    C

    You should configure SQL Server Full Text

    D

    You should configure SQL Server VSS Writer


    正确答案： B
    解析： 暂无解析

  • 第11题：

    单选题
    You need to cleanse and standardize the data on potential survey participants prior to inserting it into the staging database. What should you do?（）
    A

    Import the data into a staging table by using the OPENROWSET BULK statement. Execute a Transact-SQL stored procedure to cleanse the data and to insert the data into the database.

    B

    Import the data into a staging table by using the BULK INSERT statement. Execute a Transact-SQL stored procedure to cleanse the data and to insert the data into the database.

    C

    Create a SQL Server Integration Services （SSIS） package to import， standardize， and cleanse the data.

    D

    Create a CLR stored procedure to import the data into a staging table， to cleanse and standardize the data， and to insert the data into the database.


    正确答案： C
    解析： 暂无解析

  • 第12题：

    单选题
    You are planning to import customer data from the Customer table and the Country table in your SQL Server 2000 database into a SQL Server 2005 database. You want to ensure that during the import process，each value in the CountryCode column of the Customer table has a corresponding record in the CountryCode column in the Country table of the SQL Server 2005 database. You define a foreign key between these two tables. Thus，referential integrity will guarantee that the import routine fails if there are any records where a CountryCode value does not exist in the Country table but does exist in the Customer table. You need to ensure that the import process does not fail if records are encountered that do not exist in the Country table. What should you do？（）
    A

    Drop the foreign key. Import the data by using the script. Re-create the foreign key.

    B

    Create a CHECK constraint.

    C

    Create a DML INSTEAD OF trigger that writes the failed records to a file or table.

    D

    Create a DML AFTER trigger that writes the failed records to a file or table.


    正确答案： D
    解析： 暂无解析

  • 第13题：

    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.


    正确答案：ABE
    解析：Explanation:
    A: Bulk update locks are taken when bulk copying data into a table and either the TABLOCK hint is specified, or the table lock on bulk load table option is set using sp_tableoption. Bulk update locks allow processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table. In this scenario the bulk update lock would meet the requirement that data should be loaded as quickly as possible.

    B: SQL Server 2000 allows data to be bulk copied into a single table from multiple clients in parallel using the bcp utility or BULK INSERT statement. This can improve the performance of data load operations. To bulk copy data into an instance of SQL Server in parallel, the database must be set to Bulk-Logged Recovery, the TABLOCK hint must be specified, and the table must not have any indexes.

    E: By specifying the Bulk-Logged Recovery model, the logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations will be minimal and will therefore require less log space while also improving performance.

    Incorrect Answers:
    C: When data is bulk copied into a single table from multiple clients in parallel using the bcp utility or BULK INSERT statement to improve the performance of data load operations, the database must be set to Bulk-Logged Recovery, the TABLOCK hint must be specified, and the table must not have any indexes.

    D: When data is bulk copied into a single table from multiple clients in parallel using the bcp utility or BULK INSERT statement to improve the performance of data load operations, the database must be set to Bulk-Logged Recovery, the TABLOCK hint must be specified, and the table must not have any indexes.

  • 第14题：

    You are planning to import customer data from the Customer table and the Country table in your SQL Server 2000 database into a SQL Server 2005 database. You want to ensure that during the import process，each value in the CountryCode column of the Customer table has a corresponding record in the CountryCode column in the Country table of the SQL Server 2005 database. You define a foreign key between these two tables. Thus，referential integrity will guarantee that the import routine fails if there are any records where a CountryCode value does not exist in the Country table but does exist in the Customer table. You need to ensure that the import process does not fail if records are encountered that do not exist in the Country table. What should you do？（）

    • A、Drop the foreign key. Import the data by using the script. Re-create the foreign key.
    • B、Create a CHECK constraint.
    • C、Create a DML INSTEAD OF trigger that writes the failed records to a file or table.
    • D、Create a DML AFTER trigger that writes the failed records to a file or table.

    正确答案:C

  • 第15题：

    You lost a temporary file that belongs to the default temporary tablespace in your database. From the options provided， which approach would you take to solve the problem？ （）

    • A、flash back the database
    • B、import the temporary tablespace from the last export
    • C、restore all the data files and temporary files from the last full database backup and perform a recovery
    • D、not perform a recovery， but create a new temporary tablespace， make it the default temporary tablespace and then drop the old tablespace

    正确答案:D

  • 第16题：

    You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. There is a text file which is named SQL01.log on the E: drive. A SQL Server Agent job executes every 2 minutes and logs information to the text file. You get a report from users saying that the sever doesn't make response. As the technical support, you check and find that the SQL Server Agent service does not run and the SQL Server Agent job no longer functions. You have to identify the reason why the SQL Server Agent service is unresponsive. In the options below, which log should you check?（）  

    • A、You should check SQL1.log
    • B、You should check SQLAGENT.OUT
    • C、You should check log_xx.trc
    • D、You should check ERRORLOG

    正确答案:B

  • 第17题：

    You need to cleanse and standardize the data on potential survey participants prior to inserting it into the staging database. What should you do?（）

    • A、Import the data into a staging table by using the OPENROWSET BULK statement. Execute a Transact-SQL stored procedure to cleanse the data and to insert the data into the database.
    • B、Import the data into a staging table by using the BULK INSERT statement. Execute a Transact-SQL stored procedure to cleanse the data and to insert the data into the database.
    • C、Create a SQL Server Integration Services （SSIS） package to import， standardize， and cleanse the data.
    • D、Create a CLR stored procedure to import the data into a staging table， to cleanse and standardize the data， and to insert the data into the database.

    正确答案:C

  • 第18题：

    You are a database administrator for your company. You are responsible for a SQL Server 2005 database that has several indexes. You need to write a stored procedure that checks the indexes for fragmentation. Which Transact-SQL statement should you use？（）

    • A、DBCC DBREINDEX
    • B、SELECT * FROM sys.dm_db_index_physical_stats
    • C、DBCC INDEXDEFRAG
    • D、SELECT * FROM sys.indexes

    正确答案:B

  • 第19题：

    You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. Now you get an order from your company CIO， you have to identify whether a database integrity check （DBCC CHECKDB） was run for a particular database.  Which log file should you examine？（）

    • A、log.trc 
    • B、default.trc 
    • C、ERRORLOG 
    • D、SQLAGENT

    正确答案:C

  • 第20题：

    You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance on the same physical computer. Now users report that they are unable to connect to the named instances. You check and verify that they can only connect to the default instance. You also ensure that all SQL Server instances run normally. You have to start the service which is required to connect to the named instances.  Which service should you start?（）  

    • A、Server 
    • B、SQL Server Agent 
    • C、SQL Server Browser 
    • D、SQL Active Directory Helper

    正确答案:C

  • 第21题：

    单选题
    You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions， monitoring the database server， and design deployment. In the company， you major job is the deployment， maintenance and support of Microsoft SQL Server 2005. You work as a database administrator for your company. There is a database named Inventory on a SQL Server 2005 computer， and the computer is named SQL01. SQL01 is utilized to aggregate manufacturing part numbers from your company’s trading partners. And the company stores the manufacturing part numbers in the Product table in the Inventory database. The company requests the users to deliver data as text files from each trading partner to SQL1 every night. The data should be imported， and no duplicate manufacturing part numbers should exist in the data which is imported from the text files， and the text files are stored in the Inventory database on SQL01. You should cut the effect on your company’s trading partners and your IT department to the least.  Which action should you perform？（）
    A

    First， you should import the data from each text file into a staging table. Second， you should write a query to have a HAVING clause contained so as to migrate replicate values before results are inserted into the Product table.

    B

    First， you should make sure that each text file is stored in an Extensible Markup Language （XML） file. Second， you should utilize Extensible Stylesheet Language Transformations （XSLT） to automatically migrate replicates before data is imported into the Product table.

    C

    First， you should make sure that the unique key values for manufacturing part numbers are utilized by each of your company’s trading partners utilizes. Second， you should import the text files into the Product table.

    D

    You should place a unique index on the PartNum column.


    正确答案： C
    解析： 暂无解析

  • 第22题：

    单选题
    You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. There is a report which is often executed during business time. There is a stored procedure that is used as the data source for the report. You get a report from users saying that they receive the data returned by the report but the data is inconsistent. As the technical support, you check and find that phantom reads cause this problem. You must make sure that consistent data is returned by the report while not affecting other users. So what action should you perform to achieve this goal?（）
    A

    You should configure the database for Read Committed Snapshot isolation. 

    B

    You should modify the stored procedure to use the Snapshot isolation level 

    C

    You should configure the database for Auto Update Statistics asynchronously 

    D

    You should modify the stored procedure to use the Repeatable Read isolation level


    正确答案： D
    解析： 暂无解析

  • 第23题：

    单选题
    You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a remote SQL Server 2008 instance. You notice that the application has a poor performance. On the remote SQL Server instance， you capture a workload of the remote instance to a trace table by using SQL Profiler. Now you have to use the Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local SQL Server instance.  So what action should you perform to achieve this goal？（）
    A

    You should recapture the workload by using the data collector.

    B

    You should enable the XP_MSVER stored procedure on the local server.

    C

    You should enable the XP_MSVER stored procedure on the remote server

    D

    You should recapture the workload to a trace file by using SQL Profiler.


    正确答案： A
    解析： 暂无解析

相关内容