Exhibit:
UPDATE EzonexamConsolidated
SET District = ‘Mexico’
WHERE RtvID = 45
You work as database administrator at Ezonexam.com. You handle one SQL Server 2000 computer and one database.
When users run the query in the exhibit above the receive the following error message:
Server: Mrh3612, Level 16, Stats 1, Line 1
View or function ‘EzonexamConsolidated’ is not updateable because it
contains aggregates:
How should you enable the query to run?
A.Create a non-normalized EzonexamConsolidated table and populate the table with data from the base tables.
B.The appropriate users should be granted UPDATE permissions on each base table.
C.To enable updates on the composite tables create an INSTEAD OF trigger on the view.
D.On each base table add a cascading update trigger.
第1题:
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 data 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 data order 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.
第2题:
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 report. The planned network configuration is shown in the exhibit.
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.
第3题:
Exhibit:
BULK INSERT Ezonexam.dbo.employees
FROM ‘tk\africe\fubar512.txt’
WITH DATAFILETYPE = ‘char’,
FIELD/TERMINATOR = ‘\t’,
ROWTERMINATOR = ‘\n,’
TABLOCK
You work as database administrator at Ezonexam.com. You handle one SQL Server 2000 computer.
How should you improve the performance of the query in the exhibit above?
A. Extend the BULK INSERT statement with the BATCHSIZE option set it to equal to 5 percent of the number of rows to be loaded.
B. Extend the BULK INSERT statement with the BATCHSIZE option set it to equal to 10 percent of the number of rows to be loaded.
C. Extend the BULK INSERT statement with the ROWS_PER_BATCH option set it to equal to 5 percent of the number of rows to be loaded.
D. Extend the BULK INSERT statement with the ROWS_PER_BATCH option set it to equal to 10 percent of the number of rows to be loaded.
E. Insert a line before the BULK INSERT statement that drops the indexes for the customers table. Insert a line after the BULK INSERT statement that recreates the indexes for the customers table.
F. The TABLOCK option should be removed.
G. The ROWTERMINATOR line should be removed.
第4题:
You are the administrator of a SQL Server 2000 computer. The server is configured as shown in the Database Server Configuration exhibit.
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 in the Database Schema exhibit.
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.
第5题:
You are the administrator of a 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.
第6题:
You are the administrator of a 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.
第7题:
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.
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.
第8题:
You have an SQL query that takes one minute to execute. You use the following code segment to execute theSQL query asynchronously.Dim ar As IAsyncResult = cmd.BeginExecuteReader()You need to execute a method named DoWork() that takes one second to run while the SQL query is executing.DoWork() must run as many times as possible while the SQL query is executing.Which code segment should you use?()
A.
B.
C.
D.
第9题:
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.
第10题:
You are the administrator of 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.
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.