Examen 70-461 V002
Simulador de examenes 70-461
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 Explanation/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 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 Section: (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 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 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) Explanation/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 Explanation/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 Explanation: 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. Explanation/Reference: Reference: http://msdn.microsoft.com/en-us/library/ms188354.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx Explanation/Reference: Reference: http://msdn.microsoft.com/en-us/library/ms177634.aspx
Results
#1. 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?
#2. 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?
#3. 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?
#4. 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?
#5. 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?
#6. 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?
#7. 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?
#8. 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?
#9. 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?
#10. 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?
#11. 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?
#12. 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?
#13. 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?
#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?
#15. 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?
#16. 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?
#17. 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?
#18. 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?
#19. 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.
#20. 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?
#21. 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?
#22. 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?
