IPHaber
Son Yazılar

Yazılım


Makale: Truncate ile Delete arasındaki Fark

Temmuz 6, 2011 by admin in Yazılım with 0 Comments

TRUNCATE ve DELETE her ikiside tablolar içerisinden veri silmek için kullanılan ve hiçbir şekilde tabloyu silmeyen komutlardır. Fakat her iki komutun birbirlerine göre syntax, performance ve kaynak kullanımı açısından farklılıklar bulunmaktadır.

Truncate

TRUNCATE komutu SQLde belli bir tablo içerisindeki tüm satırları transaction logu içerisine herbir satır için loglama yapmadan siler. TRUNCATE komutu WHERE koşulu ile kulanılamıyor olmasına rağmen tablo yapısına dokunmadan silme işlemini gerçekleştirebilmek için oldukça kullanışlı gözükmektedir.

TRUNCATE komutu için Syntax:

TRUNCATE TABLE [ { database_name.[ schema_name ]. | schema_name . } ] table_name

Table_name : Truncate edilecek yata tüm satırların silineceği tablo

Syntax’ı çalıştırmak için aşağıdaki gibi query kullanılabilir:

TRUNCATE TABLE authors (Bu komut ile autohors tablosundaki tüm datalar silinir)

Delete

DELETE komutu da SQLde tablo içerisindeki satırları siler fakat her bir satır için transaction loguna silme logu atar. Ayrıca bu komutu ile WHERE koşulu kullanılarak hangi satırların silinebileceği belirlenebilir.

Burada bu komuta ait basit bir syntax gösteriliyor:

Syntax:

DELETE FROM TABLE_NAME[ { database_name.[ schema_name ]. | schema_name . } ] table_name

Database_name: Tablonun içinde yer aldığı database ismidir. Bu isteğe bağlıdır. Eğer bu bilgi yok ise query’nin çalıştırıldığı ve o an geçerli olan database kabul edilir.

Schema_name: Tablonun içinde yer aldığı schema ismidir. Bu isteğe bağlıdır. Eğer bu bilgi yok ise o an geçerli olan database kabul edilir.

Table_name : Silme işleminin yapılacağı tablo adıdır.

Aşağıda bu komutun kullanıldığı basit bir query görülmektedir:

DELETE FROM authors

Yukarıda belirtilen komut ile authors tablosu içerisindeki tüm veriler silinir.

DELETE komutu ile birlikte WHERE koşulu kullanılarak silinecek olan veri kısıtlanabilmekte ve sadece istenen verilerin silinmesi sağlanabilmektedir. Kullanım şekline örnek verilirse;

DELETE FROM authors Where AuthorId IN (1,2,3)

Bu komuta göre sadece AuthorIDsi 1,2 ve 3 olan satırla silinecektir.

 

DELETE ve TRUNCATE Komutları arasındaki farklar

Yukarıda DELETE ve TRUNCATE komutlarının kullanımını anlatmaya çalıştım. Her iki komutun aslında benzer gibi gözükselerde aralarında birkaç farklılık bulunmaktadır. Bu benzerlik ve farklılıkları aşağıda anlatmaya çalıştım;

TRUNCATE ve DELETE veri siler Tablo yapısına dokunmaz

Her iki komutta veri siler fakat tablonun yapısına, kolonlarına, constraintlerine ve indexlerine dokunmaz. Verilere ilave olarak tablo tanımlamalarını silmek istersek DROP TABLE komutunu kullanmak gerekecektir.

Verilerin koşullu silinmesi

Koşullu veri silme işleminde bütün satırla silinmez. Örnek verecek olursak; authors adlı tablo içerisinden sadece Türkiye içerisinde yaşayan yazarları silmek istediğimizde bu durum oluşmaktadır.

 

  • TRUNCATE – TRUNCATE komutunun kullanımında WHERE koşuluna izin verilmediği için koşullu silme işlemi gerçekleştirilemez.
  • DELETE – DELETE komutu ise WHERE koşulu ile birlikte kullanılabilmekte ve koşullu silme işlemi gerçekleştirilebilmektedir.

DELETE ve TRUNCATE her ikiside loglanan işlemlerdir:

Yazılan birçok makale içerisinde ‘delete loglanan Truncate ise loglanmayan bir silme işlemidir’ denilmektedir. Başka bir deyişle, delete komutunu çalıştırdığımızda silinen satırların kayıtları loglanır ve truncate komutunu çalıştırdığımızda ise herhangi bir log tutulmaz. Bu tamamiyle gerçek dışı olup her iki işlem içinde aslına loglamanın yapıldığı fakat farklı yöntemlerin kullanıldığı bilinmelidir. Truncate delete komutuna göre sadece daha az sistem ve transaction log kaynağı kullanmaktadır ve bu sebeple delete komutuna göre daha hızlı çalışmaktadır. Özetle her iki komutta loglanır fakat aşağıda örneklerde gösterildiği gibi farklı çalışırlar.

  • DELETE işleminde herbir satır için loglanır DELETE komutu ilgili tablo içerisindeki satırları bir seferde silerken aynı zamanda her silinen satır için log tutar. Bu yüzden, eğer çok büyük miktarda veri silme işlemi yapılırsa bu işlem transaction logunun büyümesine sebep olur. Başka bir deyişle, büyük miktardaki verilerin silinmesi her silinen satırın logunun tutulması sebebiyle daha fazla sunucu kaynağı kullanacaktır. Transaction loglarının hızlı bir şekilde büyümesinin sebebi budur. Aynı şekilde silinen her satırın logunun tutulması işlemide yavaş olmaktadır. Bazılarının şu soruyu sorması olasıdır: Madem delete komutu ile yapılan silme işlemleri bu kadar log tutuyor ve sistem kaynağı kullanıyor ise Microsoft neden her satırın loglanmasını engellemek için birşeyler yapmıyor??? Bunun cevabı ise şudur: Eğer database full recovery modda çalışıyor ise, SQL serverlar için database’i en sonki durumuna döndürebilmek için (Roll Back) detaylı loglama gereklidir
  • TRUNCATE işleminde verileri içeren veri sayfalarının kaldırılması loglanır  TRUNCATE komutu DELETE komutu gibi silme işlemi yapıyor olmasına rağmen daha hızlıdır. Silinen her satır için loglama yapmaz fakat tabloların veri sayfalarının deallocation (atama kaldırma) bilgileri loglanır. TRUNCATE komutu ile veriler tablo bilgilerinin ve kayıtlarının tutulduğu veri sayfaları deallocate edilerek gerçekleştirilir ve transaction logları içerisine loglanır. Aslında TRUNCATE datayı silmez fakat veri sayfalarını deallocate eder ve indexler ile pointerlerı siler. Veriler üzerine yeni bir veri yazılana kadar yada shrink edilene kadar korunur. Bu işlem çok fazla kaynak kullanımı gerektirmemektedir ve oldukça hızlıdır. TRUNCATE işleminin loglanmadığını düşünmek çok fazla düşülen bir hatadır ve yanlıştır. Veri sayfalarının deallocate edilmesi bilgileri log dosyaları içerisinde tutulmaktadır. Bu sebeple, Books Online (BOL) TRUNCATE komutunun ‘minimum loglanan’ bir işlem olduğuna işaret eder. TRUNCATE komutunu bir transaction ile kullanabilirsiniz. Ne zaman transaction geri alınırsa veri sayfaları yeniden yüklenerek database’in yeniden orjinal ve kararlı konumuna geçmesi sağlanmış olur.

Identity sütunları için Delete ve Truncate komutlarının Davranışı

Şimdi identity sütunları üzerinde duracağız. Identity sütunlarına karşı her iki komut farklı davranışlar sergilemektedir. Truncate komutu kullanıldığında identity değerleri resetlenir ve ilgili sütundaki identity değeri yeni satırlar için başlangıç değerinden başlar. Delete komutu ile identity değeri resetlenmez ve yeni satırlar için kaldığı yerden devam eder. Her iki komut içinde herhangi bir başlangıç değer set edilmediği durumlarda ise default değer 1 olarak kabul edilir. TRUNCATE komutu identity sayacını sıfırlarken, eğer identity sayacını kaldığı yerden devam ettirmek istiyor iseniz DELETE komutunu tercih etmeniz gerekmektedir.

Neden farklı davranmaktadırlar? Bilmiyorum ama T-SQL ihtiyaca göre kullanmak üzere 2 farklı yöntem sunmaktadır. Eğer tablo içerisindeki tüm verilerin silinmesi ve identity sayacının resetlenip 1’den başlatılmasını istiyorsak TRUNCATE komutu bize yardımcı olacaktır. Tüm verileri silip identity sayacını resetlemek istemiyor isek DELETE kullanmak işimizi görecektir.

DELETE DML komutu, TRUNCATE DDL komutudur

Bu oldukça bilinen ve birçok makale içerisinde rastlayabileceğimiz bir farktır. TRUNCATE bir DDL (Data Definition Language) operasyonu, DELETE ise bir DML (Data Manipulation Language) operasyonudur. Evet bu aynı zamanda SQL server’a da bağlıdır. Burada neden TRUNCATE neden DDL ve DELETE neden DML komutudur onları inceleyeceğiz,

TRUNCATE komutunu çalıştırdığımızda ilgil tablo üzerine “Schema Modification” (Sch-M) lock’ı yerleştirilir. “Schema Modification” (Sch-M) nedir?

Database Engine sütun ekleme yada tablo silme gibi DDL operasyonları sırasında Schema Modification kilitlerini kullanır. Bu işlem esnasında Sch-M aynı tabloya gelebilecek diğer erişimleri engeller. Bunun anlamı Sch-M tüm dış operasyonları silme işlemi sonlanana kadar engeller. TRUNCATE komutu ile veri silme işlemi esnasında ilgili tabloya gelebilecek herhangi bir modifikasyon talebi nasıl engelleniyor diye soracaksınız? Veri silme işlemi madalyonun sadece bir yüzüdür. Yukarıda bahsedildiği gibi, truncate işleminden gördüğümüz hiçbir verinin silinmediğidir, sadece veri sayfaları deallocate edilir. Çünkü TRUNCATE tablolar içerisinde herhangi bir değişiklik yapmaz, bu sebeple DELETE TRIGGER çalıştırılmaz. HErhangi bir tablo truncate edilirken hiçbir veri değiştirilemez yada eklenemez. Bir komutun DDL komutu olabilmesi için şağaıda yazan bazı şartların sağlanması gerekmektedir;

  • DDL operasyonu ile birlikte gelen tanımlama veya tablo yapısını değiştirmeli ve,
  • Tablo yapısını değiştirirken herhangi bir verinin değiştirilebilmesi için erişim izni vermemelidir.

