Question No: 23 HOTSPOT

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You have a database that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following

Transact SQL statements:

You must modify the ProductReview Table to meet the following requirements:

The table must reference the ProductID column in the Product table

Existing records in the ProductReview table must not be validated with the Product table.

Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.

Changes to records in the Product table must propagate to the ProductReview table.

You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.

You must modify the Orders table to meet the following requirements:

Create new rows in the table without granting INSERT permissions to the table.

Notify the sales person who places an order whether or not the order was completed.

You must add the following constraints to the SalesHistory table:

a constraint on the SaleID column that allows the field to be used as a record identifier

a constant that uses the ProductID column to reference the Product column of the ProductTypes table

a constraint on the CategoryID column that allows one row with a null value in the column

a constraint that limits the SalePrice column to values greater than four

Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.

You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirments:

The table must hold 10 million unique sales orders.

The table must use checkpoints to minimize I/O operations and must not use transaction logging.

Data loss is acceptable.

Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.

You need to create an object that allows finance users to be able to retrieve the required data. The object must not have a negative performance impact.

How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.

Answer:

Explanation:

From question: Finance department users mustbe able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column isabove a certain threshold.

CREATE VIEW (Transact-SQL) creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database.

SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.

References:https://msdn.microsoft.com/en-us/library/ms187956.aspx

Question No: 24

Background

You have a database named HR1 that includes a table named Employee.

You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.

You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.

Employee Table

You use the following Transact-SQL statements to create, configure, and populate the Employee table:

Application

You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:

The application uses views to control access to data. Views must meet the following requirements:

Exhibit

You are analyzing the performance of the database environment. You discover that locks that are held for a long period of time as the reports are generated.

You need to generate the reports more quickly. The database must not use additional resources.

What should you do?

Update the transaction level of the report query session to READPAST.

Modify the report queries to use the UNION statement to combine the results of two or more queries.

Set the READ_COMMITTED_SNAPSHOT database option to ON.

Update the transaction level of the report query session to READ UNCOMMITTED.

Question No: 25

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question os independent of the other questions in this series. Information and details provided in a question apply only to that question.

You have a database named DB1. There is no memory-optimized filegroup in the database.

You run the following query:

The following image displays the execution plan the query optimizer generates for this query:

Users frequently run the same query with different values for the local variable @lastName. The table named Person is persisted on disk.

You need to create an index on the Person.Person table that meets the following requirements:

All users must be able to benefit from the index.

FirstName must be added to the index as an included column. What should you do?

Create a clustered index on the table.

Create a nonclustered index on the table.

Create a nonclustered filtered index on the table.

Create a clustered columnstore index on the table.

Create a nonclustered columnstoreindex on the table.

Create a hash index on the table.

Answer: B Explanation:

By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkeycolumns have the following benefits:

They can be data typesnot allowed as index key columns.

They are not considered by the Database Engine when calculating the number of index key columns or index key size.

Question No: 26

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You have a database that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

You must modify the ProductReview Table to meet the following requirements:

The table must reference the ProductID column in the Product table

Existing records in the ProductReview table must not be validated with the Product table.

Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.

Changes to records in the Product table must propagate to the ProductReview table.

You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.

You must modify the Orders table to meet the following requirements:

Create new rows in the table without granting INSERT permissions to the table.

Notify the sales person who places an order whether or not the order was completed.

You must add the following constraints to the SalesHistory table:

a constraint on the SaleID column that allows the field to be used as a record identifier

a constant that uses the ProductID column to reference the Product column of the ProductTypes table

a constraint on the CategoryID column that allows one row with a null value in the column

a constraint that limits the SalePrice column to values greater than four

Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.

You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:

The table must hold 10 million unique sales orders.

The table must use checkpoints to minimize I/O operations and must not use transaction logging.

Data loss is acceptable.

Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.

You need to enable referential integrity for the ProductReview table.

How should you complete the relevant Transact-SQL statement? To answer? select the appropriate Transact-SQL segments in the answer area.

Select two alternatives.

For the first selection select: WITH CHECK

For the first selection select: WITH NOCHECK

For the second selection select: ON DELETE NO ACTION ON UPDATE CASCADE

For the second selection select: ON DELETECASCADE ON UPDATE CASCADE

For the second selection select: ON DELETE NO ACTION ON UPDATE NO ACTION

For the second selection select: ON DELETE CASCADE ON UPDATE NO ACTION

Answer: B,C Explanation:

B: We should use WITH NOCHECK as existing records inthe ProductReview table must not be validated with the Product table.

C: Deletes should not be allowed, so we use ON DELETE NO ACTION. Updates should be allowed, so we use ON DELETE NO CASCADE

NO ACTION: the Database Engine raises an error, and the updateaction on the row in the parent table is rolled back.

CASCADE: corresponding rows are updated in the referencing table when that row is updated in the parent table.

Note: ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

Specifieswhat action happens to rows in the table that is altered, if those rows have a

referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

Specifieswhat action happens to rows in the table altered when those rows have a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.

Note: You must modify the ProductReview Table to meet the following requirements:

The table must reference the ProductID column in the Product table

Existing records in the ProductReview table must not be validated with the Product table.

Deleting records in the Product table must not be allowed if records are referencedby theProductReview table.

Changes to records in the Product table must propagate to the ProductReview table.

Question No: 27 HOTSPOT

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You have a database that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

You must modify the ProductReview Table to meet the following requirements:

The table must reference the ProductID column in the Product table

Existing records in the ProductReview table must not be validated with the Product table.

Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.

Changes to records in the Product table must propagate to the ProductReview table.

You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.

You must modify the Orders table to meet the following requirements:

Create new rows in the table without granting INSERT permissions to the table.

Notify the sales person who places an order whether or not the order was completed.

You must add the following constraints to the SalesHistory table:

a constraint on the SaleID column that allows the field to be used as a record identifier

a constant that uses the ProductID column to reference the Product column of the ProductTypes table

a constraint on the CategoryID column that allows one row with a null value in the column

a constraint that limits the SalePrice column to values greater than four

Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.

You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:

The table must hold 10 million unique sales orders.

The table must use checkpoints to minimize I/O operations and must not use transaction logging.

Data loss is acceptable.

Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.

You need to update the SalesHistory table

How should you complete the Transact_SQL statement? To answer? select the appropriate Transact-SQL, segments in the answer area.

Answer:

Explanation:

Box 1:

SaleID must be the primary key, as a constraint on the SaleID column that allows the field to be used as a record identifier is required.

Box2:

A constraint that limits the SalePrice column to values greater than four.

Box 3: UNIQUE

A constraint on the CategoryID column that allows one row with a null value in the column.

Box 4:

A foreign keyconstraint must be put on the productID referencingthe ProductTypes table, as a constraint that uses the ProductID column to reference the Product column of the ProductTypes table is required.

Note: Requirements are:

You must add the following constraints to the SalesHistory table:

a constraint on theSaleID column that allows the field to be used as a record identifier

a constraint that uses the ProductID column to reference the Product column of the ProductTypes table

a constraint on the CategoryID column that allows one row with a null value in the column

a constraint that limits the SalePrice column to values greater than four

Question No: 28

Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.

The Account table was created using the following Transact-SQL statement:

There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.

You frequently run the following Transact-SQL SELECT statements:

You must avoid table scans when you run the queries. You need to create one or more indexes for the table. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

Yes

No

Answer: B Explanation:

Create a clustered index on the AccountNumber column as it is unique, not a non nonclustered one.

References:https://msdn.microsoft.com/en-us/library/ms190457.aspx

Question No: 29 DRAG DROP

Note: This question is part of a series of questions that use the same scenario. For your

convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in the series.

You have a database named Sales that contains the following database tables. Customer, Order, and Products. The Products table and the order table shown in the following diagram.

The Customer table includes a column that stores the date for the last order that the customer placed.

You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.

You need to begin to modify the table design to adhere to third normal form.

Which column should you remove for each table? To answer? drag the appropriate column names to the correct locations. Each column name may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Answer:

Explanation:

In the Products table the SupplierName is dependant on the SupplierID, not on the ProductID.

In the Orders table the ProductName is dependant on the ProductID, not on the OrderID.

Note:

A table is in third normal form when the following conditions are met:

It is in second normal form.

All nonprimary fields are dependent on the primarykey.

Second normal form states that it should meet all the rules for First 1Normnal Form and there must be no partial dependences of any of the columns onthe primary key.

First normal form (1NF) sets the very basic rules for an organized database:

Define the data items required, because they become the columns in a table. Place related data items in a table.

Question No: 30 DRAG DROP

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge, and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema named Website. You create the Customer table by running the following Transact-SQL statement:

The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts. The following table displays selected columns and rows from the Customer table.

You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers.

Website.Customer must meet the following requirements:

Allow users access to the CustomerName and CustomerNumber columns for active customers.

Allow changes to the columns that the view references. Modified data must be visible through the view.

Prevent the view from being published as part of Microsoft SQL Server replication. Sales.Female.Customers must meet the following requirements:

Allow users access to the CustomerName, Address, City, State and PostalCode columns.

Prevent changes to the columns that the view references.

Only allow updates through the views that adhere to the view filter.

You have the following stored procedures: spDeleteCustAcctRelationship and

spUpdateCustomerSummary. The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL statement:

The following table displays a selected columns and rows from the Customer table. Thr value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts.

You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

Users report that the following SELECT statement statement takes a long time to complete:

You need to create an index that meets the following requirements:

Improve the performance of the SELECT statement.

requires minimum index key size.

Only contains active customers.

Makes no change to the existing primary key.

Contains all of the columns required by the SELECT statement.

Which three Transact_SQL segments should you use to develop the solution? To answer, move the appropriate code blocks from the list of code blocks to the answer area and arrange them in the correct order.

Answer:

Explanation:

Box 1: Clustered Index

With the same size of keys, the nonclustered indexes need more space than clustered indexes.

Box 2, Box 3:

Include the CustomerStatus column in the index, and only when CustomerStatusnot equal to 1 (the active customers).