SQL HELP

Duplicate Record Count

SELECT admissionno, COUNT(*) TotalCount FROM tbstudentmaster

GROUP BY admissionno

HAVING COUNT(*) > 1

ORDER BY COUNT(*) DESC

Delete Duplicate Rows from SQL Table

I had previously penned down two popular snippets regarding deleting duplicate rows and counting duplicate rows. Today, we will examine another very quick code snippet where we will delete duplicate rows using CTE and ROW_NUMBER() feature of SQL Server 2005 and SQL Server 2008.

This method is improved over the earlier method as it not only uses CTE and ROW_NUMBER, but also demonstrates the power of CTE with DELETE statement. We will have a comprehensive discussion about it later in this article. For now, let us first create a sample table from which we will delete records.

/* Create Table with 7 entries - 3 are duplicate entries */
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO

The above table has total 7 records, out of which 3 are duplicate records. Once the duplicates are removed we will have only 4 records left.

/* It should give you 7 rows */
SELECT *
FROM DuplicateRcordTable
GO

The most interesting part of this is yet to come. We will use CTE that will re-generate the same table with additional column, which is row number. In our case, we have Col1 and Col2 and both the columns qualify as duplicate rows. It may be a different set of rows for each different query like this. Another point to note here is that once CTE is created  DELETE statement can be run on it. We will put a condition here – when we receive more than one rows of record, we will remove the row which is not the first one. When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.

/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

It is apparent that after delete command has been run, we will have only 4 records, which is almost the same result which we would have got with DISTINCT, with this resultset. If we had more than 2 columns and we had to run unique on only two columns, our distinct might have not worked here . In this case, we would have to use above the mentioned method.

/* It should give you Distinct 4 records */
SELECT *
FROM DuplicateRcordTable
GO

This method is a breeze and we can use this for SQL Server version 2005 and the later versions.

Age Calculator (jQuery)

function getAge(birthday) {

var today = new Date();

var thisYear = 0;

if (today.getMonth() < birthday.getMonth()) {

thisYear = 1;

} else if ((today.getMonth() == birthday.getMonth()) && today.getDate() < birthday.getDate()) {

thisYear = 1;

}

var age = today.getFullYear() – birthday.getFullYear() – thisYear;

return age;

}

Linked Server with MS-Access 2007

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver

@server = N’Dentist’,

@srvproduct=N’ACCESS 2007′,

@provider=N’Microsoft.ACE.OLEDB.12.0′,

@datasrc=N’C:\Documents and Settings\Administrator\Desktop\DATA20708.mdb’

GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’Dentist’,

@locallogin = NULL ,

@useself = N’False’

Auto Create Table Procedures in SQL Server (Insert,Update,Delete,Select)

Create procedure sp_AutomaticInsertUpdateDeleteProc(@TableName as varchar(250),@Cols as varchar(8000),@IDCol as varchar(max))  —                   sp_AutomaticInsertUpdateDeleteProc ‘Tbdepartments’,”,’Deptid’

as begin

Declare @prc table (txt varchar(max))

declare @sql varchar(8000),@Para varchar(max)

declare @idcol1 as varchar(max)

Set @idcol1=@idcol

if @Cols=” begin

SELECT @Cols = COALESCE(@Cols+’,~’ ,”) +name+ ‘ ‘ +case is_ansi_padded when 1 then type_name(system_Type_Id)+'(‘+

case max_Length

when -1 then ‘max’

else cast(max_length as varchar)

end +

‘)’ else

type_name(system_Type_Id)+

case when Precision>0 and Scale>0 and  type_name(system_Type_Id)<> ‘datetime’ and  type_name(system_Type_Id)<> ‘money’

and  type_name(system_Type_Id)<> ‘smallmoney’ then ‘(‘+ cast(Precision as varchar)+’,’+cast(Scale as varchar)+’)’ else ” end

end

from sys.columns where object_name(object_Id)=@TableName  and name<>@IDCol

and type_name(system_Type_Id)<> ‘timestamp’ order by name

SELECT @para = COALESCE(@para+’,’ ,”) +name from sys.columns where object_name(object_Id)=@TableName  and name<>@IDCol and type_name(system_Type_Id)<> ‘timestamp’ order by name

SELECT @IDCol =’@’+ @IDCol + ‘ ‘ +case is_ansi_padded when 1 then type_name(system_Type_Id)+'(‘+ cast(max_Length as varchar)+’)’ else type_name(system_Type_Id) end  from sys.columns where object_name(object_Id)=@TableName  and name=@IDCol

set @Cols=substring(@Cols,2,len(@Cols)-1)

end

print @Cols

print @para

print @IDCol

—Insert Procedure

insert into @prc values( ‘–                   Insert Procedure ‘)

–insert into @prc values( ‘Go’)

set @para=substring (@IDCol,2,charindex(‘ ‘,@IDCol,1)-1)+’,’+@para

set @Cols=@IDCol+’,’+@Cols

set @sql=’Create Procedure prcInsert_’+ rtrim(ltrim(@tableName)) + ‘ (‘

insert into @prc values( @sql)

set @sql=+replace( @Cols ,’,~’,’,@’)+ ‘) as begin ‘

insert into @prc values( @sql)

set @sql=’insert into ‘  +rtrim(ltrim(@tableName)) + ‘ ( ‘ +@para + ‘ ) values ( ‘+’@’+replace( @para ,’,’,’,@’)+ ‘ ) End ‘

insert into @prc values( @sql)

insert into @prc values( ‘Go’)

insert into @prc values( ”)

insert into @prc values( ”)

—Split Columns in Table

Declare @index int

Declare @index1 int

Declare @Char int

Declare @Char1 int

Declare @exp varchar(max)

Declare @exp1 varchar(max)

set @exp=replace( @Cols ,’,’,’,@’)

set @exp1=replace( ‘@’+@para ,’,’,’,@’)

set @index =0

set @index1 =0

set @Char =0

set @Char1 =0

print @exp

print @exp1

print @para

create table #temp (data varchar(max),data1 varchar(max))

while @exp> ” begin

Select @Char=charindex(‘,’,@exp,@index)

Select @Char1=charindex(‘,’,@exp1,@index1)

if @char>0 begin

insert into #temp select substring(@exp1,@index1,@CHAR1),substring(@exp,@index,@CHAR)

SET @Exp=substring(@exp,@CHAR+1,len(@exp))

SET @Exp1=substring(@exp1,@CHAR1+1,len(@exp1))

end

else begin

insert into #temp select @exp1,@exp

SET @Exp=”

end

end

—Update Procedure

insert into @prc values( ‘–                   Update Procedure ‘)

set @sql=’Create Procedure prcUpdate_’+ rtrim(ltrim(@tableName)) + ‘ (‘+replace( @Cols ,’,~’,’,@’)+’)  as begin

update ‘ + @tableName + ‘ set ‘

Declare @p1 varchar(max),@p2 varchar(max)

Declare cur  cursor for select * from #temp where data<>@IdCol–into @p1,@p2

open cur

FETCH NEXT FROM cur into @p1,@p2

while @@fetch_status =0 begin

set @sql=@sql + right(@p1,len(@p1)-1) + ‘=’ +  @p1

FETCH NEXT FROM cur into @p1,@p2

if  @@fetch_status =0 begin

set @sql=@sql +’, ‘

end

end

close cur

deallocate  cur

set @sql=@sql + ‘ where ‘+ @idcol1 + ‘=@’+@idcol1 + ‘  End ‘

insert into @prc values( @sql)

insert into @prc values( ‘Go’)

—   Delete Proc

insert into @prc values( ‘–                   Delete Procedure ‘)

set @sql=’Create Procedure prcDelete_’+ rtrim(ltrim(@tableName)) + ‘ (‘+ @idCol+’)  as begin

delete from ‘+ rtrim(ltrim(@tableName)) +’ where ‘+ @idcol1 + ‘=@’+@idcol1 + ‘  End ‘

insert into @prc values( @sql)

select * from @prc

–Select * from #temp

drop table #temp

insert into @prc values( ‘Go’)

end

Size Of Tables in Sql Server

sp_spaceused tbstudentmaster

SELECT

t.NAME AS TableName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

(SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

WHERE

t.NAME NOT LIKE ‘dt%’

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, p.Rows

ORDER BY

t.Name

Check Sql Server Queries Fired Same Day

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

Hex To Decimal Functions

Create procedure HaxToDecimal ( @HaxCode as varchar(50))  — HaxToDecimal ‘3B9ACA00’

as begin

Declare @Num as varchar(50)

Declare @k as int

Declare @i as int

Declare @Result as varchar(50)

set @Result=”

set @num=@haxCode

Set @i=0

While @i<len(@num) begin

set @k= case SubString(@Num,len(@Num)-(@i),1) when ‘A’ then 10 when ‘B’ then 11 when ‘C’ then 12 when ‘D’ then 13 when ‘E’ then 14 when ‘F’ then 15 else SubString(@Num,len(@Num)-(@i),1) end

set @Result=cast(@Result as int )+(@k * Power(16,@i))

Print ‘While ‘ + cast(@i as varchar)

Print ‘K = ‘ + cast(@k as varchar)

Print ‘Result ‘ + @Result

Set @i=@i+1

end

Select  @Result

end

SQL SERVER – Use ROLL UP Clause instead of COMPUTE BY

Note: This upgrade was test performed on development server with using bits of SQL Server 2012 RC0 (which was available at in public) when this test was performed. However, SQL Server RTM (GA on April 1) is expected to behave similarly.

I recently observed an upgrade from SQL Server 2005 to SQL Server 2012 with compatibility keeping at SQL Server 2012 (110). After upgrading the system and testing the various modules of the application, we quickly observed that few of the reports were not working. They were throwing error. When looked at carefully I noticed that it was using COMPUTE BY clause, which is deprecated in SQL Server 2012. COMPUTE BY clause is replaced by ROLL UP clause in SQL Server 2012. However there is no direct replacement of the code, user have to re-write quite a few things when using ROLL UP instead of COMPUTE BY. The primary reason is that how each of them returns results. In original code COMPUTE BY was resulting lots of result set but ROLL UP.

Here is the example of the similar code of ROLL UP and COMPUTE BY. I personally find the ROLL UP much easier than COMPUTE BY as it returns all the results in single resultset unlike the other one. Here is the quick code which I wrote to demonstrate the said behavior.

CREATE TABLE tblPopulation (
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
[Population (in Millions)] INT
)
GO
INSERT INTO tblPopulation VALUES(‘India’, ‘Delhi’,’East Delhi’,9 )
INSERT INTO tblPopulation VALUES(‘India’, ‘Delhi’,’South Delhi’,8 )
INSERT INTO tblPopulation VALUES(‘India’, ‘Delhi’,’North Delhi’,5.5)
INSERT INTO tblPopulation VALUES(‘India’, ‘Delhi’,’West Delhi’,7.5)
INSERT INTO tblPopulation VALUES(‘India’, ‘Karnataka’,’Bangalore’,9.5)
INSERT INTO tblPopulation VALUES(‘India’, ‘Karnataka’,’Belur’,2.5)
INSERT INTO tblPopulation VALUES(‘India’, ‘Karnataka’,’Manipal’,1.5)
INSERT INTO tblPopulation VALUES(‘India’, ‘Maharastra’,’Mumbai’,30)
INSERT INTO tblPopulation VALUES(‘India’, ‘Maharastra’,’Pune’,20)
INSERT INTO tblPopulation VALUES(‘India’, ‘Maharastra’,’Nagpur’,11 )
INSERT INTO tblPopulation VALUES(‘India’, ‘Maharastra’,’Nashik’,6.5)
GO
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)]
FROM tblPopulation
GROUP BY Country,[State],City
WITH ROLLUP
GO
SELECT Country,[State],City, [Population (in Millions)]
FROM tblPopulation
ORDER BY Country,[State],City
COMPUTE SUM([Population (in Millions)]) BY Country,[State]–,City
GO

Rollup And Cube

 

After writing this blog post I continuously feel that there should be some better way to do the same task. Is there any easier way to replace COMPUTE BY?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s