Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 Sample Questions:

1. You administer two Microsoft SQL Server 2012 servers named ServerA and ServerB. You use a database named AdventureWorks. You need to prepare the AdventureWorks database for database mirroring. ServerB will act as the mirror in a mirroring partnership along with ServerA. Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Build List and Reorder:

2. DRAG AND DROP You administer a Microsoft SQL Server 2012 server. A variety of issues occur from time to time in the production environment. You need to identify the appropriate tool for each issue. Which tool or tools should you use? (To answer, drag the appropriate tool or tools to the correct issue or issues in the answer area. Each tool may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)
Select and Place:

3. 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 Schema::Customers FROM Sales
B) EXEC sp_droproiemember 'Sales', 'UserA'
C) DENY SELECT ON Schema::Customers FROM UserA
D) EXEC sp_addrolemember 'Sales', 'UserA'
E) DENY SELECT ON Object::Regions FROM Sales
F) REVOKE SELECT ON Object::Regions FROM Sales
G) REVOKE SELECT ON Object::Regions FROM UserA
H) REVOKE SELECT ON Schema::Customers FROM UserA
I) DENY SELECT ON Object::Regions FROM UserA
J) REVOKE SELECT ON Schema::Customers FROM Sales

4. You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of the
XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.
Which code segment should you use?
To answer, type the correct code in the answer area.

A) DECLARE @XML1 XML @XML1 = Sales.InvoiceSchema CREATE XML SCHEMA COLLECTION XML1 AS @XML1
B) Declare @XML1=XML(Sales.InvoiceSchema)

5. You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

A) CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
B) ALTER INDEX ALL ON OrderDetail REBUILD
C) ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
D) ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

Solutions: