SQL Sorgularında "IN" - "NOT IN" Kullanımı

sqlservercentral.com'da okuduğum bir yazıyı burada paylaşmak istedim.

Konu sorgularımızda IN ve NOT IN kıyaslamalarını kullanmamız.
Aslında konuyu ikiye ayırmakta fayda var, ilk olarak performans konusunu ele almamız uygun olacaktır. Hemen en önemli kısım ile başlayalım; NOT IN kullanılan sorgularda INDEX kullanılmaz :)

Index'in mantığı gereği, bu indexte olmayanları bul dediğimizde, kayıtların sıralı olmasının bize bir faydası olmayacaktır. Tüm tablonun taranması gerekecektir. Konu ile çok ilgisi olmasada benzer bir durumu daha söylemekte fayda var. LIKE '%KRITER' şeklinde bir arama yaptığımızda Index'imizin bize faydası olmayacaktır. Bunun sebebi Index'lerin sıralı bir şekilde tablomuzda barınıyor olması, biz ilk karakteri bilemezsek bu sıranın bize faydası olmayacaktır. Bunu baş harfini bilmediğimiz bir kelimeyi sözlükte aramak olarak düşünebilirsiniz, fihristi kullanma imkanımız olmayacak, tüm kelimelere teker teker bakmamız gerekecektir.
NOT LIKE, <> gibi kriterlerde de Index kullanımı aynı sebepler yüzünden olmayacaktır. 

Hem NOT LIKE hemde '%KRITER' şeklindeki bir sorguda, SQL motoru tarafından kafamıza şişe fırlatılması ihtimali yüksektir :)

Bu sebeplerden olumsuz kriter kullanımlarımını sadece zorunlu durumlardaki sorgularımızda kullanmalıyız. Sistemi incelerken kontrollü bir şekilde bu sorguları kullanabiliriz. Ancak özellikle raporlarda bu eşitsizlik kontrollerinin kullanımı, ve özellikle büyük tablolarda kullanımı bizi çok ama çok zora sokacaktır. Kesinlikle bu tür kullanımlardan uzak durmamız gerekiyor.

Konunun bu kısmı nispeten biliniyordu, ancak bana asıl ilginç gelen NOT IN kullanımlarında dönen cevapların belirsizliği oldu, bunu ilgili sayfadaki arkadaşımız çok güzel bir örnekle açıklamış aynen iletiyorum.

IN kullanımlarında aslında arka planda şu şekilde bir sorgu çalışmış oluyor "WHERE myvalue = 'A' OR myvalue = 'B' OR myvalue = NULL"
Burada bir OR işlemi olduğundan dolayı, beklemediğimiz bir sonuç ile karşılaşmamız olası değil.

NOT IN kullanımlarında ise arka planda aslında olan 'WHERE myvalue <> 'A' AND myvalue <> 'B' AND myvalue <> NULL “
İşte burada bir AND işlemi var, NULL olan alanlar belirsiz olduklarından dolayı, sorgu sonucunda beklemediğimiz bir sonuç kümesi ile karşılaşmamız olası.

A IS NULL ve A = NULL arasında fark olduğunu unutmamak gerekli. Bir çok kişi bu basit hatadan dolayı saatlerce sorgusundaki hatayı arayabilir -ben çok aradım-.


Buradaki kıyaslama için SET ANSI_NULLS = ON/OFF durumu önemlidir. Uygun olan kullanım genelde ON şeklindedir. Yukarıda yazdığım örnekte bu ayarın ON durumunda olduğunu varsaydım.

SQL'deki Bazı Fonksiyonlar : NTILE()

NTILE msdn

Açıkçası NTILE fonskiyonuna bugüne kadar hiç işim düşmedi, ancak geçmişte böyle bir fonksiyona ihtiyacı olan kişiler varsa, onlar için hayat kurtarıcı olduğuna eminim.

Bu yazımı da okumadan önce, SQL 2005 ile gelen yeni sıra numarası fonksiyonlarının temeli olan ROW_NUMBER() fonksiyonu ile ilgili yazımı okumanızda fayda var.

NTILE() fonksiyonu, diğer sıralama fonksiyonlarından farklı olarak bir parametre ile çalışır. Parametresi NULL veya 0 olamaz. Bu parametre, dönen satırları kaç parçada gruplamak istedimiz bilgisini içerir.

Örneğin dönen 13 satırımız varsa, parametre olarak da 1 gönderirsek, her satırın sıralama değeri 1 olacaktır. Parametre olarak 13 gönderirsek sıralama değerleri, 1 den başlayacak ve 13 e kadar gidecektir. Sıralama numarasını verirken kullanılacak olan değerler OVER() içerisindeki ORDER BY kolonu ile sıralanacaktır.

Peki 13 satırı 5 grup ile sırala dersek hesaplama nasıl olacaktır?

13/5 = 2,x = 3 yani ilk grup 3 elemandan oluşacak.
10/4 = 2,x = 3, ilk 3 satır yukarıda gruplandı, 10 satır ve 4 grup kaldı geriye, 2. grupta 3 satırdan oluşacak
7/3 = 2,x = 3
4/2 = 2
2/1 = 2

Sonuç olarak NTILE(5) fonksiyonu, 13 satırımıza aşağıdaki gibi sıra numarası verecektir.

1
1
1
2
2
2
3
3
3
4
4
5
5

NTILE(5) OVER(PARTITION BY "KOLONA" ORDER BY "KOLONB") şeklinde kullanıldığı durumda, tüm satırları, KOLONA'ya göre gruplayacak ve sonrasında, her grubu kendi içerisinde KOLONB'ye göre sıralayacak ve her grubu 5 iç gruba bölecek şekilde sıralama numarasını dağıtacaktır.

Başlarda biraz karmaşık gelse de, kullandıkça mantığa alışılacaktır diye düşünüyorum, dediğim gibi benim bu fonksiyona şimdiye kadar işim düşmedi. MSDN sayfasındaki örnekler gayet güzel, AdventureWorks örnek veritabanını indirerek kendi makinanızda denemeniz faydalı olacaktır. Özellikle PARTITION BY ve ORDER BY alanlarını değiştirerek mantığı kafanızda oturtmanızda fayda var.
Ben ilk denemelerimde pek beklediğim şekilde sonuçlar alamamıştım, mantığı kafamda oturtana kadar biraz şaşı bak şaşır modunda dönen dataset'leri incelemiştim :)

SQL'deki Bazı Fonksiyonlar : RANK() ve DENSE_RANK()

RANK() msdn 
DENSE_RANK() msdn

Bu yazımı okumadan önce, ROW_NUMBER() ile ilgili yazımı okumanızda fayda olacaktır. Mantığı aynı olduğu için aynı detaylara daha fazla girmeyeceğim.

Sıralama ve gruplama fonskiyonlarından birisi de RANK() fonksiyonudur. Bu fonksiyon aslında ROW_NUMBER() ne yapıyorsa aynısını yapar, tek bir farkla. Eğer ORDER BY durumunda kullanılan değer, farklı satırlar için aynıysa, ROW_NUMBER() fonksiyonu sıra numarasını arttırmaya devam eder. Hangisine ilk sırayı verir derseniz, bence diskteki fiziksel sıralamada önde olana ilk sırayı veriyor olabilir, yani Clustered Index'e göre ilk geleni veya son geleni alabilir (ASC ise ilk DESC ise son). Bu kısımdan emin değilim, açıkçası tam olarak nasıl olduğuna pek işim düşmedi.

Şimdi aynı sorgumuzu RANK() ile çalıştırırsak, göreceğimiz tek fark, ORDER BY için kullanılan değerin aynı olması durumunda, bu kayıtların tamamına aynı sıralama numarası verileceğidir. Eş kayıtların aynı numarayı almasından sonra, sıradaki kayıt ise bir sonraki numara ile devam edecektir. Mesela : 1,2,3,3,3,4,5,5,6,7,8,9........

Gene aynı sorgumuzu DENSE_RANK() ile çalıştırırsak göreceğimiz tek fark, eş sayıların sırasından sonra gelen yeni sayının, kaldığı yerden değil, gerçek sırasından devam edeceğidir. Yani bazı sıra numaraları atlanmış olacaktır. Anlatması biraz zor olduğu için direk yukarıdaki örneğe geçiyorum, hemen anlaşılacaktır. 1,2,3,3,3,6,7,7,9,10,11,12........

SQL'deki Bazı Fonksiyonlar : ROW_NUMBER()

ROW_NUMBER() msdn 
Eksikliği MSSQL'de çok uzun zaman hissedilmiş bir fonksiyondur. SQL 2005 ile aramıza katılmıştır kendisi. Basitçe, sorgumuzdaki satırların, sıra numarasını döner diyebilir. ROW_NUMBER() fonksiyonunun çalışabilmesi için OVER ile kullanılması şarttır. OVER'ın anlamı, fonksiyona, dönen datasetin, neye göre gruplanıp, neye göre sıralanacağı bilgisini aktarmaktır. ROW_NUMBER() fonksiyonu bize kaydın, dönen verilerin içerisindeki sıralamasını verecektir, orjinal tablodaki sırasını vermeyecektir, bu ilk başlarda kafamızda durumlar canlandırırken sıkça yapabileceğimiz bir hatadır. Gerçekten ihtiyacımız bu ise, tablodaki tüm kayıtları alarak ve tablodaki Primary Key ile uygun sırada sıralama yaparak (ASC, DESC) fiziksel sıralama bilgisine ulaşabiliriz.

ORDER BY ile kullanımı 
SELECT ROW_NUMBER() OVER(ORDER BY KOLON1), *
FROM TABLO 
WHERE ...
ORDER BY KOLON2

Bu şekildeki bir sorgu, WHERE şartlarımıza uygun olarak dönen kayıtları KOLON1'e göre ASC olarak sıralayacak, ilk sıradaki kayda 1 vererek ve her satırda 1 arttırarak bize satır sıra numarasını dönecektir. Buradaki sıralama kayıtların dataset içerisindeki sıralaması değildir. Yani dönen dataset içerisindeki kayıtlar KOLON2'ye göre sıralanmış olacaktır. Bu kısım başlarda biraz karışık gelebilir, özet olarak; eğer KOLON1 ve KOLON2 farklı şekilde sıralanıyorsa, yukarıdaki sorgudaki satır sayıları sıralı olarak listelenmeyecektir.


PARTITION BY ve ORDER BY ile kullanımı
SELECT ROW_NUMBER() OVER(PARTITION BY KOLON3 ORDER BY KOLON1), * 
FROM TABLO 
WHERE ...
ORDER BY KOLON2

