Q1. 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.
Answer: D
Q2. You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN).
Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors.
You need to reduce the occurrence of these timeout errors.
What should you do?
A. Set the Merge agent on the problem subscribers to use the slow link agent profile.
B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
C. Change the Merge agent on the problem subscribers to run continuously.
D. Set the Remote Connection Timeout on the Publisher to 0.
Answer: A
Q3. You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores.
You discover performance issues when complex calculations are performed on large amounts of data under heavy system load.
You need to limit the number of cores that handle the processing.
What should you configure?
A. Max worker threads
B. Processor affinity
C. I/O affinity
D. Lightweight pooling
Answer: C
Q4. 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.
Answer: D
Q5. You administer a Microsoft SQL Server 2012 environment. One of the SQL Server 2012 instances contains a database named Sales.
You plan to migrate Sales to Windows Azure SQL Database.
To do so, you need to implement a contained database.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Enable server property Contained Database Authentication
B. Set database containment to partial
C. Set database containment to full
D. Disable server property cross db ownership chaining
E. Set database containment to AZURE
F. Disable server property Contained Database Authentication
Answer: ABE
Q6. You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 Standard Edition. The server hosts a database named Orders.
Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD.
You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.
Which Transact-SQL command should you use?
A. ALTER INDEX IX_OrderDateON CustomerOrderDISABLE
B. ALTER INDEX IX_OrderDateON CustomerOrderENABLE
C. ALTER INDEX IX_OrderDateON CustomerOrderREORGANIZE
D. ALTER INDEX IX OrderDateON CustomerOrderREBUILD
Answer: C
Q7. You administer a Microsoft SQL Server 2012 database.
You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01. You need to configure the availability group to have the highest availability. You also need to ensure that no data is lost. Which Transact-SQL statement should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q8. You administer a Windows Azure SQL Database database named Orders.
You need to create a copy of Orders named Orders_Reporting.
Which Transact-SQL command should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Q9. Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column.
You need to increase the values in the Price column of all products in the Products table by 5 percent.
You also need to log the changes to the ProductsPriceLog table.
Which Transact-SQL query should you use?
A. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
B. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, inserted.Price, deleted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
C. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price *
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
D. UPDATE Products SET Price = Price * 1.05
INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice;
SELECT ProductCode, Price, Price * 1.05 FROM Products
Answer: A
Q10. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01.
What should you create?
A. A Database Audit Specification
B. A Policy
C. An Alert
D. A SQL Profiler Trace
E. A Resource Pool
F. An Extended Event session
G. A Server Audit Specification
Answer: G