Q1. You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores. 

You discover performance issues when large amounts of data are written to tables under heavy system load. 

You need to limit the number of cores that handle I/O. 

What should you configure? 

A. Processor affinity 

B. Lightweight pooling 

C. Max worker threads 

D. I/O affinity 

View Answer

Q2. You administer a Microsoft SQL Server 2012 instance. 

You need to stop a blocking process that has an SPID of 64 without stopping other processes 

What should you do? 

A. Execute the following Transact-SQL statement: EXECUTE sp_KillSPID 64 

B. Restart the SQL Server service. 

C. Execute the following Transact-SQL statement: KILL 64 

D. Execute the following Transact-SQL statement: ALTER SESSION KILL '64' 

View Answer

Q3. You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. 

The recovery model and backup schedule are configured as shown in the following table: 

One of the hard disk drives that stores the reporting database fails at 16:40 hours. 

You need to ensure that the reporting database is restoreD. You also need to ensure that data loss is minimal. 

What should you do? 

A. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup. 

B. Perform a partial restore. 

C. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup. 

D. Perform a point-in-time restore. 

E. Restore the latest full backup. 

F. Perform a page restore. 

G. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup. 

H. Restore the latest full backup. Then, restore the latest differential backup. 

View Answer

Q4. You administer a SQL 2012 server that contains a database named SalesDB. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales. 

UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema. 

You need to remove the Select permission for UserA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions. 

Which Transact-SQL statement should you use? 

A. DENY SELECT ON Object::Regions FROM UserA 

B. DENY SELECT ON Schema::Customers FROM UserA 

C. EXEC sp_addrolemember 'Sales', 'UserA' 

D. REVOKE SELECT ON Object::Regions FROM UserA 

E. REVOKE SELECT ON Object::Regions FROM Sales 

F. EXEC sp_droproiemember 'Sales', 'UserA' 

G. REVOKE SELECT ON Schema::Customers FROM UserA 

H. DENY SELECT ON Object::Regions FROM Sales 

I. DENY SELECT ON Schema::Customers FROM Sales 

J. REVOKE SELECT ON Schema::Customers FROM Sales 

View Answer

Q5. You administer a Windows Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails. 

You add two Windows groups as logins for the server: 

CORP\Employees - All company employees CORP\HRAdmins - HR administrators only HR Administrators are also company employees. 

You need to grant users access according to the following requirements: 

CORP\Employees should have SELECT access to the Employees table. 

Only users in CORP\HRAdmins should have SELECT access to the SalaryDetails table. 

Logins are based only on Windows security groups. 

What should you do? 

A. Create a database role called Employees. 

Add CORP\Employees to the db_datareader role. 

Add all company employees except HR administrators to the Employees role. 

Deny SELECT access to the SalaryDetails table to the Employees role. 

B. Create a database role called HRAdmins. 

Add all company employees except HR administrators to the db_datareader role, 

Add all HR administrators to the HRAdmins role. 

Grant SELECT access to the SalaryDetails table to the HRAdmins role. 

Deny SELECT access to the SalaryDetails table to the db_datareader role. 

C. Create two database roles: Employees and HRAdmins. Add all company employees to the Employees role. 

Add HR administrators to the HRAdmins role. 

Grant SELECT access to all tables except SalaryDetails to the Employees role. 

Grant SELECT access to the SalaryDetails table to the HRAdmins role. 

Deny SELECT access to the SalaryDetails table to the Employees role. 

D. Create a database role called Employees. 

Add all HR administrators to the db_datareader role. 

Add all company employees to the Employees role. 

Grant SELECT access to all tables except the SalaryDetails table to the Employees role. 

Deny SELECT access to the SalaryDetails table to the Employees role. 

View Answer

Q6. You are the administrator of a Microsoft SQL Server 2012 server. 

Some applications consume significant resources. You need to manage the server workload by restricting resource-intensive applications 

You need to dynamically limit resource consumption. 

What should you do? 

A. Set up Service Broker to ensure that applications are not allowed to consume more than the specified amount of resources. 

B. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor. 

C. Configure Extended Events to monitor and restrict resource limits allowed by each application type. 

D. Create a new Plan Guide with a Scope Type of sql and define the resource limits for each application. 

View Answer

Q7. You develop a database for a travel application. You need to design tables and other database objects. You create a view that displays the dates and times of the airline schedules on a report. 

You need to display dates and times in several international formats. 

What should you do? 

A. Use the CAST function. 

B. Use the DATE data type. 

C. Use the FORMAT function. 

D. Use an appropriate collation. 

E. Use a user-defined table type. 

F. Use the VARBINARY data type. 

G. Use the DATETIME data type. 

H. Use the DATETIME2 data type. 

I. Use the DATETIMEOFFSET data type. 

J. Use the TODATETIMEOFFSET function. 

View Answer

Q8. You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. 

There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space. 

You also need to prevent reading queries from blocking writing queries. 

Which isolation level should you use? 

A. SERIALIZABLE 

B. SNAPSHOT 

C. READ COMMITTED SNAPSHOT 

D. REPEATABLE READ 

View Answer

Q9. You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies. 

The view has the following definition: You need to ensure that users can update only the phone numbers by using this view. What should you do? 

A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement. 

B. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view. 

C. Create an AFTER UPDATE trigger on the view. 

D. Create an INSTEAD OF UPDATE trigger on the view. 

View Answer

Q10. You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales. UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema. 

You need to ensure that the following requirements are met: 

The Sales role does not have the Select permission on the Customers schema. UserA has the Select permission on the Regions table. 

Which Transact-SQL statement should you use? 

A. DENY SELECT ON Object::Regions FROM Sales 

B. DENY SELECT ON Schema::Customers FROM Sales 

C. REVOKE SELECT ON Object::Regions FROM Sales 

D. REVOKE SELECT ON Schema::Customers FROM Sales 

E. DENY SELECT ON Object::Regions FROM UserA 

F. DENY SELECT ON Schema::Customers FROM UserA 

G. REVOKE SELECT ON Object::Regions FROM UserA 

H. REVOKE SELECT ON Schema::Customers FOR UserA 

I. EXEC sp_addrolemember 'Sales', 'UserA' 

J. EXEC sp_droprolemember 'Sales', 'UserA' 

View Answer