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 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 implement a stored procedure that deletes a discontinued product from the Products table. You identify the following requirements:
* If an open order includes a discontinued product, the records for the product must not be deleted.
* The stored procedure must return a custom error message if a product record cannot be deleted. The message must identify the OrderID for the open order.
What should you do? To answer, select the appropriate Transact-SQL segments in the answer area.

Answer:

Explanation: Using TRY...CATCH in Transact-SQL
Errors in Transact-SQL code can be processed by using a TRY…CATCH construct. TRY…CATCH can use the following error function to capture error information:
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.
References: https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

NEW QUESTION 2
You have a data warehouse fact table that has a clustered columnstore index. You have multiple CSV files that contain a total of 3 million rows of data.
You need to upload the data to the fact table. The solution must avoid the delta group when you import the data.
Which solution will achieve the goal in the least amount of time?

• A. Load the source data to a staging tabl
• B. Load the data to the fact table by using the insert_select statement and specify the Tablock option on the staging table.
• C. Load the source data to a staging table that has a clustered index on the primary ke
• D. Copy the data to the fact table by using the insert_select statement.
• E. Load the source data to the fact table by running bcp.exe and specify the _ Tablock option.
• F. Load the source data to the fact table by using the bulk insert statement and specify the Tablock option.

Answer: D

NEW QUESTION 3
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.
Your company has employees in different regions around the world.
You need to create a database table that stores the following employee attendance information:
- Employee ID
- date and time employee checked in to work
- date and time employee checked out of work
Date and time information must be time zone aware and must not store fractional seconds. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: datetime2 defines adate that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, andoptional user-specified precision.
References: https://msdn.microsoft.com/en-us/library/bb677335.aspx

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: Returning Data by Using OUTPUT Parameters
If you specify the OUTPUT keyword for a parameter in the procedure definition, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits.
SQL Server stored procedures, views and functions are able to use the WITH ENCRYPTION option to disguise the contents of a particular procedure or function from discovery.
Native Compilation of Tables and Stored Procedures
In-Memory OLTP introduces the concept of native compilation. SQL Server can natively compile stored procedures that access memory-optimized tables. SQL Server is also able to natively compile
memory-optimized tables. Native compilation allows faster data access and more efficient query execution than interpreted (traditional) Transact-SQL. Native compilation of tables and stored procedures produce DLLs.
References: https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx https://msdn.microsoft.com/en-us/library/dn249342.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 database that contains a table named Employees. The table stores 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 check constraint on the Employees table. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Check constraints cannot be used to track changes in a table. References: https://msdn.microsoft.com/en-us/library/bb933994.aspx

NEW QUESTION 6
You have a Microsoft Azure SQL Database named MyDb that uses server version V12.
You plan to use Query Performance Insight to troubleshoot performance problems. The database query store is not enabled.
You need to enable the database query store to meet the following requirements for the database:
- Statistics must be aggregated every 15 minutes.
- Query stores must use no more than 1,024 megabytes (MB) of storage.
- Query information must be retained for at least 15 days.
- Queries must be captured based on resource consumption.
You connect to the database by using SQL Server Managements Studio.
How should you complete the Transact-SQL statements? 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: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select. Each correct selection is worth one point.

Answer:

Explanation:
or

Both answers are correct.
References: https://msdn.microsoft.com/en-us/library/mt604821.aspx

NEW QUESTION 7
You are creating the following two stored procedures:
A natively-compiled stored procedure
An interpreted stored procedure that accesses both disk-based and memory-optimized tables
Both stored procedures run within transactions.
You need to ensure that cross-container transactions are possible. Which setting or option should you use?

• A. the SET TRANSACTION_READ_COMMITTED isolation level for the connection
• B. the SERIALIZABLE table hint on disk-based tables
• C. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database
• D. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database

Answer: C

Explanation: Provide a supported isolation level for the memory-optimized table using a table hint, such as WITH (SNAPSHOT). The need for the WITH (SNAPSHOT) hint can be avoided through the use of the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. When this option is set to ON, access to a memory-optimized table under a lower isolation level is automatically elevated to SNAPSHOT isolation.

NEW QUESTION 8
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 uspUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.
When you start uspUpdateCustomerSummary, there are no active transactions. The procedure fails at line 09 due to a CHECK constraint violation on the TotalDepositAccountCount column.
What is the impact of the stored procedure on the CustomerDetails table?

