Removing Duplicates using CTE

Monday, 31 August 2009

Here's a code snippet which removes the duplicate records,using CTE

WITH CTE ([col1], cnt)

AS

(

SELECT [col1],

ROW_NUMBER() OVER(PARTITION BY [col1] ORDER BY [col1]) AS cnt

FROM table1

)

DELETE

FROM CTE

WHERE cnt > 1

Finding unused tables in SQL Server

Monday, 1 June 2009

Here's a query which can give you a clear idea about the unused tables in your database.

select 

t.name as 'table', 

sum(i.user_seeks + i.user_scans + i.user_lookups)  as 'total accesses',

sum(i.user_seeks) as 'seeks',

sum(i.user_scans) as 'scans',

sum(i.user_lookups) as 'lookups'

from 

sys.dm_db_index_usage_stats i right outer join 

sys.tables t on (t.object_id = i.object_id)

group by 

i.object_id, 

t.name

order by [total accesses] desc

This works in SQL server 2005/later