Remove field from composite key and collate duplicate data

If the four records dictate a unique and you want the first one by date, then why not insert into another table, truncate, then insert back once you finish the table reconfigure (or create a new table with the right columns, insert into, then rename after dropping?) I don't think this can be improved upon, unless you want to delete from where not equal to, but the insert into should be faster (needs to be tested to prove for sure)


--Drop existing Primary Key
ALTER TABLE [<table>] DROP CONSTRAINT [<pk_constraint>];
GO

--Delete all rows except the first for each <Id>, ordered by <MyDateCol>
WITH delCTE AS (
  SELECT <Id>, ROW_NUMBER() OVER(PARTITION BY Id ORDER BY <MyDateCol>) AS RowNum
  FROM dbo.<Table>
)
DELETE a
FROM <table> a
JOIN delCTE b
  ON b.<Id> = a.<Id>
WHERE b.RowNum > 1;
GO

--Create the new Primary Key
ALTER TABLE [<table>] ADD CONSTRAINT [<pk_constraint>] PRIMARY KEY (<cols>);
GO

You can copy your table's data to a file using BCP (bulk copy), truncate the table, remove the old PK, create new PK (only 4 columns), import data from bcp file using a script (like Eric's script) that ignores the duplicates.

For many rows nothing can beat bulk copy :).