• A. The value of the TotalAccountCount column decreased.
• B. The value of the TotalDepositAccountCount column is not changed.
• C. The statement that modifies TotalDepositAccountCount is excluded from the transaction.
• D. The value of the TotalAccountCount column is not changed.

Answer: D

NEW QUESTION 9
You manage a Microsoft Azure SQL Database that has the Standard tier plan. The database size has increased and users experience slow performance.
You need to identify usage for the following resources:
• CPU utilization
• Disk storage
• Memory utilization
• Disk I/O
………………………………..

• A. Activity monitor
• B. sys.dm_exec_connections
• C. Azure portal
• D. sys.rtuource_usage
• E. sys . dm_db_resource_stats

Answer: BC

NEW QUESTION 10
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

You view the Deadlock Graph as shown in the exhibit. (Click the Exhibit button.)
Use the drop-down menus to select the answer choice that answers each question based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.

Answer:

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

NEW QUESTION 11
You are designing a solution for a company that operates retail stores. Each store has a database that tracks sales transactions. You create a summary table in the database at the corporate office. You plan to use the table to record the quantity of each product sold at each store on each day. Managers will use this data to identify reorder levels for products.
Every evening, stores must transmit sales data to the corporate office. The data must be inserted into the summary table that includes the storeID, Production. Qtysold. Totprodsales, and Datesold columns.
You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date.
What should you include in your solution?

• A. Create a column store index.
• B. Create a check constraint.
• C. Create a unique constraint.
• D. Create a rule and bind it to the ProductID column.

Answer: D

NEW QUESTION 12
You have two databases with the following settings:

You run the following Transact –SQL statements:

You need to select data from DiskTable and insert the data into MemTable. You must complete the insertion operation into MemTable as an explicit transaction without immediate durability.
Which four Transact-SQL segments should you use? 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: Box 1: BEGIN TRANSACTION
Box 2: UPDATE … #Disktable
Box 3: IF… SELECT INTO …#Disktable Box 4: .. DELAYED_DURABILITY = ON
The COMMIT syntax is extended so you can force delayed transaction durability. If DELAYED_DURABILITY is DISABLED or FORCED at the database level (see above) this COMMIT option is ignored.
Syntax:
COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]
References:
https://docs.microsoft.com/en-us/sql/relational-databases/logs/control-transaction-durability?view=sql-server-20

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

Customer records may be inserted individually or in bulk from an application. You observe that the application attempts to insert duplicate records.
You must ensure that duplicate records are not inserted and bulk insert operations continue without notifications.
Which Transact-SQL statement should you run?

• A. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH(ONLINE = OFF)
• B. CREATE UNIQUE INDEX IX_CUSTOMER_Code O Customer (Code) WITH (IGNORE_DUP_KEY= ON)
• C. CREATE UNIQUE INDEX IX Customer Code ON Customer (Code) WITH (IGNORE DUP KEY=OFF)
• D. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code)
• E. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH (ONLINE = ON)

Answer: B

Explanation: IGNORE_DUP_KEY = { ON | OFF } specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

NEW QUESTION 14
Note: The 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 question in the series. Information and details provided in a question apply only to that question.
You have a database named DB1. The database does not have a memory optimized filegroup. You create a table by running the following Transact-SQL statement:

The table is currently used for OLTP workloads. The analytics user group needs to perform real-time operational analytics that scan most of the records in the table to aggregate on a number of columns.
You need to add the most efficient index to support the analytics workload without changing the OLTP application.
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 columnstore index on the table.
• F. Create a hash index on the table.

Answer: E

Explanation: A nonclustered columnstore index enables real-timeoperational analytics in which the OLTP workload uses the underlying clustered index, while analytics run concurrently on the columnstore index.
Columnstore indexes can achieve up to 100xbetter performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes. These recommendations will help your queries achieve the very fast query performance that columnstore indexes are designed to provide.
References: https://msdn.microsoft.com/en-us/library/gg492088.aspx

NEW QUESTION 15
You have the following stored procedure:

The Numbers table becomes unavailable when you run the stored procedure. The stored procedure obtains an exclusive lock on the table and does not release the lock.
What are two possible ways to resolve the issue? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

• A. Remove the implicit transaction and the SET ANSI_DEFAULTS ON statement.
• B. Set the ANSI_DEFAULT statement to OFF and add a COMMIT TRANSACTION statement after the INSERT statement.
• C. Add a COMMIT TRANSACTION statement after the INSERT statement.
• D. Remove the SET ANSI DEFAULTS ON statement.

