Querying Microsoft SQL Server 2012/2014 — Question 47

You administer a Microsoft SQL Server database that includes a table named Products. The Products table has columns named ProductId, ProductName, and
CreatedDateTime.
The table contains a unique constraint on the combination of ProductName and CreatedDateTime.
You need to modify the Products table to meet the following requirements:
✑ Remove all duplicates of the Products table based on the ProductName column.
✑ Retain only the newest Products row.
Which Transact-SQL query should you use?

Answer options

• A. WITH CTEDupRecords AS ( SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON cte.ProductName = p.ProductName AND cte.CreatedDateTime > p.CreatedDateTime
• B. WITH CTEDupRecords AS ( SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON p.ProductName = cte.ProductName AND p.CreatedDateTime > cte.CreatedDateTime
• C. WITH CTEDupRecords AS ( SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName ) DELETE p FROM Products p JOIN CTEDupRecords cte ON p.ProductName = cte.ProductName
• D. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE Products FROM Products p

Correct answer: B

Explanation

Option B is correct because it correctly identifies the rows to delete by ensuring that only the newest Products row is retained based on the CreatedDateTime. Option A is incorrect as it uses the wrong join condition, which could lead to retaining the wrong rows. Option C fails to filter out duplicates based on count, and Option D incorrectly uses MAX instead of MIN, which does not meet the requirement of keeping the newest entry.