What is SPID in SQL Server

This is one of the common stuffs that every developer has to face / faced. In this post, I will explain three different methods of idenifying and deleting the duplicate records; make sure that you backup the database before running these scripts (or else you can put these scripts inside a BEGIN TRAN...ROLLBACK TRAN  for testing purpose ) .

 

Method 1 :

If you have a Primary key column, or you can do the DML operations in case there is no primary key defined on that table, then you can use this method

In case there is no primary key defined on that table, you can alter the table to add an IDENTITY column;

ALTER urOriginalTable 
ADD i int IDENTITY

Suppose the other two columns are Col1 and Col2; and your delete query will look like

DELETE
FROM urOriginalTable 
WHERE I NOT IN (SELECT MIN(i) from urOriginalTable GROUP BY Col1, col2 )

and as the final step you need to drop the identity column created earlier.

ALTER TABLE urOriginalTable
DROP COLUMN I

 

Now, if you really dont want to alter your table or you dont have the permission to do so, you can follow any of the following methods.

 

Method 2 :  Using a temp Table

This is a three step process, the first step is to copy the Distinct records to a temp table;  now as you have the distinct records in the new table, you can either drop / rename the original table; and as the final step, rename the new table to the original.

--Step 1
SELECT DISTINCT Col1, Col2 
INTO LookupTable 
FROM OriginalTable ; 

--Step2 
Exec sp_Rename 'originalTable' , 'OriginalTable_backup' 

--Step3 
Exec sp_rename 'LookupTable' , 'OriginalTable'

Method 3: Using a cursor

This is a straight forward method and I  think you can easily understood this from the followong example

DECLARE @tab TABLE (
         NAME VARCHAR(100)
        ,Sal INT
        )
DECLARE @Name VARCHAR(100)
       ,@Sal INT
       ,@Rows int
SET NOCOUNT ON 
INSERT  INTO @Tab
SELECT  'a',200
UNION ALL
SELECT  'b',300
UNION ALL
SELECT  'a',200
UNION ALL
SELECT  'c',400
UNION ALL
SELECT  'd',700
UNION ALL
SELECT  'c',400
UNION ALL
SELECT  'c',400
UNION ALL
SELECT  'e',600

SELECT  *
FROM    @Tab
DECLARE CurDelDupes CURSOR
FOR SELECT  NAME  ,Sal
FROM    @Tab
GROUP BY NAME
       ,[Sal]
OPEN CurDelDupes
FETCH NEXT FROM CurDelDupes INTO @Name, @Sal
WHILE @@FETCH_STATUS = 0 
      BEGIN
            SELECT  @Rows = COUNT(*) - 1 -- Since we need to keep one copy here, reduce one from Count
            FROM    @Tab
            WHERE   NAME = @Name
                    AND Sal = @Sal 
            WHILE @Rows > 0 
                  BEGIN
                        SET ROWCOUNT 1
                        DELETE  FROM @Tab
                        WHERE   NAME = @Name
                                AND Sal = @Sal 
                        SET ROWCOUNT 0
                        SET @Rows = @Rows - 1 
                  END
            FETCH NEXT FROM CurDelDupes INTO @Name, @Sal
      END 
CLOSE CurDelDupes
DEALLOCATE curDelDupes
SELECT  *
FROM    @Tab

Method 4: Using CTE (Only works with SQL Server 2005 or above )

In case your database is SQL server 2005, then you can make use of the CTE for this purpose; Check this example

DECLARE @tab TABLE ( NAME VARCHAR(10), age INT ) 
SET NOCOUNT ON

INSERT INTO @tab SELECT 'a',10
INSERT INTO @tab SELECT 'a',10
INSERT INTO @tab SELECT 'a',10

INSERT INTO @tab SELECT 'b',10
INSERT INTO @tab SELECT 'b',10

INSERT INTO @tab SELECT 'c',10

;WITH CTE AS 
(
SELECT ROW_NUMBER() OVER ( PARTITION BY NAME ORDER BY NAME DESC ) AS RowNum, 
NAME, age  
FROM @Tab )

SELECT * FROM Cte

I hope this article is useful for you, in case you need any help please let me know.