SQL Server Performansı için faydalı DMV(Dynamic Management View) ler

Performans sıkıntısı oluşan sorguları bulmak ve SQL Server'ın performansını arttırmak  için DMV lerden faydanılabilir.

sys.dm_exec_requests, sys.dm_exec_sessions : Her iki view de server'da şu an çalışan istekleri getirir. Anlık olarak, uzun süren ve düşük performans gösteren sorguları bulmak için kullanılabilir.

sys.dm_exec_query_stats : Çalışan sorguların cache planlarını getirir.

sys.dm_exec_sql_text En kötü performanslı sorguyu tespit ettiğinizde, bu view i kullanarak sorgunun tam metnine ulaşabilirsiniz. DBCC INPUTBUFFER a benzer ve  query handle parametresi alır. Query handle a  sys.dm_exec_requests ve sys.dm_exec_query_stats view lerinde ulaşılabilir.

sys.dm_os_wait_stats Server bazında bekleme istatistiklerini getirir ve dar boğazları tepit etmek için kullanılabilir. 

sys.dm_db_index_usage_stats  Her bir indeksin kullanım istatistiklerini gösterir. Kullanılmayan ve az kullanılan indeksleri bulmak için kullanılabilir. Kullanılmayan indeklerin kaldırılması, veri güncelleme performansını arttırır, disk kullanımını azaltır.

DMV ler, SQL server'ın son açılışından itibaren olan istatistikleri gösterir.

sys.dm_db_missing_index_details  Yeni indeks ihtiyacını tespit etmek için kullanılır.

Partitioning Nedir?

SQL Server'da partitioning yapısını, bir kitapevinde, birbirleriyle ilgili kitapları aynı raflara, ilgili olmayan kitapları farklı raflara koymaya benzetebiliriz. Romanlar bir rafa, Bilgisayar kitapları başka bir rafa gibi.

Çok büyük bir tablomuz varsa, tablolarımızı bazı özelliklerine farklı partitionlara bölebilir ve performansımızı arttırabilir ve tablonun yönetemini kolaylaştırabiliriz. Örneğin bir satış tablosunu düşünün. Üzerinde binlerce kayıt olabilir. Ancak en çok bu yılın kayıtlarına bakar, diğer kayıtları daha az sorgularız. Sorgularımızda sql server'ın bir tablonun tüm kayıtlarını aramak yerine sadece daha az sayfayı taramasını, tabloyu bölerek sağlayabiliriz.  Hatta eski kayıtlar ve yeni kayıtlar için farklı zamanlarda backup alabiliriz. Satırlara göre farklı bölümlere ayırmaya horizontal partitioning denir.

Eğer bir tablo üzerinde çok sık kullandığımız kolonların yanında çok ender sorguladığımız kolonlar varsa, tabloyu kolonlara göre de bölümlere ayırabiliriz. Buna da vertical partioning denir. Bir tabloyu satırlardan ve sütünlardan oluşan bir yapı olarak düşünürsek, bu durumda tabloyu dikey olarak bölmüş oluruz. Vertical partioning ismi de buradan gelir.

SQL Server da Index Türleri

Index, SQL Server tablolarındaki verilere kolayca ulaşmamızı sağlayan yapılardır. Bir kitabın sonundaki indekse benzetilebilir. Hangi verinin nerede bulunduğu indeksler üzerinde tutulur.

Temel olarak SQL Server'da iki tür indeks vardır.

Clustered Index ve Nonclustered Indeks. Clustered Index, tablonun aynısıdır ve tablodaki tüm alanlar yer alır, sadece veriler, clustered indeks olarak tanımlanmış alanların sırasında tutulur, ancak nonclustered index'de sadece indeksteki veriler ve o verilerin nerede bulunduğuna dair işaretler tutulur.

Üzerinde clustered indeks tanımlı olmayan tabloya heap tablo denir. Ve heap tablolarda veriler sıralı değildir. Üzerinde clustered indeks tanımlı olan bir tablonun iki versiyonu olur, heap orijinal tablodur ve bu tablonun sıralı hali de clustered indekste durur.  Özel birşey söylenmezse, primary key yaratılırken, primary key alanına göre clustered indeks yaratılır. Bir tablo için sadece 1 tane clustered indeks yaratılabilir.

Bu genel ayrımdan sonra içeriklerine göre indeksler aşağıdaki şekilde gruplandırılabilir.

Simple
Tek kolondan oluşan bir indekstir. Örneğin sadece ad kolonundan oluşan veya ad ve soyad birleşiminden oluşur.

Compound
2 veya daha fazla kolondan oluşan indekstir. 

Unique
Unique indeks olarak tanımlı alanlar için, aynı verilerden sadece bir tane girilmesine izin veren indeks türüdür. 

LOCK Türleri

Veritabanında satır, sayfa veya tablo düzeyinde locklar oluşabilir. Bu locklar iki gruba ayrılırlar.
Basic Locks :

• S:Shared
• U:Update
• X:Exclusive

Extended Locks:

• I : Intent
• Sch: Schema
• BU: Bulk Update
• KR : Key Range

Shared Lock : Bir tablo üzerinde select sorgusu çalışırken oluşur. Bu lock, okuma yapacak diğer sorguların çalışmasına izin verir. Ancak shared lock çözülene kadar hiç bir transaction okunan kayıtları güncelleyemez.

