NEW QUESTION 1
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 is independent of the other questions in this series. Information and details provided in a question apply only to that
question.

You have a Microsoft SQL Server database named DB1 that contains the following tables: You frequently run the following queries:

There are no foreign key relationships between TBL1 and TBL2.
You need to minimize the amount of time required for the two queries to return records from the tables. What should you do?

• A. Create clustered indexes on TBL1 and TBL2.
• B. Create a clustered index on TBL1 Create a nonclustered index on tbl2 and add the most frequently queried columns as included columns.
• C. Create a nonclustered index on tbl2 only.
• D. Create unique constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.
• E. Drop existing indexes on TBL1 and then create a clustered columnstore inde
• F. Create a nonclustered columnstore index on TBL1. Create a nonclustered index on TBL2.
• G. Drop existing indexes on TBL1 and then create a cluwered columnstore inde
• H. Create a nonclustered columnstore index on TBL1. Make no changes to TBL2.
• I. Create check constraints on both TBL1 and tbl2. Create a partitioned view that combines columns from TBL1 and tbl2.
• J. Create an indexed view that combines columns from TBL1 and TBL2.

Answer: F

NEW QUESTION 2
Database users report that SELECT statements take a long time to return results. You run the following Transact-SQL statement:

You need to create one nonclustered na index that contains all of the columns in the above table. You must minimize index

• A. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus, UserName);
• B. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus) INCLUDE (UserName);
• C. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserName);
• D. CREATE NONCLUSTERED INDEX IX_User ON Users (UserStatus, CountryCode) INCLUDE (UserName);

Answer: D

NEW QUESTION 3
You are evaluating the performance of a database environment.
You must avoid unnecessary locks and ensure that lost updates do not occur. You need to choose the transaction isolation level for each data scenario.
Which isolation level should you use for each scenario? To answer, drag the appropriate isolation levels to the correct scenarios. Each isolation 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: Box 1: Readcommitted
Read Committed: A transaction T1 executing under this isolation level can only access committed data. Pros: Good compromise between concurrency and consistency.
Cons: Locking and blocking. The data can change when accessed multiple times within the same transaction. Box 2: Read Uncommitted
Read Uncommitted (aka dirty read): A transaction T1 executing under this isolation level can access data changed by concurrent transaction(s).
Pros: No read locks needed to read data (i.e. no reader/writer blocking). Note, T1 still takes transaction duration locks for any data modified.
Cons: Data is not guaranteed to be transactionally consistent. Box 3: Serializable
Serializable: A transaction T1 executing under this isolation level provides the highest data consistency including elimination of phantoms but at the cost of reduced concurrency. It prevents phantoms by taking a range lock or table level lock if range lock can’t be acquired (i.e. no index on the predicate column) for the duration of the transaction.
Pros: Full data consistency including phantom protection.
Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency.
References:
https://blogs.msdn.microsoft.com/sqlcat/2011/02/20/concurrency-series-basics-of-transaction-isolation-levels/

NEW QUESTION 4
You run the following Transact-SQL statement:

There are multiple unique OrderlD values. Most of the UnitPrice values for the same OrderlD are different. You need to create a single index seek query that does not use the following operators:
*Nested loop
*Sort
*Key lookup

• A. CREATE INDEX IX_OrderLines_l ON OrderLines (OrderlD, UnitPrice) INCLUDE(Description, Quantity)
• B. CREATE INDEX IX_OrderLines_l ON OrderLines (OrderlD, UnitPrice) INCLUOE(Quantity)
• C. CREATE INDEX IX_OrderLines_l ON OrderLines (OrderlD, UnitPrice, Quantity)
• D. CREATE INDEX IX_OrderLines_l ON OrderLines (UnitPrice, OrderlD) INCLUDE(Description, Quantity)

Answer: A

NEW QUESTION 5
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?

• A. Create a clustered index on the table.
• B. Create a nonclustered index on the table.
• C. Create a nonclustered filtered index on the table.
• D. Create a clustered columnstore index on the table.
• E. Create a nonclustered columnstoreindex on the table.
• F. 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.

NEW QUESTION 6
You have a database that users query frequently.
The users report that during peak business hours, the queries take longer than expected to execute.
A junior database administrator uses Microsoft SQL Server Profiler on the database server to trace the session activities.
While performing the trace, the performance of the database server worsens, and the server crashes.
You need to recommend a solution to collect the query run times. The solution must minimize the impact on the resources of the database server.
What should you recommend?

• A. Increase the free space on the system drive of the database server, and then use SQL Server Profiler on the server to trace the session activities.
• B. Collect session activity data by using SQL Server Extended Events.
• C. Clean up tempdb, and then use SQL Server Profiler on the datafile server to trace the session activities.
• D. Collect performance data by using a Data Collector Set (DCS) in Performance Monitor.

Answer: A

NEW QUESTION 7
You are performing a code review for Transact-SQL statements.
What action does the constraint in each statement perform? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

