NEW QUESTION 1
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 create a file group for the indexes and a file group for the data files. You store the files for each file group on separate disks.
Does this meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: Consider creating two additional File Groups: Tables and Indexes. It is best not to put your stuff in PRIMARY as that is where SQL SERVER stores all of its data and meta-data about your objects. You create your Table and Clustered Index (as that is the data for the table) on [Tables] and all Non-Clustered indexes on [Indexes].

NEW QUESTION 2
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 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 ensure that the performance of each instance is consistent for the same queried and query plans. What should you do?

• 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 sys.dm_os_memory_objects query.
• E. Create a sp_configure ‘max server memory’ query.
• F. Create aSQL Profiler trace.
• G. Create asys.dm_os_wait_stats query.
• H. Create an Extended Event.

Answer: H

Explanation: Advanced Viewing of Target Data from Extended Events in SQL Server
When your event session is currently active, you might want to watch the event data in real time, as it is received by the target.
Management > Extended Events > Sessions > [your-session] > Watch Live Data.
The query_post_execution_showplan extended event enables you to see the actual query plan in the SQL Server Management Studio (SSMS) UI. When the Details pane is visible, you can see a graph of the query plan on the Query Plan tab. By hovering over a node on the query plan, you can see a list of property names and their values for the node.

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

NEW QUESTION 3
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 need to create a stored procedure that updates the Customer, CustomerInfo, OrderHeader, and OrderDetails tables in order.
You need to ensure that the stored procedure:
- Runs within a single transaction.
- Commits updates to the Customer and CustomerInfo tables regardless of the status of updates to the OrderHeader and OrderDetail tables.
- Commits changes to all four tables when updates to all four tables are successful. Solution: You create a stored procedure that includes the following Transact-SQL segment:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: References:
http://stackoverflow.com/questions/11444923/stored-procedure-to-update-multiple-tables

NEW QUESTION 4
You have a database that contains both disk-based and memory-optimized tables.
You need to create two modules. The modules must meet the requirements described in the following table.

Which programming object should you use for each module? To answer, select the appropriate object types in the answer area.

Answer:

Explanation: Module 1: Interpreted stored procedure
An interpreted stored procedure can access both disk-based and memory-optimized tables. Module 2: Natively compiled stored procedure
Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/natively-compiled-stored-procedures?

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 need to create a stored procedure that updates the Customer, CustomerInfo, OrderHeader, and OrderDetails tables in order.
You need to ensure that the stored procedure:
- Runs within a single transaction.
- Commits updates to the Customer and CustomerInfo tables regardless of the status of updates to the OrderHeader and OrderDetail tables.
- Commits changes to all four tables when updates to all four tables are successful. Solution: You create a stored procedure that includes the following Transact-SQL segment:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: References:
http://stackoverflow.com/questions/11444923/stored-procedure-to-update-multiple-tables

NEW QUESTION 6
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:
- UspA: This stored procedure updates only the EmployeeStatus column.
- UspB: This stored procedure updates only the EmployeePayRate column.
The application uses views to control access to data. Views must meet the following requirements:
- Allow user access to all columns in the tables that the view accesses.
- Restrict updates to only the rows that the view returns. Exhibit

Users must only be able to modify data in the Employee table by using the vwEmployee view. You must prevent users from viewing the view definition in catalog views.
You need to identify the view attribute to use when creating vwEmployee. In the table below, identify the attributes that you must use.
NOTE: Make only one selection in each column.

Answer:

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

NEW QUESTION 7
You plan to create a stored procedure that uses a table parameter as an input parameter. The table value parameter may hold between 1 and 10,000 rows when you run the stored procedure.
The stored procedure will use the rows within the table value parameter to filter the rows that will be returned by the SELECT statement.
You need to create the stored procedure and ensure that it runs as quickly as possible.
How should you complete the procedure? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct answer selection is worth one point.

Answer:

Explanation:

NEW QUESTION 8
You are creating a stored procedure which will insert data into the table shown in the Database schema exhibit. (Click the exhibit button.)

You need to insert a new customer record into the tables as a single unit of work.
In which order should you use the Transact-SQL segments to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange the, in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

Answer:

Explanation: The entities on the many side, of the 1-many relations, must be added before we add the entities on the 1-side.
We must insert new rows into BusinessEntityContact and BusinessEntityAddress tables, before we insert the corresponding rows into the BusinessEntity and AddressType tables.

NEW QUESTION 9
You have a Microsoft SQL Server database that has a table named Sales. The table is used for retrieving data and is updated during non business hours.
You run the following Transact-SQL statement:

You analyze the execution plan for the statement. (Click the Exhibit button).

You need to add an index that optimizes performance. How should you complete the Transact-SQL statement?

Answer:

Explanation:

NEW QUESTION 10
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 an application to track customer sales.
You need to create a database object that meets the following requirements:
- Return a value of 0 if data is inserted successfully into the Customers table.
- Return a value of 1 if data is not inserted successfully into the Customers table.
- Support logic that is written by using managed code.
- Support TRY…CATCH error handling. 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: D

Explanation: DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.
References: https://msdn.microsoft.com/en-us/library/ms178110.aspx

NEW QUESTION 11
You are analyzing the performance of a database environment. You need to find all unused indexes in the current database.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

Answer:

Explanation: Example: Following query helps you to find all unused indexes within database using sys.dm_db_index_usage_stats DMV.
-- Ensure a USE statement has been executed first. SELECT u.*
FROM [sys].[indexes] i
INNER JOIN[sys].[objects] o ON (i.OBJECT_ID = o.OBJECT_ID)
LEFT JOIN [sys].[dm_db_index_usage_stats] u ON (i.OBJECT_ID = u.OBJECT_ID) AND i.[index_id] = u.[index_id]
AND u.[database_id] = DB_ID() --returning the database ID of the current database WHERE o.[type] <>'S' --shouldn't be a system base table
AND i.[type_desc] <> 'HEAP' AND i.[name] NOT LIKE 'PK_%'
AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0 AND u.[last_system_scan] IS NOT NULL
ORDER BY 1 ASC
References: https://basitaalishan.com/2012/06/15/find-unused-indexes-using-sys-dm_db_index_usage_stats/

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 have a Microsoft SQL Server database named DB1 that contains the following tables:

Users frequently run the following query:

Users report that the query takes a long time to return results.
You need to minimize the amount of time required for the query to return data. 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 columnsfrom 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 tbli and then create a clustered columnstore inde
• H. Create a nonclustered columnstore index on tbl
• I. Make no changes to TBL2.
• J. Create CHECK constraints on both TBL1 and TBL2.. Create a partitioned view that combines columns fromTBL1 and TBL2.
• K. Create an indexed view that combines columns from TBL1 and TBL2.

Answer: D

NEW QUESTION 13
You must create two staging database tables. The tables have the following requirements:

You need to select the correct storage mechanism for each table.
Which storage mechanism should you use? To answer, drag the appropriate table types to the correct tables. Each table type 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:

NEW QUESTION 14
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 create a stored procedure in the sales schema that selects the data from the sales. orders table.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

NEW QUESTION 15
You use Query Store to optimize a query in a database. The query has two execution plans:
Plan 2 is shown in the Plan 2 Execution Plan exhibit.
Plan 10 is shown in the Plan 10 Execution Plan exhibit.
You create an index at 22:24 based on the missing index suggestion in Plan 2.
The average duration statistics for the query is shown in the Tracked Queries exhibit. (Click the Exhibit button.)
You need to analyze the operators in the two execution plans.
For each of the following statements, select Yes if the statement is true. Otherwise, select No. Plan 2 Execution Plan

Plan 10 Execution Plan

Tracked Queries

Answer:

Explanation:

NEW QUESTION 16
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 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 observe that many deadlocks appear to be happening during specific times of the day.
You need to monitor the SQL environment and capture the information about the processes that are causing the deadlocks.
What should you do?

• A. Create a sys.dm_os_waiting_tasks query.
• B. Create a sys.dm_exec_sessions query.
• C. Create a PerformanceMonitor Data Collector Set.
• D. Create a sys.dm_os_memory_objects query.
• E. Create a sp_configure ‘max server memory’ query.
• F. Create a SQL Profiler trace.
• G. Create a sys.dm_os_wait_stats query.
• H. Create an Extended Event.

Answer: F

Explanation: To view deadlock information, the Database Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server Profiler.
Trace Flag 1204 and Trace Flag 1222
When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.
References: https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

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 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:

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 must update the design of the Customer table to meet the following requirements.
* You must be able to store up to 50 accounts for each customer.
* Users must be able to retrieve customer information by supplying an account number.
* Users must be able to retrieve an account number by supplying customer information. You need to implement the design changes while minimizing data redundancy.
What should you do?

• A. Split the table into three separate table
• B. Include the AccountNumber and CustomerID columns in the first tabl
• C. Include the CustomerName and Gender columns in the second tabl
• D. Include the AccountStatus column in the third table.
• E. Split the table into two separate table
• F. Include AccountNumber, CustomerID, CustomerName and Gender columns in the first tabl
• G. Include the AccountNumber and AccountStatus columns in the second table.
• H. Split the table into two separate tables, Include the CustomerID and AccountNumber columns in the first tabl
• I. Include the AccountNumber, AccountStatus, CustomerName and Gender columns in the second table.
• J. Split the table into two separate tables, Include the CustomerID, CustomerName and Gender columns in the first tabl
• K. IncludeAccountNumber, AccountStatus and CustomerID columns in the second table.

Answer: D

Explanation: Two tables are enough. CustomerID must be in both tables.

NEW QUESTION 18
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:

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 must update the design of the Customer table to meet the following requirements.
* You must be able to store up to 50 accounts for each customer.
* Users must be able to retrieve customer information by supplying an account number.
* Users must be able to retrieve an account number by supplying customer information.
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).
References:
http://www.sqlserverlogexplorer.com/overview-of-cluster-and-noncluster-index/

NEW QUESTION 19
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:

Does the solution meet the goal?

• A. Yes
• B. 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

NEW QUESTION 20
You are analyzing the memory usage of a Microsoft SQL Server instance. You need to obtain the information described on the following table.

Which performance counter should you use for each requirement? To answer, drag the appropriate performance counters to the correct requirements. Each performance counter may be used once, more than once or not at all. You may need to drag the split bat between panes or scroll to view content.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: Requirement1: SQL Server: Memory Manager: Total Server Memory (KB)
This counter specifies the amount of memory the server has committed using the memory manager. Requirement2: SQL Server: Memory Manager: Granted Workspace Memory (KB)
Specifies the total amount of memory currently granted to executing processes, such as hash, sort, bulk copy, and index creation operations.
Requirement3: Process: working Set
Each time a process is created, it reserves the minimum working set size for the process. The virtual memory manager attempts to keep enough memory for the minimum working set resident when the process is active, but keeps no more than the maximum size.
References:
https://msdn.microsoft.com/en-us/library/ms190924.aspx https://blogs.technet.microsoft.com/askperf/2007/05/18/sql-and-the-working-set/