You need to ensure that the stored procedure that inserts new loan requests can access data in tables that are stored in another database on the same SQL Server instance. Which two actions should you perform?（）A、Ensure that the stored procedure and the ta

第1题：

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.

第2题：

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

第3题：

You are designing the loan application process for the customer terminals. You need to design a Transact-SQL stored procedure that fulfills the business and technical requirements. Which type of Transact-SQL stored procedure should you use?（）

• A、one that uses an internal SQL Server Web service endpoint
• B、one that runs a SQL Server Integration Services （SSIS） package
• C、one that uses Service Broker
• D、one that runs a user-defined function

第4题：

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. According to the company requirement, the names of all user-defined stored procedures must contain the prefix usp_ on all instances. Besides this, you must make sure that stored procedures that do not contain this prefix cannot be created by you. What should you do?（）  

• A、A policy should be created. The policy targets the name of the stored procedure that is evaluated on demand. 
• B、A policy should be created. The policy targets the name of the stored procedure that is evaluated on change. 
• C、A condition should be created. The condition targets the name of the stored procedure that is evaluated on change 
• D、A condition should be created. The condition targets the name of stored procedure that is evaluated on demand.

第5题：

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 database named DB1 in the instance. DB1 has a table which is named Table01 and a stored procedure named Procedure01. Procedure01 choose data from Table01 by using a sp_executesql Transact-SQL statement. You company security rules forbid users to access tables directly in any database. Look at the exception below: "Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'Table01', database 'DB1', schema 'dbo'." The exception is raised when Procedure01 is executed by users. You must make sure that e user can successfully execute Procedure1 complying with the company rules. So what action should you perform to achieve this goal?（）  

• A、You should execute the GRANT SELECT ON dbo.Table01 TO User1 Transact-SQL statement. 
• B、You should execute the GRANT EXECUTE ON dbo.Procedure1 TO User1 Transact-SQL statement. 
• C、You should alter Procedure01 and add the WITH EXECUTE AS OWNER option to its header 
• D、You should alter Procedure01 and add the EXECUTE AS USER = 'dbo' option immediately before the call to the sp_executesql stored procedure.

第6题：

A Windows Azure application stores data in a SQL Azure database.  The application will start an operation that includes three insert statements.  You need to recommend an approach for rolling back the entire operation if the connection to SQL Azure is lost.  What should you recommend？（）

• A、 Ensure that all statements execute in the same database transaction.
• B、 Create a stored procedure in the database that wraps the insert statements in a TRY CATCH block.
• C、 Create a stored procedure in the database that wraps the insertstatements in a TRANSACTION block.
• D、 Open a new connection to the database. Use a separate transaction scope to roll back the original operation.

第7题：

第8题：

第9题：

第10题：

第11题：

第12题：

第13题：

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.

第14题：

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. There is a stored procedure. The stored procedure implements a database maintenance process. A SQL Server Agent job should be created to that runs the stored procedure. Besides this， you must make sure that after it is completed successfully， the job is removed.  What should you do？ （）

• A、You should create a job which is assigned to the Database Maintenance category
• B、You should create an Alert which will run another to delete the maintenance job.
• C、You should create a job. You schedule the job to run only once.
• D、You should create a job. The job uses the Automatically delete job option

第15题：

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. You utilize the SP：Recompile event class to run SQL Server Profiler. You find that a great amount of recompiles occurring. The possible causes of the large number of recompiles should be identified.  From the flowing four options， which one is the most possible cause of the problem？（）

• A、The sp_executesql batches are not being recovered from the procedure cache.
• B、A database administrator executed UPDATE STATISTICS statements on all tables， and the most common stored procedures reference all the tables.
• C、One new stored procedure is created by a database administrator.
• D、Ad hoc batches are not being recovered from the procedure cache.

第16题：

You need to ensure that old data and current data can be queried according to the business requirements. What should you do?（）

• A、Specify a view based on the archival data table and current data tables.
• B、Specify that the data must be queried by using a stored procedure.
• C、Specify that both the archival data table and the current data tables must have indexes created on the same columns.
• D、Specify that a SQL Server Agent job must be used to copy current data into the archival data table each night.

第17题：

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 database named Service in the instance. There is a database user who is named ServiceGreatUser. A SQL Server login named ServiceGreatLogin maps to ServiceGreatUser in the Service database. According to the requirement of the company CIO, the database user can only perform the two tasks below: the database user executes all stored procedures that currently exist in the Sales database and executes all stored procedures that will be created in the Sales database. So what should you do to ensure this? （）

• A、ServiceGreatUser should be added to the appropriate fixed database roles. 
• B、You should assign the appropriate object-level permissions to ServiceGreatUser 
• C、You should assign the appropriate server-level permissions to ServiceGreatLogin 
• D、You should assign the appropriate database-level permissions to ServiceGreatUser

第18题：

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

第19题：

第20题：

第21题：

第22题：

第23题：