Home » Microsoft » 70-461 v.2 » How should you complete the relevant Transact-SQL statements?
DRAG DROP
You develop an SQL Server database. The database contains a table that is defined by the following T-SQL statements:
The table contains duplicate records based on the combination of values in the surName, givenName, and dateOfBirth fields.
You need to remove the duplicate records.
How should you complete the relevant Transact-SQL statements? To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area. Each code segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:
Correct Answer:
Explanation/Reference:
let us write a query which will delete all duplicate data in one shot. We will use a CTE (Common Table Expression) for this purpose. We will read in future posts what a CTE is and why it is used. On a lighter note, CTE’s can be imagined as equivalent to temporary result sets that can be used only in an underlying SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement. ;WITH CTE AS
(
SELECT Name
, City
, [State]
, ROW_NUMBER() OVER(PARTITION BY Name, City, [State] ORDER BY [Name]) AS Rnum FROM Persons
)
DELETE FROM CTE WHERE Rnum <> 1
In the code by saying WHERE Rnum <> 1, we are asking SQL Server to keep all the records with Rank 1, which are not duplicates, and delete any other record. After executing this query in SQL Server Management Studio, you will end up with no duplicates in your table. To confirm that just run a simple query against your table.
Reference: How to Remove Duplicates from a Table in SQL Server
http://social.technet.microsoft.com/wiki/contents/articles/22706.how-to-remove-duplicates-from-a-table-in-sql-server.aspx