Update lock : Bir tablo üzerinde update  işleri yapılırken oluşur. SQL Server, güncellenen verileri okumaya kalktığında kullanılır. Güncelleme sırasında, update lock, exclusive lock a dönüşür.  Exclusive lock, birden fazla transaction ın aynı satırları güncellemeye çalışıp, deadlock oluşturmasını engeller.

Exclusive lock :   Diğer işlemlerin kilitlenmiş kaynaklara ulaşmasını engeller. Okuma işlemi sırasında, tabloda NOLOCK kullanıldıysa veya Isolation Level,  READ UNCOMMITTED  olarak ayarlandıysa, exclusive lock konulmuş olsa da veri okunur. Ancak diğer işlemler, exclusive lock kalkana kadar yapılamaz.

Intent Lock : 6 farklı türde intent lock vardır. 
Intent shared (IS) lock istekleri veya shared lock ları korur. Bir kayıtda bir shared lock oluşmuş ise, ilgili kaydın bulunduğu page üzerinde intent shared lock oluşur.
Intent exclusive (IX) lock Intent shared lock ın bir üstü seviyesidir. Bir kayıtda exclusive lock varsa, ilgili kaydın bulunduğu page üzerinde Intent Exclusive Lock konulur.
Shared with intent exclusive (SIX) lock kilitleme hiyerarşisinde daha alt seviyede duran shared lock ları korur. Bir tabloda shared with intent exclusive lock oluşursa, değişiklik yapılan page üzerinde intent exclusive lock da yer alır.
Intent update (IU) lock  güncellenen tablonun bulunduğu pagelerdeki, shared ve istenilelen diğer lockları korur.
Shared intent update lock,  shared ve intent update locklarının birleşimininden oluşur. Bir transaction bir tablodan okuma yaptığında shared lock oluşur, daha sonra aynı transaction bir update işlemi yaparsa o zaman shared intent update lock a dönüşür. Bir tabloda select cümlesi çalıştırılırken, PAGLOCK hint i kullanılarak çekildiğinde de oluşan lock türüdür.

Birbirini kapsayan birden fazla lock olmasının nedeni, SQL Server'ın aynı anda bir tablo üzerinde oluşan birçok lock yerine, tek bir lock ile çalışmasının daha verimli olmasındandır.

TRANSACTION Türleri

SQL Server 2 tür transaction yapısını destekler.
Local Transaction : Aynı server üzerinde kalan transactionlardır. BEGIN TRANSACTION veya daha kısa yazımıyla BEGIN TRAN ifadesi ile başlatırlar.

Distributed Transaction : BEGIN DISTRIBUTED TRANSACTION veya BEGIN DISTRIBUTED TRAN ifadesi ile başlatırlar. Bu tür transaction türleri, eğer bir transaction içerisinde server dışına çıkılacak ise kullanılır. Bu daha çok bir query, linked server ile başka bir servera bağlanıyorsa veya OPENROWSET kullanılıyorsa oluşur. Bu transactionlar sadece SQL Server için değil, distributed transaction yapısını destekleyen Oracle, DB2 gibi veri tabanlarına bağlanılıyorsa da kullanılabilir.

Transactionlar Hakkında Doğru Bilinen Yanlışlar

 Eğer transaction içerisinde bir komut hata verirse, diğer komutların dataları da işlenmez. 
 Eğer bir hata yakalama mekanizması kullanmadıysanız ve hata durumunda rollback yapmadıysanız, commit cümlesi çalıştığında sorun olmayan komutlar çalışır ve işlemlerini yaparlar.

Bir stored procedure zaten bir transaction dır. 
Bir prosedür içerisindeki her bir kod tek başına bir transaction dır.  Aslında bu stored procedure içinde olmasa da, tüm yazılan insert, update ve delete kodları otomatik commit edilen bir transaction olarak çalışırlar. Ancak stored procedure kodunun tümü tek bir transaction değildir.  Eğer bir stored procedure, tek bir transaction olarak çalıştırılmak isteniyorsa, Begin Transaction ve Commit Transaction bloğu içine yazılmalıdır.  Ancak unutmamak gerekir ki, transaction isolation levela göre bu tablolarda uzun süreli lock oluşmasına, bu arada diğer kullanıcıların hiç bir işlem yapamamasına neden olabilir.

Bir transaction yaratılırsa ve bu transaction içinde bir select ifadesi çalıştırılırsa, kimse select ile çekilen satırlara ulaşamaz. 
 Bu transaction isolation level'a göre doğru da olabilir, yanlış da. Bunun için Transaction Isolation Level ile ilgili yazıma bakabilirsiniz.

Transaction nedir?

Transaction, bir veri tabanına aynı anda işlenen (commit)  veya işlenmesi geri alınabilen (rollback) bir grup komuttur.
Transaction yapısı ile farklı tablolara kayıt ekleyebilir, güncelleme yapabilir veya kayıt silebiliriz ve bu işlemlerden herhangi birinde bir sorun olursa, tüm yapılan işlemleri geri alabiliriz.
Transaction sırasında, tablolar üzerinde olabilecek lock ları, transaction isolation level'ını değiştirerek kontrol edebiliriz.

ASSEMBLY Yaratmak ve Permission Set

Diyelim CLR ile SQL Server da çalışmak için bir assembly yazdınız ve bu assembly yi  register ediyorsunuz.

CREATE ASSEMBLY MyCLRFunction
AUTHORIZATION [dbo]
FROM 'C:\MyCLRFunction.dll'
WITH PERMISSION_SET = SAFE;

Yukarıdaki örnekte göründüğü gibi, CREATE ASSEMBLY ile yazabildiğimiz parametrelerden  biri de PERMISSION_SET.

Permission Set, SQL Server'da oluşturduğumuz assembly için, hangi güvenlik kısıtılarının uygulanacanı belirlediğimiz yer.  Bu güvenlik kısıtları, tabii ki sadece bu assembly yi SQL Server da  kullanırken uygulanıyor olacak (Bir CLR fonksiyonun içinde örneğin)

 SQL Server 2008 de 3 farklı set var

• SAFE
• EXTERNAL_ACCESS
• UNSAFE

SAFE: Safe en fazla kısıtlayıcı olan ve aynı zamanda CREATE ASSEMBLY ifadesinde permission set için bir şey yazılmazsa SQL Server'ın kabul ettiği varsayılan değer.
Bu şekilde ayarlanmış bir assembly, network, dosya veya windows registry gibi dış kaynaklara ulaşamaz.  Hesaplama işleri veya kullanıcı tanımlı veri tipi için yazılmış bir assembly için en uygun ayardır.

EXTERNAL_ACCESS seçeneği ile kayıt edilmiş olan assembly ler, dosya sistemine ulaşıp, değiştirebilirler, event log'a, registry ye veya active directory ye ulaşabilirler, web veya smtp bağlantıları kurabilirler.
 Assembly şe dış kaynaklara ulaşırken, SQL Server service account u ile bu bağlantıyı kurarlar. Ayrıca unsafe olarak işaretlenmiş kodları da çalıştıramazlar.

UNSAFE : EXTERNAL_ACCESS ile izin verilern her şeyi yapabilir. Aralarındaki tek fark, UNSAFE, unsafe olarak işaretlenmiş ve unmanaged kod denilen kodları da çalıştırabiliyor olmasıdır.   Gerçekten gerekli olmadıkça, unmanaged bir kodu unsafe olarak sql server a tanıtmamak gerekir. Bu kodlar sql server'ın hem güvenliğine, hem de çalışmasına zarar verebilirler.

Assembly kodları, SQL Server servisi tarafından , SQL Server Service Account u ile çalıştırılırlar. Eğer SQL Server, Local System kullanıcısı gibi yetkileri kısıtlı bir kullanıcı ile çalıştırılıyorsa, network gibi yetkisi olmayan kaynaklara ulaşamaz.  Assembly kodu içerisinden, başka bir kullanıcı olarak çalıştırmaya izin vardır ancak burada da, windows un güvenlik kısıtları devreye girer.
CLR çağırırken, dışarıdaki bir kaynağa ulaşabilmek için, SQL Server'a windows kullanıcısı ile bağlanmak gerekir. Windows login'i ile giriş yapılmış olsa da, assembly tarafından giriş yapılan kullanıcının değil, sql server servisini çalıştıran kullanıcının yetkileri kullanılır.

 SQL Server 2008 veritabanlarının  TRUSTWORTHY özellikleri vardır. Bu özellik, veritabanı objelerinin (fonksiyonlar ve prosedürler gibi), dış kaynaklara ulaşıp ulaşamayacağını belirler. TRUSTWORTHY özelliği  ON ise CLR kullanan veritabanı nesneleri, sql server servis kullanıcısının yetkileri dahilinde dış kaynaklara ulaşabilir , eğer "OFF" ise, CLR nesneleri, dış bir kaynağa ulaşmaya çalışırsa, hata alırlar.

SQL Server 2008 de View Türleri

SQL Server 2008 de birkaç çeşit view yaratabiliriz.

Standard view Bir veya birden fazla tablo içerebilir ve tablolar join ler ile birbirlerine bağlanabilir. WHERE ifadesi ile filtreleme yapılabilir, TOP ve ORDER BY ifadeleri ile kayıt sayısı sınırlandırılabilir (Order By View lerde TOP olmadan kullanılamaz)

Updateable view  Tek bir tablodan oluşur ve üzerinde INSERT, UPDATE, DELETE, ve MERGE gibi veriyi değiştiren ifadeler direk olarak çalışabilir. Ayrıca, birden fazla tablodan oluşan bir view üzerine INSTEAD OF trigger yazılarak, View deki hangi verinin hangi tabloyu güncelleyeceği bu trigger üzerinde yazılabilir.

Indexed view Bazen, bir view e index koymak optimizasyon için iyi sonuçlar üretebilir.  View le üzerine indexler tıpkı tablolarda olduğu gibi "CREATE INDEX" ifadesi ile yaratılırlar. Indexed Viewler WITH Schemabinding seçeneği ile oluşturulmalıdırlar. Bu da view in içerisinde kullanılan kolonların yapısının değiştirilmesini engeller. Viewlerde kullanılan kolonun veri tipi değişitirilemez, drop edilemez veya kullanılan tablo drop edilemez. Öncelikle view drop edilmeli, tablolardaki gerekli değişikliklerden yapıldıktan sonra yeniden yaratılmalıdır.

