Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
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 to set up a monitoring process that will capture any cases where statistics could have been useful if they existed.
What should you do?
A. Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the primary key columns in the database.
B. Use the missing_column_statistics extended event.
C. Query the sys.statistics system view to see all cases where the statistics were last needed.
D. Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes, filtering on is_hypothetical.
Answer: B
Q2. You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that the index consumes more physical disk space than necessary.
You need to minimize the amount of disk space that the index consumes.
What should you set from the index options?
A. STATISTICS_NORECOMPUTE = OFF
B. FILLFACTOR = 80
C. FILLFACTOR = 0
D. STATISTICS_NORECOMPUTE = ON
Answer: C
Q3. You need to recommend a solution that addresses the file storage requirements.
What should you include in the recommendation?
A. FileStream
B. FileTable
C. The varbinary data type
D. The image data type
Answer: B
Q4. You need to encapsulate a T-SQL script into a reusable user-defined object.
The object must meet the following requirements:
. Permit insertions into a table variable.
. Support structured exception handling.
. Prevent changes to the definition of referenced objects.
. Support the use of the APPLY operator on the output of the object.
Which type of object should you use?
A. An inline table-valued function
B. A stored procedure
C. A scalar user-defined function
D. A multi-statement table-valued function
Answer: C
Q5. You need to recommend a solution to meet the security requirements of the junior database administrators.
What should you include in the recommendation?
A. A server role
B. A database role
C. A credential
D. A shared login
Answer: C
Q6. You have a table named Rooms that contains three columns. You execute the following query:
You discover the execution plan shown in the exhibit. (Click the Exhibit button.)
You need to recommend a solution to reduce the amount of time it takes to execute the query.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Include the RoomName column and the Position column in the Room_IX index.
B. Create a nonclustered index for RoomName, Id, and Position.
C. Create a clustered index for Id.
D. Use the WITH (INDEX(Room_IX),NOLOCK) query hint.
Answer: B
Q7. You are creating a table to support an application that will cache data outside of SQL Server.
The application will detect whether cached values were changed before it updates the values.
You need to create the table, and then verify that you can insert a row into the table. Which code segment should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q8. You have a SQL Server 2014 instance named SQL1.
SQL1 creates error events in the Windows Application event log.
You need to recommend a solution that will run an application when SQL1 logs a specific error in the Application log.
Which SQL elements should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)
A. A policy
B. A maintenance plan
C. An alert
D. A job
E. A trigger
Answer: DE
Q9. 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
Q10. You have a SQL Server 2014 environment That includes four servers. The servers are configured as shown in the following table.
You plan to configure Policy-Based Management to enforce the following rules:
. On Server1, enable SQL Server password policies and enable the default trace. . On Server3, ensure that the names of user-defined stored procedures begin with the prefix "usp_" and ensure that all databases use a case-sensitive collation.
You need to recommend which server you must configure as a Central Management Server.
Which server should you recommend? (Each correct answer presents a complete solution. Choose all that apply.)
A. Server1
B. Server2
C. Server3
D. Seiver4
Answer: AC
Q11. You have a SQL Server 2012 database named Database1. You execute the following code:
You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1.
What should you do?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Q12. You need to modify usp.GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.
What should you add to usp.GetOrdersAndItems?
A. Add WITH (NOLOCK) to the end of line 47.
B. Add SET TRANSACTION ISOLATION LEVEL READ COMMITTED to line 44.
C. Add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to line 44.
D. Add WITH (READPAST) to the end of line 47.
Answer: B
Q13. You have a SQL Azure database.
You need to identify which keyword must be used to create a view that will be indexed.
Which keyword should you identify?
A. SCHEMABINDING
B. VIEW_METADATA
C. DISTINCT
D. DEFAULT
Answer: A
Q14. You have a table named Table1 that stores customer data.
Each customer has a credit limit that can only be discovered by querying multiple tables.
You need to ensure that the value of the credit limit is returned by executing a query on Table1.
What should you create?
A. A trigger that uses a ranking function
B. A trigger that uses a table-valued function
C. A calculated column that uses a table-valued function
D. A calculated column that uses a scalar function
Answer: C
Q15. You have a SQL Server 2014 environment That contains 20 servers.
The corporate security policy states that all SQL Server 2014 instances must meet specific security standards.
You need to recommend a management strategy for the SQL Server 2014 servers.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Multi server jobs
B. Policy-Based Management
C. Common criteria compliance
D. Maintenance plans
Answer: B
Q16. You are creating a table named Orders.
You need to ensure that every time a new row is added to the Orders table, a table that is used for auditing is updated.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A Data Definition Language (DDL) trigger
B. A DEFAULT constraint
C. A CHECK constraint
D. A FOREIGN KEY constraint
E. A data manipulation language (DML) trigger
Answer: E