NEW QUESTION 1
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 Transact-SQL statement:

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.
You need to create Website Customer.
How should you complete the view definition? To answer, drag the appropriate Transact-SQL segments to the correct locations, Each Transact-SQL segment 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.
NOTE: Each correct selection is worth one point. NOTE: Each correct selection is worth one point.

Answer:

Explanation: Box 1: WITH ENCRYPTION
Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication. Box 2: WITH CHECK OPTION
CHECK OPTION forces all data modification statements executed against theview to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.
Note: Website.Customer must meet the followingrequirements:

NEW QUESTION 2
You have a Microsoft Azure SQL database. Some queries take a long time to execute.
You need to identify whether any of the long-runffiing queries prevent users from updating data in the database.
Which query should you execute? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation:

NEW QUESTION 3
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.
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 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.
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

NEW QUESTION 4
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a database that contains a table named Employees. The table stored information about the employees of your company.
You need to implement the following auditing rules for the Employees table:
- Record any changes that are made to the data in the Employees table.
- Customize the data recorded by the audit operations.
Solution: You implement a stored procedure on the Employees table. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: We should use table-valued functions, not procedures, to customize the recorded change data. References: https://msdn.microsoft.com/en-us/library/cc645858.aspx

NEW QUESTION 5
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a table that has a clustered index and a nonclustered index. The indexes use different columns from the table. You have a query named Query1 that uses the nonclustered index.
Users report that Query1 takes a long time to report results. You run Query1 and review the following statistics for an index seek operation:

You need to resolve the performance issue. Solution: You defragment both indexes. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: We see Actual Number of Row is 3571454, while Estimated Number of Rows is 0. This indicates that the statistics are old, and need to be updated.

NEW QUESTION 6
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 by 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:
CREATE CLUSTERED INDEX PK_Account ON Account(ProductCode); Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: We need an index on the productCode column as well.
References: https://msdn.microsoft.com/en-us/library/ms190457.aspx

NEW QUESTION 7
You need to create a view that can be indexed. You write the following statement.

What should you add at line 02?

• A. with check_option
• B. with recompile
• C. with view metadata
• D. with schenabinding

Answer: D

NEW QUESTION 8
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 by 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:
CREATE NONCLUSTERED INDEX IX_Account_ProductCode ON Account(ProductCode); Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: References: https://msdn.microsoft.com/en-za/library/ms189280.aspx

NEW QUESTION 9
You are experiencing performance issues with the database server.
You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems. What should you create?

• A. a System Monitor report
• B. a sys.dm_tran_database_transaction dynamic management view query
• C. an Extended Events session that uses Query Editor
• D. an Activity Monitor session in Microsoft SQL Management Studio.

Answer: D

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

NEW QUESTION 10
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.
You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.
You need to improve the application response time.
Solution: You add a unique clustered index to the Employees table. Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

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

NEW QUESTION 11
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a database that contains a table named Employees. The table stores information about the employees of your company.
You need to implement and enforce the following business rules:
- Limit the values that are accepted by the Salary column.
- Prevent salaries less than $15,000 and greater than $300,000 from being entered.
- Determine valid values by using logical expressions.
- Do not validate data integrity when running DELETE statements.
Solution: You implement cascading referential integrity constraints on the table. Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: References: https://technet.microsoft.com/en-us/library/ms186973(v=sql.105).aspx

NEW QUESTION 12
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
The Account table was created by using the following Transact-SQL statement:

There are more than 1 billion records in the Account table. The AccountNuwber 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:
SELECT ProductCode, SUM(Balance) AS TotalSUH FROM Account WHERE ProductCode <> 'CD' GROUP 8Y ProductCode; SELECT AccountNupber, Balance FROM Account WHERE ProductCode - 'CD";
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 statements:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

NEW QUESTION 13
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.
You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.
You need to improve the application response time.
Solution: You replace the stored procedure with a user-defined function. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

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

NEW QUESTION 14
Your company runs end-of-the-month accounting reports. While the reports run. other financial records are updated in the database.
Users report that the reports take longer than expected to run.
You need to reduce the amount of time it takes for the reports to run. The reports must show committed data only.
What should you do?

• A. Use the NOLOCK option.
• B. Execute the BDCC UPDATEUSAGE statement.
• C. Use the max worker threads Option.
• D. Use a table-valued parameter.
• E. Set SET ALLOW_SNAPSHOI_ISOLATION to OH.
• F. Set SET XACT ABORT to ON.
• G. Execute the alter table ti set (Lock_ESCALATION = AUTO); statement.
• H. Use the output parameters.

Answer: G

NEW QUESTION 15
You are experiencing performance issues with the database server.
You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems. What should you create?

• A. a System Monitor report
• B. a sys.dm_exec_query_stats dynamic management view query
• C. a sys.dm_exec_session_wait_stats dynamicmanagement view query
• D. an Activity Monitor session in Microsoft SQL Management Studio.

Answer: C

Explanation: sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed for each session. You can use this view to diagnose performance issues with the SQL Server session and also with specific queries and batches.
Note: SQL Server wait stats are, at their highest conceptual level, grouped into two broad categories: signal waits and resource waits. A signal wait is accumulated by processes running on SQL Server which are waiting for a CPU to become available (so called because the process has “signaled” that it is ready for processing). A resource wait is accumulated by processes running on SQL Server which are waiting fora specific resource to become available, such as waiting for the release of a lock on a specific record.

