How to delete duplicate records on table without primary key

Deleting from the table which has a primary key is straight forward. If we have to delete a record from a table which does not have any primary key, then how do we delete it?
Lets take an example of table


CREATE TABLE [dbo].[Employee](
[Name] [varchar](50) NULL,
[DeptId] [int] NULL
)

Now I will insert few records to this table


Insert into Employee values ('User1',1)
Insert into Employee values ('User2',1)
Insert into Employee values ('User1',1)

So now we have 2 records with User1 and one record with User2. Lets try to delete the User1 from above table using below query

delete from Employee where Name='User1'

(2 row(s) affected)

But I just wanted to delete only one record. Here is how we can do it


SET ROWCOUNT 1
delete from Employee where Name='User1'
SET ROWCOUNT 0

(1 row(s) affected)

SET ROWCOUNT Causes SQL Server to stop processing the query after the specified number of rows are returned. For more details you can check http://technet.microsoft.com/en-us/library/ms188774.aspx


In Sql 2005 and above we can also use Top 1 in our Delete query.