Yukarıdaki sorgunun şu sırayla çalıştığını hayal edelim, WHERE şartlarımıza uyan kayıtlar, KOLON2' e göre sıralı olarak bize döner, ROW_NUMBER() alanı şu anda null'dır, bu kayıtlara orjinaldataset gibi bir isim verelim :) Kayıtların tamamı elde edildikten sonra, datasetimiz içerisindeki kayıtlar KOLON3'e göre gruplanır, her bir grup için kendi içerisinde KOLON1'e göre bir sıralama yapılır. Bu sıralama 1'den başlar ve 1 artarak ilerler. Her grubun kendi içerisinde sıralandığı unutulmamalıdır, yani grup sayısı kadar 1 değerine sahip ROW_NUMBER() olacaktır. Bu şekilde elde edilen satır sayıları ile orjinaldataset üzerindeki null olan ROW_NUMBER() alanı güncellenir ve elimizde son hali ile kayıtlarımız oluşmuş olur.

Prosedür (SQL Stored Procedure) Parametreleri Nasıl Sorgulanır

Veritabanı hakkında bilgi toplayan veya bazı kod parçacıklarını dinamik olarak oluşturan araçlar yazamamız gerektiğinde, bir Stored Procedure'den parametre bilgilerini almamız gerekebilir. Bu durum pek sık karşılaşılan bir durum olmasada, başımıza gelirse ne şekillerde bu sorunu çözebiliriz, ben nasıl çözdüm paylaşmak istedim.

Öncelikle elimizin altında her zaman "sp_helptext" SP'si var. Bu şekilde hakkında bilgi almak istediğimiz SP'leri sorgulayabiliriz. "sp_helptext SP_ADI" şeklinde sorguladığımız zaman SP'nin tüm içeriğini text olarak bize dönecektir. Buradan sonra string işlemleri ile gereken bilgilere ulaşabiliriz. Bu durum sorunumuz için kullanabileceğimiz en kötü yöntem ama genede bir çözüm. Özellikle standart bir kod yazım stiline sahip olmayan kodlamalarda, bir çok kez sıkıntı yaşamamız olasıdır.

Enterprise Library kullanıyorsak eğer, burada oluşurduğumuz Database'in GetStoredProcCommand metodundan bir DBCommand (adı cmd olsun) oluşturduktan sonra, gene Database'in DiscoverParameters metodunu oluşturduğumuz cmd ile kullanarak SP'mizin tüm parametrelerini alabiliriz. Bu yöntem yukarıdaki ilk öneriden çok daha garantili ve performanslı bir yöntemdir. 
DiscoverParameters metodu bize basitçe bir DbParameterCollection'ı hazırlayacaktır. Artık cmd.Parameters dediğimizde istediğimiz parametrenin her bilgisine ulaşabiliriz.

Peki Enterprise Library bu bilgileri nasıl alıyor derseniz. En bağımsız son yöntem direk veritabanına SP parametrelerini sormak. SQL Server versiyonu bu sorgularda önemlidir.

2000 İçin;
[sp_procedure_params_rowset]
Örnek:

USE MYDB

exec [sp_procedure_params_rowset] @procedure_name = 'SP_NAME', @procedure_schema = 'SP_SCHEMA'

2005 ve Muhtemelen 2008 İçin
[sys].[sp_procedure_params_managed]

Bu SP'ler detaylı parametre bilgilerini bizim seçtiğimiz SP'ler için dönecektir.

SQL 2005 üzerinde bulunan bu tür diğer kullanışlı sys SP'leri;
[sys].sp_procedure_params_90_rowset
[sys].sp_procedures_rowset
[sys].sp_procedures_rowset2 
[sys].sp_procedure_params_90_rowset2
[sys].sp_procedure_params_rowset
[sys].sp_procedure_params_rowset2

Tabloların Fiziksel Yapıları

SQL 2005 ve muhtemelen SQL 2008 için, tablo dediğimiz yapılar nasıldır, mantıksal ve fiziksel olarak nasıl işlenir dersek...

Büyükten küçüğe doğru gidersek Tablo, Extent, Page olarak sıralamamızı yapabiliriz. Bu sıradan da anlaşılacağı gibi Page'ler ile Extent'ler oluşur. Extent'ler ile de tablo dediğimiz yapı oluşur.

SQL'de tabloların oluşabileceği 3 çeşit allocation unit vardır. Tablo bunlardan bir veya daha fazlası ile oluşturulabilir. Her tabloda mutlaka olan tek allocation unit IN_ROW_DATA'dır. Diğerleri ise LOB_DATA ve ROW_OVERFLOW_DATA'dır.

IN_ROW_DATA türü allocaion unitler; büyük olmayan veri tiplerini barındırır. Eğer varchar, nvarchar, varbinary, sql_variant veri tiplerinin toplam satır boyutu 8KB üzerinde çıkarsa, ROW_OVERFLOW_DATA kullanılmaya başlanır. Bu kısımların detaylarını Page'leri incelerken göreceğiz.

LOB_DATA türü allocation unit'ler; text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) türlerini barındırır.

SQL'de Page boyutu 8KB'dir. Disk işlemleri Page üzerinden yapılır yani Page üzerinde okuma veya yazma işlemleri yapılır. Her Page 96 byte'lık bir header bilgisi ile başlar ve bu bilginin hemen ardından Data Row'lar gelir. Header içerisinde Page Number, Page Type, Page için kalan boş alan, ve Page'ın owner (Extent) bilgisi tutulur. Her bir Data Row'un, Page üzerindeki ilk Data Row'dan ne kadar sonra başladığı bilgisi Page'in en altındaki offset tablosunda tutulur. Bu tablodaki sıralama Data Row'ların Page içerisindeki sıralamasının tam tersidir.

