Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 - 070-457 FREE EXAM DUMPS QUESTIONS & ANSWERS]

Question 1

You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance. What should you do?

A. Query the sys.dm_db_missing_index_columns DMV. B. Query the sys.dm_db_index_usage_stats DMV. C. Use the Database Engine Tuning Advisor. D. Query the sys.dm_db_missing_index_details DMV.

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 2

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server. What should you create?

A. a Resource Pool B. an Alert C. a Database Audit Specification D. a Server Audit Specification E. a Policy F. an Extended Event session G. a SQL Profiler Trace

Discussion 0

Correct Answer: G Vote an answer

Question 3

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 CustomerIdvalue set to 1 in the following XML format.

Which Transact-SQL query should you use?

A. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO B. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers=CustomerId = 1 FOR XML RAW, ELEMENTS D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO E. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW F. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS G. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers') H. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 4

You administer a single server that contains a Microsoft SQL Server 2012 default instance. You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions. You need to ensure that the application login is unable to access other production databases. What should you do?

A. Use the SQL Server default instance and configure an affinity mask. B. Use the SQL Server default instance and enable Contained Databases. C. Install a new named SQL Server instance on the server. D. Install a new default SQL Server instance on the server.

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 5

You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

A. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement. B. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role. C. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure. D. Grant the sysadmin role on the database to User1. E. Grant the db_owner role on the database to User1.

Discussion 0

Correct Answer: A,C Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 6

You administer a Microsoft SQL Server 2012 database. The database contains a customer table created by using the following definition:

You need to ensure that the minimum amount of disk space is used to store the data in the customer table. What should you do?

A. Implement row-level compression. B. Implement page-level compression. C. Implement Unicode compression. D. Convert all indexes to Column Store indexes.

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 7

You use Microsoft SQL Server 2012 to develop a database application. Your application sends data to an NVARCHAR(MAX) variable named @var. You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9). Which code segment should you use?select

A. BEGIN TRY
SELECT convert(decimal(36,9), @var) AS Value, 'True' AS BadCast
END TRY
BEGIN CATCH
SELECT convert(decimal(36,9), @var) AS Value, 'False' AS BadCast
END CATCH B. SELECT
IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False')
AS BadCast C. SELECT
CASE
WHEN convert(decimal(36,9), @var) IS NULL
THEN 'True'
ELSE 'False'
END
AS BadCast D. TRY( SELECT convert(decimal(36,9), @var) SELECT 'True' AS BadCast
)
CATCH(
SELECT 'False' AS BadCast
)

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 8

You use Microsoft SQL Server 2012 to develop a database that has two tables named Div1Cust and Div2Cust. Each table has columns named DivisionID and CustomerId . None of the rows in Div1Cust exist in Div2Cust. You need to write a query that meets the following requirements:
The rows in Div1Cust must be combined with the rows in Div2Cust.
The result set must have columns named Division and Customer.
Duplicates must be retained.
Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Build List and Reorder:

Discussion 0

Correct Answer:

Explanation:
--Burgos - NO
Reference: http://msdn.microsoft.com/en-us/library/ms180026.aspx Reference: http://msdn.microsoft.com/en-us/library/ms188055.aspx

Question 9

Question 10

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: At 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are corrupted on the transactional database. You need to ensure that the transactional database is restored. You also need to ensure that data loss is minimal. What should you do?

A. Perform a partial restore. B. Perform a point-in-time restore. C. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup. D. Restore the latest full backup. Then, restore the latest differential backup. E. Restore the latest full backup. F. 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. G. Perform a page restore. H. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.

Discussion 0

Correct Answer: G Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 11

You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition:

You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

A. Implement row-level compression. B. Implement page-level compression. C. Implement Unicode Compression. D. Convert all indexes to Column Store indexes.

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 12

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(Sales.InvoiceSchema) B. DECLARE @XML1 XML @XML1 = Sales.InvoiceSchema CREATE XML SCHEMA COLLECTION XML1 AS @XML1

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 13

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. You need to ensure that when the transaction fails, dbo. ModifyData meets the following requirements:
Does not return an error
Closes all opened transactions
Which Transact-SQL statement should you use?

A. BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
END CATCH B. BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
THROW;
END CATCH C. BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ TRANCOUNT = 0
ROLLBACK TRANSACTION;
END CATCH D. BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT = 0
ROLLBACK TRANSACTION;
THROW;
END CATCH

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 14

Question 15

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. You need to ensure that the following requirements are met:
Students must be ranked based on their average marks.
If one or more students have the same average, the same rank must be given to these students.
Consecutive ranks must be skipped when the same rank is assigned.
Which Transact-SQL query should you use?

A. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1 B. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1 C. SELECT StudentCode as Code,
DENSE_RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode D. SELECT StudentCode as Code,
RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode E. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1 F. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1 G. SELECT Id, Name, Marks,
DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank
FROM StudentMarks H. SELECT StudentCode as Code,
NTILE(2) OVER(ORDER BY AVG (Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).