Removing Duplicate Records

--Show Duplicate Phonenumbers in Phonebook
SELECT phonenumber, COUNT(*) FROM phonebook
 GROUP BY phonenumber HAVING COUNT(*) > 1
 ORDER BY COUNT(*) DESC

--Delete All Duplicate Records from Phonebook
CREATE TABLE #tmp (
 [phonenumber] [varchar] (30),
 [firstname] [varchar] (30),
 [lastname] [varchar] (30),
 [company] [varchar] (100)
)

--Create a unique index on phonenumber column
--"WITH IGNORE_DUP_KEY" is the key 
CREATE UNIQUE INDEX unqT ON #tmp (phonenumber) WITH IGNORE_DUP_KEY

INSERT INTO #tmp SELECT 
 [phonenumber] ,
 [firstname] ,
 [lastname] ,
 [company]
 FROM phonebook

--Truncate original table
TRUNCATE TABLE phonebook

INSERT INTO phonebook SELECT * FROM phonebook

DROP TABLE #tmp