Delete duplicate rows in sql server

Fist way:

CREATE TABLE users
     (
      username varchar(15),
      pwd varchar(15)
     ) ;
-----
DECLARE @username varchar(15)
DECLARE @cnt int

DECLARE cur CURSOR READ_ONLY
FOR
SELECT username
     , count(*) -1 as cnt
     FROM users
        GROUP BY username 
            HAVING Count(*) > 1
OPEN cur
FETCH cur 
    INTO @username
       , @cnt

WHILE @@FETCH_STATUS = 0
    BEGIN
        DELETE TOP(@cnt) 
             FROM users 
                WHERE username = @username
FETCH cur  
    INTO @username
       , @cnt
END
CLOSE cur
DEALLOCATE cur

Second way from sqlauthority.com:


DELETE
     FROM MyTable
        WHERE ID NOT IN ( SELECT MAX(ID)
                               FROM MyTable
                                  GROUP BY DuplicateColumn1 
                                         , DuplicateColumn2
                                         , DuplicateColumn3)
Advertisements

Determing SQL Server Table Size

First create bellow sp :


CREATE PROCEDURE GetAllTableSizes
AS
/*
Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100) --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName

--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT *
FROM #TempTable

--Final cleanup!
DROP TABLE #TempTable

GO

Then, just execute and enjoy:


USE [master] -- change master to your database
GO
DECLARE    @return_value int
EXEC    @return_value = [dbo].[GetAllTableSizes]
SELECT    'Return Value' = @return_value
GO

select distinct row_number

 

SELECT DISTINCT
       col ,
       ROW_NUMBER() OVER ( ORDER BY col) AS rownum
     FROM test_table

As you see in above query, when we want use DISTINCT with ROW_NUMBER, we get strange result.This is because the ROW_NUMBER is processed before the DISTINCT clause.So we can use this one:

SELECT
       col ,
       ROW_NUMBER() OVER ( ORDER BY col) AS rownum
     FROM test_table
        GROUP BY col