Today Big Q: 70-457 preparation?

The Microsoft technicians will almost always be looking for a way to decipher the latest [productsort] on the web questions and answers. You are able to down load the latest [productsort] dumps about our Examcollection internet site. We not necessarily offer only [productsort] pdf examinations but also the software edition. Examcollection [productsort] examination engine results in a simulator with the atmosphere when the [productsort] appears, offers a fantastic possibility to practice the particular Microsoft examination communicative atmosphere. According to your own exam preparing, you are able to pick any kind of [productsort] edition to study flexibility. Transferring [productsort] exam will end up more standard quicker simply by learning Microsoft [productsort] practice examinations.

2016 Aug sql server 2012 certification 70-457:

Q21. You use Microsoft SQL Server 2012 to develop a database application. 

You need to create an object that meets the following requirements: 

Takes an input variable 

Returns a table of values 

Cannot be referenced within a view 

Which object should you use? 

A. Scalar-valued function 

B. Inline function 

C. User-defined data type 

D. Stored procedure 

Answer: D 


Q22. 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? 

Which Transact-SQL query should you use? 

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW 

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS 

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId 

WHERE Customers.CustomerId = 1 FOR XML AUTO 

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO 

F. SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

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

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

Answer: E 


Q23. You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition: 

CREATE TABLE Inventory ( 

ItemID int NOT NULL PRIMARY KEY, 

ItemsInStore int NOT NULL, 

ItemsInWarehouse int NOT NULL) 

You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. 

The new column is expected to be queried heavily, and you need to be able to index the column. 

Which Transact-SQL statement should you use? 

A. ALTER TABLE Inventory 

ADD TotalItems AS ItemslnStore + ItemsInWarehouse 

B. ALTER TABLE Inventory 

ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED 

C. ALTER TABLE Inventory 

ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED 

D. ALTER TABLE Inventory 

ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) 

Answer: B 

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


Q24. You are a database developer of a Microsoft SQL Server 2012 database. 

You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. 

A sample of this data is as shown in the following table. 

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. 

Which Transact-SQL statement should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: D 


Q25. You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed. 

You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables. 

You need to configure the SQL Server environment to support these packages. 

What should you do? 

A. Create SSIS configuration files for the packages. 

B. Create an Integration Services catalog. 

C. Install Data Quality Services. 

D. Install Master Data services. 

Answer: B 

Reference: 

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


70-457 exam

Updated 70-457 training material:

Q26. You administer all the deployments of Microsoft SQL Server 2012 in your company. You have two servers in the same data center that hosts your production database. 

You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs. You also need to maintain transactional consistency of the data across both servers. You need to achieve these goals without manual intervention. 

Which configuration should you use? 

A. . Two servers configured in a Windows Failover Cluster in the same data center 

SQL Server configured as a clustered instance 

B. . SQL Server that includes an application database configured to perform transactional replication 

C. . Two servers configured in the same data center 

. A primary server configured to perform log-shipping every 10 minutes 

. A backup server configured as a warm standby 

D. . Two servers configured in different data centers 

. SQL Server Availability Group configured in Synchronous-Commit Availability Mode 

. One server configured as an Active Secondary 

E. . Two servers configured in the same data center 

. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode 

. One server configured as an Active Secondary 

F. . Two servers configured in different data centers 

. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode 

G. . SQL Server that includes an application database configured to perform snapshot replication 

H. . Two servers configured on the same subnet 

. SQL Server Availability Group configured in Synchronous-Commit Availability Mode 

Answer: H 

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


Q27. You have a database that contains the tables shown in the exhibit. (Click the Exhibit button). 

You need to create a query for a report. The query must meet the following requirements: 

*NOT use object delimiters. 

*Return the most recent orders first. 

*Use the first initial of the table as an alias. 

*Return the most recent order date for each customer. 

*Retrieve the last name of the person who placed the order. 

*Return the order date in a column named MostRecentOrderDate that appears as the last column in the report. 

The solution must support the ANSI SQL-99 standard. 

Which code segment should you use? 

To answer, type the correct code in the answer area. 

A. SELECT c.CustomerID -- optional 

B. LastName, max(o.OrderDate) 'MostRecentOrderDate' FROM Customer c LEFT OUTER JOIN Orders o ON o.CustomerID = c.CustomerID GROUP BY c.CustomerID, c.LastName ORDER BY 3 DESC 

C. select C.Lastname, P.MostRecentOrderDate from customers AS C inner join ( select customID, MostRecentOrderDate=max(orderDate) from orders group by customID ) P on C.customerID=P.CustomerID order by P.MostRecentOrderDate desc 

D. SELECT C.LastName, O.OrderDate AS MostRecentOrderDate FROM Customers AS C INNER JOIN Orders AS O ON C.CustomerID = O.CustomerID ORDER BY O.OrderDate DESC 

Answer: A 


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

Answer: A 


Q29. You administer a Microsoft SQL Server 2012. 

A process that normally runs in less than 10 seconds has been running for more than an hour. 

You examine the application log and discover that the process is using session ID 60. 

You need to find out whether the process is being blocked. 

Which Transact-SQL statement should you use? 

A. SELECT ~ FROM sys.dm_exec_sessions WHERE session_id = 60 

B. DBCC OPENTRAN 

C. EXEC sp_helpdb 60 

D. EXEC sp_who 60 

Answer: D 


Q30. You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type. 

You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate. 

You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted. 

Which Transact-SQL statement should you use? 

A. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END 

B. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END 

C. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN IF EXISTS ( SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN ROLLBACK TRAN END END 

D. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN IF EXISTS ( SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN ROLLBACK TRAN END END 

Answer: A 



see more 70-457 dumps