Q1. You have two SQL Server 2014 instances named SQLDev and SQLProd.
You plan to create a new database on SQLProd that will use SQL Server Authentication.
You need to ensure that when the new database is copied from SQLProd to SQLDev, users can connect to the database on SQLDev even if they do not have a login on the SQLDev instance.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Triggers
B. Contained database
C. SQL Server Analysis Services (SSAS) scripts
D. Extended Events
E. SQL Server Integration Services (SSIS) scripts
Answer: B
Q2. You have a SQL Server 2012 database named Database1.
Database1 has a table named Customers. Customers contains more than 1 million rows.
The database has a stored procedure that was created by using the following script:
You need to ensure that up_customers returns rows when the following statement is executed:
EXECUTE up_customers'1,2,3,4,5'
What should you do?
A. Update @CustcmerTypelist to use the int data type.
B. Convert @CustomerTypeList to a table variable.
C. Convert @CustomerTypeList to an XML variable.
D. Update @CustomerTypeList to use the XML data type.
Answer: B
Q3. You plan to execute the following code:
You need to identify how many rows will be in dbo.Table1 after you execute the code. How many rows should you identify?
A. 0
B. 1
C. 2
D. 3
Answer: A
Q4. You have a database that contains a user-defined function named Schema1.Udf1 and two tables named Schema1.Table1 and Schema1.Table2.
Schema1.Table1 has 1 million rows. The schema for Schema1.Table1 is configured as shown in the following table.
Schema1.Udf1 was defined by using the following code:
You need to write a query that will contain the following columns:
Country CountryID CustomerName
The solution must meet the following requirements:
Rows must be returned only if the function returns data.
The amount of time it takes the query to execute must be minimized.
Which query should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
Q5. You need to implement a solution that addresses the upload requirements. Which code segment should you use to implement the Conversions assembly?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q6. You are designing a SQL Server database for an order fulfillment system. You create a table named Sales.Orders by using the following script:
Each order is tracked by using one of the following statuses:
Fulfilled
Shipped
Ordered
Received
You need to design the database to ensure that you can retrieve the status of an order on a given date. The solution must ensure that new statuses can be added in the future.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Implement change data capture on the Sales.Orders table.
B. To the Sales.Orders table, add a column named Status that will store the order status. Update the Status column as the order status changes.
C. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.
D. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes.
Answer: B
Q7. You use SQL Server 2014 to maintain the data used by applications at your company.
You want to execute two statements.
You need to guarantee that either both statements succeed, or both statements fail together as a batch.
Which code should you use?
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
Answer: D
Q8. You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateSpeakerName.
What should you include in the recommendation?
A. The Distributed Transaction Coordinator (DTC)
B. Transactional replication
C. Change data capture
D. Change tracking
Answer: A
Q9. You have a server that has SQL Server 2014 installed. The server contains 100 user databases.
You need to recommend a backup solution for the user databases.
The solution must meet the following requirements:
Perform a transaction log backup every hour.
Perform a full backup of each database every week.
Perform a differential backup of each database every day.
Ensure that new user databases are added automatically to the backup solution.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Policy-Based Management
B. A Data Definition Language (DDL) trigger
C. SQL Server Agent jobs
D. A maintenance plan
Answer: D
Q10. You need to recommend a solution to minimize the amount of time it takes to execute USP_1.
With what should you recommend replacing Table1?
A. An indexed view
B. A function
C. A table variable
D. A temporary table
Answer: D