Sql - Server – 2005 – 2008 – Delete Duplicate Rows

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 DuplicateRcordTableSELECT 1, 1UNION ALLSELECT 1, 1 --duplicateUNION ALLSELECT 1, 1 --duplicateUNION ALLSELECT 1, 2UNION ALLSELECT 1, 2 --duplicateUNION ALLSELECT 1, 3UNION ALLSELECT 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 onerows 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) ASDuplicateCountFROM DuplicateRcordTable)DELETE
FROM CTEWHERE 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 laterversions.

Without wizard you can import/export data/schema in sql server

Without wizard you can import/export data/schema..
——————————————————————

CREATE PROCEDURE [dbo].[sp_Table_Data_Script]
@SchemaName VARCHAR(MAX),
@TableName VARCHAR(Max),
@WhereClause NVARCHAR(Max),
@TopNo INT
AS

DECLARE @SQL VARCHAR(max)
DECLARE @nSQL NVARCHAR(Max)
DECLARE @RecordCount INT
DECLARE @FirstColumn NVARCHAR(Max)

–SET @TableName=QUOTENAME(@TableName) — Quote the table name
SET @TableName =@SchemaName + ‘.’ + QUOTENAME(@TableName)
Print @TableName

/*We need to find the record count in order to remove Union ALL from the last row*/
SET @nSQL=”
SET @nSQL= N’SELECT @RecordCount=COUNT(*) FROM ‘ + @TableName + (CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ Where ‘ + @Whereclause ELSE ” END)
Print @nSQL
EXEC sp_executesql @query = @nSQL, @params = N’@RecordCount INT OUTPUT’, @RecordCount = @RecordCount OUTPUT
/*****************************************************************/

