Best way to Remove Duplicate Records with help of CTE in sql

Nice method to avoid using temp/derived and group by operation in removing duplicate records.


COMMON TABLE EXPRESSION(CTE)

DROP TABLE DuplicateRcordTableCREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)INSERT INTO DuplicateRcordTableSELECT 1, 1UNION ALLSELECT 1, 1 --duplicateUNION ALLSELECT 1, 1 --duplicateUNION ALLSELECT 1, 2UNION ALLSELECT 1, 2 --duplicateUNION ALLSELECT 1, 3UNION ALLSELECT 1, 4SELECT * FROM DuplicateRcordTableWITH CTE (COl1,Col2, DuplicateCount)AS(SELECT COl1,Col2,ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount DuplicateRcordTable)DELETE
FROM CTEWHERE
GO

Thanks

Puneet Kumar

DuplicateCount > 1

FROM

Index Rebuilt SQL Query

QUERY to rebuild all indexes
USE DBNAME
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO

Regards
Puneet Kumar

Remove unrecognized characters while exporting excel from your display result

Hi Guys,

Many times you have find the issue of some unrecognized characters is coming while  exporting excel from your display result.

To resolve this issue many developers handled it via replacing  special character in other format in the database or in dataset, or doing some other things (Jugaadr) .But I have find the absolute solution for this, which I want to share you all ,The solution of the issue is :

Add two line in your excel export  code.

Response.Charset = "";

Response.ContentEncoding = System.Text.Encoding.Default;

By adding this you will never find any unrecognized character in excel while exporting.

Hope it will help you all.

Puneet Special Zone

Your favrouite Puneet Special Zone is now converted into happydeveloperzone

Hi All,

I have to inform you that my previous blog Puneet Special Zone (puneetspecialzone.007sites.com) has been expired.Now I have created this new blog for all developers,hope you all will like this blog as you are familiar with Puneet Special Zone.

So guys start asking your quries in Happy Developer Zone regarding sql ,I am again available for you all for support.

Thanks
Puneet Kumar Srivastava
Sr. Software Developers
Agilyst,Chandigarh
09803479904

Creating Global Table in T-Sql,and its advantage............

Hi,Here I am submitting an intresting topic related to global table, As every sql developer known red query. But the major problem is that ,in red query you can not create temporary table.But with use of global table you can create a temporary table with in in red query.

Example:

Declare @sql Varchar(Max)
set @sql ='Select * into #tempemp from table tblEmployee'
exec(@sql)

 It will gives you an error, so here you can create a Global table,which is also a temporary table but have a global access...

Declare @sql Varchar(Max)
set @sql ='Select * into ##tempemp from table tblEmployee'
exec(@sql)

Now this sql statement will execute easily..
In the above example ##tempemp is used as a gloabal table.

Hope this intresting thing will help to all developers...........

cheers

Puneet Kumar

 

How to achieve comma seperated entries in a table,expand in seperate rows by using xml.......

Hi,
 If you have data like this in your table:

AppId   Gender
1               x
2              y
3               x, y
4              x, y, z

and  you need to transform like this:
AppID          Gender
1                  x
2                  y
3                  x
3                  y
4                  x
4                  y
4                  z

Then you can achieve it without using loop or cursor, like this :

DECLARE @t TABLE (AppID INT, Gender VARCHAR(100))

INSERT INTO @t(AppID, Gender)

SELECT 1, 'x' UNION ALL

SELECT 2, 'y' UNION ALL

SELECT 3, 'x,y' UNION ALL

SELECT 4, 'x,y,z'

SELECT AppID,

i.value('.', 'VARCHAR(20)') AS Item

FROM

(

    SELECT

    AppID, Gender,

    CAST('<i>' + REPLACE(Gender, ',', '</i><i>') + '</i>' AS XML) AS GenderXml

    FROM @t

) a

CROSS APPLY GenderXml.nodes('//i') x(i)

Thanks

Puneet Kumar