2013 Latest MCSA 70-461 Exam Questions 41-45

Ensurepass QUESTION 41 Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. You need to ensure that your query executes in the minimum possible time. Which query should you Read more [...]

2013 Latest MCSA 70-461 Exam Questions 36-40

Ensurepass QUESTION 36 A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its preceding year. Which Transact-SQL query should you use? A. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit FROM Profits B. SELECT Territory, Year, Profit, LAG(Profit, Read more [...]

2013 Latest MCSA 70-461 Exam Questions 31-35

Ensurepass QUESTION 31 You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements: Status information must be logged to a status table. If the status table does not exist at the beginning of the batch, it must be created. Which object should you use? A. Scalar user-defined function B. Inline user-defined function C. Table-valued user-defined function D. Stored procedure Correct Answer: D Explanation/Reference: Verified answer Read more [...]

2013 Latest MCSA 70-461 Exam Questions 26-30

Ensurepass QUESTION 26 You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) EmTmp " Column Nam Condensed Type EmployeeIDint EmployeeNumchar{lO) LastNamenv har(21 ) FirstNamenvarchar(2 }_ MiddlNamnvarchar(200) DatHireddate DepartmentIDint JobTrtJevarchar(200) RportsToJD nt Col umn name Oescriptjon EmployeeID(pk) uniquely identifies the employee record in the table Used Read more [...]

2013 Latest MCSA 70-461 Exam Questions 21-25

Ensurepass QUESTION 21 You have a database that contains the tables as shown below: You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column Read more [...]

2013 Latest MCSA 70-461 Exam Questions 16-20

Ensurepass QUESTION 16 You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.) You have the following query: You need to recreate the query to meet the following requirements: Reference columns by using one-part names only. Sort aggregates by SalesTerritoryID, and then by ProductID. Order the results in descending order from SalesTerritoryID to ProductID. The solution must use the existing SELECT clause and FROM clause. Which code segment should you use? To Read more [...]

2013 Latest MCSA 70-461 Exam Questions 11-15

Ensurepass QUESTION 11 You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. You need to ensure that users are able to modify data by using the view. What should you do? A. Create an AFTER trigger on the view. B. Modify the view to use the WITH VIEW_METADATA clause. C. Create an INSTEAD OF trigger on the view. D. Modify the view to an indexed view. Correct Answer: C Explanation/Reference: Verified answer Read more [...]

2013 Latest MCSA 70-461 Exam Questions 96-199

Ensurepass QUESTION 1 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 NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID Read more [...]

2013 Latest MCSA 70-461 Exam Questions 6-10

Ensurepass QUESTION 6 You develop a Microsoft SQL Server 2012 database that contains a table named Customers. The Customers table has the following definition: You need to create an audit record only when either the MobileNumber or HomeNumber column is updated. Which Transact-SQL query should you use? A. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobileNumber) - - Create Audit Records B. CREATE TRIGGER TrgPhoneNumberChange ON Customers Read more [...]

2013 Latest MCSA 70-461 Exam Questions 91-95

Ensurepass QUESTION 6 You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements: Displays the details of only students from Canada. Allows insertion of details of only students from Canada. 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.) Build List and Reorder: Correct Answer: Explanation/Reference: Verified Read more [...]

2013 Latest MCSA 70-461 Exam Questions 86-90

Ensurepass QUESTION 1 You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you Read more [...]

2013 Latest MCSA 70-461 Exam Questions 81-85

Ensurepass QUESTION 36 You use a Microsoft SQL Server 2012 database. You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer. Which four T-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.) Build List and Reorder: Correct Answer: Explanation/Reference: Reference: http://msdn.microsoft.com/en-us/library/ms191432.aspx QUESTION Read more [...]

2013 Latest MCSA 70-461 Exam Questions 76-80

Ensurepass QUESTION 31 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 Read more [...]

2013 Latest MCSA 70-461 Exam Questions 71-75

Ensurepass QUESTION 26 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 top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2. Which Transact-SQL query should you use? A. SELECT StudentCode as Code, RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM Read more [...]

2013 Latest MCSA 70-461 Exam Questions 66-70

Ensurepass QUESTION 21 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 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 Read more [...]

2013 Latest MCSA 70-461 Exam Questions 61-65

Ensurepass QUESTION 16 Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed on the current day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use? A. SELECT COUNT(*) FROM SalesOrders WHERE Read more [...]

2013 Latest MCSA 70-461 Exam Questions 56-60

Ensurepass QUESTION 11 You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. You need to improve the performance of the view by persisting data to disk. What should you do? A. Create an INSTEAD OF trigger on the view. B. Create an AFTER trigger on the view. C. Modify the view to use the WITH VIEW_METADATA clause. D. Create a clustered index on the view. Correct Answer: D Explanation/Reference: Verified Read more [...]

2013 Latest MCSA 70-461 Exam Questions 51-55

Ensurepass QUESTION 6 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 Read more [...]

2013 Latest MCSA 70-461 Exam Questions 46-50

Ensurepass QUESTION 1 You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. You write the following Transact-SQL query: INSERT INTO OrdersHistorical SELECT * FROM CompletedOrders You need to optimize transaction logging and locking for the statement. Which table hint should you use? A. HOLDLOCK B. ROWLOCK C. XLOCK D. UPDLOCK E. TABLOCK Correct Answer: E Explanation/Reference: Reference: http://technet.microsoft.com/en-us/library/ms189857.aspx Reference: Read more [...]

2013 Latest MCSA 70-461 Exam Questions 1-5

Ensurepass QUESTION 1 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. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId Read more [...]