Limited Time Discount Offer
30% Off - Ends in 02:00:00

X

Microsoft 70-457 Dumps

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

Questions & Answers for Microsoft 70-457

Showing 1-15 of 183 Questions

Question #1

You administer a Microsoft SQL Server 2012 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the
following statements:

You attempt to restore the Orders database and the restore fails. You copy the encryption
file to the original location.
A hardware failure occurs and so a new server must be installed and configured.
After installing SQL Server to the new server, you restore the Orders database and copy
the encryption files to their original location. However, you are unable to access the
database.
You need to be able to restore the database.
Which Transact-SQL statement should you use before attempting the restore?

A. Option A

B. Option B

C. Option C

D. Option D

Reference: http://msdn.microsoft.com/en-us/library/ff773063.aspx

Question #2

You administer a Microsoft SQL Server database named Sales. The database is 3
terabytes in size.
The Sales database is configured as shown in the following table.

You discover that all files except Sales_2.ndf are corrupt.
You need to recover the corrupted data in the minimum amount of time.
What should you do?

A. Perform a restore from a full backup.

B. Perform a transaction log restore.

C. Perform a file restore.

D. Perform a filegroup restore.

Question #3

You are developing a database application by using Microsoft SQL Server 2012.
You have a query that runs slower than expected.
You need to capture execution plans that will include detailed information on missing
indexes recommended by the query optimizer.
What should you do?

A. Add a HASH hint to the query.

B. Add a LOOP hint to the query.

C. Add a FORCESEEK hint to the query.

D. Add an INCLUDE clause to the index.

E. Add a FORCESCAN hint 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 ON statement before you run the query.

J. Include a SET STATISTICS PROFILE ON statement before you run the query. K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query. L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query. M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query. N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Question #4

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. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01\Contoso01' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)

B. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01\Contoso01' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL)

C. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01\Contoso01' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)

D. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01\Contoso01' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL)

Question #5

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. Install a new named SQL Server instance on the server.

C. Use the SQL Server default instance and enable Contained Databases.

D. Install a new default SQL Server instance on the server.

Reference:
http://msdn.microsoft.com/en-us/library/ms187104.aspx

Question #6

You use Microsoft SQL Server 2012 to develop a database application.
You create two tables by using the following table definitions.

Which five Transact-SQL statements should you use? (To answer, move the appropriate
SQL statements from the list of statements to the answer area and arrange them in the
correct order.)

Question #7

Your application contains a stored procedure for each country. Each stored procedure
accepts an employee identification number through the @EmpID parameter.
You plan to build a single process for each employee that will execute the stored procedure
based on the country of residence.
Which approach should you use?

A. a recursive stored procedure

B. Trigger

C. An UPDATE statement that includes CASE

D. Cursor

E. The foreach SQLCLR statement

Question #8

You have a view that was created by using the following code:

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory.
Sales.fn_OrdersByTerritory must meet the following requirements:
*Accept the @T integer parameter.
*Use one-part names to reference columns.
*Filter the query results sorted by SalesTerritoryID.
*Return the columns in the same order as the order used in OrdersByTerritoryView.
Which code segment should you use?
To answer, type the correct code in the answer area.

CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int)
RETURNS TABLE
AS
RETURN
(
SELECT OrderID,OrderDate,SalesTerrirotyID,TotalDue
FROM Sales.OrdersByTerritory
WHERE SalesTerritoryID = @T
)

Question #9

You want to add a new GUID column named BookGUID to a table named dbo.Book that
already contains data.
BookGUID will have a constraint to ensure that it always has a value when new rows are
inserted into dbo.Book.
You need to ensure that the new column is assigned a GUID for existing rows.
Which four Transact-SQL statements should you use? (To answer, move the appropriate
SQL statements from the list of statements to the answer area and arrange them in the
correct order.)

Question #10

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

Question #11

You develop a Microsoft SQL Server 2012 database. The database is used by two web
applications that access a table named Products.
You want to create an object that will prevent the applications from accessing the table
directly while still providing access to the required data.
You need to ensure that the following requirements are met:
Future modifications to the table definition will not affect the applications' ability to access
data.
The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the existing
applications.
What should you create for each application?

A. views

B. table partitions

C. table-valued functions

D. stored procedures

Question #12

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

Explanation:
Http://msdn.microsoft.com/en-us/library/ms177564.aspx

Question #13

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 retrieve the students who scored the highest marks for each subject along with
the marks.
Which Transact-SQL query should you use?

A. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode

B. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks

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, NTILE(2) 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, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1

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

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

Question #14

You administer a Microsoft SQL Server 2012 database.
The database contains a table that has the following definition:

You want to export data from the table to a flat file by using the SQL Server Import and
Export Wizard.
You need to ensure that the following requirements are met:
✑ The first row of the file contains the first row of data.
✑ Each record is of the same length.
✑ The date follows the U.S. date format.
✑ The file supports international characters.
What should you do? (To answer, configure the appropriate option or options in the dialog
box in the answer area.)

Question #15

You develop a Microsoft SQL Server 2012 database that contains tables named
Customers and Orders.
The tables are related by a column named CustomerId.
You need to create a query that meets the following requirements:
✑ Returns the CustomerName for all customers and the OrderDate for any orders
that they have placed.
✑ Results must not include customers who have not placed any orders.
Which Transact-SQL query should you use?

A. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CuscomerlD = Orders.CustomerId

B. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerId

C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerId = Orders.CustomerId

D. SELECT CustomerName, OrderDate FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId

Reference: http://msdn.microsoft.com/en-us/library/ms177634.aspx

×