You administer a Microsoft SQL Server 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?
To reduce fragmentation of a non-clustered index without taking it offline, you should use the 'ALTER INDEX REORGANIZE' command. This command compacts the pages of the index, fixing fragmentation while allowing the index to remain available. The 'REBUILD' command would also reduce fragmentation but it can take the index offline unless the ONLINE option is specified.
Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column.
The business team wants a report that displays the total number of purchases made 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?
To count the number of purchases made on the current day in the most efficient way, it is essential to write the query such that it benefits from the non-clustered index on the PurchaseTime column. The query should avoid using any functions on the column, as that would prevent the use of the index. The query that best satisfies these conditions is: SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())). This query effectively filters the results within the desired date range without altering the PurchaseTime column, thus allowing the query optimizer to use the index efficiently.
You administer a Microsoft SQL Server database that supports a shopping application.
You need to retrieve a list of customers who live in territories that do not have a sales person.
Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)
To retrieve a list of customers who live in territories that do not have a sales person, we need to identify the customers whose TerritoryID is not matched by any TerritoryID in the Salesperson table. The correct solutions are: Using 'TerritoryID <> ALL (SELECT TerritoryID FROM Salesperson)' ensures it checks against all territory IDs in the Salesperson table and only includes customers whose territories are not represented at all, and using 'TerritoryID NOT IN (SELECT TerritoryID FROM Salesperson)' explicitly filters out territories that exist in the Salesperson table, effectively achieving the same result as the previous query by excluding any territories linked to a salesperson.
SIMULATION -
You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)
You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements:
UnitPrice must be returned in descending order.
✑ The query must use two-part names to reference the table.
✑ The query must use the RANK function to calculate the results.
✑ The query must return the ranking of rows in a column named PriceRank.
✑ The list must display the columns in the order that they are defined in the table.
✑ PriceRank must appear last.
To answer, type the correct code in the answer area.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.
The correct T-SQL statement to meet the requirements is as follows: SELECT CatID, CatName, ProductID, ProdName, UnitPrice, RANK() OVER (ORDER BY UnitPrice DESC) AS PriceRank FROM Sales.ProductCatalog ORDER BY PriceRank. This code accomplishes the following: 1. It uses two-part names to reference the table (Sales.ProductCatalog). 2. It uses the RANK function to rank the products by UnitPrice in descending order. 3. It displays the columns in the order they are defined in the table, with PriceRank appearing last. 4. It orders the final results by the calculated ranking (PriceRank).
You use Microsoft SQL Server to develop a database application.
You create a stored procedure named DeleteJobCandidate.
You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number.
Which Transact-SQL statement should you use?
To ensure the stored procedure reports the error number if it encounters an error, you need to capture the error using the @@ERROR system function immediately after the execution of the stored procedure. The solution involves declaring a local variable to store the error number, executing the stored procedure, and then checking the captured error variable. If there is an error, the error number and the number of row count should be printed. The best option for this is to declare error and row count variables, execute the stored procedure, capture the error and row count immediately, and then check the error variable and print the appropriate message. Option A accomplishes this by declaring @ErrorVar to store the error code, executing the procedure, capturing the error code with @ErrorVar = @@ERROR, and checking if @ErrorVar is not zero to print the error. This sequence appropriately captures and checks for errors within the execution context of the stored procedure.