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

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.

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.