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 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.
• 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 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.

Comment: *

Name: *

Email: *

Verification: *

Which of the following SQL Server 2012 tools coordinates transactions across SQL Servers in a clustered environment?

• A.SQL Server Profiler
• B.SQL Server Query Optimizer
• C.SQL Server Management Studio
• D.Microsoft Distributed Transaction Coordinator

Comment: *

Name: *

Email: *

Verification: *

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to
1 in the following XML format.

Which Transact-SQL query should you use?
A:

B:

C:

D:

E:

F:

G:

H:

• A.Option A
• B.Option B
• C.Option C
• D.Option D
• E.Option E
• F.Option F
• G.Option G
• H.Option H

Correct Answer: E

Explanation/Reference:
Explanation:
For XML clause in Select Query allows you to return the query results in XML format. You can specify Auto, RAW, EXPLICIT and PATH in For XML clause.
Example:

Incorrect:
Not F: The following line is incomplete (a 1 need to be appended to end of it):
WHERE Customers.CustomerId
References: http://www.techbubbles.com/sql-server/for-xml-auto-mode-example-in-sql-server/

Comment: *

Name: *

Email: *

Verification: *

HOTSPOT
You administer two Microsoft SQL Server 2012 databases named Contoso and ContosoWarehouse.
You plan to replicate tables from the Contoso database to the ContosoWarehouse database.
Indexes will be added to the tables in ContosoWarehouse to improve the performance of reports.
You need to ensure that the following requirements are met:
* Indexes are not modified when the subscriptions are reinitialized.
* Only the clustered index from Contoso will be replicated.
* Minimal transaction log activity during the reinitialization.
What should you do? Choose the correct option(s).

Correct Answer:

Comment: *

Name: *

Email: *

Verification: *

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly.
You discover that a large amount of memory is consumed by single-use dynamic queries.
You need to reduce procedure cache usage from these statements without creating any additional indexes.
What should you do?

• A.Add a HASHhint to the query.
• B.Add a LOOPhint to the query.
• C.Add a FORCESEEKhint to the query.
• D.Add an INCLUDEclause to the index.
• E.Add a FORCESCANhint to the Attach query.
• F.Add a columnstore index to cover the query.
• G.Enable the optimize for ad hoc workloads option.
• H.Cover the unique clustered index with a columnstore index.
• I.Include a SET FORCEPLAN ONstatement before you run the query.
• J.Include a SET STATISTICS PROFILE ONstatement before you run the query.
• K.Include a SET STATISTICS SHOWPLAN_XML ONstatement before you run the query.
• L.Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READstatement before you run the query.
• M.Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOTstatement before you run the query.
• N.Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLEstatement before you run the query.

Comment: *

Name: *

Email: *

Verification: *