In this example we have a table called employes with first name last name

Table

emp_id f_name l_name
1 mary mcluskey
2 mary mcluskey
3 james fredrick
4 ray blume
5 ray blume

First identify the dups

 SELECT f_name,l_name FROM employees
GROUP BY f_name,l_name
HAVING COUNT(*) > 1

Results

f_name l_name
ray blume
mary mcluskey 

 Delete dups

DELETE FROM employees WHERE emp_id IN
(SELECT MAX(emp_id) FROM employees
GROUP BY f_name,l_name
HAVING COUNT(*) > 1)

Results

 emp_i d f_name l_name
1 mary mcluskey
3 james fredrick
4 ray blume

Note you may have to use while exists when you have 3 dups of same as this only deletes max id where a name is repeated

This is an incredible article that describes indexes done by Paul Randal. It is the core to understanding how a sql server database stores data and how you must understand how it functions.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/12/13/more-undocumented-fun_3a00_-dbcc-ind_2c00_-dbcc-page_2c00_-and-off_2d00_row-columns.aspx

It is amazing the time I spend on tasks only to find that the project plan was lacking and when I got to step 10 (there were only 2 steps in the beginning), step 2 was wrong. As a SQL DBA, Project Manager, Application Support… you must ask questions.

One example was a emergency request I received to support a project. They had restored databases and login would not work. I ran sp_fix_user_login and they were good to go. Case closed, I have a million other things to do anyway.

I received a call from the production team a couple months later saying I had supported a roll to QA and needed all the documnetation. I looked back and wait I just helped them with a issue. I never even saw that it was a qa roll and not marked “issue”

I had to restore and do the full roll over again deal with all the issues because they wanted dbo on the whole databases. Get blamed for delaying the project..

Ask questions