Q1. You need to configure security on the Product table for customer support representatives. Which two actions should you perform? Each correct answer presents part of the solution. Choose two. A. Create a view called CustProduct that includes columns ProductID. ProductName, Product Description, QuantityOnHand, ProductPrice, ProductCost, and ProductSupplierID. B. GRANT ALL on CustProduct TO OurDomain\CustomerSupport C. Create a user-defined data type called CustProduct that…
Q1. You need to recommend a change to USP_3 to ensure that the procedure completes only if all of the UPDATE statements complete. Which change should you recommend? A. Set the XACT_ABORT option to off B. Set the XACT_ABORT option to on. C. Set the IMPLICIT_TRANSACTIONS option to off. D. Set the IMPLICIT_TRANSACTIONS option to on. View AnswerAnswer: B Q2. You need to recommend a solution for…
Q1. You have a server named Server1 that has 16 processors. You plan to deploy multiple instances of SQL Server 2014 to Server1. You need to recommend a method to allocate processors to each instance. What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer. A. Processor affinity B. Windows System Resource Manager (WSRM) C. Max Degree…
Q1. You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table. After the failover is complete, a user receives the following error message when connecting to DB1 on Server2: "Msg 916, Level 14, State 1, Line 1 The server principal "Account1" is not able to access the database "DB1" under the…
Q1. You are the new database administrator for a SQL Server 2014 instance. You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off. You see no evidence that any maintenance has been occurring. You want to set up monitoring to see if query performance is being affected. You need…
Q1. You are evaluating the table design. You need to recommend a change to Tables.sql that reduces the amount of time it takes for usp_AttendeesReport to execute. What should you add at line 14 of Tables.sql? A. FullName nvarchar(100) NOT NULL CONSTRAINT DF_FuIlName DEFAULT (dbo.CreateFullName (FirstName, LastName)), B. FullName AS (FirstName +‘ ’+ LastName), C. FullName nvarchar(100) NOT NULL DEFAULT (dbo.CreateFullName (FirstName, LastName)). D. FullName AS…
Q1. You need to recommend a disaster recovery strategy for the Inventory database. What should you include in the recommendation? A. Log shipping B. SQL Server Failover Clustering C. AlwaysOn availability groups D. Peer-to-peer replication View AnswerAnswer: A Q2. Your company has a SQL Azure subscription. You implement a database named Database1. Database1 has two tables named Table1 and Table2. You create a stored procedure named sp1. Sp1 reads…
Q1. You need to modify usp_SelectSpeakersByName to support server-side paging. The solution must minimize the amount of development effort required. What should you add to usp_SelectSpeakersByName? A. A table variable B. An OFFSET-FETCH clause C. The ROWNUMBER keyword D. A recursive common table expression View AnswerAnswer: B Q2. You are planning the ManufacturingSteps table. You need to define the ProductID column in the CREATE TABLE statement. Which code segment…
