Microsoft-PRO Designing Database Solutions and Data Access Using Microsoft SQL Server 2008 Set 2

Question - 15 Maximum mark : - 29

Home
All Exams
Microsoft-PRO Designing Database Solutions and Data Access Using Microsoft SQL Server 2008 Online Practice Exams
Microsoft-PRO Designing Database Solutions and Data Access Using Microsoft SQL Server 2008 Set 2

The database will contain a table. Customer data are stored in the table as XML data. The data supports an application that cannot be altered. You plan to prevent the types of errors below in the XML data: Invalid values in the Gender field, NULL values in the Customer Name field, Non-numeric values in the Customer Telephone field. What should you do to implement the plan while not modifying the application?

You should use the FileStream data type ✘ You should change the XML data type to Typed XML ✘ You should validate data by using the HierarchyID data type ✘ You should save the XML data in a standard table format. Specify the correct data types, constraints, andNOT NULL parameters in the standard table. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

The database will hold incoming XML responses for an EDI system. You have the requirements for the database: The database is scalable and accessible to heterogeneous platforms. The database stores large amounts of data and various types of reports from multiple sources. The solution allows search by keywords. So what should you do to make the database to meet these requirements?

You should store data and include proper indexes by using SQL Server 2008 tables ✘ You should store numerical reports by using SQL Server 2008 tables and store text reports by using ANSItext files, ✘ You should save reports in binary format in a file within a Windows folder. Save the path of the file in SQLServer 2008 tables ✘ You should store reports in XML format, and store the data by using SQL Server 2008 tables. Index theXML data to improve performance. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You own the Sales schema and a user named MarketingMan is the owner of the Marketing schema. You are allowed to create objects in all schemas in the database. But users of the Marketing schema are not allowed to access the Sales schema which has a table named Clients. In the Marketing schema, you intend to create a stored procedure for the marketing team. The stored procedure will select data from the Clients table and will be owned by MarketingMan. What should you do to make sure that the marketing team is able to execute the stored procedure?

You should use the EXECUTE AS OWNER option to create the procedure ✘ You should use the EXECUTE AS SELF option to create the procedure ✘ You should use the EXECUTE AS CALLER option to create the procedure ✘ You should use the EXECUTE AS USER=MarketingUser option to create the procedure ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

A common language runtime (CLR) user-defined scalar function will be contained in the database. An integer value will be returned by the function. What should you do to make sure that the computed columns that use the result from this function can be indexed?

You should make sure that the logic of the function returns a different value for the same input values and the same database state. Make sure that you have set the IsDeterministic property to True. ✘ You should make sure that the logic of the function returns the same value for the same input values andthe same database state. Make sure that you have set the IsDeterministic property to True ✘ You should make sure that a different value is returned by the logic of the function for the same input valuesand the same database state. Make sure that you have set the IsDeterministic property to False. ✘ You should make sure that the same value is returned by the logic of the function returns for the same inputvalues and the same database state. Make sure that you have set the IsDeterministic property to False ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You plan to create a stored procedure. The procedure produces forecast data and queries a sales table. You don't own the database and you do not have administrative permissions, but you are allowed to create stored procedures. Users will only be allowed to execute your stored procedures. What should you do to make sure that users can execute the stored procedures?

The TRUSTWORTHY property of the database should be set to ON ✘ When each stored procedure is created, you should include an EXECUTE AS OWNER clause ✘ When each stored procedure is created, you should include an EXECUTE AS CALLER clause ✘ In each stored procedure, before you query the sales table, you should include a SETUSER statement. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

There's a database contains a view. This view contains a WHERE clause that filters specific records and allows data updates. You have to prevent data modifications which do not conform to the WHERE clause while using as little effort as possible. So what should you do?

On the view, an INSTEAD OF trigger should be created ✘ On the view, a unique clustered index should be created ✘ You should add the WITH CHECK OPTION clause to alter the view. ✘ You should add the WITH SCHEMABINDING clause to alter the view ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

There's a database that contains a table. Besides this, it also contains a table-valued function which accepts the primary key from the table as a parameter. You intend to write a query. The query joins the table to the results of the table-valued function. You must make sure that rows from the table returned are only those produce a result set from the table-valued function. So which join predicate should you choose to use?

INNER JOIN ✘ CROSS APPLY ✘ OUTER APPLY ✘ LEFT OUTER JOIN ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You use SQL Server 2008 to create an online transaction processing (OLTP) database in an enterprise environment. A table named SellingData is contained in the database. Each record in the table contains data in any one of the following pairs of nullable columns: ForeignSellingTargets and ForeignSelling, InternetSellingTargets and InternetSelling, ResellerSellingTargets and ResellerSelling. There're three NOT NULL key columns in the table. A large number of records are inserted on a daily basis into the SellingData table. From the SellingData table, summary reports are generated, each of which is based on aggregated data from any one of the pairs of nullable columns. The requirements below must be satisfied: The SellingData table cannot be directly modified. The performance of the reports is maximized. The amount of storage space for each report is minimized. You have to design a view or views to meet these requirements. So what should you do?

You should create an indexed view from the SellingData table. The table should contain aggregated data of all the columns required by all the reports. ✘ In order to make each view contain aggregated data of only the columns required by the respective report,you should create multiple indexed views from the SellingData table. ✘ In order to make each Report table contain aggregated data of only the columns required by the respectivereport, you should create multiple Report tables from the SellingData table. And on top of each of the Report tables create views ✘ At the end of each month, you should transfer aggregated new records to a staging table quickly. Create anindexed view from the staging table. The table contains aggregated data of all the columns required by all the reports ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

A stored procedure that uses the TRY/CATCH syntax is created in a new database. When the stored procedure is executed, the store procedure logs information about each step in the TRY block into a table when it is executed, the table is named dbo.RunningLog. If an error happens, the stored procedure must roll back the changes made to the target tables and retain the log entries stored in the dbo.RunningLog table. So what should you do to make sure that the given tasks can be performed by the stored procedure?

In the TRY block, you should start a transaction. Define a transactional save point before each step. Insert log entries into the dbo.RunningLog table after each step. Roll back to the transactional save points in the CATCH block. Commit the transaction after the CATCH block. ✘ In the TRY block, you should start a transaction in the TRY block. Insert log entries into the dbo.RunningLogtable after each step. Commit the transaction in the CATCH block after the CATCH block. Use data in the dbo.RunningLog table to reverse any changes made to the target tables. Commit the transaction if one exists ✘ Before the TRY block, you define a table variable by using the same columns as that of the dbo.RunningLogtable. In the TRY block, start a transaction. After each step, insert log entries into the table variable. Roll back the transaction in the CATCH block. After the CATCH block, insert the rows from the table variable into the dbo.RunningLog table. Commit the transaction if one exists ✘ Before the TRY block, you should use the same columns as that of the dbo.RunningLog table to define atemporary table by. In the TRY block, start a transaction. After each step, insert log entries into the temporary table. In the CATCH block, roll back the transaction. Insert the rows from the temporary table into the dbo.RunningLog table after the CATCH block. Commit the transaction if one exists. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

10.

A table named PurchaseCustoms which has no indexes contained in your data warehouse. A table named SellRequests is contained in your online transaction processing (OLTP) database. Data between the two tables has to be synchronized on a weekly basis. The following are requirements for the synchronization process: New records in the SalesOrders table are inserted in the factBuyingHabits table. Records that are deleted from the SalesOrders table are also deleted from the factBuyingHabits table. When a record is modified in the SalesOrders table, the modification is updated in the factBuyingHabits table. You have to design a suitable synchronization solution while using as little coding and administrative efforts as possible. So what should you do?

You should design a single SSIS package. The package should use the Slowly Changing Dimension task. And then schedule a job to run this package ✘ For the INSERT, UPDATE, and DELETE operations each, you should design an SSIS package. And thenschedule a job to run this package. ✘ For the INSERT, UPDATE, and DELETE operations each, you should write three stored procedures. Andthen you schedule a job to run the stored procedures in a sequential manner. ✘ You should write one stored procedure that contains a MERGE statement to perform the INSERT,UPDATE, and DELETE operations. And then schedule a job to run the stored procedure. ✔

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 Database Solutions and Data Access Using Microsoft SQL Server 2008 Set 2

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