چطور می توان سطرهای تکراری را در SQL Server حذف کرد؟

من یک پایگاه داده حجیم دارم که یکی از جداول اون شامل حدودا بیش از ۳۰۰ هزار رکورد هست. حالا می خواستم ردیف های تکراری رو از داخل اون پاک کنم.

جدول زیر در واقع داخل پایگاه داده من هست که شناسه اصلی هر ردیف RowID هست:

RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null

نمیدونم بهترین روش برای حذف ردیف های تکراری در SQL Server چی هست؟

با فرض اینکه هیچ یک از ID های ردیف ت خالی یا null نیست میشه این نسخه رو پیچید:

ابتدا باید ستون های unique یا یکتا رو با استفاده از دستور GROUP BY جداسازی کنی و سپس مقادیر MIN یا MAX با استفاده از دستور SELECT جدا سازی بشه.

بعد دستور DELETE رو برای هر یک از RowId ها اعمال کنی. به صورت خلاصه کدش به شکل زیر میشه:

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

از طرفی اگر به جای id عددی (Integer) از GUID استفاده می کنی، می تونی مقدار زیر رو:

MIN(RowId)

با این مقدار جایگزین کنی:

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

با فرض انکه هیچ کدام از ستون های جدول از نوع binary نباشند می توان از روش زیر استفاده کرد.

ابتدا رکوردهای تکراری را در یک جدول به نام temp قرار می دهیم.

SELECT * into temp  FROM mytable GROUP BY col1, col2, col3 HAVING COUNT(*) > 1

در ادامه با استفاده از دستور delete سطرهای تکراری را از جدول اصلی (mytable) حذف می کنیم.

DELETE FROM t1 FROM  mytable t1 INNER JOIN temp t2 ON (t1.col1 = t2.col1 AND t1.col2= t2.col2 AND t1.col3 = t2.col3)

در پایان داده های موجود در جدول temp  را به کمک دستور insert در جدول اصلی قرار می دهیم.

INSERT INTO mytable SELECT * FROM temp

حال تمام سطرهای تکراری از جدول mytable حذف شدند.

یک روش ساده تر وجود دارد که در آن می خواهیم برای تمام رکوردهای تکراری، تنها رکوردی را حفظ کنم که مقدار فیلد RowID  ان بیشینه باشد. برای درک بهتر فرض کنید که در جدول mytable سفارشات مشتریان ذخیره می شود و ما می خواهیم سفارشات قدیمی را حذف و فقط آخرین سفارش را حفظ کنیم. می توانیم از کوئری زیر استفاده کنیم.

DELETE FROM t FROM mytable AS t
WHERE  RowID < (SELECT MAX(RowID) FROM  mytable  WHERE col1 = t.col1 AND  col2 = t.col2 AND col3 = t.col3 )

یک کپی از جدول mytable به نام t ایجاد می کنیم. سپس یک پیوند طبیعی بین جدول mytable و t برقرار می نماییم. حال سطرهایی از جدول t را که مقدار RowID آنها از  مقدار متناظر  RowID در جدول mytable کمتر هستند را با دستور delete  حذف می کنیم.

می توان از دستور NOT IN هم استفاده کرد:

DELETE FROM t FROM mytable AS t
WHERE RowID NOT IN (SELECT MAX(RowID) FROM mytable GROUP BY col1 ,col2 ,col3)

می توان از دستور NOT EXISTS هم استفاده کرد.

DELETE FROM t  FROM mytable  AS t
WHERE NOT EXISTS (SELECT *   FROM mytable   WHERE col1 = t.col1 ANDcol2 = t.col2 AND col3 = t.col3  HAVING MAX(RowID) = t.RowID)