If you search for "Duplicate rows sql server", in Google you will get about 136,000 results. This will tell how series the problem is. I am not going to add another post to above huge number of articles. What I going to explain here is how some other database system does this.
I was able to attend to a MongoDB conference last month and came across few features which are nice to have features in SQL Server.
In MongoDB, there are few ways for creating indexes, one is stated below.
Just to tell you what the above script is, users is the "table" (Collection in MongoDB) and UserID is the "column" (Attribute in MongoDB") and ensureIndex is the command to create the index.
so, in this statement, unique:true will create a unique index BUT if there are duplicates records, it will retain only the first "record" ( Document in MongoDB" ) and others will be deleted because of the dropDups:true statment.
Now, this is very efficient way of deleting duplicates, but you need to pay more attention before issuing this command.