Delete duplicate rows with no primary key on a SQL Server table

Answer 1

CREATE TABLE dbo.duplicateTest
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White')
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
If we select all data we get the following:

SELECT * FROM dbo.duplicateTest

SELECT * FROM dbo.duplicateTest WHERE ID = 1 AND FirstName = 'Bob' AND LastName = 'Smith'

SELECT * FROM dbo.duplicateTest

SET ROWCOUNT 1
DELETE FROM dbo.duplicateTest WHERE ID = 1
SET ROWCOUNT 0

SELECT * FROM dbo.duplicateTest