TRUNCATE’in yukarıda belirtilen aktiviteleri getirmesi bu komutun DDL komutu olduğunu ispatlamaktadır.

Şimdi biraz DELETE komutundan bahsedelim. DELETE komutu çalıştırıldığı esnada hangi lock’ın uygulandığından emin olmamakla birlikte, bildiğimiz önemli nokta DELETE komutunun herbir satırı tek tek sildiğidir. Tablo içerisindeki verileri silerek bu verileri değiştirir ve bu sebeple DELETE TRIGGER çalıştırılmaktadır. TRUNCATE komutunun yaptığı Identity sayacını resetlemek gibi tablo yapısını değiştiren hiçbir değişiklik bu komut tarafından yapılmamaktadır.

DML operasyonu içerisinde aşağıda belirtilen şartların sağlanması gerekmektedir:

  • Tablo verilerini değiştirmek.
  • Tablo verilerini değiştirirken bu arada tablo yapısıyla ilgili hiçbir değişikliğe izin vermez.

Triggerlar için Truncate ve Delete davranışları

Triggerlar SQL sunucuları için oldukça önemli konular olup, burada TRUNCATE ve DELETE komutlarının triggerlar için nasıl farklı çalıştığını anlatmaya çalışacağım. Hepimizin bildiği gibi tablo içerisinde herhangi bir veri değişikliği olamsı durumunda trigger çalışır. TRUNCATE ve DELETE komutları, tablo içerisinden veri sildikleri için DELETE TRIGGER eğer var ise çalıştırılır ve aynı zamanda INSTEAD OF ve AFTER trigegrlarıda eğer var ise çalıştırılabilir.Triggers are important topic in SQL Server, and here I am talking about how both TRUNCATE and DELETE behave differently for Triggers.  INSERT ve UPDATE triggeri bu noktada çalışıtırılmaz.

Her ikisini teker teker incelersek:

  • TRUNCATE –  Tablo içerisindeki bütün satırları silmek için bu komut çalıştırıldığında, aslında satırlar silinmemekte sadece veri sayfaları deallocate edilmektedir. Bu durumda herhangi bir veri değişikliği olmadığı için hiçbir trigger çalışmayacaktır. Bildiğimiz üzere Truncate DDL komutudur ve sadece tablo tanımlamalarını ve yapısını değiştirir.
  • DELETE – DELETE komutunun çalıştırılamsı durumunda eğer var ise DELETE TRIGGER ve aynı zamanda INSTEAD OF ve AFTER triggerları çalıştırılacaktır. Bildiğimiz üzere Delete komutu DML komutudur ve satır satır veriyi silme işlemini gerçekleştirir. BAşka bir deyişle tablo içerisinde veri üzerinde değşiklik yapacaktır ve herhangi bir DML komutunun çalıştırılması yukarıda belirtilen triggerların çalışmasını sağlayacaktır.

Bu komutları nerede kullanabilirz?
Bu omutların kullanılması ile ilgili bazı kısıtlamalar vardır:

Delete için

  • DELETE komutu, triggerı bozma durumunda veya başka bir tabloda FOREIGN KEY ile constraint tanımlanmış bir satır silinmeye çalışıldığında iptal edilir.  Eğer DELETE komutu birçok satırı aynı anda silmeye çalışırken bu satırlardan herhangi birisi üzerinde bir trigger yada constraint tanımlı ise, komut iptal edilir ve buna karşılık bir hata döner. Bu durumda hiçbir satır silinmez.

Truncate için
TRUNCATE TABLE komutu aşağıda belirtilen tablolar üzerinde çalışmaz:

  • FOREIGN KEY constraint ile refere edilmiş,
  • Indexlenmiş,
  • İşlemsel replikasyon yada birleştirme replikasyonu kullanılan tablolarda.

TRUNCATE ve DELETE operasyonlarının çalıştırılması için yetkilendirme

Her iki komutun çalıştırılması için server üzerinde yetki tanımlanmış olması gerekiyor. Tabloyu truncate edebilmek için en azından o tablo üzerinde ALTER yetkisinin verilmiş olması gerekmektedir (table owner, sysadmin fixed role ve db_owner ve db_ddladmin fixed database role yetkileri verilmiş olmalı). Satırları silmek için ise sadece DELETE yetkisine ihtiyaç bulunmaktadır.

Sevgiler

Cenk GÜNDÜZ

 

Tagged , , , ,

Related Posts

Leave a reply

E-posta hesabınız yayımlanmayacak.

Kapat