SQL Server ASP.NET C# Kitabı

Daha önce yazdığımız ipuçlarına ek olarak bu yazıda aşağıdaki ipuçlarını vereceğiz.

• SQL Server servisi her çalıştığında bazı komutları nasıl çalıştırabiliriz.

• SQL Server Enterprise Management konsolde açılmıyor.
• “IS NULL” ve “= NULL” arasındaki fark nedir.
• COALESCE(), ISNULL() ve NULLIF() fonksiyonlar arasındaki fark nedir.
• NEWSEQUENTIALID() ve NEWID() arasındaki fark nedir.
• TRUNCATE ile DELETE arasındaki fark nedir.
• T-SQL aracılığıyla uzaktan eriÅŸim nasıl açılır/kapatılır.
• Veritabanı dosyalarının büyüklüğünü her gün mail ile nasıl alabilirim.
• Ä°ki saat arasındaki farkı saat olarak yazdırmak

1 – Sql Server her çalıştığında belli bir procedure çalıştırmak

Bir SQL Server yöneticisi olarak her SQL Server çalıştığında standart bazı stored procedure veya jobların devreye girip çalışmasına ihtiyaç duymuşumdur. Örneğin SQL Server başladığında otomatik olarak SQL Agent veya SQL Trace araçlarını başlatmak bir gereksinim olabilir. Bir Windows servisi olan MSSQLSERVER servisi başladığında belli komutların da çalışmasın için sp_procoption yordamını sunar.

sp_procoption [ @ProcName = ] 'procedure'
    , [ @OptionName = ] 'option'
    , [ @OptionValue = ] 'value' 

İlk parametre, SQL Server başladığı zaman otomatik olarak çalıştırılacak yordamı belirtir. İkinci parametre startup değerini alıp ilk parametredeki yordamın otomatik çalıştırılacağını belirtir. Üçüncü parametre ikinci parametredeki koşulun değerini belirtip on (true veya on) veya off (false veya off) değerlerini alır.

Her SQL Server açıldığında belirlediğimiz bir tabloya açılış saatini yazdıralım. Bu basit log tablosunun şeması şu şekilde olsun.

USE Master
GO
CREATE TABLE BaslangicLog
(
	LogId INT IDENTITY(1,1) NOT NULL,
	Tarih DATETIME NOT NULL
)

Bu tabloya kayıt atacak olan bir procedure oluşturalım.

USE Master
GO
CREATE PROCEDURE LogTabloYaz
AS
SET NOCOUNT ON
INSERT INTO BaslangicLog  VALUES (GETDATE())

LogTabloYaz yordamını başlangıç yordamı olarak düzenlemek için aşağıdaki gibi sp_procoption yordamına parametre olarak geçiyoruz.

USE Master
GO
sp_procoption @ProcName = 'LogTabloYaz',
              @OptionName = 'startup',
              @OptionValue = 'on'

Bu işlemden sonra SQL Server servisinin yeniden başlattığımızda BaslangicLog tablosuna bir log satırının kaydedildiğini görürüz.

Buna benzer olarak bazı kodların SQL Server Agent’in baÅŸlangıcına baÄŸlamak istersek bunun için bir Job tanımlayıp o Job için oluÅŸturulacak olan schedule’da bir zaman tanımlaması yapmak yerine “Start Automatically when SQL Server Agent Starts” seçeneÄŸi iÅŸaretlenir.

Servisin başlangıcında çalışan yordamların ExecIsStartup özelliği true olarak set edilmiştir. Aşağıdaki script bu yordamları listeler.

SELECT * FROM sysobjects
WHERE OBJECTPROPERTY (id,'ExecIsStartup') = 1

2 – SQL Server Enterprise Management konsolde açılmıyor

Bazı aksi durumlarda Enterprise Management’i açmaya çalışırken aÅŸağıdaki hatayla karşılaşırız.
MMC cannot open the file C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC.

Bu hata mesajı Enterprise Manager’in Microsoft Yönetim Konsolu (Microsoft Management Console-MMC) üzerinde açılmasını saÄŸlayan MSC dosyasının bozulmasından kaynaklanmaktadır. Bunu için aÅŸağıdaki iÅŸlemler yapılır.

1. “Start » Run” alanına girip MMC komutunu çalıştıralım
2. Console aracında File menüsünden – “Add/Remove Snap-in…” menüsünü tıklayalım
3. Açılan pencerede Add… düğmesini tıklayalım.
4. Listeden “Microsoft SQL Enterprise Manager” aracını seçip “Add » Close” düğmelerini tıklayalım.
5. Ok düğmesini tıklayıp konsole geri dönelim.
6. File menüsünden “Save As…” menüsü aracılığıyla dosyayı orijinal MMC dosyasının üstüne yazalım.
7. MSC dosyasını “C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC” olarak kaydedebileceÄŸimiz gibi farklı bir isimde de kayıt edebiliriz.

3 – “IS NULL” ve “= NULL” arasındaki fark nedir

SQL Server üzerinde null yani geçerli olmayan veri (boş) türündeki kayıtlar üzerinde filtreleme yapılırken WHERE bölümünde nasıl bir ifadenin yazılacağı ANSI_NULLS özelliğine bağlıdır. ANSI_NULLS özelliği, Eşit-Equals (=) veya Eşit Değil-Not Equal to (<>) kıyaslamalarında null kayıtlarına nasıl davranılacağını belirtir.

SET ANSI_NULLS {ON | OFF}

ANSI_NULLS özelliği ON olarak düzenlendiğinde null kayıtlarla yapılan karşılaştırmalar false sonucunu döndürür yani arama listesine dahil edilmez. Bu durumda aşağıdaki sorgu AdSoyad kolonunda null kayıtlar bulunsa bile herhangi bir kayıt döndürmez.

SELECT * FROM Musteri WHERE AdSoyad=NULL

ANSI_NULLS özelliğini OFF olarak düzenlendiğinde null kayıtlar diğer kayıtlarla karşılaştırılabilir duruma getirilir. Bu durumda üstteki sorguya null olan kayıtlar dahil edilmiş olur.

SQL Server varsayılan olarak “SET ANSI_NULLS ON” modundadır. Bu deÄŸer aynı zamanda SQL-92′nın standartıdır. Bu standart dahilinde null kayıtlar üzerinde filtreleme yapmak için “IS NULL” veya “IS NOT NULL” koÅŸul deyimi kullanılır.

SELECT * FROM Musteri WHERE AdSoyad IS NULL

Yeri gelmişken aşağıdaki maddeleri hatırlatmamızda fayda var;

SQL Server’de aggregate fonksiyonlarında (Average, Count, Maximum, Minimum, Sum) null kayıtlar dikkate alınmaz. AÅŸağıdaki tabloyu düşünelim.

c1	c2
10	10
20	20
30	30
NULL	40

Bu tablodaki c1 ve c2 kolonları üzerinde aşağıdaki sorguları çalıştırdığımızda açıklama olarak verilmiş olan sonuçlar döner.

SELECT COUNT(*) FROM Table1 --4
SELECT COUNT(c2) FROM Table1 --4
SELECT COUNT(c1) FROM Table1 --3
SELECT COUNT(DISTINCT c1) FROM Table1 --3
SELECT AVG(c1) FROM Table1 --20
SELECT AVG(c2) FROM Table1 --25
SELECT MIN(c1) FROM Table1 --10
SELECT MAX(c1) FROM Table1 --30

GROUP BY ile yapılan gruplamalarda null kayıtlar gözönünde bulundurulur.

SELECT c1,COUNT(*) [Satır Sayısı],
COUNT(c1) [c1'in Dolu Olduğu Satır Sayısı] FROM Table1
GROUP BY c1

Bu sorgunun sonucu aşağıdaki gibi olur.

c1	Satır Sayısı	c1′in Dolu OlduÄŸu Satır Sayısı
NULL	1	0
10	1	1
20	1	1
30	1	1

SQL Server’de kayıtlar ORDER BY ile sıralanırken varsayılan olarak null kayıtlar ilk sırada gelir. Malesef T-SQL’de PL/SQL’de bulunan ve ORDER BY ile birlikte NULLS FIRST veya NULLS LAST ifadeleri bulunmamaktadır. null kayıtların sıralama yönünü deÄŸiÅŸtirmek için programcının çözüm sunması gerekir. Bunun için aÅŸağıdaki gibi UNION ifadesi veya ISNULL(), COALESCE() fonksiyonları kullanılabilir.

SELECT * FROM Table1 WHERE c1 IS NOT NULL
UNION ALL
SELECT * FROM Table1 WHERE c1 IS NULL

Bu ifade c1 ismindeki sütununda null kayıtları listenin sonuna yazdırır. Bu kolon sayısal bir kolon olduğu için null kayıtları ISNULL() aracılığıyla en yüksek rakam olacak şekilde düzenlersek yine null kayıtlar listenin sonunda çıkmış olur.

SELECT * FROM Table1 ORDER BY ISNULL(c1,99999)

4 – ISNULL, COALESCE ve NULLIF arasındaki fark nedir

ISNULL() fonksiyonu dışarıdan iki parametre alıp ilk parametre geçerli olmayan veri BoÅŸ (null) içeriyorsa ikici parametredeki ifadeyi deÄŸilse kendisini döndürür. AÅŸağıdaki ifade ilk parametre null olduÄŸu için ikinci parametredeki ifadeyi yani “Ä°stanbul” deÄŸerini döndürür.

SELECT ISNULL(NULL,'Istanbul')

COALESCE() yordamı birçok parametre alıp parametreler arasında ilk null olmayan kaydı döndürür.

COALESCE ( expression [ ,...n ] )

Eğer tüm değerler null ise COALESCE fonksiyonu geriye null döndürür. Aşağıdaki ifade geriye 2008 değerini döndürür.

SELECT COALESCE(null,null,2008,'SQL Server')

null kayıtlar için kullanılan diğer fonksiyon ise NULLIF() fonksiyonudur. İki parametre alan bu fonksiyon iki parametre birbirine eşit olduğu durumda geriye null diğer durumda birinci parametreyi döndürür.

NULLIF ( expression , expression )

NULLIF(c1, c2) ifadesi aşağıdaki gibi çalışır.

CASE WHEN c1 = c2 THEN NULL ELSE c1 END

Bu fonksiyon genellikle sıfıra bölünme hatasının oluşma durumlarında tercih edilir. Aşağıdaki ifadede (c1+c2) toplamı sıfır olduğu zaman sorgu hataya neden olacaktır.

SELECT c1 / (c1 + c2) FROM  Table1

Bunu engellemek için CASE ve WHERE koşulları kullanılır.

SELECT c1 / (c1 + c2) FROM  Table1
WHERE c1 + c2 <> 0

Özellikler büyük sorgularda WHERE koşulunun bulunması bir performans sorunu olabilir. Sifira bölünme hatasından kurtulmak için NULLIF kullanılabilir. Sorguyu aşağıdaki gibi (c1+c2) işlemi sıfır olduğu zaman null döndürecek şekilde değiştirirsek çalışma anında hatadan kurtulmuş oluruz.

SELECT c1 / NULLIF(c1+c2,0) FROM Table1

Bu sorgu sonucunda c1 ve c2′nin toplamının sıfır olduÄŸu satırlarda sonuç kolonu null olarak döner.

5 – NEWSEQUENTIALID() ve NEWID() arasındaki fark nedir

SQL Server’da uniqueidentifier türünde tekil deÄŸer üretmek için NEWID() fonksiyonu kullanılır. BilindiÄŸi gibi uniqueidentifier türü (GUID), bilgisayarın tarih, saat ve diÄŸer özellikleri referans alınarak eÅŸsiz olarak oluÅŸturulan 43F5BE27-A029-4FEA-B13E-74C47F465E10 gibi 32 basamaklı hexadecimal bir sayıdır. SQL Server uniqueidentifier türünde deÄŸer üretmek için 2005 versiyonuyla birlikte NEWSEQUENTIALID() fonksiyonu da sunulmaktadır. NEWID() ve NEWSEQUENTIALID() en önemli fark, NEWID() fonksiyonu rastgele deÄŸer üretirken NEWSEQUENTIALID() sıralı deÄŸer üretir. DiÄŸer fark ise NEWID() her yerde kullanılabilirken NEWSEQUENTIALID() tabloda uniqueidentifier türündeki kolonlarda sadece DEFAULT constraint olarak kullanılabilir.

CREATE TABLE Tablo
    (Kolon uniqueidentifier DEFAULT NEWSEQUENTIALID()) 

NEWSEQUENTIALID() fonksiyonu da sıralı değer ürettiği için güvenli uygulamalarda tercih edilmemelidir. Bununla birlikte insert, delete, update gibi işlemlerde NEWSEQUENTIALID() ile oluşturulmuş kolonun üzerindeki index NEWID() ile oluşturulmuş kolon üzerindeki indexe göre daha performanslı olacaktır. Çünkü bu işlemlerde sözkonusu kolondaki değerler her defasında yeniden sıralanacaktır. NEWSEQUENTIALID() ile oluşturulmuş kolon zaten sıralı olduğu için pek zaman kaybettirmeyecektir.

6 – TRUNCATE ile DELETE arasındaki fark nedir

Tablodaki kayıtları silmek için kullanılan bu iki komut arasındaki farklar şunlardır;

• TRUNCATE, tablodaki tüm kayıtları siler. DELETE ise WHERE içerisinde verilmiÅŸ koÅŸula uyan satırları siler. WHERE koÅŸulu tanımlanmamışsa tüm kayıtları siler. Bu yüzden WHERE koÅŸulu TRUNCATE ile birlikte kullanılamaz.
• DELETE iÅŸlemi her satırı fiziksel olarak siler ve silme iÅŸlemini log dosyasında saklar. TRUNCATE ise kayıtları fiziksel olarak silmek yerine bu kayıtların bulunduÄŸu data page’leri silinmiÅŸ olarak iÅŸaretler ve extentleri yeni kullanılabilmesi için boÅŸ olarak iÅŸaretler. Bu yüzden DELETE iÅŸlemi özellikle büyük tablolarda daha uzun sürer ve o log dosyasının büyümesine neden olur. Bununla birlikte TRUNCATE iÅŸleminin hiç log tutmadığını söylememiz doÄŸru olmaz sadece DELETE iÅŸleminden farklı olarak her satır için data page için basit loglama yapar.
• TRUNCATE iÅŸleminden sonra log tutulmadığı için ROLLBACK yapılamaz. DELETE iÅŸleminde ise ROLLBACK ihtimali mevcuttur. Bu yüzden TRUNCATE komutunun bilinçli kullanılması gerekir.
• Ãœzerinde “Foreign Key Constraint” tanımlı olan tabloya TRUNCATE iÅŸlemi uygulanamaz. Öncelikle bu tür constraint’lerin silinmesi gerekir.
• BilindiÄŸi gibi normal ÅŸartlar altında IDENTITY tipindeki bir kolondaki tüm satırlar DELETE ile silinse bile identity deÄŸeri en son kaldığı yerden devam eder yani sıfırlanmaz. Identity alanını ilk deÄŸerinden baÅŸlatmak için tablonun TRUNCATE ile silinmesi gerekir.
• DELETE iÅŸleminde varsa tablo üzerindeki DELETE TRIGGER çaÄŸrılır fakat TRUNCATE iÅŸleminde doÄŸrudan datanın kendisi üzerinde bir deÄŸiÅŸiklik olmadığı için silme trigger’i çaÄŸrılmaz.
• Transactional replication veya merge replication modelinde yayınlanan tablolar üzerinde TRUNCATE iÅŸlemi uygulanamaz.
• Tablo üzerinde TRUNCATE iÅŸlemini uygulamak için ALTER yetkilerine, DELETE iÅŸlemi içinse DELETE yetkilerine sahip olmak gerekir.
• TRUNCATE komutu bir DDL operasyonu, DELETE ise bir DML operasyonu olarak tanımlanır.

BildiÄŸin gibi verileri MDF dosyası içerisindeki extent’ler içerisinde data page’ler içerisinde bulunur. Her data page içerisinde dolu ve boÅŸ alanı ayıran bir sınır mevcuttur. Bu sınıra high water mark (hwm) denilir. Tablo okumaları bu sınıra kadar olur. Kayıt eklendikçe bu sınır biraz daha geniÅŸlenir. TRUNCATE komutunun yaptığı iÅŸ hwm iÅŸaretçisini sıfırlamasıdır. DELETE iÅŸlemi bu iÅŸaretçiyi geriye doÄŸru çekmez.

7 – T-SQL aracılığıyla uzaktan eriÅŸim nasıl açılır/kapatılır

DoÄŸrudan T-SQL kodlarını kullanarak SQL Server 2005′i uzaktan eriÅŸime açıp kapatamıyoruz. Bunu ancak “SQL Server 2005 Surface Area Configuration” aracından yapabiliriz. Fakat bu aracın yazdığı registry alanında deÄŸiÅŸiklik yapacak bir T-SQL yordamı yazılırsa o zaman doÄŸrudan SQL scriptleriyle bu iÅŸlem yapılabilir. Sunucu üzerinde kurulu SQL Server’in uzaktan eriÅŸilebilirlik durumu aÅŸağıdaki registry anahtarında tutulur.

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Adı>\MSSQLServer\SuperSocketNetLib

master.dbo.xp_regread yordamını kullanarak bu alan üzerinde Np (PipeName) ve Tcp (TcpPort) anahtarları düzenlenebilir.

8 – Veritabanı dosyalarının büyüklüğünü her gün mail ile nasıl alabilirim.

Bunun için SQL Server Agent üzerinde bir Job hazırlanır. Job, veritabanlarına ait data ve log dosyalarının büyüklüğünü okuyup SQL Mail veya Database Mail aracılığıyla ilgili kullanıcıya gönderir. SQL Server üzerinde tanımlı veritabanlarının dosyaları hakkındaki bilgileri SQL Server 2000′de sysfiles, SQL 2005′te sys.database_files tablosunda tutulmaktadır. AÅŸağıdaki sorgu mevcut veritabanının dosyalarının büyüklüğünü listeler.

SELECT RTRIM(name) [Dosya Adı]
,CAST(((SIZE * 8.00)/1024.00) AS DECIMAL(18,2)) [Büyüklüğü (MB)]
FROM sys.database_files

Bu sorguyu her veritabanı için çalıştırmak için sp_msforeachdb yordamı kullanılabilir. Bu listeyi ilgili kişilere mail olarak atmak için geçici bir tablo oluşturulur ve her veritabanının dosyaları geçici tabloya aktarılır ardından geçici tablo mail olarak gönderilir. Aşağıdaki kodlarda bunun örneğini bulabilirsiniz.

--Geçici tabloyu oluşturalım
CREATE TABLE #T (Name char(50),
	DatabaseSize numeric(9,2))

