In MS SQL 2005 and later editions, a much better way has been provided to remove duplicate records smartly, while having full control of which records to remove. It can be accomplished by using the ROW_NUMBER() which will return a number of row within a partition of a result set.
SQL 2012 or higher - Processing hundreds of millions records can be done in less than an hour. Processing hundreds of millions of records requires a different strategy and the implementation should be different compared to smaller tables with only several millions of records.