Windows PowerShell ile SQL Server 2012 (3. Bölüm)

    Merhaba arkadaşlar, geçen hafta sizlere Windows PowerShell ile SQL Server 2012'de bazı önemli veri tabanı işlemlerinden bahsetmiştim. Bugün sizlere Windows PowerShell ile ileri düzey (advanced) veri tabanı işlemlerinden bahsetmek istiyorum. Bu işlemlere örnek olarak "stored procedure", "trigger" ve "index"'i verebilirim. Bu işlemler veri tabanı tasarımında ve kullanımında geliştiriciye (developer) ve son kullanıcıya birçok kolaylık sağlamaktadırlar.


    Veri tabanına erişim esnasında veya veri tabanı üzerinde işlemler yaparken, sorguları tek bir satırda yapmamız gerekiyor. "Stored procedure", "function", "trigger" gibi teknikleri kullanarak bu zorluktan kurtulabiliriz. Kodları birer kod blokları içerisine yazmak bize birçok avantaj sağlar. Kod parçacıklarını tekrar tekrar kullanabilmemize ve hızlı bir şekilde sorgu yapmamıza olanak sağlar.

"Stored Procedure" Kullanımı

    İlk olrak "stored procedure"'ın ne olduğundan bahsetmek istiyorum. Veri tabanının "server" tarafında tutulan ve ilk derlemeden sonra bir daha derlemeye ihtiyaç duyulmayan SQL ifadeleridir. "Stored procedure"'ler parametreli ve parametresiz olarak çalışabildiği gibi geriye bir değer de döndürebilirler. [1]

    "Stored procedure"'ın SQL Server'da kullanımı aşağıdaki şekildedir. Aldığı parametre yardımıyla soyadı sizin belirlediğiniz bir değer olan ilk 10 öğrenciyi getirir.

CREATE PROCEDURE [dbo].[SISProc] @LastName [varchar]
(50)
WITH ENCRYPTION
AS
SELECT
TOP 10
StudendID,
LastName
FROM
Student.Student
WHERE
LastName = @LastName

    Bu örneği asıl konumuz olan Windows PowerShell ISE'de nasıl yapabiliriz? Aşağıdaki örneği inceleyebilirsiniz.

Import-Module SQLPS -DisableNameChecking

$instanceName = "KAZUYIX\SQLSERVER2012"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

$dbName = "SIS"
$tableName = "Student"
$db = $server.Databases[$dbName]

$sprocName = "SISProc"
$sproc = $db.StoredProcedures[$sprocName]

#stored procedure varsa sil
if ($sproc){
    $sproc.Drop()
}

$sproc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure -ArgumentList $db, $sprocName
$sproc.TextMode = $false
$sproc.IsEncrypted = $true
$paramtype = [Microsoft.SqlServer.Management.SMO.Datatype]::VarChar(50);
$param = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedureParameter -ArgumentList $sproc,"@LastName",$paramtype
$sproc.Parameters.Add($param)

$sproc.TextBody = @"
SELECT
TOP 10
StudentID,
LastName
FROM
Studenr.Student
WHERE
LastName = @LastName
"@

$sproc.Create()

"Trigger" Kullanımı

    "Trigger" tetikleyici anlamına gelir ve programlarımızda belirli bir işlem gerçekleştiğinde programımızı tetiklemeye yarar veya tablolar arası veri bütünlüğünü korumak için kullanılırlar. Aslında "trigger" bir "stored procedure"'dır. Fakat kendiliğinden çalışırlar. "Trigger"'larda ya hep ya hiç mantığı vardır. Program zorlukla karşı karşıya kaldığında bütün işlem geri döner. "Trigger" tanımlarken "transaction" bloğu olması gerekir. [2]

    SQL Server'da "trigger" kullanımı aşağıdaki şekildedir. "Student" tablosuna eklenen ve tablodan silinen değerleri gösteren bir "trigger" örneğidir.

CREATE TRIGGER [Student].[triggerStudent]
ON [Student].[Student]
AFTER UPDATE
AS
SELECT
GETDATE() AS UpdatedOn,
SYSTEM_USER AS UpdatedBy,
i.LastName AS NewLastName,
i.FirstName AS NewFirstName,
d.LastName AS OldLastName,
d.FirstName AS OldFirstName
FROM
inserted i
INNER JOIN deleted d
ON i.StudentID = d.StudentID

    Bu işlem Windows PowerShell ISE'de nasıl yapılır onu göstermek istiyorum. Aşağıdaki örneği inceleyebilirsiniz.