Bir Data Row birden fazla Page içerisinde olamaz. Peki o zaman bizim 8K'dan büyük satır veya alanlarımız ne olacak derseniz. Büyük veri barındıran Data Row'lar farklı tür bir allocation unit içerisindeki Page'lerde saklanır. text, ntext, image, nvarchar(max), varchar(max), varbinary(max), ve xml veri tipleri LOB_DATA türünde, varchar, nvarchar, varbinary, sql_variant veri tiplerinden toplam içeriği 8KB üzerinde olanlar ROW_OVERFLOW_DATA türündeki allocation unit içerisindeki Page'lerde saklanır.

Normal şartlarda varchar, nvarchar, varbinary, sql_variant veri tiplerin tek başına uzunluğu 8K'yı geçemez ancak birlikte toplam uzunlukları bu değeri geçebilir. Bir INSERT veya UPDATE işlemi toplam row boyutunu (tüm kolonlar ile) 8K üzerine taşırsa, bu row ROW_OVERFLOW_DATA üzerindeki bir Page'e taşınır, bu taşımada en büyük olan kolon uzunluğuna göre bir yeni Page uzunluğu belirlenir. Taşınmadan önceki IN_ROW_DATA üzerindeki değeri de 24 byte'lık bir pointer ile güncellenir. Toplam row boyutu 8K altına düştüğünde ise, ilgili işlemler geri alınır ve row tekrar IN_ROW_DATA allocation unit üzerindeki Page' taşınır.

IN_ROW_DATA ve ROW_OVERFLOW_DATA arasındaki yazma işlemleri zaman alıcı işlemlerdir. Ayrıca taşınan kolonlar sorgu için kullanıldıklarında bu işlemde normal IN_ROW_DATA üzerindeki veri okumasına göre daha uzun sürmektedir. Bu sebeplerden tablo oluştururken bu tür toplam değerlerin 8K'yı geçip geçmediği ayrıca kontrol edilmelidir.
Bu alanların ne sıklıkla sorgulara dahil edildiği düşünülmelidir. Hem IN_ROW_DATA hemde ROW_OVERFLOW_DATA üzerinden senkron okumalar yapmak maliyetli ve zaman alıcı işlemlerdir. Eğer yapılabiliyorsa bu alanlar farklı bir tabloya çekilmelidir. Bu şekilde senkron okuma sayısı azaltılabilir. Farklı tablo join edildiğinde asenkron okuma kullanılacak, iki farklı tablo okunacak ve birleştirilecektir, kısaca ana tablo değerleri daha hızlı elde edilecektir.
Buradaki bir kritik nokta da şudur; Clustered Index Key alanları ROW_OVERFLOW_DATA üzerinde asla olamaz, eğer varchar alanımız Clustered Index Key ise, toplam uzunluğun 8K'yı geçirdiği durumunda, işlemler başarısız olacaktır. Bu konu ile ilgili daha detaylı bilgiye buradan ulaşılabilir.

Page'lerin gruplanmasıyla Extext'ler oluşur. Extent'ler (boş) alan yönetimlerinin kullanımı için kullanılır. Bir extent 8 ardışık Page'den veya 64K'dan oluşur. Verimli kullanımlar için Extent'ler birden fazla tablo Page'lerini barındırabilir, bu tür Extent'lere Mixed Extent denir. Sadece tek tablo Page'lerini bulunduran Extent'lere Uniform Extent denir. Mixed Extent'ler genelde ufak tablolar için kullanılır. Bir tablo 8 Page boyutunu aşarsa, ilgili tabloya ait Mixed Extent'ler Uniform Extent'lere dönüştürülür. Bu konunun detayını burada bulabilirsiniz.

Peki tablo türlerine göre daha kabaca yapıları incelersek nasıl bir durum ile karşılaşırız...

Heap
Heap dediğimiz aslında Clustered Index'i olmayan bir tablodur, bu tabloda kayıtlar herhangi bir sırada değildir. Heap'ler yukarıdaki üç allocation unit türlerine sahip olabilir. Hangisi veya hangilerine sahip olacaklarını yukarıda anlatılan row boyutu ve veri türleri belirler. Heap'lerin Clustered Index veya Non-Clustered Index'lerden farkları; Heap Data Page kayıtları sıralı ve birbirleri ile bağlantılı değildir. (Not Linked.) sys.partitions içerisinde, tek Partition'lı Heap'ler için 1 kayıt gelir ve index_id alanının değeri 0'dır. Birden fazla partition kullanılmış ise Partition sayısı kadar kayıt gelecektir.
Aşağıdaki Heap veri tutma şeması size bir fikir verebilir. Konu ile ilgili detaya buradan ulaşabilrisiniz.
Aşağıdaki şema bir Partition içindir, Heap eğer birden fazla Partition'dan oluşuyorsa, her bir Partition üzerinde aşağıdaki yapıdan birer tane vardır.

Clustered Index
Clustered Index'lerde, Index'ler B-tree mantığı ile tutulur. En üstte bir Root Node, en altta verileri tutan Leaf Node ve araların Intermediate Level denilen yerde bağlantı Node bulunur.
Leaf Node verileri barındıran Data Page'leri tutarken, Intermediate ve Root Level Node'lar Index bilgilerini barındıran Index Page'lerini tutar. Her bir Index Row, bir değer ve bir pointer tutar. Bu pointer B-tree üzerindeki Intermediate Level Page veya Leaf üzerindeki bir Data Row olabilir. Clustered Index'lerde Page'ler, birbirlerine çift olarak bağlıdır. (Doubly-Linked) Clustered Index üzerinde yapılan aramalarda, bu bağlı olma durumu, aramanın çok hızlı bir şekilde yapılmasına olanak vermektedir.
sys.partitions içerisinde, tek Partition'lı Clustered Index için 1 kayıt gelir ve index_id alanının değeri 1'dir. Birden fazla partition kullanılmış ise Partition sayısı kadar kayıt gelecektir. Aşağıdaki şema tek Partition içindir, çoklu Partition kullanımında, aşağıdaki şemadan her bir Partition için ayrı ayrı olacaktır.Konu ile ilgili detaya buradan ulaşabilrisiniz.

Non-Clustered Index
Non-Clustered Index'ler aslında Clustered Index'ler gibi B-tree yapısındadır. Aralarındaki en önemli fark ise Non-Clustered Index'ler veri tutmazlar, Key kolonları ve Clustered Index'e veya Heap'e referans bilgisini tutarlar. Dolayısıyla Leaf Node Data Page yerine Index Page'lerden oluşur. (Include kolonlar hariç.)
sys.partitions içerisinde, index_id alanı 0'dan büyüktür. Partition'lara göre davranışı Clustered Index ile aynıdır. Bir Partition için oluşan yapısının şeması aşağıdaki gibidir.
Detaylı bilgiye buradan ulaşabilirsiniz. Non-Clustered Index'lerin key kolon sayısı en fazla 16 olabilir ve bu kolonların toplam uzunluğu 900 byte'ı geçemez.

Non-Clustered Index'lerin Heap, Unique Clustered Index ve Non-Unique Clustered Index'ler ile nasıl eşleştiğinin detaylarını buradaki yazımdan bulabilirsiniz. Include kolonları ve işleyişleri ile ilgili olarak da buradaki yazımdan faydalanabilirsiniz.

Clustered Index Seçimi

Clustered Index seçimi tablolarımızdaki en önemli seçimdir diyebiliriz. Clustered Index, fiziksel olarak disk üzerindeki sıralamanın oluşturulmasında kullanılan clustered key veya clustered key'ler içerir. Detayını buradaki yazımda okuyabilirsiniz.

Peki Clustered Index seçerken nelere dikkat etmeliyiz, veya kesinlikle önerilen bir yöntem varmıdır dersek. Genel veritabanı kuralı olan duruma göre değişir lafı burada da geçerli :)

Öncelikle hangi tablolarda Clustered Index kullanmamalıyız konusunu aradan çıkaralım. Sürekli güncellenen veya tablolar arası taşıma durumlarının çok olduğu tablolarda Clustered Index kullanılmayabilir, bu sadece bir öneridir, uygun durum sistemden sisteme değişebilir, bu sebepten iki seçenekte denenerek daha verimli olan tercih edilmelidir. Bir diğer durum ise, büyük kolonlardan oluşan tablolarda her Non-Clustered Index için Clustered Index Key Logical RID kullanılacağından, index boyutları çok büyük olacaktır. Bu tür tablolarda da Clustered Index ve Heap denenmeli, daha verimli olan yöntem tercih edilmelidir.

Clustred Index kullanmaya karar verdiysek, şimdi hangi kolon veya kolonları seçmemiz gerektiğine karar vermemiz kalıyor geriye.

Örneğin çok sayıda INSERT alan tablolarda yeni gelen kaydın tablonun en altına eklenmesi genellikle tercih edilen bir yöntemdir. Bu durumda giderek artan değerlerden oluşan bir Clustered Index yapısı tercih edilebilir. Bu tür bir seçimde seçilen key'in Unique olup olmaması konusuda önemlidir, onun detaylarını da buradan okuyabilirsiniz.

Clustered Index key güncellenen bir kolon olmamalıdır. Bu her güncelemede kaydın fiziksel olarak yer değiştirmesi demektir, bu hem performansı çok kötü etkileyecek hemde index üzerindeki fragmantation'ı arttıracaktır.

Clustered Index, disk üzerindeki fiziksel sıralama demek olduğundan, BETWEEN, >, >=, <, <= gibi sorgu filtreleri eğer Clustered Index Key üzerinden sorgulama yaparlarsa SQL kaynak kullanımı en alt seviyede olacak ve disk üzerinden fiziksel okuma ile kayıtlar çok hızlı elde edilebilecektir. Clustered Index Key aynı şekilde Sort işlemlerinde kullanıldığında, veriler zaten sıralı olduğundan ek bir işleme gerek kalmayacaktır. Group By kullanımında gene sıralı verilerin gruplanması çok daha hızlı olacaktır.

Bu bilgilerin dışında ben kişisel olarak, bu tabloların ne kadar sürede bakım görecekleri ile de çok ilgilenirim. Uygun şartlarda veritabanları haftada bir veya en azından iki haftada bir bakımdan geçmelidir. Ancak benim çalıştığım neredeyse hiç bir şirket bu şekilde bir bakımı düzgün olarak yapmamaktadır. Index yapıları bozulduğunda index'ler sisteme destek değil köstek olmaya başlarlar. Fragmantation değerlerinin %10'lara geldiği nice kritik kullanılan tablo görmüşümdür ve bu şirketler günde yüzbinler veya bazen milyonlarca transaction alan şirketler. Bakım yapmama konusunda inatçı veya umursamaz olan müşterilere veritabanı tasarlarken Clustered Index Key kesinlikle artan bir değer olmalıdır diyebilirim. Non-Clustered Index'ler bozulduğunda buna yapabileceğimiz birşey yok ama en azından bu şekilde Clustered Index'lerin bozulmamasını sağlamış oluyoruz.

Düzgün bakım yapıldığını varsayarak ilerlersek. Az INSERT alan tablolarda INSERT maliyeti kesinlikle ihmal edilmelidir. Yani müşteri bilgilerini tutan bir tablo ile müşterilerin satın alma bilgilerini tutan tablo aynı şekilde değerlendirilmemelidir.

Bir bankayı düşünelim, sizce bir banka günde kaç yeni müşteriyi veritabanına ekleyebilir. Bir de şunu düşünelim, bu bankanın tüm müşterileri bir günce kaç kredi kartı işlemi yapabilir. Bu şekilde düşündüğümüzde sürekli INSERT alan ve az INSERT alan tabloları kafamızda canlandırabilmiş oluyoruz.

Bankamız eğer müşteri numarası ile işlemleri yapıyorsa MüşteriNo alanımızı Clustered Index Key olarak seçebiliriz, ama genellikle isim ve soyad bilgisi ile sorgularımızı yapıyorsak o zaman burada İsim ve Soyad alanları beraber Clustered Key Index olarak seçilebilir. Bu şekilde isim ile yapılan aramalarımız çok daha hızlı sonuçlanacaktır. Soyad bilgisinin isme göre daha değişken olduğunu da düşünürsek Soyad ve İsim sırası ile Clustered Key Index oluşturmamız daha da mantıklı olacaktır. Buradaki amacımız isim ve soyad ile yapılan aramalarda en üst performansı almaktı ve bunu başardık. 
Peki dezavantajlarımız neler oldu?
Öncelikle Soyad ve İsim Unique değildir, bir çok kişi aynı Clustered Index Key'e sahip olacaktır, SQL bunu engellemek için uniquifier denilen 4 byte'lık bir görünmez kolonu Clustered Index'imize ve dolayısıyla tüm Non-Clustred Index'imize ekleyecektir.
Tablomuza az da olsa yeni eklenen kayıtlar sıralı olmadığından, fiziksel olarak diğer kayıtların aralarına girmek durumunda kalacak, kayıt kaydırma işlemleri başlayacak ve tablomuzda fragmantation oluşacaktır, bunu engellemek için düzenli bakım yapmamız ve fill factor değerini yüksek tutmamız gerekecektir.

Peki sırayla artan bir Müşteri No bizim Clustered Index Key'imiz olsaydı ve Soyad İsim alanına Non-Clustered bir index tanımlamış olsaydık olmaz mıydı?

Olurdu, bu şekilde uniquifier alanına gerek kalmazdı ve Clustered Index fragmantation derdimizde ortadan kalkardı, müşteri no ile gelen sorgular çok hızlı olurdu ve sıralı çok sayıda müşteri numarasını çok hızlı bir şekilde sorgulayabilir, gruplayabilir ve sıralayabilirdir.

Kısacası bunlar bizim tercihlerimiz ile ilgili. Bizim tercihlerimizi de sistemin kullanılma şekli, ve sistemden beklelenlerin öncelikleri belirlemeli. Burada ilk başta yapılan tasarım daimi olacak diye bir kural yok, yaptığımız hatanın farkına varınca gerekli değişiklikleri yapma şansımız olabilir, sonuçta bazı durumlar denenmeden bilinemiyor.

Sürekli çalışan sistemlerdeki tablolara dokunmak son derece zor veya bazen imkansız olabiliyor, bu tür durumlarda SQL 2005 ile gelen ve çeşitli durumlarda kullanılabilen ONLINE ve MAXDOP parametrelerini araştırmakta fayda var.

Temel Index İşlemleri

Index'ler üzerinde ne yapabiliz konusuna başlamadan önce, clustered ve non-clustered index'ler nelerdir diyorsanız sizi buraya davet ediyoruz, bu yazıda anlatılan Physical RID ve Logical RID mantığı bir kere oturduktan sonra büyük ihtimalle SQL'in nasıl davranacağını tahmin edebilir hale geliyoruz. Tek fark versiyonların çalışma şekillerinin değişebiliyor olması.

Sonradan Clustered Index Oluşturmak 
Bir tabloda clustered index sonradan oluşturulduğunda, bu değişiklik tüm kayıtların fiziksel oladak disk üzerinde sıralamalarının değişmesi ve tüm non-clustered indexlerin de eskiden kullandıkları physical RID alanlarının clustered key Logical RID bilgisi ile güncellenmesi manasına gelir. Bu durumda tüm non-clustered index'ler Rebuild edilir(baştan oluşturulur).

Clustered Index'imiz Var Ama Siliyoruz 
Bir tabloda varolan bir clustered index'i sildiğimizde, yani heap'e geçerken; üstteki işlemin tersine, non-clustered indexlerde bulunan Logical RID bilgileri Physical RID bilgileri ile değiştirilir. Bu durumda da tüm non-clustered index'ler Rebuild edilir.

Unique Clustered Index ve Non-Unique Clustered index farkları için buradaki yazımı okuyabilirsiniz.

Index Rebuild çalışmalarında öncelikle Clustered Index Rebuild edilmeli, sonrasında Non-Clustered Index'ler rebuild edilmelidir. Clustered Index ilk oluşturulurken, işlem tamamen bitene kadar disk üzerindeki eski veriler saklı kalır. Büyük tablolarda yeterli disk alanının olduğu kontrol edildikten sonra, Clustered Index oluşturulmalı veya değiştirilmelidir.

Clustered Index Rebuild İşlemi
Varolan bir Unique Clustered Index'imizi Rebuild ettiğimizde, disk üzerinde clustered key bilgisine göre, Clustered Index sıralamasına uygun olarak düzenlenir. Burada Logical RID değişmediği için Non-Clustered Index'ler bu işlemden etkilenmezler.

Non-Unique Clustered Index Rebuild edildiğinde ise, SQL 2000 buradaki uniquifier alanını günceller ve bu sebepten tüm Non-Clustered Index'ler Rebuild edilir. Ancak bilğim kadarıyla SQL 2005 ve SQL 2008 aynı uniquifier'ı kullanmaya devam ettiği için Non-Clustered Index'ler bu işlemden etkilenmezler.

Clustered Index Değişimi
Clustered Index için kullanılan kolon veya kolonlar değiştirildiğinde, -barındırdığı veri değil, clustered key olarak seçilen kolonların yerine farklı kolonlar seçildiğinde, eklendiğinde, çıkarıldığında- Clustered Index baştan oluşturulur, bu disk üzerindeki sıralamanın değişmesi ve dolayısıyla Non-Clustered Index'lerde bulunan Logical ID'lerin artık geçersiz olması demek, çünkü barındırdıkları Logical ID eski clustered key için gerçerli olarak kalacak. Bu sebepten Non-Clustered Index'ler Rebuild edilir.

SQL 2005 ile gelen table partition işlemleri, sadece clustered key alanının disk yerini etikelediği için bu durumlarda Non-Clustered Index'ler tekrar Rebuild edilmezler. Kullandıkları Logical RID partition sonrasında da geçerli olacaktır. Aynı durum filegroup değiştirilmesi içinde geçerlidir.

Clustered ve Non-Clustered Index

Index'ler konusuna gelirsek iki index örneğimiz var, clustered index bir anlamda tablonun kendisi demek, non-clustered index ise, kabaca belirli alanların direk clustered key ile eşleştirilerek tüm tablonun taranmasına engel olmak demek diyebiliriz. Yazımın sonunda basit bir örnek vereceğim.

Detaylara girmeden önce sıkça kullanılan bir kısaltma RID : Record ID anlamına gelir. İki türlü karşımıza çıkar, Physical ve Logical. Bildiğim kadarıyla Physical RID disk bilgisini içerirken, Logical RID clustered index bilgisini içerir.

Heap : Bir tabloda eğer clustered index yoksa bu tabloya heap diyebiliriz. Bu tablolarda bir kaydı bulmanın tek yolu full table scan yapmaktır. Pek rastlanmaz ama, tablomuzda clustered index yok ama non-clustered index var diye düşünürsek, SQL her non-clustered index kullanımında table scan yapmaktansa, non-clustered index'in heap üzerindeki Physical RID ile eşleştirir. Bu non-clustered index key için kullanılacak olan satır bu disk alanındadır şeklinde bir işaretleme olarak düşünebiliriz bu durumu.

Clustered Index: Disk üzerinde fiziksel olarak tablodaki kayıtların sıralanmasını sağlayan index'tir, aslında tablonun kendisidir de diyebiliriz. Buradaki sıralamayı sağlayan alana cluster key diyebiliriz. Cluster key olabiliyorsa unique olmalıdır. Ancak bazı durumlar için bu şekilde bir yapı kullanılamayabilir. Non-Unique Clustered Index Kullanımı için buradaki yazımı okuyabilirsiniz. Clustered index ve non-clustered index barındıran tablolarda, non-clustered index'ler clustered index'lerin Logical RID bilgisini tutarlar. Heap durumunda bu Physical RID idi. Bu non-clustered index key için kullanılacak olan satır bu Logical RID ile eşleşen clustered index alanındadır diye bunu düşünebiliriz. Clustered index zaten bulunduğu disk bilgisine sahiptir ve bu şekilde uygun disk alanına ulaşılır. Clustered index eğer araya kayıt alırsa disk üzerinde belirli kaydırma işlemleri yapmak durumunda kalacaktır, bu kaydırma işlemlerini, clustered index disk bilgilerinin güncellenmesi olarak düşünebiliriz. Non-clustered index'ler disk alanı (Physical RID) ile değil, clustered index'in Logical RID bilgisi ile eşleştiği için, bu kaydırmalarda non-clustered index'ler etkilenmezler.
Heap kullanımında ise emin olmamakla beraber sanırım bütün yeni kayıtlar disk üzerindeki son tablo kaydının sonrasına aktarılır. Zaten kayıtlar için belirli bir sıra gerekmediğinden yeni kayıt, son kayıt ardına yazılır ve bir kaydırma işlemi olmaz. Yani ben yapsam böyle yapardım :)

Heap ve Clustered Index arasındaki bir fark da Read performansındadır. Clustered Index üzerinde kayıtlar sırayla bulunduğundan dolayı, Heap'e kıyasla 8 kat daha hızlı veri okunabilir. Bunu güzel ama eski bir yazıdan okumuştum, yeni SQL versiyonları ile bu değerler değişmiş olabilir. Kaynak.

