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