NEW QUESTION 16
You use Microsoft SQL Server Profile to evaluate a query named Query1. The Profiler report indicates the following issues:
At each level of the query plan, a low total number of rows are processed.
The query uses many operations. This results in a high overall cost for the query. You need to identify the information that will be useful for the optimizer.
What should you do?

• A. Start a SQL Server Profiler trace for the event class Performance statistics in the Performance eventcategory.
• B. Create one Extended Events session with the sqlserver.missing_column_statistics event added.
• C. Start a SQL Server Profiler trace for the event class Soft Warnings in the Errors and Warnings event category.
• D. Create one Extended Events session with the sqlserver.error_reported event added.

Answer: A

Explanation: The Performance Statistics event class can be used to monitor the performance of queries, stored procedures, and triggers that are executing. Each of the six event subclasses indicates an event in the lifetime of queries, stored procedures, and triggers within the system. Using the combination of these event subclasses and the associated sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_trigger_stats dynamic management views, you can reconstitute the performance history of any given query, stored procedure, or trigger.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/performance-statistics-event-class?view=

NEW QUESTION 17
You are planning a set of stored procedures that must be able to access memory-optimized tables. You need to optimize the performance of the stored procedures. Which statement should you include in the stored procedure definitions?

• A. WITH RECOMPILE
• B. WITH NATIV£_COMPILATION
• C. WITH EXECUTE AS SELF
• D. WITM NO INFOMSG5

Answer: D

NEW QUESTION 18
You have a table named Person.Address that includes the following columns:
AddressID
AddressLine1
AddressLine2
City
StateProvinceID
PostakCode
RowGuid
ModifiedDate
You need to create a nonclustered index on PostalCode named IX_Address_PostalCode that uses the following included columns:
AddressLine1
AddressLine2
City
StateProvinceID
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact_SQL segments in the answer are.

Answer:

Explanation: Box 1: INDEX
Box 2: ON
Box 3: INCLUDE
INCLUDE (column [ ,... n ] ) specifies the non-key columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or non-unique.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2021

NEW QUESTION 19
You are developing an app that allows users to query historical company financial data. You are reviewing email messages from the various stakeholders for a project.
The message from the security officer is shown in the Security Officer Email exhibit below. TO: Database developer
From: Security Officer
Subject: SQL object requirements
We need to simplify the security settings for the SQL objects. Having a assign permissions at every object in SQL is tedious and leads to a problem. Documentation is also more difficult when we have to assign permissions at multiple levels. We need to assign the required permissions at one object, even though that object may be obtaining from other objects.
The message from the sales manager is shown in the Sales Manager Email exhibit below. TO: Database developer
From: Sales Manager Subject: Needed SQL objects
When creating objects for our use, they need to be flexible. We will be changing the base infrastructure frequently. We need components in SQL that will provide backward compatibility to our front end applications as the environments change so that do not need to modify the front end applications. We need objects that can provide a filtered set of the data. The data may be coming from multiple tables and we need an object that can provide access to all of the data through a single object reference.
This is an example of the types of data we need to be able to have queries against without having to change the front end applications.

The message from the web developer is shown in the Web Developer Email exhibit below. TO: Database developer
From: Web Developer
Subject: SQL Object component
Whatever you will be configuring to provide access to data in SQL, it needs to connect using the items referenced in this interface. We have been using this for a long time, and we cannot change this from end easily. Whatever objects are going to be used in SQL they must work using object types this interface references.

You need to create one or more objects that meet the needs of the security officer, the sales manager and the web developer.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Answer:

Explanation: * Stored procedure: Yes
A stored procedure to implement the following:
Whatever you will be configuring to provide access to data in SQL, it needs to connect using the items referenced in this interface. We have been using this for a long time, and we cannot change this from end easily. Whatever objects are going to be used in SQL they must work using object types this interface references.
* Trigger: No
No requirements are related to actions taken when changing the data.
* View: Yes
Because: We need objects that can provide a filtered set of the data. The data may be coming from multiple tables and we need an object that can provide access to all of the data through a single object reference.

NEW QUESTION 20
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.

• A. For the first selection select: WITH CHECK
• B. For the first selection select: WITH NOCHECK
• C. For the second selection select: ON DELETE NO ACTION ON UPDATE CASCADE
• D. For the second selection select: ON DELETE CASCADE ON UPDATE CASCADE
• E. For the second selection select: ON DELETE NO ACTION ON UPDATE NO ACTION
• F. For the second selection select: ON DELETE CASCADE ON UPDATE NO ACTION

Answer: BC

Explanation: B: We should use WITH NOCHECK as existing records in the 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 update action 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 }
Specifies what 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 }
Specifies what 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:
1. The table must reference the ProductID column in the Product table
2. Existing records in the ProductReview table must not be validated with the Product table.
3. Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.
4. Changes to records in the Product table must propagate to the ProductReview table. References: https://msdn.microsoft.com/en-us/library/ms190273.aspx https://msdn.microsoft.com/en-us/library/ms188066.aspx