Import-Module SQLPS -DisableNameChecking

$instanceName = "KAZUYIX\SQLSERVER2012"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

$dbName = "SIS"
$tableName = "Student"
$db = $server.Databases[$dbName]

$tableName = "Student"
$schemaName = "dbo"

$table = $db.Tables | Where Schema -Like "$schemaName" | Where Name -Like "$tableName"
$triggerName = "tr_Student"
$trigger = $table.Triggers[$triggerName]

if ($trigger){
    $trigger.Drop()
}
$trigger = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Trigger -ArgumentList $table, $triggerName
$trigger.TextMode = $false

$trigger.Insert = $false
$trigger.Update = $true
$trigger.Delete = $false

$trigger.InsertOrder = [Microsoft.SqlServer.Management.SMO.Agent.ActivationOrder]::None
$trigger.ImplementationType = [Microsoft.SqlServer.Management.SMO.ImplementationType]::TransactSql

$trigger.TextBody = @"
SELECT
GETDATE() AS UpdatedOn,
SYSTEM_USER AS UpdatedBy,
i.LName AS NewLastName,
i.FName AS NewFirstName,
d.LName AS OldLastName,
d.FiName AS OldFirstName
FROM
inserted i
INNER JOIN deleted d
ON i.StudentID = d.StudentID
"@

$trigger.Create()

    Resimde gördüğünüz gibi Windows PowerShell'de oluşturduğumuz "trigger"'ın içerisindeki kodları SQL Server ekranında görebiliyoruz. Hatta bunun üzerinde güncellemeler yapabiliriz.

"Index" Kullanımı

    Veri tabanı üzerinde kayıt sayısı arttıkça çekilen sorguların döndüreceği değerlerin getirilmesi de o kadar zaman alacaktır. Verilere hızlı erişim için kullanılması gereken yöntem "index"'leme yöntemidir. SQL Server sorgusu çalıştırıldığında öncelikle "index"'leme var mı diye kontrol eder. Sorguyu bu duruma göre çalıştırır. Özellikle büyük veri tabanlarında kullanılması son kullanıcıya çok kolaylık sağlar.

    İki çeşit "index" vardır. Bunlar "Clustrered Index" ve "Non-Clustered Index"'dir. "Clustered Index", verilerin "index key" değerine göre fiziksel olarak alfabetik sıralandığı "index" türüdür. Bir tabloda en fazla bir tane bulunur. "Non-Clustered Index" mimarisinde fiziksel bir sıralama söz konusu değildir. "Index" değeri verinin kendisi değil "pointer" değeridir. Bu "pointer" değeri verinin nerede olduğunu gösterir. [3]

    SQL Server'da "index" kullanımı aşağıdaki şekildedir.

CREATE NONCLUSTERED INDEX [idxNLameFName]
ON [Person].[Person]
(
[LName] ASC,
[FName] ASC
)
INCLUDE ( [MiddleName])
GO

    Aynı örneğin Windows PowerShell'de kullanımı aşağıdaki şekildedir.

Import-Module SQLPS -DisableNameChecking

$instanceName = "KAZUYIX\SQLSERVER2012"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

$dbName = "SIS"
$tableName = "Student"
$db = $server.Databases[$dbName]

$tableName = "Student"
$schemaName = "dbo"

$table = $db.Tables | Where Schema -Like "$schemaName" | Where Name -Like "$tableName"

$indexName = "idxLNameFName"
$index = $table.Indexes[$indexName]

if ($index){
$index.Drop()
}
$index = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index -ArgumentList $table, $indexName

$idxCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $index, "LName", $false
$index.IndexedColumns.Add($idxCol1)

$idxCol2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $index, "FName", $false
$index.IndexedColumns.Add($idxCol2)

$inclCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $index, "MiddleName"
$inclCol1.IsIncluded = $true
$index.IndexedColumns.Add($inclCol1)
$index.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::None
$index.IsClustered = $false
$index.FillFactor = 70

$index.Create()

    Windows PowerShell hakkında anlatacaklarım bu kadar. İlerleyen zamanlar farklı bir konuyla sizlerle yeniden buluşmak dileğiyle, hoşçakalın...


Referanslar:

Kitap: "SQL Server 2012 with PowerShell V3 Cookbook"

[1]http://www.datawarehouse.gen.tr/Makale.aspx?ID=528
[2]http://www.serdardemir.net/sql-trigger-nedir-resimli-anlatim.html
[3]http://www.yazilimciblog.com/sql-index-yapilari/