/*Need to check either top No of record is less than record count
in order to remove union all from the last row */
IF @TopNo IS NOT NULL And @TopNo<@RecordCount
BEGIN
SET @RecordCount=@TopNo
END
/***************************************************************/
/*** Here we need to find the first column name to generate a serial number and insert an "Insert into statement" in the first row ***/
SET @nSQL=''
SET @nSQL= N'SELECT @FirstColumn=[name] FROM sys.columns WHERE [Column_id]=1 And object_ID=object_ID('''+ @TableName + ''')'
Print @nSQL
EXEC sp_executesql @query = @nSQL, @params = N'@FirstColumn nvarchar(Max) OUTPUT', @FirstColumn = @FirstColumn OUTPUT
/**************************************************************/

DECLARE @FieldName VARCHAR(max)
SET @FieldName=''

If (Select Count(*) FROM sys.columns WHERE object_id=object_id('' + @TableName + '') And is_identity0)=1

BEGIN
SET @FieldName = STUFF(
(
SELECT ‘,’ + QUOTENAME([Name]) FROM sys.columns WHERE object_id=object_id(” + @TableName + ”) Order By [column_id]
FOR XML PATH(”)), 1, 1, ”)
Set @FieldName =’(‘ + @FieldName + ‘)’
Print @FieldName
Print len(@FieldName)
END

/*******Create list of comma seperated columns *******/
SET @SQL= (SELECT STUFF((SELECT(CASE
WHEN system_type_id In (167,175,189) THEN + ‘ Cast(ISNULL(LTRIM(RTRIM(”N”””+Replace(‘ + QUOTENAME([Name])+ ‘,””””,””””””)+””””’+ ‘)),”NULL”) as varchar(max)) + ” AS ‘ + QUOTENAME([Name]) + ”’ + ” ,”’+’+ ‘
WHEN system_type_id In (231,239) THEN + ‘ Cast(ISNULL(LTRIM(RTRIM(”N”””+Replace(‘ + QUOTENAME([Name])+ ‘,””””,””””””)+””””’+ ‘)),”NULL”) as nvarchar(max)) + ” AS ‘ + QUOTENAME([Name]) + ”’ + ” ,”’+’+ ‘
WHEN system_type_id In (58,61,36) THEN + ‘ ISNULL(LTRIM(RTRIM(”N””” + Cast(‘ + QUOTENAME([Name])+ ‘ as varchar(max))+””””’ + ‘)),”NULL”) + ” AS ‘ + QUOTENAME([Name]) + ”’+ ” ,”’+’ + ‘
WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN + ‘ ISNULL(Cast(‘ + QUOTENAME([Name])+ ‘ as varchar(max)),”NULL”)+ ” AS ‘ + QUOTENAME([Name]) + ”’ + ” ,”’+’+ ‘
END
)
FROM
sys.columns WHERE object_ID=object_ID(”+ @TableName + ”) FOR XML PATH(”)),1,1,’ ‘))

/*******************************************************/

/* Here 500 means if the record count is 500 or top no 500 then it will generate “Insert into select ..Union All ”
Because more than 500 might reduce its performance. */
IF @TopNo <500 or @RecordCount<500
BEGIN
IF @TopNo IS NULL
BEGIN
SET @SQl='SELECT (Case When ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') =1 THEN '' INSERT INTO ' + @TableName + ' ' + ' '+ @FieldName + ' ''' + ' ELSE '''' END) + ''SELECT ''+'
+ Left(@SQL,Len(@SQL)-8) + ' + (CASE WHEN ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') ‘ + CONVERT(VARCHAR(10),@RecordCount)
+ ‘ THEN ” UNION ALL” ELSE ”” END) AS [Name] ‘ + ‘ FROM ‘ + @TableName +(CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ WHERE ‘ + @Whereclause ELSE ” END)
END
ELSE
BEGIN
SET @SQl= ‘SELECT TOP ‘ + CONVERT(VARCHAR(10),@TopNo) + ‘ (CASE WHEN ROW_NUMBER() OVER (ORDER BY ‘ + QUOTENAME(@FirstColumn) + ‘) =1 THEN ” INSERT INTO ‘
+ @TableName + ‘ ‘ + ‘ ‘+ @FieldName + ”” + ‘ ELSE ”” END) + ”SELECT ”+’ + LEFT(@SQL,LEN(@SQL)-8) + ‘ + (CASE WHEN ROW_NUMBER() OVER (ORDER BY ‘ + QUOTENAME(@FirstColumn) + ‘) ‘
+ CONVERT(VARCHAR(10),@RecordCount) + ‘ THEN ” Union All” ELSE ”” END) AS [Name]‘ + ‘ FROM ‘ + @TableName + (CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ WHERE ‘ + @Whereclause ELSE ” END)
END
END
ELSE
— Greator then 500 will generate “insert into select *” … for each record.
BEGIN
IF @TopNo IS NULL
BEGIN
SET @SQl=’SELECT ” INSERT INTO ‘ + @TableName + ‘ ‘ + ‘ ‘+ @FieldName + ‘ ” + ”SELECT ”+’ + Left(@SQL,Len(@SQL)-8)
+ ‘ AS [Name] ‘ + ‘ FROM ‘ + @TableName +(CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ WHERE ‘ + @Whereclause ELSE ” END)
END
ELSE
BEGIN
SET @SQl=’SELECT TOP ‘ + CONVERT(VARCHAR(10),@TopNo) + ”’ INSERT INTO ‘ + @TableName + ‘ ‘ + ‘ ‘+ @FieldName
+ ‘ ” + ”SELECT ”+’ + Left(@SQL,Len(@SQL)-8) + ‘ AS [Name] ‘ + ‘ FROM ‘ + @TableName +(CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ WHERE ‘ + @Whereclause ELSE ” END)
END
END
EXEC (@SQL)

GO

CREATE PROCEDURE [dbo].[spS_DatabaseInformationGet]
@getValue VARCHAR(50) = NULL
AS

SET NOCOUNT ON;

IF @getValue = ‘DATABASE’
BEGIN
SELECT [name]
FROM sys.databases
END
ELSE IF @getValue = ‘TABLE’
BEGIN
declare @TableList TABLE
(
name nvarchar(max)
)
DECLARE @name VARCHAR(128),@setvalgo varchar(5)
SET @setvalgo = ‘GO’
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘U’ AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
insert into @TableList
SELECT ‘IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”’ +RTRIM(@name)+”’) AND type in (N”U”)) DROP TABLE ‘+RTRIM(@name)
–SELECT ‘DROP TABLE [dbo].[' + RTRIM(@name) +']‘
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘U’ AND category = 0 AND [name] > @name ORDER BY [name])
insert into @TableList
select @setvalgo
END

insert into @TableList
SELECT ‘create table [' + so.name + '] (‘ + o.list + ‘)’ + CASE WHEN tc.Constraint_Name IS NULL THEN ” ELSE ‘ALTER TABLE ‘ + so.Name + ‘ ADD CONSTRAINT ‘ + tc.Constraint_Name + ‘ PRIMARY KEY ‘ + ‘ (‘ + LEFT(j.List, Len(j.List)-1) + ‘)’ END
as name
from sysobjects so
cross apply
(SELECT
‘ ['+column_name+'] ‘ +
data_type + case data_type
when ‘sql_variant’ then ”
when ‘text’ then ”
when ‘decimal’ then ‘(‘ + cast(numeric_precision_radix as varchar) + ‘, ‘ + cast(numeric_scale as varchar) + ‘)’
else coalesce(‘(‘+case when character_maximum_length = -1 then ‘MAX’ else cast(character_maximum_length as varchar) end +’)’,”) end + ‘ ‘ +
case when exists (
select id from syscolumns
where object_name(id)=so.name
and name=column_name
and columnproperty(id,name,’IsIdentity’) = 1
) then
‘IDENTITY(‘ +
cast(ident_seed(so.name) as varchar) + ‘,’ +
cast(ident_incr(so.name) as varchar) + ‘)’
else ”
end + ‘ ‘ +
(case when IS_NULLABLE = ‘No’ then ‘NOT ‘ else ” end ) + ‘NULL ‘ +
case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN ‘DEFAULT ‘+ information_schema.columns.COLUMN_DEFAULT ELSE ” END + ‘, ‘
from information_schema.columns where table_name = so.name
order by ordinal_position
FOR XML PATH(”)) o (list)
left join
information_schema.table_constraints tc
on tc.Table_name = so.Name
AND tc.Constraint_Type = ‘PRIMARY KEY’
cross apply
(select ‘[' + Column_Name + '], ‘
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH(”)) j (list)
where xtype = ‘U’
AND name NOT IN (‘dtproperties’)
select * from @TableList
END
ELSE IF @getValue = ‘TABLE_DATA’
BEGIN
DECLARE @TableName VARCHAR(1000)
DECLARE @TableDataRow BIGINT
DECLARE @TabIdenOn varchar(100)
DECLARE @TabIdenOff varchar(100)
create table #temp(name varchar(MAX))
–Cursor
DECLARE @getTable CURSOR
SET @getTable = CURSOR FOR
SELECT TABLE_NAME
FROM information_Schema.tables
OPEN @getTable
FETCH NEXT
FROM @getTable INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TableDataRow=rows FROM sys.partitions
WHERE object_id = object_id(@TableName)
AND index_id 0
BEGIN
IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N”+@TableName+”), ‘TableHasIdentity’)) = 1)
BEGIN
set @TabIdenOn = ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ ON’
set @TabIdenOff = ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ OFF’
insert into #temp(name)
select @TabIdenOn
insert into #temp(name)
exec sp_Table_Data_Script ”,@TableName,NULL,NULL
insert into #temp(name)
select @TabIdenOff
END
ELSE
BEGIN
insert into #temp(name)
exec sp_Table_Data_Script ”,@TableName,NULL,NULL
END

END
FETCH NEXT
FROM @getTable INTO @TableName
END
CLOSE @getTable
DEALLOCATE @getTable

SELECT name
FROM #temp
DROP TABLE #temp
END
ELSE IF @getValue = ‘P_F_T_V_SCRIPT’
BEGIN
–SELECT object_definition(object_id) AS name
–FROM sys.objects
–WHERE type_desc in (‘SQL_SCALAR_FUNCTION’,
– ‘SQL_STORED_PROCEDURE’,
– ‘SQL_TABLE_VALUED_FUNCTION’,
– ‘SQL_TRIGGER’,
– ‘VIEW’)

DECLARE @ProcName VARCHAR(200),@FunName VARCHAR(200),@PExist VARCHAR(MAX)
DECLARE @MyCursor CURSOR,@MyCursorSPS CURSOR,@MyCursorFUN CURSOR,@MyCursorFUNS CURSOR
DECLARE @setval1 VARCHAR(25),@setval2 VARCHAR(5),@setval3 VARCHAR(25),@setval4 VARCHAR(5)
SET @setval1=’SET ANSI_NULLS ON ‘
SET @setval2=’GO’
SET @setval3=’SET QUOTED_IDENTIFIER ON’

DECLARE @StoredProcsList TABLE
(
name TEXT
)
–Add Go
INSERT INTO @StoredProcsList
SELECT @setval2

DECLARE @SPCHECKEXIST CURSOR
SET @SPCHECKEXIST = CURSOR FOR
–Select
SELECT name FROM sys.objects WHERE type = ‘P’
OPEN @SPCHECKEXIST
FETCH NEXT
FROM @SPCHECKEXIST INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PExist= ‘IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”’ +@ProcName+”’) AND type in (N”P”, N”PC”)) DROP PROCEDURE ‘+@ProcName
INSERT INTO @StoredProcsList
SELECT @PExist
FETCH NEXT
FROM @SPCHECKEXIST INTO @ProcName
END
CLOSE @SPCHECKEXIST
DEALLOCATE @SPCHECKEXIST

DECLARE @FUNCHECKEXIST CURSOR
SET @FUNCHECKEXIST = CURSOR FOR
–Select
SELECT name FROM sys.objects where type_desc in (‘SQL_SCALAR_FUNCTION’,’SQL_TABLE_VALUED_FUNCTION’)
OPEN @FUNCHECKEXIST
FETCH NEXT
FROM @FUNCHECKEXIST INTO @FunName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PExist= ‘IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”’ +@FunName+”’) AND type in (N”FN”, N”IF”, N”TF”, N”FS”, N”FT”)) DROP FUNCTION ‘+@FunName
INSERT INTO @StoredProcsList
SELECT @PExist
FETCH NEXT
FROM @FUNCHECKEXIST INTO @FunName
END
CLOSE @FUNCHECKEXIST
DEALLOCATE @FUNCHECKEXIST

DECLARE @SPGENERATESCRIPT CURSOR
SET @SPGENERATESCRIPT = CURSOR FOR
–Select
SELECT name FROM sys.objects WHERE type = ‘P’
OPEN @SPGENERATESCRIPT
FETCH NEXT
FROM @SPGENERATESCRIPT INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @StoredProcsList
SELECT @setval1
INSERT INTO @StoredProcsList
SELECT @setval2
INSERT INTO @StoredProcsList
SELECT @setval3
INSERT INTO @StoredProcsList
SELECT @setval2
INSERT INTO @StoredProcsList
EXEC sp_helptext @ProcName
FETCH NEXT
FROM @SPGENERATESCRIPT INTO @ProcName
END
CLOSE @SPGENERATESCRIPT
DEALLOCATE @SPGENERATESCRIPT

DECLARE @FUNGENERATESCRIPT CURSOR
SET @FUNGENERATESCRIPT = CURSOR FOR
–Select
SELECT name FROM sys.objects where type_desc in (‘SQL_SCALAR_FUNCTION’,’SQL_TABLE_VALUED_FUNCTION’)
OPEN @FUNGENERATESCRIPT
FETCH NEXT
FROM @FUNGENERATESCRIPT INTO @FunName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @StoredProcsList
SELECT @setval1
INSERT INTO @StoredProcsList
SELECT @setval2
INSERT INTO @StoredProcsList
SELECT @setval3
INSERT INTO @StoredProcsList
SELECT @setval2
INSERT INTO @StoredProcsList
EXEC sp_helptext @FunName
INSERT INTO @StoredProcsList
SELECT @setval2
FETCH NEXT
FROM @FUNGENERATESCRIPT INTO @FunName
END
CLOSE @FUNGENERATESCRIPT
DEALLOCATE @FUNGENERATESCRIPT

SELECT name FROM @StoredProcsList

END
ELSE IF @getValue = ‘D_USER’
BEGIN
select princ.name
, princ.type_desc
, perm.permission_name
, perm.state_desc
, perm.class_desc
, object_name(perm.major_id)
from sys.database_principals princ
left join
sys.database_permissions perm
on perm.grantee_principal_id = princ.principal_id
where type_desc in (‘SQL_USER’) and state_desc in (‘GRANT’)
END

GO

Difference between Session and Cache

Both session and cache can be used to store the data at the server level (also "Caching" can also be done at client side for static files). Main defference is that Session is created for each user/browser request. It is user specific. and the data stored in session expires when the complete session is expired.

Cache data is not user specific (but if you want to make use of user specific cache data then you can create keys which is very unique to the user, lets say using a very unique "userid" value etc), it can be shared among all the users and we can specify the expiry time for the cache object.

Types of session:

- InProc: Data is stored in server memory.
- StateServer: which stores session state in a separate process (ASP.NET state service). this data can be shared amoung multuple web servers in web farm.
- SqlServer: session is saved in the sqlserver database. even if  webapplication is restarted, session state is preserved in the database.
- Custom: We can specify custom storage of session.
- Off: we can disable storing session.

Types of Cache:

- Output Cache: Server side caching where the page can be cached at the server memory, and the data of the cache can be retrieved when the request is made from client and the data is served from the cache.
- Fragment Cache: Server side cache for the part of the page (using usercontrol).  
- Data Cache: We can store any data in Cache object, and retrieve the data from the memory.
- 304 Response >> for unchanged content and tell browser to take the local copy of the file. this check is done at the server level and if the file is not changed then return 304 response.
- Client side caching: For the images, js, css files which dont change very often. we can set approprite header for these  

One more interesting thing to read about cache is SqlCacheDependency. It is used to check if the data in the database is modified/altered which will automatically invalidate the cached object. we can use this for output cache and application cache.

How to resolve canonical url issue in asp.net

In this article we will see how we can resolve canonical url issue. As we know www and non-www urls are treated as 2 different types of urls. so how we can solve this issue.

Example
- yourdomain.com
- www.yourdomain.com

Method 1:
One way is to rewrite the url based on the match. Let's say a user comes to visit with non-www url (it will be your match pattern) on your site then you can rewrite that url to a www url.

We can add following rule to web.config to handle this situation.

 <system.webServer> <rewrite> <rules>
 <rule name="Redirect to www" >
 <match url="(.*)" ignoreCase="true" />
 <conditions>
 <add input="{HTTP_HOST}" pattern="^yourdomain\.com" />
 </conditions>
 <action type="Redirect" url="http://www.yourdomain.com/{R:1}" redirectType="Permanent" />
 </rule>
 </rules>
 </rewrite>
 </system.webServer>

NOTE: In this case you will have to install the  url rewriter tool. And it will work for IIS 7.0 and above.

============================================================================================
Method 2:

you can also do a 301 redirect using global.ascx. you can check if the url is non-www url, if so you can replace it with www url and do a redirect.

 void Application_BeginRequest(object sender, EventArgs e)
 {
            string nonWWWUrl = @"http://yourdomain.com";    
            string WWWUrl = @"http://www.yourdomain.com";
  
            if (HttpContext.Current.Request.Url.ToString().ToLower().Contains(nonWWWUrl))
            {
                HttpContext.Current.Response.Status = "301 Moved Permanently";
                HttpContext.Current.Response.AddHeader("Location",
                Request.Url.ToString().ToLower().Replace(nonWWWUrl, WWWUrl));
            }
 }

ref : http://www.gilgh.com/article/how-to-resolve-canonical-url-issue-in-asp-net

SQL SERVER – Search Table Name From Store Procedure In Database

Type 1 :

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

Type 2 :

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'