Basit bir örnek vermeyi deneyeyim;
Can diye bir müşteri olsun ve bu müşterinin 1000 tane alış işlemi olsun, oldukça iyi bir müşteri :)
Alım işlemleri tablomuzda 20.000 işlem olsun, ve işlem numaramız bizim clustered key alanımız olsun. Örneğimizi basit tutmak için müşterilerin de isimle bu tabloda tutulduğunu varsayalım. 

Bu durumda 5 numaralı alım işlemini bulmak istediğimizde clustered index scan yaparak SQL olabilecek en hızlı şekilde bize kayıt bilgilerini dönecektir.

Can'ın işlemlerini bulmak istediğimizde, sistem tüm tabloyu tarayacak ve 20.000 kaydı inceledikten sonra bulduğu kayıtları işlem numarası ile eşleştirerek yukarıdaki senaryodaki gibi devam edecektir. 

Peki biz Can'ın isim bilgisinin olduğu alana bir non-clustered index eklersek ne olacak? Gene 20.000 kaydı incelemeyecek miyiz?

Cevap hayır, sebebi ise non-clustered index alanlarının sıralı olarak saklanıyor olması. Kabaca düşünürsek ismi C ile başlamayan kayıtların SQL'in incelemesine gerek kalmayacaktır. Gene kabaca düşünürsek; tabloyu tararken D harfine geldiğimizi varsaysak devam etmemize gerek kalmacayaktır.

Non-Unique Clustered Index Kullanımı

SQL server işleyişinde önerilen, clustered index'in olabiliyorsa unique olmasıdır. Peki eğer biz unique olmayan bir clustered index tanımlamayı seçersek neler olur. Bu durumda SQL tabloya yeni bir kolon daha ekler, bu kolona uniquifier denir ve 4 byte yer kaplar. Bu kolon tablodaki her satırda vardır ve clustered index'in aynı olabildiği durumlar için belirleyici görevi görür. Satırın belirleyici olmasında rol aldığı için, diğer non-clustered indexlerde de bu kolon vardır. Bu sebeplerden non-unique clustered index kullanımlarında ek bir disk alanı kullanımı olacağı unutulmamalıdır. Bu ek kolonun performansa da az da olsa etkisi olacaktır.

Non-Unique Clustered Index'leri belirli durumlarda kullanmanız gerekebilir, örneğin veritabanına bakım yapmama konusunda inatçı bir müşteri ile çalışıyorsanız :)

Tablolar için fragmantation çok önemlidir, ve önerilen durum clustered index'in artan değerler alması ve dolayısıyla yeni kaydın her zaman tablonun en alt kısmında yer almasıdır. Genellikle sorun yaşanan veritabanları eski ve bir çok yeni geliştirme geçirmiş, ilk tasarlanırken akla gelmeyen bir çok işi de yapar hale gelmiş veritabanlarıdır. Bu tür durumlarda performans çalışmalarında tablolarda kritik değişiklikler yapmak sistemin işleyişini tümden etkileyebilir. Örneğin, tüm sistemde unique'lik bir GUID ile sağlanıyorsa ve bu GUID sizin işlem tablolarınızda bir clustered index ise, aynı zamanda önceki örnekteki gibi bakım yapmayan bir müşteri ile karşı karşıyasanız, kısaca durum şudur: tabloda artık düzgün çalışan bir clustered index kalmamış demektir.

Bu tür durumlarda, GUID'i clustered index olmaktan çıkartarak unique index olarak eklemek uygun olabilir, clustered index olarak bir artan ID veya işlem zamanı kullanılabilir. Ancak farklı kayıtlar için, işlem zamanı -zor da olsa- sistemde aynı olabilir, aynı olmasa bile  SQL aynı olabilme ihtimaline karşı yukarıdaki uniquifier kolonunu tabloya eklemek zorunda kalacaktır.

Sonuç olarak her neredeyse tabloda bir clustered index olmalıdır ancak clustered index her zaman unique olmalıdır diye bir gereklilik yok, imkan varsa unique olması sisteme büyük performans sağlayacaktır. Bu bilgileri düşünerek non-unique clustered index kullanılabilir, veya kullanılmayabilir :)

SQL 2008 ve Date, Time Veri Tipleri

Time : SQL 2008 ile aramıza katılan bir veri tipi. Sadece saati veya sadece tarihi tutmak istediğimizde char kullanmak yerine bu tipleri kullanabilyoruz. Ancak Time 3-5 byte yer kaplıyor, eski dostumuz smalldatetime 4 byte, yani Time tipini kullanırken amacımız yerden kazanmak değil. 100 nanosaniyeye kadar hassas bir saat bilgisi tutabiliyor. Bu bilgiyi işletim sisteminden sorguluyormuş, ne kadar hassas bilgi alacağı biraz da işletim sistemine bağlı. Ancak bu kadar hassas saat bilgisi kolay kolay ihtiyaç duyulacak bir bilgi gibi gelmedi bana, belki bilimsel çalışmalarda önemli olabilir.

Date : Bu veri tipide SQL 2008 ile geldi. Gün bazında tarih bilgisini tutuyor ve 3 byte yer kaplıyor. char(8) olarak tutulan tarih bilgilerinin aslında bu şekilde tutulması hem yer kazanımı açısından hemde SQL'in tarih kıyaslamalarının hızlanması açısından faydalı olacaktır gibi geldi bana, ama sonuçta SQL bu, en iyisini bulmak için bolca denemek gerekiyor :)

Eski veritiplerinin yukarıdaki yeni tiplere nasıl çevrileceği konusunuda araştırmak gerekli.

MSDN sayfasına buradan ulaşabilirsiniz.