Sql Server 2008, T-SQL diline yapılmış birkaç ek geliştirme ile birlikte geldi. Daha önce yazdığım şu makaleden SQL Server 2008 MERGE operatörü hakkında bilgi alabilirsiniz.

Bugün yazacağım yazı ile EXCEPT ve INTERSECT operatörlerini inceleyeceğim.

Hem EXCEPT, hem de INTERSECT operatörü iki farklı sorgunun karşılaştırılması ve bir sonuç kümesi döndürülmesi ilkesi ile çalışır.

EXCEPT anahtar kelimesi ile, bir sorgunun sonuç kümesinde olan, fakat diğer sorgunun sonuç kümesinde kesinlikle olmayan kayıtları buluruz.

TABLO1′de bulunup, TABLO2′de bulunmayan kayıtlar

SELECT * FROM TABLO1
EXCEPT
SELECT * FROM TABLO2

TABLO2′de bulunup, TABLO1′de bulunmayan kayıtlar

SELECT * FROM TABLO2
EXCEPT
SELECT * FROM TABLO1

INTERSECT anahtar kelimesi ile ise, her iki sorgunun sonuç kümesinde de kesinlikle olan kayıtları bulabiliriz.

Hem TABLO1′de bulunup, hem de TABLO2′de bulunan kayıtlar

SELECT * FROM TABLO1
INTERSECT
SELECT * FROM TABLO2

Bir örnek ile daha iyi anlaşılacak. Öncelikle Personel ve Stajer tablolarımızı oluşturalım.

CREATE TABLE dbo.Personel
(
	ID INT NOT NULL IDENTITY,
	AdSoyad VARCHAR(100) NOT NULL,
	SicilNo CHAR(6) NOT NULL,
	EMail VARCHAR(100) NOT NULL,
	IseGirisTarihi SMALLDATETIME NOT NULL,
	YoneticiID INT NOT NULL,
	DepartmanID NOT NULL
)

CREATE TABLE dbo.Stajer
(
	ID INT NOT NULL IDENTITY,
	AdSoyad VARCHAR(100) NOT NULL,
	StajerNo CHAR(6) NOT NULL,
	EMail VARCHAR(100) NOT NULL,
	StajBaslangicTarihi SMALLDATETIME NOT NULL,
	YoneticiID INT NOT NULL,
	StajDepartmanID NOT NULL
)

İlk olarak, staj yapmamış personel’in adını seçeceğimiz sorguyu yazalım;

SELECT AdSoyad FROM dbo.Personel
EXCEPT
SELECT AdSoyad FROM dbo.Stajer

İkinci olarak, işe başlamamış olan stajer’lerin adını seçeceğimiz sorguyu yazalım;

SELECT AdSoyad FROM dbo.Stajer
EXCEPT
SELECT AdSoyad FROM dbo.Personel

Son olarak, hem staj yapıp, hem de işe başlamış olan personel’in adını seçeceğimiz sorguyu yazalım;

SELECT AdSoyad FROM dbo.Personel
INTERSECT
SELECT AdSoyad FROM dbo.Stajer

BilgeAdam’daki eski öğrencilerimden biri şu soruyu sordu; “Herhangi bir veritabanındaki tüm tablolarda bulunan Identity Sütunları belirlemenin bir yolu var mıdır?”

Aslında bir değil, tam üç yolu var;

Yöntem 1 : COLUMNPROPERTY fonksiyonunu kullanmak

SELECT
	TABLE_NAME,
	COLUMN_NAME
FROM
	INFORMATION_SCHEMA.COLUMNS
WHERE
	COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'ISIDENTITY') = 1
ORDER BY
	TABLE_NAME

Yöntem 2 : SYS.ALL_COLUMNS View’unu kullanmak

SELECT
	OBJECT_NAME(AC.OBJECT_ID),
	SO.NAME
FROM
	SYS.ALL_COLUMNS AS AC
	INNER JOIN SYS.OBJECTS AS SO ON OBJECT_NAME(AC.OBJECT_ID) = SO.NAME
WHERE
	AC.IS_IDENTITY = 1 AND
	SO.TYPE = 'U'

Aynı yöntemi biraz faklı olarak şöyle de yazabiliriz;

SELECT
	OBJECT_NAME(OBJECT_ID),
	NAME
FROM
	SYS.ALL_COLUMNS
WHERE
	IS_IDENTITY = 1 AND
	OBJECTPROPERTY(OBJECT_ID, 'ISUSERTABLE') = 1

Yöntem 3 : SYS.IDENTITY_COLUMNS View’unu kullanmak

SELECT
	OBJECT_NAME(IC.OBJECT_ID),
	SO.NAME
FROM
	sys.identity_columns AS IC
	INNER JOIN SYS.OBJECTS AS SO ON OBJECT_NAME(IC.OBJECT_ID) = SO.NAME
WHERE
	SO.TYPE = 'U'

Aynı yöntemi biraz faklı olarak şöyle de yazabiliriz;

SELECT
	OBJECT_NAME(OBJECT_ID),
	NAME
FROM
	sys.identity_columns
WHERE
	OBJECTPROPERTY(OBJECT_ID, 'ISUSERTABLE') = 1

SQL Server servislerinden SQL Agent servisinin çalışıp çalışmadığını anlamamız gerektiği durumlarda msdb veritabanında tanımlı sp_help_job stored procedure‘ünü kullanabiliriz;

EXEC msdb..sp_help_job

Sorgu sonucunda dönen sonuç kümesinden, name alanında servisin ismi yer alırken, current_execution_status alanında servisin çalışma durumu yer alır.

Servisin çalışma durumunu gösteren birden fazla değer olabilir;

• 1 : (Executing) Çalışıyor
• 2 : (Waiting for thread) Çalışmaya devam ediyor
• 3 : (Between retries) Tekrar deneniyor
• 4 : (Idle) Boşta
• 5 : (Suspended) Durdurulmuş
• 7 : (Performing completion actions) Tamamlama görevi sürdürülüyor

Sql Server‘da belli bir veritabanında en büyük tabloları bulmak için aşağıdaki sorguyu çalıştırabiliriz;

DECLARE @TabloBoyutlari TABLE (TabloAdi VARCHAR(50), SatirSayisi INT, AyrilmisAlan VARCHAR(50), KullanilanAlan VARCHAR(50), IndexBoyu VARCHAR(50), KullanilmayanAlan VARCHAR(50))

INSERT INTO @TabloBoyutlari
EXECUTE sp_MSforeachtable 'sp_spaceused [?]'

SELECT TOP 10 * FROM @TabloBoyutlari ORDER BY CONVERT(INT, REPLACE(AyrilmisAlan, ' KB', '')) DESC

Bu sorgu sayesinde ilgili veritabanındaki tabloların,

• Tablo Adı
• Satır Sayısı
• MDF dosyasında ilgili tablo için ayrılmış alanın boyutu
• MDF dosyasında ilgili tablo için ayrılmış alanın kullanılan boyutu
• MDF dosyasında ilgili tablonun indexleri için ayrılmış alan
• MDF dosyasında ilgili tablo için kullanılmayan alan

bilgilerine ulaşabiliriz.

İlgili tablo için aşağıdaki eşitlik her zaman doğrudur;

Kullanılan Alanı + Index Alanı + Kullanılmayan Alanı = Ayrılmış Alan

Benim bilgisayarımdaki AdventureWorks2008R2 örnek veritabanında yukarıdaki sorguyu çalıştırdığımda, şöyle bir sonuç alıyorum;

Uzman TV ile yaptığımız Sql röportajlarında bana sordukları sorular ve cevaplarım;

SQL nasıl bir veritabanı dilidir? (SQL nasıl ortaya çıktı? Kim tarafında bulundu? Veritabanı nedir?)

Önce veritabanı ne demektir, ondan bahsetmek lazım. Veritabanı, bilgisayar ortamında bir düzen ile saklanan, programatik erişim imkanı bulunan, yönetilebilir, güncellenebilir, birbirleri arasında ilişkiler tanımlanabilir, bilgiler kümesidir.

SQL, ilk olarak IBM Research Center tarafından DB2 veritabanı ailesi için geliştirilmiştir. Daha sonra SQL dili, ANSI tarafından standartlaştırılmıştır.

Türkçe açılımı Yapılandırılmış Sorgu Dili anlamına gelmektedir. Verileri ve veri kümelerini modellemek ve yönetmek için kullanılır.

SQL, tanımlı anahtar kelimelerden oluşturulan sql cümleleri ile, veritabanına kayıt ekleyebilir, güncelleyebilir, silebilir veya varolan kayıtları listeleyebilir.

Microsoft Sql Server, Oracle ve hatta Access kendi SQL cümle yapısına yani diyalektiğine sahiptir.

Hangi tür şirketler SQL’den yararlanabilir? (Veritabanı uygulamalarını tercih eden firmalar nelerdir? SQL kullanması gereken uygulamalar hangileri?)

Günümüzde bilgisayar ve dolayısıyla yazılım kullanmayan, bunlara ihtiyaç olmayan iş kolu kalmadı. Ne kadar küçükte olsa, ne kadar büyükte olsa, her alandaki her firmanın mutlaka bilgisayar ve yazılım kullan birimleri vardır.

Genelde firmalar, kullandıkları bilgisayar programlarının verileri güvenli bir ortamda kaydetmesini, daha sonra aradığında bulabilmesini, gerekirse analiz edebilmesini, yaptığı analize ilişkin raporlar üretebilmesini beklemektedir.

Veritabanı uygulamaları kullanmadan böyle bir işlevselliği sağlayabilmek olanaksız olmasa da çok zor olacağından, hemen hemen tüm firmaların bir veritabanı yönetim sistemine ihtiyacı vardır diyebiliriz.

Bu açıdan bakacak olursak, ticari değer taşıyan hemen hemen tüm uygulamaların ve web sitelerinin veritabanına ihtiyacı vardır.

Şirketler kendilerine uygun veritabanı uygulamasını nasıl seçmeli? (Küçük ve orta ölçekli şirketler açık yazılımlar mı tercih etmeli? Veritabanı desteği veren firmalar var mı?)

Küçük ve orta şirketli firmalar genel de ücretsiz olan açık kaynak kodlu veritabanı yönetim sistemlerini tercih etmektedirler.

Daha büyük ölçekli firmalar ise, hem donanım, hem işletme, hem de personel maliyetlerini göze alabilerek, ücretli veritabanı yönetim sistemlerini tercih edebilirler.

Açık kaynak kodlu veritabanı yönetim sistemlerinin, genelde desteğini veren firma bulmak zordur. Örneğin, bugün piyasada MySql desteği veren firma bulmak hakikatten zordur.

Microsoft Sql Server, Oracle, IBM DB2 gibi veritabanı yönetim sistemlerinin ise, desteği üretici firmaların Türkiye acentelerinden edinilebilir.

Firmalar, Microsoft Sql Server’ın desteğini, Microsoft Türkiye’den, Oracle’ın desteğini Oracle Türkiye’den, IBM DB2’nun desteğini IBM Türkiye’den alabilirler.

Veritabanı uygulamaları arasında ne tür farklar var? (MySQL, Windows SQL gibi programlar nasıl karşılaştırılmalı? Eksik ve fazlaları nasıl değerlendirilmeli?)

Microsoft’un Microsoft Sql Server ve Sql Express uygulamaları, Oracle’ın Oracle uygulaması, IBM’in DB2 uygulaması, gene Microsoft’un Access uygulaması ve açık kod olan MySQL uygulaması veritabanı yönetim sistemlerine örnektir.

Bu veritabanı yönetim sistemleri içinde ücretsiz olanlar vardır; Microsoft’un Sql Express versiyonu gibi. Bu ücretsiz veritabanı yönetim sistemleri genelde bazı kısıtlara sahiptirler. Örneğin, sunucunun hafızasının ve işlemcisinin sadece belli bir miktarını kullanabilirler, belli boyuta kadar veri saklayabilirler, gibi.

Ölçeklendirecek olursak, “büyük” ölçekli diyebileceğimiz Microsoft Sql Server, Oracle, IBM DB2 gibi veritabanı yönetim sistemleri, hem satın alma anlamında, hem de çalışmak için ihtiyaç duyduğu donanım ve yönetim için için gerekli personel maliyeti anlamında pahalıdır.

Bunların yanında MySql gibi, açık kaynaklı yazılımlar sayesinde, düşük maliyetli veritabanı yönetim sistemi sahibi olunabilir.

Neden olduğunu anlayamadığım bir sebepten dolayı Microsoft tablodaki bir kolonun ismini değiştirmek için bir tool sunmuyor.

Peki bir kolonun ismini değiştirmek istersek ne yapmalıyız?

MSDN‘de yer alan şu makalede anlatıldığı gibi, sp_rename stored procedure‘ünü kullanabiliriz.

Örneğin;

EXEC sp_rename 'TabloAdi.KolonAdi', 'YeniKolonAdi', 'COLUMN';

Üçüncü parametrenin alabileceği diğer değerler;

COLUMN : Bir kolonun yeniden isimlendirileceğini belirtir
DATABASE : Veritabanının yeniden isimlendirileceği durumda kullanılır
INDEX : Kullanıcının oluşturduğu index yeniden isimlendirileceğinde kullanılır

C# tarafında rastgele sayıya ihtiyacımız olduğunda Random class’ından yeni bir nesne üretip kullanabiliyoruz. Eğer rastgele sayıya Sql tarafında ihtiyacımız olursa yapmamız gereken, RAND() fonksiyonunu kullanmaktır.

RAND() fonksiyonu ile ilgili detaylı bilgiye MSDN‘de yeralan şu makaleden ulaşabilirsiniz.

RAND([seed]) fonksiyonu parametre olarak seed değeri alabilir ve geriye float tipinde sonuç döner.

seed parametresi int, smallint veya tinyint tipinde olabilir.

RAND() fonksiyonu aynı seed değeri ile her çalıştırıldığında aynı “rastgele” sayıyı döndürür. Eğer seed verilmezse, Sql Server rastgele bir seed değeri atayarak, “rastgele” sayı oluşturur.

Eğer RAND() fonksiyonundan float tipinde noktalı sayı değil, int tipinde tamsayı dönmesini istiyorsak, basit bir convert işlemine tabi tutabiliriz;

SELECT CONVERT(INT, RAND() * 100)

İstatistikler, verinin veritabanı nesnelerinde (tablo, index, vs) nasıl dağıldığı bilgisini içerdiği için, veritabanında en önemli nesnelerden biridir.

Veritabanı Yöneticilerinin (Database Administrators) eskimiş istatistiklerden şikayetlerini çok duydum. Yapılması gereken istatistikleri güncellemektir.

Eğer bir tablodaki indexlere ait istatistiklerin son güncelleme zamanını öğrenmek istiyorsanız, aşağıdaki sql sorgusunu çalıştırabilirsiniz;

SELECT
	NAME AS INDEX_ADI,
	STATS_DATE(OBJECT_ID, INDEX_ID) AS SON_GUNCELLEME
FROM
	SYS.INDEXES
WHERE
	OBJECT_ID = OBJECT_ID('HumanResources.Department')

Eğer tüm indexler için aynı bilgiyi almak istiyorsanız, WHERE filtresini kaldırmanız yeterli.

Benim test bilgisayarımda aldığım sonuç;

PK_Department_DepartmenID 2010-01-20 15:30:17:200
AK_Department_Name 2010-01-20 15:30:26:053

Eğer istatistiklerinizin zamanı geçmiş olduğunu düşünüyorsanız ve güncellemek istiyorsanız, aşağıdaki sql komutunu çalıştırabilirsiniz;

UPDATE STATISTICS HumanResources.Department

Bu komutu çalıştırdığınızda ilgili tablonun istatistik verilerinin güncellendiğini göreceksiniz. Eğer komutu WITH FULLSCAN komutu ile birlikte çalıştırırsanız, tablodaki tüm kayıtlar baştan taranacak ve istatistik baştan oluşturulacaktır.

UPDATE STATISTICS HumanResources.Department
WITH FULLSCAN

Dikkat : İstatistikleri güncellemek için yukarıdaki komutları, sadece istatistiklerinizin gerçekten eskimiş olduğuna inandığınızda, sorgularınız için oluşturulan query plan’ların hatalı olduğunu gördüğünüzde ve sql server’ın yoğun olmadığı zamanlarda çalıştırmanızı tavsiye ederim.

Kendi test bilgisayarımda, istatistik güncelleme komutunu çalıştırdıktan sonra, aşağıdaki sonuçları elde ettim;

PK_Department_DepartmenID 2010-01-30 21:10:06:560
AK_Department_Name 2010-01-30 21:10:06:594

Gördüğünüz gibi, ilgili tablodaki indexlerin istatistikleri güncellendi.

Sql Server‘da her sorgu çalıştırdığımızda, sorgu sonucu, etkilenen satır sayısı ile birlikte, sorguyu çalıştıran uygulamaya geri gönderilir.

Bazı durumlarda bu bilgi işimize yarasa bile, genellikle kullanmayız.

Sql Server‘ın bu bilgiyi hesaplamasını ve uygulamaya geri göndermesini engelleyerek, çok ufakta olsa kazanç sağlayabiliriz.

Yapmamız gereken, sorgudan önce aşağıdaki komutu çalıştırmak olacaktır;

SET NOCOUNT ON

Sql Server sadece ilgili sorgu için, etkilenen satır sayısını hesaplama işlemini yapmayacaktır.

Bilgisayar Programcılığı dünyasında genellikle bir işi yapmanın birden fazla yolu vardır. Özellikle veritabanı tarafında bu yollar daha da fazla olabilir. Uygulama Geliştiriciler olarak yeni bir kod parçası yazacağımız zaman, genellikle bu yollardan en çok kullandığımız bir tanesini seçeriz ve onu kullanırız.

Ne var ki, seçtiğimiz yol herzaman en doğru yol olmayabilir. Sql‘de join‘li sorgulama yaparken, filtreleme için kullandığımız yöntem de bunlardan biri olabilir.

Bu yazımda join‘li sorgularda filtreleme için seçebileceğimiz iki yolu karşılaştıracağım. Karşılaştırma yapmak için kendi bilgisayarımda kurulu olan Sql Server 2008 üzerinde AdventureWorkd2008 R2 veritabanını kullandım.

Eğer birden fazla tablonun join‘ler ile ilişkilendirildiği bir sorgu yazıyorsak, sonuç kümesini filtreleme için kullanabileceğimiz iki yöntem vardır;

WHERE Filtreleme

Sonuç kümesinde olmasını/olmamasını istediğimiz kayıtları sorgunun WHERE cümlesinde tanımlarız. Şablon;

SELECT
	[TABLE1.ALANADLARI],
	[TABLE2.ALANADLARI]
FROM
	[TABLE1]
	JOIN [TABLE2] ON [TABLE1.ALAN1] = [TABLE2.ALAN1]
WHERE
	[TABLE1.ALAN2] = DEGER AND
	[TABLE2.ALAN2] = DEGER

JOIN Filtreleme

Sonuç kümesinde olmasını/olmamasını istediğimiz kayıtları sorgunun JOIN cümlelerinde tanımlarız. Şablon;

SELECT
	[TABLE1.ALANADLARI],
	[TABLE2.ALANADLARI]
FROM
	[TABLE1]
	JOIN [TABLE2] ON [TABLE1.ALAN1] = [TABLE2.ALAN1] AND [TABLE1.ALAN2] = DEGER AND [TABLE2.ALAN2] = DEGER

AdventureWorks2008 R2 veritabanında aşağıdaki iki sorguyu çalıştırdıktan sonra, performans analizini yapalım;

WHERE Filtreleme

SELECT
	*
FROM
	Sales.SalesOrderHeader AS SOH WITH (NOLOCK)
	JOIN Sales.SalesOrderDetail AS SOD WITH (NOLOCK) ON SOH.SalesOrderID = SOD.SalesOrderID
	JOIN Sales.SalesOrderHeaderSalesReason AS SOHSR WITH (NOLOCK) ON SOHSR.SalesOrderID = SOH.SalesOrderID
	JOIN Sales.SalesReason AS SR WITH (NOLOCK) ON SOHSR.SalesReasonID = SR.SalesReasonID
WHERE
	SOH.CustomerID > 15000 AND
	SOD.LineTotal > 2000 AND
	SR.SalesReasonID > 5

JOIN Filtreleme

SELECT
	*
FROM
	Sales.SalesOrderHeader AS SOH WITH (NOLOCK)
	JOIN Sales.SalesOrderDetail AS SOD WITH (NOLOCK) ON SOH.SalesOrderID = SOD.SalesOrderID AND SOH.CustomerID > 15000 AND SOD.LineTotal > 2000
	JOIN Sales.SalesOrderHeaderSalesReason AS SOHSR WITH (NOLOCK) ON SOHSR.SalesOrderID = SOH.SalesOrderID
	JOIN Sales.SalesReason AS SR WITH (NOLOCK) ON SOHSR.SalesReasonID = SR.SalesReasonID AND SR.SalesReasonID > 5

Her iki sorgu da çalıştıktan sonra 1321 satır geri döndürdü. Performans incelemesini üç alanda yapacağız;

• CPU Kullanımı
• Disk Kullanımı
• Hafıza Kullanımı

Bu üç performans kriteri için verileri  SYS.SYSPROCESSES DMV‘sinden alabiliriz. SYS.SYSPROCESSES için detaylı bilgiye MSDN‘deki şu makaleden ulaşabilirsiniz. Aşağıda ilgili üç performans kriteri için verileri elde edebileceğimiz sorgu var;

SELECT
	DB_NAME(SP.DBID) AS VERITABANI,
	EST.TEXT AS SORGU,
	CPU,
	PHYSICAL_IO AS DISK_OKUMA,
	MEMUSAGE AS HAFIZA_KULLANIM
FROM
	SYS.SYSPROCESSES AS SP
	CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) AS EST

Bu sorguyu çalıştırdıktan sonra benim test bilgisayarımda şu verileri elde ettim;

• VERITABANI : AdventureWorks2008R2
• CPU : 1965
• DISK_OKUMA : 211
• HAFIZA_KULLANIM : 2

• VERITABANI : AdventureWorks2008R2
• CPU : 156
• DISK_OKUMA : 0
• HAFIZA_KULLANIM : 2

Gördüğünüz gibi kaynak kullanımı açısından (özellikle Disk Okuma ve CPU Kullanımı açısından) JOIN Filtreleme, WHERE Filtrelemeden daha avantajlı.

JOIN Filtreleme tabloları eşlerken filtrelenmiş verileri kullanıyor, WHERE Filtreleme ise, önce tabloları eşleştiriyor sonra filtreliyor. Özellikle çok kayıt bulunan/bulunacak olan tablolarınıza sorgu yazarken, JOIN Filtreleme’yi kullanmanızı tavsiye ederim.