--Her veritabanı için sorguyu çalıştırıp sonucu geçici tabloya aktaralım
EXECUTE sp_msforeachdb 'INSERT INTO #T SELECT RTRIM(name)
,CAST(((SIZE * 8.00)/1024.00) AS DECIMAL(18,2))
FROM [?].sys.database_files'

--Mail olarak atacağımız HTML tablosunu oluşturalım
DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'
Veritabanı Dosya Büyüklükleri
' +
    N'
' +
    N'


' +
    CAST ( ( SELECT td = RTRIM(Name),       '',
                    td = DatabaseSize
              FROM #T ORDER BY DatabaseSize DESC
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'

Dosya Adı
Dosya Büyüklüğü (MB)


' ;

--Mailin başlığını ve diğer özelliklerini set edelim
DECLARE @baslik varchar(100)
SET @baslik='Veritabanı Dosyalarının Büyüklüğü - '+ CONVERT(CHAR(10),GETDATE(),104)

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'AhmetKaymazProfil',
    @recipients = 'ahmet.kaymaz@sirket.com.tr',
    @subject = @baslik,
	@body = @tableHTML,
	@body_format = 'HTML';

SQL Server’de mail gönderme iÅŸlemlerinin hangi yöntemlerle yapılabileceÄŸini bu linkte daha detaylı bulabilirsiniz.

9 – Ä°ki saat arasındaki farkı saat olarak yazdırmak

DECLARE @Saat1 char(5), @Saat2 char(5)

SELECT @Saat1='09.57', @Saat2='12.35'

SELECT convert(varchar(5),
	dateadd(day,0,replace(@Saat2,'.',':'))-
	dateadd(day,0,replace(@Saat1,'.',':')),108)

02:38