lee

Comments

Post a Comment

Popular posts from this blog

lAST USED TABLE

SELECT      OBJECT_NAME(i.object_id) AS TableName,     i.last_user_seek,     i.last_user_scan,     i.last_user_lookup,     i.last_user_update FROM      sys.dm_db_index_usage_stats i JOIN      sys.objects o ON i.object_id = o.object_id WHERE      o.type = 'U' -- User tables     AND i.database_id = DB_ID() ORDER BY      GREATEST(ISNULL(i.last_user_seek, '1900-01-01'),              ISNULL(i.last_user_scan, '1900-01-01'),              ISNULL(i.last_user_lookup, '1900-01-01'),              ISNULL(i.last_user_update, '1900-01-01')) ASC;
Read more

dlete dulate with

CTE Delete Duplicates Example CTE + ROW_NUMBER() to Delete Duplicates Step 1: Original Table ID Name Email 1 Ram ram@gmail.com 2 Shyam shyam@gmail.com 3 Ram ram@gmail.com 4 Mohan mohan@gmail.com 5 Ram ram@gmail.com Duplicate Email: ram@gmail.com Step 2: Apply ROW_NUMBER() WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID) AS rn FROM Employees ) SELECT * FROM CTE; ID Name Email rn 1 Ram ram@gmail.com 1 3 Ram ram@gmail.com 2 5 Ram ram@gmail.com 3 2 Shyam shyam@gmail.com 1 4 Mohan mohan@gmail.com 1 Explanation: PARTITION BY Email → Groups duplicates ORDER BY ID → Sorts records ROW_NUMBER() → Assigns numbers (1,2,3...) Step 3: Delete Duplicates WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID) AS rn FROM Employees ) DELETE FROM CTE WHERE rn > 1; Final Result (After Delete) ID Name Email 1 Ram ram@gmail.com 2 Shyam shyam@gmail.com 4 Mohan mohan...
Read more