Partitioned view Bir tabloyu horizontal olarak (yani satırlarına göre) parçalamış isek bu view ile farklı parçalara ayrılmış tabloları tek bir view de biraraya getirebiliriz.
Örneğin bu yılın satışlarını 1 tabloda, geçen yılın satışlarını başka bir tabloda, 2 yıl ve daha eski satışları da başka bir tabloda saklıyor isek, partitioned view ile bu 3 tablodakii verileri birarada gösterebiliriz.

SQL Server da Constraint ler Nasıl Çalışır?

SQL Server'daki tablolara konulmuş constraint(kısıtlar)ler, bir tabloda bir DML kodu (INSERT, DELETE, UPDATE, MERGER) çalışırken kontrol edilir.

Eğer gelen veri, tablodaki tüm kurallardan geçiyorsa işlem tamamlanır, eğer herhangi bir kolondaki, herhangi bir kısıta takılıyorsa o zaman transaction rollback edilir ve hiç bir veri güncelleme işlemi yapılmaz.

Örneğin email alanına bir check constraint koyduysanız ve aynı anda 10 kayıt birden insert ediyorsanız, ancak sadece 1 tanesi sorunluysa, tüm insert cümlesi rollback edilir ve hiç bir kayıt tabloya eklenmez.

CHECK Constraint Nedir?

SQL Server'da bir çok constraint (kısıt) kullanılabilir.
"Check Constraint" verinin doğruluğunu ve bütünlüğünü korumak üzere kullanılan bir kısıttır. Check constraint eklenmiş bir kolona yeni bilgi eklenirken veya bilgi güncellenirken, veri yazılmış olan kurallara göre kontrol edilir ve kurala uymuyorsa bir hata verilir ve veri kaydedilmez.

Check constraint ifadesi, true veya false döndüren bir kural setidir. Bu bir scalar function olabileceği için bir query de olabilir. Check constraint sadece false değer döndüğünde hata verir. Eğer ifade null döndürürse, bunu true gibi değerlendirir ve herhangi bir hata vermeden veriyi günceller.

Örneğin bir kolonda sınavlardan alınacak notları tutacak olalım. Notlar da 0-100 arasında değişiyor olmalı. Grade alanına bu kısıtı ekleyelim ve 100 ün üzerinde data girilmesini engelleyelim.

ALTER TABLE myTable ADD CONSTRAINT
CK_Grade CHECK(Grade <= 100 AND Grade >= 0)


Eğer tablomuzda veriler varsa ve daha önceden girilmiş verilerin kontrol edilmesini istemiyorsak with nocheck ifadesiyle bunu yapabiliriz. Aksi takdirde, veriler eklenen kurala uygun hale getirilene kadar check constraint eklenemez.

ALTER TABLE myTable WITH NOCHECK ADD CONSTRAINT
CK_Grade CHECK(Grade <= 100 AND Grade >= 0)

Computed Columns Nedir? Nasıl Kullanılır?

Computed column, bir tablonun aynı satırındaki diğer alanlara referans ederek hesaplama yapan bir formüldür aslında. Scalar fonksiyonlar da (tek bir değer döndüren fonksiyon), computed column tanımlarken kullanılabilir. Bir computed column, başka bir tablodaki verilere (tabii fonksiyon kullanmıyorsa) ulaşamaz veya sub query içeremez.

Varsayılan olarak, SQL Server'daki computed column lar sanaldır. Yani içerdikleri değerler diskte saklanmazlar, veri çekildiği zaman hesaplanırlar. Bu nedenle computed column içeren bir tabloyu SELECT * FROM ile çekmek performans sıkıntılarına yol açabilir.

Computed kolonları daha etkin tutmak ve performansını arttırmak için, disk üzerinde de saklayabiliriz. Bunun için PERSISTED anahtar kelimesi kullanılabilir.
SQL Server, persisted ile işaretlenmiş computed alanları, gerçek bir değer olarak disk üzerinde saklar ve bu alanı etkileyecek herhangi bir değişiklik olduğunda veya yeni kayıt eklendiğinde bu alanı da günceller.

Sadece deterministic fonksiyon kullanan computed kolonlar PERSISTED olarak işaretlenebilir.
Deterministic fonksiyon, aynı değer verildiğinde aynı sonucu döndüren fonksiyondur. Örneğin avg fonksiyonu, aynı değerler ile her zaman aynı sonucu döndürür. Oysa getdate(), deterministic bir fonksiyon değildir.

Bir computed column, deterministic olsun veya olmasın içerisindeki veri güncellenemez. Dolayısıyla hiçbir zaman insert veya update cümlesi içerisinde güncellenecek alanlar içerisinde yer alamaz.

Bir örnek ile nasıl computed kolon yaratıldığını görelim.
Bir tabloda ürünün birim satış fiyatı ve vergi oranı olsun. Vergi eklenmiş hali kaç fiyata satılacağını computed kolon kullanarak bulabiliriz.

ALTER TABLE myTable ADD PriceWithTax as UnitPrice + (UnitPrice*TaxRatio)

sys.Objects View ile veritabanındaki nesnelerin görüntülenmesi

SELECT * FROM sys.objects

kodunu çalıştırarak veritabanındaki tüm nesnelerin listesine ulaşılabilir.

Bu kodun döndürdüğü alanlardan bazılarına bakalım.
name :nesnesnin adı
object_id: SQL Server'deki her bir nesnenin tek bir id si vardır. Sistem tablolarında veya view lerinde bu id ile kullanırlar. Örnein bir tablonun içindeki kolonlar sys.columns view i kullanılarak görülebilir. Bir kolonun hangi tabloya ait olduğu bilgisi saklanırken, objenin adı değil, id si saklanır. Bir nesnenin id sini öğrenmek için de object_id sistem fonksiyonu kullanılabilir.

SELECT object_id('myobjectname')

type : SQL Server objesinin ne tür bir obje olduğunu gösterir. Aşağıda bu türlerin listesini bulabilirsiniz.

C Check constraint
D Default constraint
F Foreign Key constraint
FN Transact-SQL scalar function
FS Clr scalar function
IT Internal table
P SQL stored procedure
PK Primary Key constraint
S System table
SQ Service queue
TF SQL table valued function
TR SQL trigger
U User table
UQ Unique constraint
V View

SQL Server da Tarih Formatlama

SQL Server'daki DataTime tipindeki alanları formatlama ihtiyacımız olduğunda kolayca kullanabilecek bir fonksiyon.
Tek yapmanız gereken tarihi ve hangi formatta istedinizi söylemek. Uzun uzun convert kodları yazmaya gerek yok.

CREATE FUNCTION dbo.fn_FormatDate(@Type varchar(10),@Date Datetime)

RETURNS varchar(50)

AS BEGIN

  /*

  SELECT NULL [Type] ,CONVERT(varchar,GETDATE(),121) [Date]

  UNION SELECT NULL   ,dbo.fn_FormatDate(NULL,GETDATE())

  UNION SELECT 'M'    ,dbo.fn_FormatDate('M',GETDATE())

  UNION SELECT 'L'    ,dbo.fn_FormatDate('L',GETDATE())

  UNION SELECT 'S'    ,dbo.fn_FormatDate('S',GETDATE())

  UNION SELECT 'SR'   ,dbo.fn_FormatDate('SR',GETDATE())

  UNION SELECT 'DL'   ,dbo.fn_FormatDate('DL',GETDATE())

  UNION SELECT 'D'    ,dbo.fn_FormatDate('D',GETDATE())

  UNION SELECT 'DR'   ,dbo.fn_FormatDate('DR',GETDATE())

  UNION SELECT 'DN'   ,dbo.fn_FormatDate('DN',GETDATE())

  UNION SELECT 'H'    ,dbo.fn_FormatDate('H',GETDATE())

  UNION SELECT 'LM'   ,dbo.fn_FormatDate('LM',GETDATE())

  */

  DECLARE @RetVal varchar(50)

  SELECT @RetVal = NULL

  IF @Date IS NULL

    GOTO exit_proc

  IF ISDATE(@Date) = 0

    SELECT @Date = CONVERT(Datetime,'1900-01-01 00:00:00.000')

  IF @Type = 'M' BEGIN -- Long2

    SELECT @RetVal = dbo.fn_LeadZero(CONVERT(varchar,DAY(@Date)),2) + ' ' + DATENAME(month,@Date) + ' ' + CONVERT(varchar,YEAR(@Date))

    GOTO exit_proc

  END

  IF @Type = 'L' BEGIN -- Long

    SELECT @RetVal = CONVERT(varchar(20),@Date,104) + ' ' + DateName(weekday,@Date) + ', ' + CONVERT(varchar(5),@Date,108)

    GOTO exit_proc

  END

  IF @Type = 'S' BEGIN -- Short

    SELECT @RetVal = CONVERT(varchar(10),@Date,104) + ' ' + CONVERT(varchar(5),@Date,108)

    GOTO exit_proc

  END

  IF @Type = 'DL' BEGIN -- Day With long format

    SELECT @RetVal = CONVERT(varchar(20),@Date,104) + ' ' + DateName(weekday,@Date)

    GOTO exit_proc

  END

  IF @Type = 'D' BEGIN -- Only day

    SELECT @RetVal = CONVERT(varchar(10),@Date,104) 

    GOTO exit_proc

  END

  IF @Type = 'DR' BEGIN -- Day Reverse

    SELECT @RetVal = CONVERT(varchar(10),@Date,102) 

    GOTO exit_proc

  END

  IF @Type = 'DN' BEGIN -- Only Dayname

    SELECT @RetVal = DateName(weekday,@Date)

    GOTO exit_proc

  END

  IF @Type = 'H' BEGIN -- Only Hour

    SELECT @RetVal = CONVERT(varchar(5),@Date,108)

    GOTO exit_proc

  END

  IF @Type = 'LM' BEGIN -- Long With Month Name

    SELECT @RetVal = CONVERT(varchar(20),@Date,106) + ' ' + DateName(weekday,@Date) + ', ' + LEFT(CONVERT(varchar(30),@Date,108),5)

    GOTO exit_proc

  END

  IF @Type = 'SR' BEGIN -- Short Reverse

    SELECT @RetVal = CONVERT(varchar(10),@Date,120) + ' ' + CONVERT(varchar(5),@Date,108)

    GOTO exit_proc

  END

  IF ISNULL(@Type,'') = '' BEGIN

    SELECT @RetVal = CONVERT(varchar,@Date,121)

    GOTO exit_proc

  END

  exit_proc:

  IF ISNULL(@RetVal,'') = ''

    SELECT @RetVal = CONVERT(varchar(10),@Date,104) + ' ' + CONVERT(varchar(5),@Date,108)

  RETURN @RetVal

END

Function içerisine comment olarak eklenmiş olan örnek çalıştırma kodunu çalıştırırsak şöyle bir sonuç görebiliriz.

Type	Date
	2010-04-29 18:56:30.610
D	29.04.2010
DL	29.04.2010 Thursday
DN	Thursday
DR	2010.04.29
H	18:56
L	29.04.2010 Thursday, 18:56
LM	29 Apr 2010 Thursday,  18:56
M	29 April 2010
S	29.04.2010 18:56
SR	2010-04-29 18:56

Excel'den SQL Server'a Veri Okuma

Excel'den SQL Server'a data okumak için kullanılacak birçok yöntem var.


Eğer excel'den okuyacağımız data, tek bir tablo içine eklenecek veya tek bir tabloyu güncelleyecek ise en çok kullandığım ve bana en pratik gelen yöntem ise, excel de sql script ini oluşturmak ve bunu çalıştrmak.


Test etmek için basit bir tablo oluşturalım ve içine birkaç kayıt ekleyelim.

CREATE TABLE Musteri(MusteriNo int identity(1,1) primary key,

                     AdSoyad varchar(100), Adres varchar(100),

                     Ilce varchar(30), Il varchar(30),DogumTarihi datetime)

GO

INSERT INTO Musteri(AdSoyad, Adres, Ilce, Il,DogumTarihi) VALUES

('Deneme 1','Adres 1','Ilce 1', 'Il 1','1975-10-11'),

('Deneme 2','Adres 2','Ilce 2', 'Il 2','1980-01-01')

Şimdi de bir excel dosyamız olsun ve excel dosyasındaki kayıtları bu tabloya ekleyecek script'i oluşturalım.

="INSERT INTO Musteri(AdSoyad, Adres, Ilce, Il,DogumTarihi) VALUES('" & A2 & "','" & B2 & "','" & C2 & "','" & D2 & "','" & TEXT(E2;"yyyy-aa-gg") & "')"

Burada dikkat edilecek 2 nokta var. 1. si karakter verileri tek tırnak (') içine almak, 2. ise, tarih tipindeki verileri formatlamak.
Bunun için de Text fonksiyonunu kullandım ve "yyyy-aa-gg" formatında gelmesini istedim. Bu sql server'ın herhangi bir convert işlemi yapmayı gerektirmeden kolaylıkla anladığı tarih formatı. 15 Şubat 2010 tarihi bu format ile '2010-02-15' şeklinde görünecek.

Formülü oluşturduktan sonra tek yapmam gereken F2 hücresini tutup aşağı doğru çekmek ve oluşan script'i bir SQL IDE'sine kopyalamak.

INSERT INTO Musteri(AdSoyad, Adres, Ilce, Il,DogumTarihi) VALUES('Deneme 3','Adres 3','Ilce 3','Il 3','1974-12-15')

INSERT INTO Musteri(AdSoyad, Adres, Ilce, Il,DogumTarihi) VALUES('Deneme 4','Adres 4','Ilce 4','Il 4','1966-08-10')

INSERT INTO Musteri(AdSoyad, Adres, Ilce, Il,DogumTarihi) VALUES('Deneme 5','Adres 5','Ilce 5','Il 5','1976-05-27')

INSERT INTO Musteri(AdSoyad, Adres, Ilce, Il,DogumTarihi) VALUES('Deneme 6','Adres 6','Ilce 6','Il 6','1980-05-12')

INSERT INTO Musteri(AdSoyad, Adres, Ilce, Il,DogumTarihi) VALUES('Deneme 7','Adres 7','Ilce 7','Il 7','1966-02-01')

INSERT INTO Musteri(AdSoyad, Adres, Ilce, Il,DogumTarihi) VALUES('Deneme 8','Adres 8','Ilce 8','Il 8','1978-12-03')

INSERT INTO Musteri(AdSoyad, Adres, Ilce, Il,DogumTarihi) VALUES('Deneme 9','Adres 9','Ilce 9','Il 9','1972-07-05')

INSERT INTO Musteri(AdSoyad, Adres, Ilce, Il,DogumTarihi) VALUES('Deneme 10','Adres 10','Ilce 10','Il 10','1979-10-10')

T-SQL ile Fibonacci Serisi

İşte  Fibonacci Serisinin ilk 30 sayısını getiren kod.  @CountOfNumbers'ı değiştirerek daha fazla sayıya da ulaşabilirsiniz. Tabii ki, bigint veri tipinin izin verdiği aralık içinde kalıyorsa.

DECLARE @CountOfNumbers int = 30

DECLARE @Fibonacci TABLE(OrderNo int, FibonacciNumber bigint)

INSERT INTO @Fibonacci(OrderNo,FibonacciNumber)

  VALUES

   (1,0), -- 1. sayı = 0

   (2,1)  -- 2. sayı = 1

DECLARE @i  int = 3, -- 3 den başlatalım, 1. ve 2. yi sayıları tabloya ekledi

        @F1 bigint = 0,

        @F2 bigint = 1,

        @F  bigint

WHILE @i<=@CountOfNumbers BEGIN

  SET @F = @F1 + @F2

  INSERT INTO @Fibonacci(OrderNo,FibonacciNumber) VALUES(@i,@F)

  SET @F1 = @F2

  SET @F2 = @F

  SET @i+=1

END

SELECT * FROM @Fibonacci

Bir Tablodan Rastgele 5 Kayıt Getirmek

-- Önce test etmek için geçici bir tablo tanımlayalım.  

DECLARE @Dummy TABLE(ID int identity(1,1),Number int)

-- Bu tabloya 100 tane kayıt atalım.

-- SQL Server 2008 de değişkeni tanımlarken başlangıç değeri de verebilirsiniz. SQL Server 2008 öncesinde tanımlamayı ve başlangıç değeri vermeyi ayrı satırlarda yapmalısınız.

DECLARE @i int = 1

WHILE @i<=100 BEGIN

  INSERT INTO @Dummy(Number) VALUES(@i*@i)

  SELECT @i+=1 -- Bu da SQL Server 2008 e ait bir özellik. SELECT @i=@i+1 ifadesine karşılık gelir.

END

-- Dataları çekeceğimiz tablomuzu oluşturduk. Bakalım nasıl kayıtlar var.

SELECT * FROM @Dummy

    ID    Number

    1    1

    2    4

    3    9

    4    16

    5    25

    6    36

    ..    ..

    99    9801

    100    10000

Şimdi bu tablodan rastgele 5 tanesini seçelim. 

SELECT TOP 5  *

FROM @Dummy

ORDER BY NewID()

ID    Number

99    9801

25    625

65    4225

51    2601

15    225

NewID fonksiyonu  uniqueidentifier tipinde bir veri oluşturur ve her çektiğinizde de farklı bir değer oluşturur. Her seferinde farklı değerler üreten bu fonksiyonu ORDER BY a ekledim, böylece bizim tablomuzun kayıt yaratılma sırasına göre değil, anlık üretilen NewID ye göre sıralayacak kayıtları. "TOP 5" ifadesi de ilk 5 kaydın gelmesini sağlayacak.

DECLARE @Guid uniqueidentifier = NewID()

SELECT @Guid as Guid

Guid

7facc454-b048-4e44-8d72-b7a9b97fb370

Akla hemen neden random sayı üreten RAND() fonksiyonunu kullanmadık diye bir soru da gelebilir.

RAND() fonksiyonu  bir transaction içinde aynı sayıyı üretir.

SELECT RAND() as Random, * FROM @Dummy

kodunu çalıştırırsak Random kolonunda hep aynı sayının geldiğini görebilirsiniz.

SQL Server 2008 de kullanıcının yetkili olduğu rolleri öğrenmek.

SQL Server 2008' de kullanıcıların hangi veritabanı ve server rollerinde olduğunu anlamak için kullanılabilecek bir fonksiyon. 

CREATE FUNCTION dbo.fnSecurity_UserRoles(@UserName nvarchar(80))

RETURNS @Result TABLE (Role nvarchar(80),isDBRole bit)

AS BEGIN

  IF @UserName IS NULL

    SET @UserName = system_user

   DECLARE @member_principle_id int

   SELECT @member_principle_id  = principal_id

   FROM sys.database_principals

   WHERE name = @UserName

   INSERT INTO @Result(Role,isDBRole)

   SELECT p.name,1 FROM sys.database_role_members r

   JOIN sys.database_principals  p on p.principal_id = r.role_principal_id 

   WHERE member_principal_id= @member_principle_id

   SELECT @member_principle_id  = principal_id

   FROM sys.server_principals

   WHERE name = @UserName

   INSERT INTO @Result(Role,isDBRole)

   SELECT p.name,0

   FROM sys.server_role_members m

   join sys.server_principals p on m.role_principal_id = p.principal_id

   WHERE m.member_principal_id = @member_principle_id

   RETURN

END 

GO

-- Kullanıcının 'user name' ile listeleyebilir

SELECT * FROM dbo.fnSecurity_UserRoles('birsen')

-- Ya da aktif kullanıcının rollerini getirebiliriz

SELECT system_user, * FROM dbo.fnSecurity_UserRoles(system_user)

SQL Server Business Intelligence İçin Kitap Önerileri

Data Warehouse ve SQL Server Business Intelligence ile ilgili beğendiğim kitaplar.

The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
Ralph Kimball , Margy Ross

Data Warehouse ile ilgilenen herkesin mutlaka okuması gereken bir kitap



Product Details :

• Paperback: 464 pages
• Publisher: Wiley; 2 edition (April 26, 2002)
• Language: English
• ISBN-10: 0471200247
• ISBN-13: 978-0471200246
• Product Dimensions: 9.2 x 7.2 x 1 inches
• Shipping Weight: 1.5 pounds (View shipping rates and policies)
• Average Customer Review: 4.5 out of 5 stars See all reviews (33 customer reviews)

Practical Business Intelligence with SQL Server 2005 John C. Hancock (Author), Roger Toren (Author)

Product Details :

• Paperback: 432 pages
• Publisher: Addison-Wesley Professional; 1 edition (September 7, 2006)
• Language: English
• ISBN-10: 0321356985
• ISBN-13: 978-0321356987
• Product Dimensions: 9 x 7 x 1 inches
• Shipping Weight: 1.2 pounds (View shipping rates and policies)
• Average Customer Review: 4.3 out of 5 stars See all reviews (6 customer reviews)

Delivering Business Intelligence with Microsoft SQL Server 2008 (Paperback) Brian Larson (Author)


Product Details

• Paperback: 792 pages
• Publisher: McGraw-Hill Osborne Media; 2 edition (November 19, 2008)
• Language: English
• ISBN-10: 0071549447
• ISBN-13: 978-0071549448
• Product Dimensions: 9 x 7.3 x 1.6 inches
• Shipping Weight: 2.8 pounds (View shipping rates and policies)
• Average Customer Review: 4.9 out of 5 stars See all reviews (8 customer reviews)

Microsoft SQL Server 2008 Analysis Services Step by Step (Step By Step (Microsoft)) ~ Scott Cameron


Product Details

• Paperback: 448 pages
• Publisher: Microsoft Press; 1 edition (April 15, 2009)
• Language: English
• ISBN-10: 0735626200
• ISBN-13: 978-0735626201
• Product Dimensions: 8.9 x 7.3 x 1.4 inches
• Shipping Weight: 2 pounds (View shipping rates and policies)
• Average Customer Review: 5.0 out of 5 stars See all reviews (3 customer reviews)

SQL Server da stored procedure daki işlemler ne kadar sürede çalışıyor?

SQL Server'da bir stored procedure yazdınız ve işinde birçok iş yapıyorsunuz. Ancak prosedür yavaş çalışıyor. Ne yaparsınız ?

Ya execution plan'i çalıştırır ve hangi kodun yavaş çalıştığını anlamaya çalışırız veya teker teker kod parçalarını çalıştırız.

Bunun için kullandığım yöntem, stored procedure'a @Timer diye bir parametre eklemek ve bu parametreye 1 geçilirse her işlem için bu zamanları milisaniye cinsinden hesaplamak.

Örnek prosedür :

1 CREATE PROCEDURE dbo.sp_SampleProcedure

2 @Timer smallint = 0

3 AS BEGIN

4

5 IF @Timer = 1 BEGIN

6 DECLARE @Tm TABLE(dt datetime,com varchar(255), tm int NULL)

7 DECLARE @BeginDate datetime,@EndDate datetime,@OldDate datetime

8 SELECT @BeginDate = GETDATE()

9 INSERT INTO @Tm(dt,com) VALUES(GETDATE(),'***BEGIN***')

10 SELECT @OldDate = GETDATE()

11 END

12

13

14 -- Do something

15 SELECT 'This is a sample operation 1 '

16

17 IF @Timer = 1 BEGIN

18 INSERT INTO @Tm(dt,com,tm)

VALUES(GETDATE(),'10.Sample Operation 1',DATEDIFF(ms,@OldDate,GETDATE()))

19 SELECT @OldDate = GETDATE()

20 END

21

22

23 -- Do another thing

24 DECLARE @Result TABLE (OrderNo int, Number int )

25

26 DECLARE @i int = 1,@OrderNo int = 0 , @Step int = 1

27

28 WHILE @i<= 200 BEGIN

29 INSERT INTO @Result

30 VALUES(@OrderNo,@i)

31 SELECT @i = @i + @Step, @OrderNo = @OrderNo + 1

32 END

33

34 SELECT * FROM @Result

35

36 IF @Timer = 1 BEGIN

37 INSERT INTO @Tm(dt,com,tm) VALUES(GETDATE(),'20.Sample Operation 2',DATEDIFF(ms,@OldDate,GETDATE()))

38 SELECT @OldDate = GETDATE()

39 END

40

41

42 -- Do one more operation and get the size of all tables. This will take a time

43

44 CREATE TABLE #TableSize

45 (

46 [name] nvarchar(255),

47 [rows] int,

48 [reserved] varchar(20),

49 [data] varchar(20),

50 [index_size] varchar(20),

51 [unused] varchar(20)

52 )

53 -- Mevcut database deki tüm tablolar için sp_spaceused proc. ü çağırılıp, dönen recordset #TableSize tablosu eklenir

54 EXEC sp_MSforeachtable @command1='INSERT INTO #TableSize EXEC sp_spaceused ''?'' '

55

56 SELECT * FROM #TableSize

57

58

59 IF @Timer = 1 BEGIN

60 INSERT INTO @Tm(dt,com,tm) VALUES(GETDATE(),'30.Get Table sizes',DATEDIFF(ms,@OldDate,GETDATE()))

61 SELECT @OldDate = GETDATE()

62 END

63

64 ---------------------------------------------

65 -- Get the execution time

66 IF @Timer = 1 BEGIN

67 INSERT INTO @Tm(dt,com) VALUES(GETDATE(),'***END***')

68 SELECT @EndDate = GETDATE()

69 INSERT INTO @Tm(dt,com,tm) VALUES(GETDATE(),'Total SP Duration',DATEDIFF(ms,@BeginDate,@EndDate))

70 SELECT * FROM @Tm

71 END

72 SET NOCOUNT OFF

73 SET ANSI_WARNINGS ON

74

75 END

76 GO

Prosedürü oluşturduktan sonra çalıştıralım :

exec sp_SampleProcedure @Timer = 1

En son sonuç olarak bize her işlemin ne kadar sürede çalıştığını gösterecek.

dt	com	tm
14.04.2010 15:34	***BEGIN***
14.04.2010 15:34	10.Sample Operation 1	0
14.04.2010 15:34	20.Sample Operation 2	6
14.04.2010 15:34	30.Get Table sizes	4843
14.04.2010 15:34	***END***
14.04.2010 15:34	Total SP Duration	4850