NEW QUESTION 8
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some questions sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that is 130 GB and contains 500 million rows of data.
Granular transactions and mass batch data imports change the database frequently throughout the day. Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.
You discover that some reports time out before they complete. You need to reduce the likelihood that the reports will time out. Solution: You partition the largest tables.
Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 9
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 Auto Stats in the Performance event category.
• B. Create one Extended Events session with the sqlserver.missing_column_statistics eventadded.
• 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.missing_join_predicate event added.

Answer: D

Explanation: The Missing JoinPredicate event class indicates that a query is being executed that has no join predicate. This could result in a long-running query.

NEW QUESTION 10
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 named DB1 that includes a table named sales . Orders. You grant a user named User1 select permissions on the sales schema.
You need to ensure that User1 can select data from the sales, orders table without specifying the schema name in any Transact SQL statements.
Solution: You move the sales.orders table to the dbo schema. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 11
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.
When a procedure calls spDeleteCustAcctRelationship, if the calling stored procedures has already started an active transaction, all the detections made by the spDeleteCustAccRelationship stored procedure must be committed by the caller; otherwise changes must be committed within the spDeleteCustAcctRelationship stored procedure.
If any error occurs during the delete operation, only the deletes made by the soDeleteCustACCTRelationships stored procedure must be rolled back and the status must be updated.
You need to complete the stored procedure to ensure all the requirements are met.
How should you complete the procedure? To answer, drag the Transact-SQL segments to the correct location. 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.

Answer:

Explanation: Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.
References: https://technet.microsoft.com/en-us/library/ms178157(v=sql.105).aspx

NEW QUESTION 12
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 is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a raid array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.
You need to examine delays in executed threads, including errors with specific queries and batches.

• A. Create a sys.dm_os_waiting^tasks query.
• B. Create a sys.dm_exec_sessions query.
• C. Create a Performance Monitor Data Collector Set.
• D. Create a ays .dm_os_memory_objeccs query.
• E. Create a sp_configure 'max server memory' query.

Answer: B

NEW QUESTION 13
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 update statistics for the nonclustered index. Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

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 14
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 is independent of the other questions in the series. Information and details provided in a question apply only to that question.
You are developing and application to track customer sales.
You need to return the sum of orders that have been finalized, given a specified order identifier. This value will be used in other Transact-SQL statements.
You need to create a database object. What should you create?

• A. extended procedure
• B. CLR procedure
• C. user-defined procedure
• D. DML trigger
• E. scalar-valued function
• F. table-valued function

Answer: F

Explanation: User-defined scalar functions return a single data value of the type defined in the RETURNS clause. References: https://technet.microsoft.com/en-us/library/ms177499(v=sql.105).aspx

NEW QUESTION 15
You have multiple stored procedures inside a transaction.
You need to ensure that all the data modified by the transaction is rolled back if a stored procedure causes a deadlock or times out.
What should you do?

• A. Use the NOLOCK option.
• B. Execute the DBCC UPDATEUSAGE statement.
• C. Use the max worker threads Option.
• D. Use a table-valued parameter.
• E. Set SET ALLOW SNAPSHOT ISOLATION to ON,
• F. Set SET XACT ABORT to ON.
• G. Execute the alter table T1 set (lock escalation = auto); statement.
• H. Use the output parameters.

Answer: B

NEW QUESTION 16
You have a trigger named CheckTriggerCreation that runs when a user attempts to create a trigger. The CheckTriggerCreation trigger was created with the ENCRYPTION option and additional proprietary business logic.
You need to prevent users from running the ALTER and DROP statements or the sp_tableoption stored procedure.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Answer:

Explanation:

NEW QUESTION 17
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 Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

The customer table includes a column that stores the data 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 create triggers that meet the following requirements:
* Optimize the performance and data integrity of the tables.
* Provide a custom error if a user attempts to create an order for a customer that does not exist.
* In the Customers table, update the value for the last order placed.
* Complete all actions as part of the original transaction.
In the table below, identify the trigger types that meet the requirements.
NOTE: Make only selection in each column. Each correct selection is worth one point.

Answer:

Explanation: INSTEAD OF INSERT triggers can be defined on a view or table to replace the standard action of the INSERT statement.
AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully.
References: https://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx

NEW QUESTION 18
You run the following Transact-SQL statements:

Records must only be added to the orders table by using the view. If a customer name does not exist then a new customer name must be created.
You need to ensure that you can insert rows into the orders table by using the view. What should you do?

• A. Add the CustomerlD column from the Orders table and the WfTH CHECK OPTION statement to the view.
• B. Create an INSTEAD of trigger on the view.
• C. Add the WITH SCHEMABINDING statement to the view statement and create a clustered index on the view.
• D. Remove the subquery from the view, add the WITH SCHEMABINDING statement, and add a trigger to the Orders table to perform the required logic.

Answer: C

NEW QUESTION 19
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 a check constraint on the table.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: References: https://en.wikipedia.org/wiki/Check_constraint

NEW QUESTION 20
You are developing a stored procedure with the following requirements:
*Accepts an integer as input and inserts the value into a table.
*Ensures new transactions are committed as part of the outer transactions.
*Preserves existing transactions if the transaction in the procedure fails.
*If the transaction in the procedure fails, rollback the transaction.
How should you complete the procedure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: