Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005

Question - 60 Maximum mark : - 113

Home
All Exams
Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Online Mock Tests
Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005

You need to design the validation of the phone number format to fulfill the requirements. What should you do?

Design all stored procedures that insert and update tables containing phone numbers to verify that the phone numbers have the correct format by using a user-defined function. ✘ Design CHECK constraints on the tables containing phone numbers to verify that the phone numbers havethe correct format by using a user-defined function. ✘ Design AFTER triggers on the tables containing phone numbers to verify that the phone numbers have thecorrect format by using a user-defined function. ✘ Design INSTEAD OF triggers on the tables containing phone numbers to verify that the phone numbershave the correct format by using a user-defined function. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to design a plan for finding out which configuration changes might have been made shortly before the system failure. Which plan should you recommend?

Query the dynamic management views in the most recent database snapshot. ✘ Restore the most recent full backup of the database, using another database name. Query the dynamicmanagement views in the restored database. ✘ Use SQL Server Profiler to start a trace. ✘ Query the default trace for what occurred before the failure. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to minimize the memory usage of the Customers table as much as possible to fulfill the requirements. What should you do?

Rebuild the clustered index with a fill factor of 10. ✘ Rebuild the clustered index with a fill factor of 100. ✘ Rebuild the index on the primary key column. ✘ Change the data type of the primary key column to a smaller data type. ✔ Use a different column with a smaller data type for the clustered index. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You are attempting to identify the query performance problems that are being reported by the testers of the MRS application. You need to identify the problem queries. Which tool should you use?

SQL Server Profiler ✘ Database Engine Tuning Advisor ✘ SQLDiag utility ✘ dta utility ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You create the BillingByPostalCode stored procedure by using the following Transact-SQL code. CREATE PROCEDURE BillingByPostalCode (@BeginPostalCode char(5), @EndPostalCode char(5)) AS SELECT AddressLine1, AddressLine2, City, StateID, PostalCode FROM Address WHERE PostalCode BETWEEN @BeginPostalCode and @EndPostalCode During performance testing, you discover that the performance of this stored procedure is not fulfilling requirements. You need to optimize this stored procedure. Which optimization strategy should you use?

Create a nonclustered index on PostalCode. ✘ Create a nonclustered index on PostalCode, and use the INCLUDE clause to include all other columns inthe SELECT statement. ✘ Create an indexed view on the PostalCode and City columns. ✘ Create a clustered index on the PostalCode, AddressLine1, AddressLine2,City columns. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You are designing a stored procedure that will be exposed to the companys energy trading partners. These partners are assigned minimal permissions in the BAP database. This procedure contains logic that constructs dynamic SQL that requires elevated permissions. You must provide the appropriate permissions. What should you do?

Add WITH EXECUTE AS OWNER to the procedure definition. ✘ Add SETUSER before the dynamic SQL statement. ✘ Add EXECUTE AS CALLER before the dynamic SQL statement. ✘ Add REVERT after the dynamic SQL statement. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You are testing the PlantGeneration stored procedure. When you query for the current months data, query performance is exceeding 30 seconds. You need to correct the performance problem. What should you do?

Create a federation of database servers, and partition the data by date. ✘ Create a distributed partitioned view across multiple instances of SQL Server. Distribute the data by date. ✘ Create a view on the data, and order by date. ✘ Create a partitioned table for the data, and partition the data by date. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You are executing performance testing on the BAP application. You discover that the system is not performing according to requirements. In addition, you discover that there is excessive disk activity. You need to find out which stored procedures are causing the excessive disk activity. Which Transact-SQL statement should you run prior to executing each stored procedure that you suspect is performing poorly?

SET STATISTICS IO ON ✘ SET SHOWPLAN_TEXT ON ✘ SET SHOWPLAN_ALL ON ✘ SET STATISTICS TIME ON ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to design the process for uploading the data from the partner applications. What should you do?

Allow access through an HTTP endpoint that uses SSL ports, and design the endpoint to run a stored procedure. ✘ Allow access through an HTTP endpoint that uses SSL ports, and design the endpoint to run a user-definedfunction. ✘ Allow access through the ADO.NET System.Data.SqlClient namespace. Grant the partner applicationaccess to the appropriate stored procedure. ✘ Allow access through the ADO.NET System.Data.SqlClient namespace. Grant the partner applicationaccess to the appropriate user-defined function. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

10.

You need to design the download process of PDF documents by customers, while fulfilling the requirements. What should you do?

Store the name of each PDF document in the database.Store the PDF documents on the database servers hard disks. Perform the following steps. 1. When the first customer downloads a given PDF document, copy the document to the Web servers hard disk. 2. Perform subsequent downloads of that document against the Web servers hard disk. ✘ Store the PDF documents in a varbinary(max) field in the database.Take customer downloads of the PDF documents directly from the database. ✘ Store the PDF documents in a varbinary(max) field in the database.Perform the following steps. 1. When the first customer downloads a given PDF document, copy the document to an ASP.NET cache object on the Web server. 2. Perform subsequent downloads of that document against the Web servers ASP.NET cache object. 3. If the PDF document in the database changes, design the data access object to repeat step 1. ✘ Store the PDF documents in a varbinary(max) field in the database.Perform the following steps. 1. When the first customer downloads a given PDF document, copy it to the Web servers hard disk. 2. Perform subsequent downloads of that document against the Web servers hard disk. 3. If the PDF document in the database changes, design the data access object to repeat step 1. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Prev Next

Congratulations

you have successfully completed the test

Back to Question View Result

Detail Form

We will send your result on your email id and phone no. please fill detail

Name

Email ID Note: Report will be send to the above Email ID

Phone No

Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner