QUESTION 104Hotspot QuestionYou create a database table named FactSales by running the following Transact-SQL statements:You must optimize the indexes without making changes to the ix_FactSales_EmployeeKey index.You need to implement a columnstore index for the table.How should you complete the Transact-SQL code? To answer, select the appropriate Transact-SQL segments in the answer area.NOTE: Each correct selection is worth one point.Answer:

QUESTION 105Drag and Drop QuestionYou 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:

QUESTION 106Drag and Drop QuestionYou need to build a function that meets the following requirements:– Returns multiple rows– Optimizes the performance of the query within the functionHow should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL statements 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.Answer: Explanation:https://technet.microsoft.com/en-us/library/ms189294(v=sql.105).aspx

QUESTION 107Drag and Drop QuestionYou 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:

QUESTION 108Hotspot QuestionBackgroundYou 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 TableYou use the following Transact-SQL statements to create, configure, and populate the Employee table:ApplicationYou 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.ExhibitUsers 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:https://msdn.microsoft.com/en-us/library/ms187956.aspx

QUESTION 109Drag and Drop QuestionCase StudyBackgroundYou 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 TableYou use the following Transact-SQL statements to create, configure, and populate the Employee table:ApplicationYou 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.ExhibitBoth of the stored procedures experience blocking issues. UspB must not abort if UspA commits changes to a row before UspB commits changes to the same row. UspA must not abort if UspB commits changes to a row before UspA commits changes to the same row.You need to specify the transaction isolation levels to enable row versioning.How should you complete the Transact-SQL statements? To answer, drag the 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.Answer:

QUESTION 110Drag and Drop QuestionYou 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 TRANSACTIONBox 2: UPDATE … #DisktableBox 3: IF… SELECT INTO …#DisktableBox 4: .. DELAYED_DURABILITY = ONThe 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 } ) ]https://docs.microsoft.com/en-us/sql/relational-databases/logs/control-transaction-durability?view=sql-server-2017

QUESTION 111Drag and Drop QuestionYou 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:ExampleThe 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 READONLYEtc.?/* 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.00FROM AdventureWorks2012.Person.StateProvince;/* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP;GOhttps://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters- database-engine?view=sql-server-2017

QUESTION 112Drag and Drop QuestionYou need to implement triggers to automate responses to the following events:– SQL Server logons– Database schema changes– Database updatesWhich trigger types should you use? To answer, drag the appropriate trigger types to the appropriate scenarios. Each trigger 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.NOTE: Each correct selection is worth one point.Answer: Explanation:Box 1: LOGONLogon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server.Box 2: INSTEAD OF INSERTAn “INSTEAD of trigger” is executed instead of the original operation, and not combining with the operation. INSTEAD OF triggers override the standard actions of the triggering statement. It can be used to bypass the statement and execute a whole different statement, or just help us check and examine the data before the action is done.Box 3: DDLDDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the databaseNote:You can write triggers that fire whenever one of the following operations occurs:DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any userDDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the databaseDatabase events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the databaseReferences:https://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htmhttps://social.technet.microsoft.com/wiki/contents/articles/28152.t-sql-instead-of-triggers.aspxhttps://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-2017

QUESTION 113Hotspot QuestionYou have a table named Person.Address that includes the following columns: AddressID AddressLine1 AddressLine2 City StateProvinceID PostakCode RowGuid ModifiedDateYou need to create a nonclustered index on PostalCode named IX_Address_PostalCode that uses the following included columns: AddressLine1 AddressLine2 City StateProvinceIDHow should you complete the Transact-SQL statement? To answer, select the appropriate Transact_SQL segments in the answer are.Answer: Explanation:Box 1: INDEXBox 2: ONBox 3: INCLUDEINCLUDE (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.https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql- server-2017

QUESTION 114Hotspot QuestionYou are developing queries and stored procedures to support a line-of-business application.You need to use the appropriate isolation level based on the scenario.Which isolation levels should you implement? To answer, select the appropriate isolation level for each scenario in the answer area. Each isolation level may be used only one.NOTE: Each correct selection is worth one point.Answer: Explanation:Box 1: READ UNCOMMITTEDTransactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction.Box 2: READ COMMITTEDREAD COMMITTED specifies that statements cannot read data that has been modified but not committed by other transactions.SERIALIZABLE specifies that statements cannot read data that has been modified but not yet committed by other transactions.Box 3: REPEATABLE READREPEATABLE READ specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.Box 4: SNAPSHOTIf READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement.https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017