This page was exported from Braindump2go Free Exam Dumps with PDF and VCE Collection [ https://www.mcitpdump.com ] Export date:Fri Apr 26 17:31:39 2024 / +0000 GMT ___________________________________________________ Title: Microsoft 70-462 Latest Important Questions with Answers and Explanation Shared By Braindump2go (181-197) --------------------------------------------------- 2015 Free Download of Latest Microsoft 70-462 Practce Exam Questions from Braindump2go will help you have a 100% success of 70-462 real exam! All questions are the latest checked and released! Answers are 100% correct guaranteed! In order to increase your confidence, 100% Full Money Back Guarantee is promised by Braindump2go! Instant Download Now! Vendor: MicrosoftExam Code: 70-462Exam Name: Administering Microsoft SQL Server 2012 Databases Exam QUESTION 181A 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 previous year.Which Transact-SQL query should you use? A.    SELECT Territory, Year, Profit,LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM ProfitsB.    SELECT Territory, Year, Profit,LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM ProfitsC.    SELECT Territory, Year, Profit,LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM ProfitsD.    SELECT Territory, Year, Profit,LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits Answer: CExplanation:http://msdn.microsoft.com/en-us/library/hh231256.aspx http://msdn.microsoft.com/en-us/library/hh213125.aspx QUESTION 182Your 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 SalesOrdersWHERE OrderTime = CONVERT(DATE, GETDATE())B.    SELECT COUNT(*) FROM SalesOrdersWHERE OrderTime = GETDATE()C.    SELECT COUNT(*) FROM SalesOrdersWHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112))D.    SELECT COUNT(*) FROM SalesOrdersWHERE OrderTime >= CONVERT(DATE, GETDATE())AND OrderTime < DATEADD(DAY, CONVERT(DATE, GETDATE())) Answer: D QUESTION 183You 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 transactionsWhich Transact-SQL statement should you use? A.    BEGIN TRANSACTIONBEGIN TRYEXEC dbo.ModifyDataCOMMIT TRANSACTIONEND TRYBEGIN CATCHIF @@ TRANCOUNT = 0ROLLBACK TRANSACTION;END CATCHB.    BEGIN TRANSACTIONBEGIN TRYEXEC dbo.ModifyDataCOMMIT TRANSACTIONEND TRYBEGIN CATCHIF @@ERROR != 0ROLLBACK TRANSACTION;THROW;END CATCHC.    BEGIN TRANSACTIONBEGIN TRYEXEC dbo.ModifyDataCOMMIT TRANSACTIONEND TRYBEGIN CATCHIF @@TRANCOUNT = 0ROLLBACK TRANSACTION;THROW;END CATCHD.    BEGIN TRANSACTIONBEGIN TRYEXEC dbo.ModifyDataCOMMIT TRANSACTIONEND TRYBEGIN CATCHIF @@ERROR != 0ROLLBACK TRANSACTION;END CATCH Answer: D QUESTION 184You 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. Answer: K QUESTION 185You 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 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. Answer: GExplanation:http://msdn.microsoft.com/en-us/library/cc645587.aspx QUESTION 186You 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 StudentMarksGROUP BY StudentCodeB.    SELECT Id, Name, Marks,DENSE_RANK() OVER (ORDER BY Marks DESC) AS RankFROM StudentMarksC.    SELECT StudentCode as Code,DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarksGROUP BY StudentCodeD.    SELECT StudentCode as Code,NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarksGROUP BY StudentCodeE.    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) tmpWHERE Rank = 1F.    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) tmpWHERE Rank = 1G.    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) tmpWHERE Rank = 1H.    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) tmpWHERE Rank = 1 Answer: D QUESTION 187You develop a database for a travel application.You need to design tables and other database objects.You create the Airline_Schedules table.You need to store the departure and arrival dates and times of flights along with time zone information.What should you do? A.    Use the CAST function.B.    Use the DATE data type.C.    Use the FORMAT function.D.    Use an appropriate collation.E.    Use a user-defined table type.F.    Use the VARBINARY data type.G.    Use the DATETIME data type.H.    Use the DATETIME2 data type.I.    Use the DATETIMEOFFSET data type.J.    Use the TODATETIMEOFFSET function. Answer: IExplanation:http://msdn.microsoft.com/en-us/library/ff848733.aspx http://msdn.microsoft.com/en-us/library/bb630289.aspx QUESTION 188You develop a database for a travel application.You need to design tables and other database objects.You create a stored procedure.You need to supply the stored procedure with multiple event names and their dates as parameters.What should you do? A.    Use the CAST function.B.    Use the DATE data type.C.    Use the FORMAT function.D.    Use an appropriate collation.E.    Use a user-defined table type.F.    Use the VARBINARY data type.G.    Use the DATETIME data type.H.    Use the DATETIME2 data type.I.    Use the DATETIMEOFFSET data type.J.    Use the TODATETIMEOFFSET function. Answer: E QUESTION 189You 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 applications.What should you create for each application? A.    SynonymsB.    Common table expressionsC.    ViewsD.    Temporary tables Answer: CExplanation:http://msdn.microsoft.com/en-us/library/ms190174.aspx QUESTION 190You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation. The reference data to be used in the transformation does not change. You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance. What should you do? A.    Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.B.    Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.C.    Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.D.    Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedureE.     Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure. Answer: AExplanation:http://msdn.microsoft.com/en-us/library/ms137786.aspx QUESTION 191You are developing a SQL Server Integration Services (SSIS) package. You need to design a package to change a variable value during package execution by using the least amount of development effort.What should you use? A.    Expression taskB.    Script taskC.    Execute SQL taskD.    Execute Process taskE.    Term Extraction transformation Answer: AExplanation:http://msdn.microsoft.com/en-us/library/hh213137.aspx QUESTION 192You are creating a SQL Server Master Data Services (MDS) model for a company. The source data for the company is stored in a single table that contains the manager-to-subordinate relationships. You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use? A.    NaturalB.    ExplicitC.    ParentD.    Recursive Answer: D QUESTION 193You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS). You need to complete the post-installation configuration. What should you do? A.    Run the Configuration component in the Data Quality Client.B.    Install ADOMD.NET.C.    Run the Data Quality Server Installer.D.    Make the data available for DQS operations. Answer: CExplanation:http://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/gg492277.aspx QUESTION 194You are the data steward for a Business Intelligence project. You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project. You need to produce the CSV file with the least amount of development effort. What should you do? A.    Create an Integration Services package and use a Data Profiling transform.B.    Create a custom .NET application based on the Knowledgebase class.C.     Create a data quality project.D.    Create a CLR stored procedure based on the Knowledgebase class.E.    Create a Master Data Services (MDS) business rule. Answer: CExplanation:http://msdn.microsoft.com/en-us/library/hh213052.aspxhttp://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.services.datacontracts.knowledgebase.aspxhttp://msdn.microsoft.com/en-us/library/bb895263.aspx QUESTION 195You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named Quotes. The table has no indexes and consists of seven columns:- [ID]- [QuoteDate]- [Open]- [Close]- [High]- [Low]- [Volume]Each of the following queries must be able to use a columnstore index:- SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.- SELECT AVG([High] - [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND'20101231'.- SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.You need to ensure that the indexing strategy meets the requirements. The strategy must also minimize the number and size of the indexes. What should you do? A.    Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].B.    Create three coiumnstore indexes:One containing [QuoteDate] and [Close]One containing [QuoteDate], [High], and [Low]One containing [QuoteDate] and [Volume]C.    Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].D.    Create two columnstore indexes:One containing [ID], [QuoteDate], [Volume], and [Close] One containing [ID], [QuoteDate], [High], and [Low] Answer: CExplanation:http://msdn.microsoft.com/en-us/library/gg492088.aspx http://msdn.microsoft.com/en-us/library/gg492153.aspx QUESTION 196You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day. Referential integrity must be enforced declaratively. You need to design a solution that can join a single time dimension to both fact tables. What should you do? A.    Join the two fact tables.B.    Merge the fact tables.C.    Create a time dimension that can join to both fact tables at their respective granularity.D.    Create a surrogate key for the time dimension. Answer: DExplanation:With dimensionally modeled star schemas or snowflake schemas, decision support queries follow a typical pattern: the query selects several measures of interest from the fact table, joins the fact rows with one or several dimensions along the surrogate keys, places filter predicates on the business columns of the dimension tables, groups by one or several business columns, and aggregates the measures retrieved from the fact table over a period of time. The following demonstrates this pattern, which is also sometimes referred to as a star join query:- select ProductAlternateKey,- CalendarYear,sum(SalesAmount)- from FactInternetSales Fact- join DimTime- on Fact.OrderDateKey = TimeKey- join DimProduct- on DimProduct.ProductKey =- Fact.ProductKey- where CalendarYear between 2003 and 2004- and ProductAlternateKey like 'BK%'- group by ProductAlternateKey,CalendarYear QUESTION 197You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases. You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure. Which Transact-SQL statement should you use? A.    USE Database2B.    EXECUTE AS OWNERC.    USE Database1D.    EXECUTE AS CALLER Answer: BExplanation:http://msdn.microsoft.com/en-us/library/ms188354.aspx http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling- storedprocedure-on-linked-server/ 2015 Braindump2go 70-462 Latest Updaed Braindumps Including All New Added 70-462 Exam Questions from Exam Center which Guarantees You Can 100% Success 70-462 Exam in Your First Try Exam! http://www.braindump2go.com/70-462.html --------------------------------------------------- Images: http://www.itexamquiz.com/braindump2go/bdimages/84eaa9822c9f_D54A/11022.png http://www.itexamquiz.com/braindump2go/bdimages/84eaa9822c9f_D54A/1522.png --------------------------------------------------- --------------------------------------------------- Post date: 2015-02-07 07:11:14 Post date GMT: 2015-02-07 07:11:14 Post modified date: 2015-02-09 01:34:53 Post modified date GMT: 2015-02-09 01:34:53 ____________________________________________________________________________________________ Export of Post and Page as text file has been powered by [ Universal Post Manager ] plugin from www.gconverters.com