70-461 Examen FEATURED Examen 70-461 V002 17 octubre, 201818 octubre, 2018 JuanCastillo 0 comentarios Simulador de examenes 70-461 Username * User Password * User Email * Confirm Password * Submit Pregunta #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? Query the sys.dm_db_index_usage_stats DMV. Query the sys.dm_db_missing_index_details DMV Query the sys.dm_db_missing_index_columns DMV. Use the Database Engine TuningAdvisor. Pregunta #2: Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence. Which approach should you use? user-defined function Cursor BULK INSERT SELECT statement that includes CASE ViewExplanation: SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. Pregunta #3: 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 previous year. Which Transact-SQL query should you use? SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM ProfitsSection: (none) Explanation Explanation/Reference: Explanation: LAG accesses data from a previous row in the same result set without the use of a self-join in SQL Server 2016. LAG provides access to a row at a given physical offset that comes before the current row. Usethis analytic function in a SELECT statement to compare values in the current row with values in a previous row. Use ORDER BY Year, not ORDER BY Territory. Example: The following example uses the LAG function to return the difference in sales quotas fora specific employee over previous years. Notice that because there is no lag value available for the first row, the default of zero (0) is returned. USE AdventureWorks2012; GO SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006'); Incorrect Answers: A, D: LEAD accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2016. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row. B: Use ORDER BY Year, not ORDER BY Territory. References: https://msdn.microsoft.com/en-us/library/hh231256.aspx Pregunta #4: You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistorical. 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? UPDLOCK HOLDLOCK ROWLOCK XLOCK TABLOCKExplanation: http://technet.microsoft.com/en-us/library/ms189857.aspx Pregunta #5: You have a database application that uses Microsoft SQL Server 2012. You have a query named Query1 that returns four columns from a frequently updated table that has a clustered index. Three of the columns are referenced in the WHERE clause of the query. The three columns are part of a non-clustered index. The fourth column is not referenced in the WHERE clause. Users report that the application begins to run slowly. You determine that the root cause for the performance issue is Query1. You need to optimize the statement. What should you do? Enable the optimize for ad hoc workloads option Add a FORCESCAN hint to the Attach query Add a HASH hint to the query Add a columnstore index to cover the query Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query Add an INCLUDE clause to the index. Cover the unique clustered index with a columnstore index Include a SET FORCEPLAN ON statement before you run the query. Add a FORCESEEK hint to the query. Add a LOOP hint to the query.SET SHOWPLAN_XML (Transact-SQL) causes SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document Incorrect Answers: F: Columnstore indexes in the SQL Server Database Engine can be used to significantly speed-up the processing time of common data warehousing queries. Typical data warehousing workloads involve summarizing large amounts of data. But in this question the query is run on a table that is updated frequently, not a warehousing table. Reference: SET SHOWPLAN_XML(Transact-SQL) https://msdn.microsoft.com/en-us/library/ms187757.aspx Pregunta #6: You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. Which type of user should you create? SQL user with login Domain user SQL user without login SQL user with a custom SID Pregunta #7: You are developing a database in SQL Server 2012 to store information about current employee project assignments. You are creating a view that uses data from the project assignment table. You need to ensure that the view does not become invalid if the schema of the project assignment table changes. What should you do? Create the view by using an account in the sysadmin role. Add a DDL trigger to the view to block any changes. Create the view in a new schema. Add a DDL trigger to the project assignment table to re-create the view after any schema change.Section: (none) Explanation Explanation/Reference: Explanation: DDL triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations. Reference: DDL Triggers https://technet.microsoft.com/en-us/library/ms190989(v=sql.105).aspx Pregunta #8: 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 use? GRANT SELECT ON Purchases.Suppliers TO ContosoUser CREATE PROCEDURE Purchases.PurgeInactiveSuppliers WITH EXECUTE AS USER = 'dbo' AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser GRANT DELETE ON Purchases.Suppliers TO ContosoUser CREATE PROCEDURE Purchases.PurgeInactiveSuppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUserExplanation/Reference: Reference: http://msdn.microsoft.com/en-us/library/ms188354.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx Pregunta #9: 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? An UPDATE statement that includes CASE Recursive stored procedure Trigger Cursor The for each SQLCLR statement Pregunta #10: You are writing a set of queries against a FILESTREAM-enabled database. You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure? SET XACT_ABORT OFF SET IMPLICIT_TRANSACTIONS OFF SET IMPLICIT_TRANSACTIONS ON SET XACT_ABORT ON SET TRANSACTION ISOLATION LEVEL SNAPSHOT SET TRANSACTION ISOLATION LEVEL SERIALIZABLEExplanation/Reference: Explanation: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. Reference: SET XACT_ABORT (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms188792.aspx Pregunta #11: You use Microsoft SQL Server to develop a database application. You need to implement a computed column that references a lookup table. What should you do? Create a BEFORE trigger that maintains the state of the computed column. Reference a user-defined function within the computed column Add a default value to the computed column that implements hard-coded CASE statements. Add a default value to the computed column that implements hard-coded values Pregunta #12: You are designing a table for a SQL Server database. The table uses horizontal partitioning. You have the following requirements: Each record in the table requires a unique key. You must minimize table fragmentation as the table grows. You need to choose the appropriate data type for the key value. What should you do? Generate a random value that uses the char(16) data type. Use the NEWSEQUENTIALID function to create a unique identifier Generate a random value that uses the bigint datatype. Use the NEWID function to create a unique identifier.Explanation/Reference: Explanation: Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows. For example, a table that contains 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a specific year. Any queries requiring data for a specific month only reference the appropriate table. NEWSEQUENTIALID creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages. References:https://msdn.microsoft.com/en-us/library/ms189786.aspx Pregunta #13: 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. SELECTCustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerId SELECT CustomerName, OrderDate FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerId SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerId = Orders.CustomerIdExplanation/Reference: Reference: http://msdn.microsoft.com/en-us/library/ms177634.aspx Pregunta #14: 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? SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = GETDATE() SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = CONVERT(DATE, GETDATE()) SELECT COUNT(*) FROM SalesOrders WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)) SELECT COUNT(*) FROM SalesOrders WHERE OrderTime >= CONVERT(DATE, GETDATE()) AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) Pregunta #15: You plan to write a query for a new business report that will contain several nested queries. You need to ensure that a nested query can call a table-valued function for each row in the main query. Which query operator should you use in the nested query? PIVOT OUTER JOIN CROSS APPLY INNER JOINExplanation/Reference: Explanation: The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input. There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSSAPPLY returns only rows from the outer table that produce a result set from the tablevalued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function. References: https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx Pregunta #16: You develop a database application. You create four tables. Each table stores different categories of products. You create a Primary Key field on each table. You need to ensure that the following requirements are met: The fields must use the minimum amount of space. The fields must be an incrementing series of values. The values must be unique among the four tables. What should you do? Create a TIMESTAMP column. Use the UNIQUEIDENTIFIER data type along with NEWSEQUENTIALID() Create a ROWVERSION column. Use the INTEGER data type along with IDENTITY Create a SEQUENCE object that uses the INTEGER data type. Pregunta #17: You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do? Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication. Set the Remote Connection Timeout on the Publisher to 0. Set the Merge agent on the problem subscribers to use the slow link agent profile. Change the Merge agent on the problem subscribers to run continuously. Pregunta #18: You use Microsoft SQL Server to develop a database application. You need to create an object that meets the following requirements: Takes an input parameter Returns a table of values Can be referenced within a view Which object should you use? scalar-valued function stored procedure inline table-valued function user-defined data type Pregunta #19: You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space.You also need to prevent reading queries from blocking writing queries. Which isolation level should you use? SERIALIZABLE REPEATABLE READ READ COMMITTED SNAPSHOT SNAPSHOTReference: http://msdn.microsoft.com/en-us/library/ms173763.aspx Pregunta #20: 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? User-defined data type Stored procedure Scalar-valued function Inline function Pregunta #21: You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use? Numeric Varchar Money Small moneyExplanation/Reference: Explanation: Numeric data types that have fixed precision and scale. decimal[ (p[ , s] )] and numeric[ (p[ , s] )] where * p (precision) The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through https://www.gratisexam.com/ the maximum precision of 38. The default precision is 18. * (scale) The number of decimal digits that will be stored to the right of the decimal point. Reference: decimal and numeric (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms187746.aspx Pregunta #22: A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history. The Production.Product table contains the following two columns: ProductID Name The Sales.SalesOrderDetail table contains the following three columns: SalesOrderID ProductID OrderQty You need to create a query listing all of the products that were never ordered. Which statements should you execute? Explanation: EXCEPT and INTERSECT returns distinct rows by comparing the results of two queries. EXCEPT returns distinct rows from the left input query that aren’t output by the right input query. Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query. Example: The following query returns any distinct values from the query to the left of the EXCEPT operator that are not also found on the right query. -- Uses AdventureWorks SELECT CustomerKey FROM FactInternetSales EXCEPT SELECT CustomerKey FROM DimCustomer WHERE DimCustomer.Gender = 'F' ORDER BY CustomerKey; --Result: 9351 Rows (Sales to customers that are not female.) Incorrect Answers: B: Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query. C, D: INTERSECT returns distinct rows that are output by both the left and right input queries operator. References: https://msdn.microsoft.com/en-us/library/ms188055.aspx Congratulations, you passed! I'm sorry but you did not achieve the required score.