Answer: B

NEW QUESTION 16
Note: The 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 question in the series. Information and details provided in a question apply only to that question.
You have a database named DB1. The database does not use a memory-optimized filegroup. The database contains a table named Table1. The table must support the following workloads:

You need to add the most efficient index to support the new OLTP workload, while not deteriorating the existing Reporting query performance.
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 clusteredcolumnstore index on the table.
• E. Create a nonclustered columnstore index on the table.
• F. Create a hash index on the table.

Answer: C

Explanation: A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed
filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
References: https://technet.microsoft.com/en-us/library/cc280372(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 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 modify the database design to meet the following requirements:
* Rows in the Orders table must always have a valid value for the ProductID column.
* Rows in the Products table must not be deleted if they are part of any rows in the Orders table.
* All rows in both tables must be unique.
In the table below, identify the constraint that must be configured for each table. NOTE: Make only one selection in each column.

Answer:

Explanation: A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Here the foreign key constraint is put on the ProductID in the Orders, and points to the ProductID of the Products table.
With a check constraint on the ProductID we can ensure that the Products table contains only unique rows.
References:
http://www.w3schools.com/sql/sql_foreignkey.asp

NEW QUESTION 18
You manage a database with tables named Invoice and InvoiceDetails. Each invoice may have multiple records.
Users update the InvoiceDetails table by using a .NET web application. The application retrieves records from both tables and updates the tables by running an inline update statement.
Users experience slow performance when updating records in the application. The solution must meet the following requirements:
Must use a stored procedure.
Must not use inline update statements
Must use a table-valued parameter.
Must call the stored procedure to update all records. You need to optimize performance.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer:

Explanation: Box 1: Create a user-defined table type…
Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.
Box 2: ..read-only input parameter.
Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. Box 3:
Example
The following example uses Transact-SQL and shows you how to create a table-valued parameter type, declare a variable to reference it, fill the parameter list, and then pass the values to a stored procedure.
USE AdventureWorks2012;
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50)
, CostRate INT ); GO
/* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE dbo. usp_InsertProductionLocation
@TVP LocationTableType READONLY Etc.¨
/* Declare a variable that references the type. */ DECLARE @LocationTVP AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate) SELECT Name, 0.00
FROM AdventureWorks2012.Person.StateProvince;
/* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO
References:
https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?vi

NEW QUESTION 19
You have two database tables. Table1 is a partitioned table and Table 2 is a non-partitioned table.
Users report that queries take a long time to complete. You monitor queries by using Microsoft SQL Server Profiler. You observe lock escalation for Table1 and Table 2.
You need to allow escalation of Table1 locks to the partition level and prevent all lock escalation for Table2. Which Transact-SQL statement should you run for each table? To answer, drag the appropriate Transact-SQL statements to the correct tables. Each command 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: Since SQL Server 2008 you can also control how SQL Server performs the Lock Escalation – through the ALTER TABLE statement and the property LOCK_ESCALATION. There are 3 different options available:
Box 1: Table1, Auto
The default option is TABLE, means that SQL Server *always* performs the Lock Escalation to the table level –even when the table is partitioned. If you have your table partitioned, and you want to have a Partition Level Lock Escalation (because you have tested your data access pattern, and you don’t cause deadlocks with it), then you can change the option to AUTO. AUTO means that the Lock Escalation is performed to the partition level, if the table is partitioned, and otherwise to the table level.
Box 2: Table 2, DISABLE
With the option DISABLE you can completely disable the Lock Escalation for that specific table.
For partitioned tables, use the LOCK_ESCALATION option of ALTER TABLE to escalate locks to the HoBT level instead of the table or to disable lock escalation.
References:
http://www.sqlpassion.at/archive/2014/02/25/lock-escalations/

NEW QUESTION 20
You are a database developer for a company that delivers produce and other refrigerated goods to grocery stores. You capture the food storage temperature for delivery vehicles by using Internet of Things (loT) devices. You store the temperature data in a database table named vchicleTewperatures. The chillerSensorNumber column stores the identifier for the loT devices.
You need to create an indexed view that meets the following requirements:
• Persists the data on disk to reduce the amount of I/O.
• Provides the number of chillerSensorNumber items.
• Creates only a set sof summary rows.

Answer:

Explanation: