SQL

gg

Delete Duplicate Records in SQL Server (Simple Guide) 🧹 How to Delete Duplicate Rows in SQL Server (Step-by-Step for Beginners) This simple guide shows you how to remove duplicate rows from a SQL Server table using a CTE (Common Table Expression) . 🎯 Step 1: Understand your table Imagine you have a table named Employees that looks like this: EmployeeID FirstName LastName Email 1 John Doe john@example.com 2 John Doe john@example.com 3 Jane Smith jane@example.com 4 Jane Smith jane@example.com Here, John Doe and Jane Smith each appear twice. We want to keep only one copy of each. ⚙️ Step 2: Find duplicates We’ll use a helper query called a CTE to find duplicate rows: WITH CTE AS ( SELECT EmployeeID, ROW_NUMBER() OVER ( PARTITION BY FirstName, LastName, Email ORDER BY EmployeeID ) AS rn FROM Employees ) SELECT * FROM CTE; This does the following: Groups rows that have the same FirstName...

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;