https://www.buraksenyurt.com/Burak Selim Şenyurt - T SQL2020-12-05T15:39:09+00:00Matematik Mühendisi Bir Bilgisayar Programcısının NotlarıBurak Selim SenyurtBlogEngine.Net Syndication Generatorhttps://www.buraksenyurt.com/opml.axdBurak Selim SenyurtMatematik Mühendisi Bir Bilgisayar Programcısının Notlarıtr-TRBurak Selim Şenyurt0.0000000.000000https://www.buraksenyurt.com/post/bilmiyordum-ogrendim-sql-mergeBilmiyordum, Öğrendim : SQL Merge2019-01-13T13:03:00+00:00bsenyurt<p><img style="float: right;" src="https://www.buraksenyurt.com/image.axd?picture=/2018/11/sqlmerge_0.jpg" alt="" />Merhaba Arkadaşlar,</p>
<p>Gün geçmiyor ki çevremdeki insanlardan yeni bir şeyler daha öğrenmeyeyim. Bugün o günlerden biriydi...</p>
<p>İş yerinde elimizin her an üzerinde olabileceği binlerce SQL nesnemiz var. Tablolar, fonksiyonlar, sp'ler... Bazen iş biriminden gelen istekler doğrultusunda onlara müdahale etmemiz veya yenilerini yazmamız gerekiyor. Sorun şu ki 2000li yılların başından kalan ve yorum satırlarına bakıldığında üzerinden bir çok geliştiricinin geçtiği spl'lerimiz var. Bazen buradaki kalabalık sorgular arasında samanlıkta iğne ararcasına sorun çözmeye çalıştığımız oluyor. Çok motive edici bir durum değil takdir ederseniz ki. Şükür ki alanlarında yetkin ekip arkadaşlarımız var ve yeri geldiği zaman söyledikleri ufak bir ipucu ile hayatımızı kolaylaştırıyorlar <em>(ki bu etkili yardımlaşmada agile metodolojide koşan bir takım olmamızın da büyük etkisi var)</em></p>
<p>İşte geçenlerde çok uzun sürdüğü için sorun yaratan bir sp<em>(Stored Procedure)</em> ile cebelleşirken değerli bir yardım geldi. Ekip arkadaşımın bir önerisi üzerine kendimi SQL Merge komutunu araştırırken/öğrenirken buldum. 2008den beri var olan benim bihaber olduğum bu komutu öğrenirken keyifli anlar da yaşadım. Normalde çok kötü bir SQLciyimdir ama Merge komutunu uygulamalı olarak denedikten sonra şirketteki o kallavi sorgunun hem daha da hızlandığını hem de daha okunur hale geldiğini gördüm. Sonunda konuyu kalem alıp paylaşmanın iyi olacağını fark ettim. Hem kendim için kayıt altına almış hem de yazıp çizerek konuyu daha iyi öğrenmiş olmam da ödülüm olacak tabii. Dilerseniz vakit kaybetmeden konumuza geçelim. Başlangıç için aşağıdaki veri içeriklerine sahip iki tablomuz olduğunu düşünelim.</p>
<p><span style="text-decoration: underline;">Kaynak tablo içeriği(Book)</span></p>
<pre class="brush:plain;auto-links:false;toolbar:false" contenteditable="false">BookID Title ListPrice StockLevel
----------- -------------------------------------------------- --------------------- ----------
1 Clean Architecture 34,55 5
2 Clean Code 20,00 5
3 Anti-patterns explained 15,99 10
4 Programming C# 50,40 20</pre>
<p><span style="text-decoration: underline;">Hedef tablo içeriği(Store)</span></p>
<pre class="brush:plain;auto-links:false;toolbar:false" contenteditable="false">BookID Title ListPrice StockLevel
----------- -------------------------------------------------- --------------------- ----------
1 Clean Architecture 34,55 5
2 Clean Code 10,00 5
3 Anti-patterns explained 15,99 8
6 Cloud for dummies 44,44 3</pre>
<p>Veritabanı ile çalışan pek çok uygulamada bu tip birleştirme odaklı tablolara rastlayabiliriz. Genellikle dışarıdan belirli periyotlarla beslenen bir tablo ve bu tablodaki veri içeriğine göre kendini sürekli olarak güncel tutan bir başka tablo olur. Aynen yukarıdaki senaryoda görülen Kaynak ve Hedef tablolar gibi. Özetle hedef tabloyu kaynak tablodaki değişikliklere göre güncel tutmak istediğimizi düşünebiliriz. Kaynaktan silinenlerin hedeften de silinmesi, güncellenenlerin aynı şekilde hedefte de güncellenmesi veya kaynağa yeni gelenlerin hedef tabloya da aktarılması gibi işlemlerden bahsediyoruz.</p>
<p>Book ve Store tablolarını göz önüne aldığımızda Store tablosundan silinen<em>(4 nolu kayıt)</em>, eklenen<em>(6 nolu kayıt</em>), güncellenen<em>(2 ve 3 nolu kayıtlar)</em> ve hiç bir değişikliğe uğramayan<em>(1 nolu kayıt)</em> kitap bilgileri olduğunu görüyoruz. Şimdi Book tablosunu Store tablosuna göre güncellememiz gerekiyor. Elbette bunun bir çok yolu var. Örneğin Cursor açıp kaynak tabloyu baştan sona tarayarak bu işlemi gerçekleştirebiliriz. Ya da Select into ifadesi ile birlikte insert, update, delete sorgularını kullanabiliriz. Belki başka çözümler de söz konusu olabilir. Buradaki gibi az sayıda satır içeren veri kümeleri için seçilen tekniğin bir önemi yok aslında. Ancak tablo kayıt sayısı aynen şirketimizdeki senaryodaki gibi milyonlar seviyesine çıkınca performans sorunları yaşayabiliriz. Bir alternatif olarak üzerinde insert, update ve delete işlemlerini uygulamak için tek bir birleştirme maliyeti üzerinden hareket etmek çok daha verimli olabilir. Merge bu noktada devreye giriyor. </p>
<p>Normal şartlarda yukarıdaki içerikleri eşleştirmek adına pekala aşağıdaki gibi sorgular yazılabilir <em>(Bildiğim kadarı ile yazdım. Bu konuda alternatifler için aydınlatılmaya ihtiyacım var)</em></p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">Update Book
Set
Title=S.Title,
ListPrice=S.ListPrice,
StockLevel=S.StockLevel
from Store S
inner join Book B
on B.BookID=S.BookID
where
B.ListPrice<>S.ListPrice or B.Title<>S.Title or B.StockLevel <> S.StockLevel;
Delete from Book Where BookID not in
(Select S.BookID from Store S where S.BookID in (Select BookID from Book));</pre>
<p>İlk olarak farklılıkları bulup gerçekleştirdiğimiz bir Insert işlemi var. Burada alt sorgu kullandığımızı görebilirsiniz. Güncelleme işleminde ise bir inner join kullanımına gittik. En beter sorgu da silme operasyonu için yazdığım olmalı sanıyorum ki. Bu sorguları işlettiğimizde Book ve Store tabloları eşlenecektir. Lakin bir taşla üç kuş vurabiliriz de. Şimdi konuyu merge ifadesini baz alarak ele alalım. Aşağıdaki uçtan uca sorgu işimizi görür<em>(Ben diğer veritabanlarını kirletmemek adına LearningDb isimli ayrı bir veritabanında çalıştım)</em></p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">Create database LearningDb;
Use LearningDb;
Create Table Book
(
BookID int primary key,
Title varchar(50),
ListPrice money,
StockLevel smallint
)
Go
insert into Book
Values
(1,'Clean Architecture',34.55,5),
(2,'Clean Code',20.00,5),
(3,'Anti-patterns explained',15.99,10),
(4,'Programming C#',50.40,20)
Go
Create Table Store
(
BookID int primary key,
Title varchar(50),
ListPrice money,
StockLevel smallint
)
Go
insert into Store
Values
(1,'Clean Architecture',34.55,5), --Değişiklik yok
(2,'Clean Code',10.00,5), -- Fiyat değişti
(3,'Anti-patterns explained',15.99,8), --Stok seviyesi değişti
(6,'Cloud for dummies',44.44,3) -- Yeni geldi
--(4,'Programming C#',50.40,20) -- Silindi
Go
Select * from Book;
Select * from Store;
Merge Book AS T
Using Store As S
on (T.BookID=S.BookID)
When Matched and T.Title <> S.Title Or T.ListPrice<>S.ListPrice Or T.StockLevel<>S.StockLevel Then --Herhangibir güncelleme varsa
Update Set T.Title=S.Title,T.ListPrice=S.ListPrice,T.StockLevel=S.StockLevel
When Not Matched By Target Then -- Yeni eklenmiş kitaplar varsa
Insert (BookID,Title,ListPrice,StockLevel)
Values (S.BookID,S.Title,S.ListPrice,S.StockLevel)
When Not Matched By Source Then -- Silinmiş kitaplar varsa
DELETE
OUTPUT $action [Event], DELETED.BookID as [Target BookID],DELETED.Title as [Target Title],DELETED.ListPrice as [Target ListPrice],DELETED.StockLevel as [Target StockLevel],
INSERTED.BookID as [Source BookID],INSERTED.Title as [Source Title],INSERTED.ListPrice as [Source ListPrice],INSERTED.StockLevel as [Source StockLevel];
Select * from Book;
Select * from Store;</pre>
<p>Merge kısmına kadar yapılan hazırlıklarda örnek bir veritabanı oluşturup içerisine Book ve Store isimli tablolarımızı açıyoruz<em>(Buralarda if exist kullanımına gitmekte yarar olabilir ya da başlarda drop table kullanılabilir)</em> Sonrasında ise Merge ifademiz başlıyor. Book ve Store tablolarını BookID alanı üzerinden birleştirdikten sonra When kelimesi ile başlayan üç ayrı kısım yer alıyor.</p>
<p>Eğer bir eşleşme var ve tabloların Title, ListPrice, StockLevel alanlarının en azn birisinde veya tümünde farklılıklar söz konusuysa Then kelimesinden sonra gelen Update ifadesi çalıştırılıyor. Update ifadesinde T ile belirtilen hedef tablo alanlarının S ile belirtilen kaynak tablo alanları ile beslendiğine dikkat edelim. Eğer hedef tabloda kaynaktaki satırlar ile BookID üzerinden bir eşleşme yoksa 'When not matched by Target Then' sonrasında gelen Insert sorgusu çalışıyor. Burada da kaynak tablodaki alan değerlerinin eklendiğine dikkat edelim. Son olarak hedefte olduğu halde kaynakta olmayan satırlar varsa 'When not matched by source then' sonrasındaki Delete ifadesi çalışıyor ve hedef tablodaki ilgili kayıtlar siliniyor.</p>
<p>Merge sorgusunun tamamlanması için mutlaka ; işareti ile ifadeyi bitirmemiz gerekiyor. Bunu yapmadan önce meydana gelen değişiklikleri takip edebilmek adına output ifadesini çalıştırıyoruz. Burada $action değişkeni ile meydana gelen olay yakalanıyor<em>(o satır için insert, update, delete olaylarından hangisi olduysa)</em> DELETED ve INSERTED isimli hazır tabloları kullanaraktan da hangi tabloda ne gibi bir alan değişikliği olduğunu rahatlıkla görebiliyoruz. Sonuçlar aşağıdaki gibi olacaktır.</p>
<pre class="brush:plain;auto-links:false;toolbar:false" contenteditable="false">Event Target BookID Target Title Target ListPrice Target StockLevel Source BookID Source Title Source ListPrice Source StockLevel
---------- ------------- -------------------------------------------------- --------------------- ----------------- ------------- -------------------------------------------------- --------------------- -----------------
UPDATE 2 Clean Code 20,00 5 2 Clean Code 10,00 5
UPDATE 3 Anti-patterns explained 15,99 10 3 Anti-patterns explained 15,99 8
DELETE 4 Programming C# 50,40 20 NULL NULL NULL NULL
INSERT NULL NULL NULL NULL 6 Cloud for dummies 44,44 3
(4 row(s) affected)
BookID Title ListPrice StockLevel
----------- -------------------------------------------------- --------------------- ----------
1 Clean Architecture 34,55 5
2 Clean Code 10,00 5
3 Anti-patterns explained 15,99 8
6 Cloud for dummies 44,44 3
(4 row(s) affected)
BookID Title ListPrice StockLevel
----------- -------------------------------------------------- --------------------- ----------
1 Clean Architecture 34,55 5
2 Clean Code 10,00 5
3 Anti-patterns explained 15,99 8
6 Cloud for dummies 44,44 3
(4 row(s) affected)</pre>
<p>Artık her iki tablonun verileri de eş.</p>
<p>Gece yayınevlerinden son listeleri alan servis çalıştığında Store tablosunda yapılan değişiklikler, yukarıdaki sorgu sayesinde Book tablosuna da yansıtılacak ve o günün bayilerinin bakacağı asıl içerik eşleştirilmiş olacak. Bu senaryoyu bir düşünüp kurgulamaya çalışın derim. Görüldüğü üzere merge esasında oldukça pratik bir kullanıma sahip ve birleştirme senaryoları için ideal. Pek tabii kurumun iş kuralları gereği bir merge işlemi her zaman için buradaki using ifadesi kadar sade olmayabilir. Örneğimizde doğrudan primary key alanlar üzerinden bir eşleşme yaptık ancak farklı senaryolar olduğu takdirde using ifadesine parantez açılıp daha karmaşık select ifadelerine ait sonuçların kaynak olarak gösterilmesi de sağlanabilir. Lakin maliyeti yüksek olduğu için kaçınmaya çalıştığımız çeşitli sorguları<em>(sub query'ler, çok sayıda tablolu join'ler vb)</em> buraya almanın çok önemli bir pozitif katkısı olmayabilir. Sonuç itibariyle büyük veri kümelerini kullanarak performans testlerini yapmakta ve execution planlara bakıp gerekli müdahaleleri yapmakta yarar var. Bizim senaryomuz için çalışma zamanı planlarına baktığımızda en azından üç iş yerine tek seferlik bir maliyetin altına girdiğimizi görebiliriz.</p>
<p>İlk uygulama biçimimiz için aşağıdaki gibi bir plan oluşur.</p>
<p><img src="https://www.buraksenyurt.com/image.axd?picture=/2018/11/sqlmerge_1.gif" alt="" /></p>
<p>Table Spool maliyetleri biraz yüksek görüldüğü üzere. Merge çalışma planında ise durum aşağıdaki gibidir. Şekilde görülmese de %25lik bir Full Outer Join maliyeti var.</p>
<p><img src="https://www.buraksenyurt.com/image.axd?picture=/2018/11/sqlmerge_2.gif" alt="" /></p>
<p>İşin aslı konuyu SQL performans yönetimi konusunda uzman birisinin incelemesi daha doğru olabilir. Genellikle şirketlerin veritabanı operasyon ekipleri perfomans arttırımı gerektiren sorgular için destek oluyorlar. Yine de iş oraya gelmeden önce gerekli ön tedbirleri alıp performans iyileştirmelerini yapmak da biz geliştiricilere düşen önemli bir görevdir. Böylece geldik bir makalemizin daha sonuna. Tekrardan görüşünceye dek hepinize mutlu günler dilerim.</p>2019-01-13T13:03:00+00:00sqlmerget-sqldockerquerylinuxubuntusub queryselectjoindata mergingmigrationsql serverstored proceduresbsenyurtGün geçmiyor ki çevremdeki insanlardan yeni bir şey daha öğrenmeyeyim. İş yerindeki sıkıcı sayılabilecek SQL sorguları arasında gezinirken takım arkadaşım Mustafa'nın bir önerisi üzerine SQL Merge komutunu araştırmaya başladım. 2008den beri var olan benim bihaber olduğum bu komutu öğrenirken keyifli anlar yaşadım. Normalde çok kötü bir SQLciyimdir ama Merge komutunu uygulamalı olarak denedikten sonra şirketteki o kallavi sorgunun hem daha da hızlandığını hem de daha okunur hale geldiğini gördüm. Sonunda konuyu kaleme alıp paylaşmanın iyi olacağını fark ettim. Haydi gelin başlayalım.https://www.buraksenyurt.com/pingback.axdhttps://www.buraksenyurt.com/post.aspx?id=b6de0c7b-f645-42d0-b578-535b815ab7914https://www.buraksenyurt.com/trackback.axd?id=b6de0c7b-f645-42d0-b578-535b815ab791https://www.buraksenyurt.com/post/bilmiyordum-ogrendim-sql-merge#commenthttps://www.buraksenyurt.com/syndication.axd?post=b6de0c7b-f645-42d0-b578-535b815ab791https://www.buraksenyurt.com/post/Tek-Fotoluk-Ipucu-49e28093Daha-Hc4b1zlc4b1-CountTek Fotoluk İpucu 49–Daha Hızlı Count2012-03-06T22:50:00+00:00bsenyurt<p>Merhaba Arkadaşlar,</p>
<p>Çok yüksek rakamlarda satır içeren(Milonylarca Satır) tablolar söz konusu olduğunda, bunların satır sayılarını, Count Aggregate fonksiyonu ile bulurken süre kaybı yaşıyorsak ve sonuçları geç alıyorsak daha hızlı bir yola başvurabiliriz. Nasıl mı? <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-bottom-style: none; border-left-style: none; border-top-style: none; border-right-style: none" src="http://www.buraksenyurt.com/pics/wlEmoticon-winkingsmile_78.png" alt="Winking smile" /></p>
<p><a href="http://www.buraksenyurt.com/pics/artcl_10_1.gif"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="artcl_10_1" src="http://www.buraksenyurt.com/pics/artcl_10_1_thumb.gif" border="0" alt="artcl_10_1" width="281" height="582" /></a></p>2012-03-06T22:50:00+00:00sqlsql server 2008t-sqltransact sqlrow countcountselectbsenyurthttps://www.buraksenyurt.com/pingback.axdhttps://www.buraksenyurt.com/post.aspx?id=75780203-fe3f-4d53-938e-87fd2b5588010https://www.buraksenyurt.com/trackback.axd?id=75780203-fe3f-4d53-938e-87fd2b558801https://www.buraksenyurt.com/post/Tek-Fotoluk-Ipucu-49e28093Daha-Hc4b1zlc4b1-Count#commenthttps://www.buraksenyurt.com/syndication.axd?post=75780203-fe3f-4d53-938e-87fd2b558801https://www.buraksenyurt.com/post/T-SQL-ile-Eglenmeye-Devam(Ikinci-Devre)T-SQL ile Eğlenmeye Devam(İkinci Devre)2012-01-01T14:06:00+00:00bsenyurt<p><a href="https://www.buraksenyurt.com/pics/fun2.jpg"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; padding-top: 0px; border: 0px;" title="fun2" src="/pics/fun2_thumb.jpg" alt="fun2" width="261" height="304" align="right" border="0" /></a>Merhaba Arkadaşlar,</p>
<p>Hatırlayacağınız üzere geçtiğimiz günlerde kafayı <strong>T-SQL</strong> ile bozmuş ve can sıkıntısından eğlenceli ifadeler yazmaya çalışmıştım. Sanırım söz konusu bu eğlence sonraki günlere de sirayet etti ve yine bir kaç eğlenceli <strong>T-SQL</strong> sorgusu ile karşınızdayım <em>(İnsan ne oldum dememeli ne olacağım demeli belki de…Ben ki SQL’ den nefret eden bir birey olarak bu hale geldiysem… <img class="wlEmoticon wlEmoticon-smile" style="border-style: none;" src="/pics/wlEmoticon-smile_26.png" alt="Smile" /> )</em></p>
<p>Aslında hiç vakit kaybetmeden sorgularımızı incelemeye başlayalım dilerseniz. Elbetteki yine merak ettiğim ve aklıma gelen bazı ihtiyaçlar dahilinde bu sorgular ortaya çıkmakta. Örneğin sakin sakin otururken ilk aklıma gelen <strong>T-SQL</strong> tarafında bizim söyleyeceğimiz bazı kriterlere göre rastgele şifre üretecek bir fonksiyon yazmak oldu <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_77.png" alt="Winking smile" /></p>
<p>Bunun için aşağıdaki<strong> T-SQL</strong> betiğini yazdım.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">Use AdventureWorks
Go
-- Function içerisinde Rand() fonksiyonunu kullanamayız(Invalid use of side-effecting or time-dependent operator in 'rand' within a function.) hatası alırız. Bu yüzden bir hile yapacağız ve rastgele sayıyı bir view içerisinden alacağız ;) Steve Kass' ın güzide çözümlerinden birisidir.
create view ViewRandomNumbers
as
select rand( ) as Number
go
create Function ufnGeneratePassword(
@PasswordLength int -- Kaç karakterlik password oluşturacağız
,@StartChar tinyint -- başlangıç karakterinin ascii karşılığı sayısal değeri
,@CharRange tinyint -- Son karakterin ascii karşılığı sayısal değeri
,@ExcludedChars varchar(50) -- şifre içerisinde bulunmaması istenen karakterler
)
returns varchar(50)
as
begin
Declare @Password varchar(50)=''
Declare @char char -- Belirtilen aralıkta üretilen karakteri tutan değişken
while @PasswordLength> 0 begin
-- Önce @StartChar' dan itibaren @CharRange mesafesine kadarlık bir alan içerisinde rastgele bir char üretilir
select @char = char(round((Select Number from dbo.ViewRandomNumbers) * @StartChar + @CharRange, 0))
-- şifrede bulunması istenmeyen karakter olup olmama durumuna göre şifre üretilir ve sayac 1 azaltılır
if charindex(@char, @ExcludedChars) = 0 begin
set @Password =@Password+ @char
set @PasswordLength = @PasswordLength - 1
end
end
return(@Password)
end
Go</pre>
<p>Burada tanımlamış olduğumuz <strong>ufnGeneratePassword</strong> isimli fonksiyon parametre olarak üretilecek şifre uzunluğunu, bu şifrenin <strong>ASCII</strong> tablosundaki hangi değer aralığında olacağını ve şifre içerisinde olmasını istemediğimiz karakterleri almaktadır. Fonksiyon kendi içerisinde söz konusu <strong>ASCII</strong> değer aralığında bir üretim gerçekleştirmek için de, rastgele sayı üretme işini üstlenen bir <strong>View’</strong> dan yararlanmaktadır. Fonksiyonu aşağıdaki <strong>T-SQL</strong> ifadesinde olduğu gibi kullanıp bir kaç kez test ettiğimizde başarılı sonuçlar elde ettiğimizi görebiliriz.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">Declare @Password1 nvarchar(10)
Set @Password1= dbo.ufnGeneratePassword(10,65,29,'abcdefg')
Select @Password1 [Password]
Declare @Password2 nvarchar(10)
Set @Password2= dbo.ufnGeneratePassword(10,30,50,'/.+-|@')
Select @Password2 [Password]
Declare @Password3 nvarchar(10)
Set @Password3= dbo.ufnGeneratePassword(10,30,150,'0?*/&^#>é!')
Select @Password3 [Password]</pre>
<p><a href="https://www.buraksenyurt.com/pics/artcl_4_1.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_4_1" src="/pics/artcl_4_1_thumb.gif" alt="artcl_4_1" width="195" height="230" border="0" /></a></p>
<p>Bu ilginç ama bana göre oldukça işe yarayacak<strong> T-SQL</strong> ifadesinden sonra bir başkası ile devam edelim. Söz gelimi veritabanınızda yer alan belirli bir <strong>Şemaya(schema)</strong> ait tablolarınızı yeni bir schema adına taşımak istiyorsunuz<em>(İstemem demeyin <img class="wlEmoticon wlEmoticon-smile" style="border-style: none;" src="/pics/wlEmoticon-smile_26.png" alt="Smile" /> )</em> Bu durumda ne yaparsınız? <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_77.png" alt="Winking smile" /> Aslında geçtiğimiz yazımızdaki örneklerimizde kullandığımız<strong> system view</strong> nesnelerini göz önüne alırsak; öncelikli olarak ilgili şemadaki tabloları bulmamız ve her biri için dinamik bir<strong> T-SQL</strong> ifadesini yürütmemiz gerektiği ortadadır. Temel olarak şema transferi için aşağıdaki gibi bir <strong>T-SQL</strong> ifadesi kullanılabilir.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">alter schema YeniSchemaAdi transfer [HumanResources].[Employee]</pre>
<p>Söz gelimi bu ifade ile <strong>Employee</strong> tablosunun <strong>HumanResources</strong> şemasından <strong>YeniSchameAdi</strong> şemasına transfer edilmesi sağlanmaktadır. Ancak işi zorlaştıran kısım bu<strong> T-SQL</strong> ifadesinin dinamik olarak oluşturulması ve yürütülmesi sırasında ortaya çıkmaktadır. Dolayısıyla bir <strong>cursor</strong> kullanımı ve söz konusu şemaya ait tablolar üzerinden dolaşılması, diğer yandan her bir tablo için ilgili şema transfer işini üstlenen <strong>T-SQL</strong> ifadesinin dinamik olarak oluşturulması ve bu ifadeninde dinamik olarak çalıştırılması gerekmektedir. Aynen aşağıdaki T-SQL betik bloğunda görüldüğü gibi <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_77.png" alt="Winking smile" /></p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">-- Önce yeni bir schema üretelim
create schema HumanResourcesNew
go
declare @NewSchemaName sysname
declare @CursorObject sysname
declare @SqlExpression nvarchar(1000)
set @NewSchemaName = quotename('HumanResourcesNew')
-- sys.objects içerisinde dolaşıp HumanResources şemasına ait tüm kullanıcı tanımlı tabloları dolaşacak bir Cursor açıyoruz
declare crsr cursor for select quotename([name])from sys.objects where schema_id = schema_id('HumanResources') and type in ('U')
open crsr
fetch from crsr into @CursorObject
while @@fetch_status=0 begin
--her bir tablo için gerekli şema transfer etme T-SQL ifadesini üretiyoruz
set @SqlExpression = 'alter schema '+@NewSchemaName+' transfer [HumanResources].'+@CursorObject
print @SqlExpression
-- üretilen T-SQL ifadesini sp_executeSQL Stored Procedure' ü yardımıyla çalıştırıyoruz
exec sp_executeSQL @SqlExpression
fetch next from crsr into @CursorObject
end
close crsr
deallocate crsr</pre>
<p>bu T-SQL ifadesini yürüttüğümüzde HumanResources şemasındaki kullanıcı tanımlı tabloların, HumanResourcesNew şemasına taşındığını görürüz.</p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_4_2.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_4_2" src="/pics/artcl_4_2_thumb.gif" alt="artcl_4_2" width="333" height="271" border="0" /></a></p>
<p>Sizi bilmem ama ben çok eğleniyorum. Hız kesmeden farklı bir <strong>T-SQL</strong> ifadesi ve ihtiyacı ile devam edelim. Bu kez merak ettiğim şuydu. Acaba sistemimde ki veritabanlarında yer alan toplam kullanıcı tanımlı tablo sayıları ne kadardı?</p>
<p>Tüm veritabanlarını gezmek için yine sistem <strong>SP</strong>’ lerinden birisi olan sp_<strong>MSforeachdb’</strong> den yararlanabilirdim. Hatta daha önceden yaptığımız gibi bir <strong>temp</strong> tablo kullanıp tüm sonuçları buraya da aktarabilirdim. Hımm…Beni biraz uğraştıran bir sorgu oldu aslında. Nitekim toplam tablo sayısını bulmak için öncelikli olarak her bir veritabanı bağlantısı altında çalıştırılacak <strong>T-SQL</strong> ifadeleri gerekiyordu. Bir başka deyişle yine dinamik olarak üretilecek ve her bir veritabanı için çalıştırılacak bir <strong>T-SQL</strong> ifadesi söz konusuydu <img class="wlEmoticon wlEmoticon-confusedsmile" style="border-style: none;" src="/pics/wlEmoticon-confusedsmile_15.png" alt="Confused smile" /> Ancak biraz uğraştıktan ve epey bir hata aldıktan sonra aşağıdaki <strong>T-SQL</strong> sorgusunu yazmayı başarabilmiştim.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">-- Önce veritabanı adı ve buradaki toplam tablo sayısını tutacak olan Temp tabloyu üretelim
Create Table #AllTables
(
DbName varchar(50)
,TableCount int
)
--sp_Msforeachdb SP' sinden yararlanarak tüm veritabanlarını dolaşalım
EXEC sp_MSforeachdb '
USE
?
Declare @TableCount int
Set @TableCount=(Select Count(name) from sys.objects where type=''U'')
Insert into #AllTables Values (''?'',@TableCount)
' -- Her bir veritabanı için USE ile o veritabanı alanına geçiyor ve sys.objects' den yararlanarak toplam tablo sayılarını bulup @TableCount isimli değişkende tuttuğumuz bu sayıları ve güncel veritabanı adını insert sorgusu ile temp tabloya alıyoruz
Select * from #AllTables order by TableCount desc
Drop Table #AllTables</pre>
<p>ve işte sonuçlar <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_77.png" alt="Winking smile" /></p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_4_3.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_4_3" src="/pics/artcl_4_3_thumb.gif" alt="artcl_4_3" width="277" height="338" border="0" /></a></p>
<p>Merak ettiğim bir diğer konu ise <strong>Insert</strong> işlemlerine ilişkindi. Bazı hallerde bir <strong>Insert</strong> işlemi gerçekleştirildiğinde, insert edilen verilerin başka bir tabloya da aktarılması istenebilir. Söz gelimi bir tablo için gerçekleştirilen <strong>Insert</strong> işlemi sırasında, <strong>History</strong> bilgisini tutan başka bir tabloya da veri aktarımı yapılması sırasında... Burada aslında <strong>output</strong> anahtar kelimesi ve <strong>Inserted</strong> elemanının kullanıldığı bir ifade dizimi söz konusudur. Çoğumuz Insert işlemini bu tip bir şekilde çok fazla kullanmamışızdır eminim ki <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_77.png" alt="Winking smile" /> Senaryo gereği <strong>OziRestoran</strong> isimli bir veritabanı oluşturup üzerine <strong>Siparis</strong> ve <strong>SiparisTarihce</strong> isimli iki tablo ekledim.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">Create database OziRestoran
go
Create table Siparis
(
SiparisId int identity(1,1) primary key
,Aciklama nvarchar(250)
,Tarih date
)
Create table SiparisTarihce
(
SiparisTarihceId int identity(1,1) primary key
,SiparisId int
,Aciklama nvarchar(250)
,Tarih date
,Onaylayan nvarchar(20)
)
Go</pre>
<p><strong>Insert</strong> işlemimizde şunu yapmak istediğimizi düşünelim ; <strong>Siparis</strong> tablosuna bir satır eklenirken, üretilen otomatik <strong>SiparisId</strong>, <strong>Aciklama</strong> ve <strong>Tarih</strong> alanları değerlerinin, siparisi onaylayan kişi bilgisi ile birlikte tarihçe tablosuna yazdırılmasını istiyoruz <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_77.png" alt="Winking smile" /> İşte <strong>Insert</strong> sorgumuz.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">Use OziRestoran
Go
insert into Siparis( Aciklama, Tarih )
output inserted.SiparisId, inserted.Aciklama,inserted.Tarih,'bsenyurt'
into SiparisTarihce
(
SiparisId,
Aciklama,
Tarih,
Onaylayan
)
values ( 'Bir adet LG marka laptop sipariş edildi',GETDATE())
Go
Select * from Siparis
Select * from SiparisTarihce</pre>
<p>Görüldüğü üzere <strong>Insert</strong> ifadesi yazılırken <strong>output</strong> anahtar kelimesinden itibaren <strong>SiparisTarihce</strong> içerisine de veri aktarımının yapılacağı belirtilmektedir. Sonrasında <strong>values</strong> anahtar kelimesini takip eden kısımda, asıl <strong>Siparis</strong> tablosu için eklenecek içerik set edilmektedir. Sonuçlar aşağıdaki gibi olacaktır.</p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_4_4.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_4_4" src="/pics/artcl_4_4_thumb.gif" alt="artcl_4_4" width="540" height="159" border="0" /></a></p>
<p>Hazır <strong>Insert</strong> işlemlerinden konu açılmışken acaba içeriğini rastgele test verisi ile dolduracağımız devasa boyutlu tabloları nasıl oluşturabiliriz sorusu aklıma geldi <img class="wlEmoticon wlEmoticon-smile" style="border-style: none;" src="/pics/wlEmoticon-smile_26.png" alt="Smile" /> Aslında bu konuda bir önceki çalıştığım firmada <strong>Database Developer</strong> arkadaşlarımın yaptığı önemli çalışmalar vardı. Milyonlarca anlamlı veri yığını oluşturuyorlardı. Onların eline su dökemem belki ama en azından kendi çapımda bir şeyler yapabilirim diye düşündüm. İşe basit bir senaryo ile başladım. Örneğin rastgele <strong>Ad,Sodad,Şehir,Maaş</strong> ve <strong>Seviye</strong> bilgilerinden oluşacak bir veri tablosunu üretmeye çalıştım. Bu amaçla aşağıdaki gibi bir sorgu oluşturdum.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">Create Table Adlar
(
Ad nvarchar(50)
)
Create Table Soyadlar
(
Soyad nvarchar(50)
)
Create Table Sehirler
(
Sehir nvarchar(50)
)
Go
Insert into Adlar values ('Burak')
Insert into Adlar values ('Kamil')
Insert into Adlar values ('Burcu')
Insert into Adlar values ('Elif')
Insert into Adlar values ('Sinem')
Insert into Adlar values ('Hakan')
Insert into Adlar values ('Bill')
Insert into Adlar values ('Murat')
Insert into Adlar values ('Nazım')
Insert into Adlar values ('Cansu')
Insert into Soyadlar values ('Şenyurt')
Insert into Soyadlar values ('Kırmızı')
Insert into Soyadlar values ('Sucu')
Insert into Soyadlar values ('Salimoğlu')
Insert into Soyadlar values ('Arabacı')
Insert into Soyadlar values ('Kısakol')
Insert into Soyadlar values ('Odabaşı')
Insert into Soyadlar values ('Şamil')
Insert into Soyadlar values ('Limoncu')
Insert into Soyadlar values ('Kurtaran')
Insert into Sehirler values ('İstanbul')
Insert into Sehirler values ('İzmir')
Insert into Sehirler values ('Ankara')
Insert into Sehirler values ('Eskişehir')
Insert into Sehirler values ('Trabzon')
Insert into Sehirler values ('Antalya')
Insert into Sehirler values ('Gaziantep')
Insert into Sehirler values ('Manchester')
Insert into Sehirler values ('New York')
Insert into Sehirler values ('Samsun')
Insert into Sehirler values ('Aydın')
Insert into Sehirler values ('Moskova')
select
Ad
,Soyad
,Sehir
,Maas=ROUND(ABS(CHECKSUM(NEWID()))/10000,0)
,Level=ABS(CHECKSUM(NewId())) % 14
into PersonelTestTable FROM Adlar
cross join Soyadlar
cross join Sehirler
Select * From PersonelTestTable</pre>
<p>Şimdi burada işin püf noktası <strong>Adlar, Soyadlar</strong> ve <strong>Sehirler</strong> tablolarının <strong>CROSS JOIN</strong> ile birleştirilmesi ve <strong>PersonelTestTable</strong> içerisine atılması işlemidir. Çok doğal olarak ortaya <strong>1200</strong> satırlık <strong>(10 Ad X 10 Soyad X 12 Şehir)</strong> veri kümesi çıkacaktır <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_77.png" alt="Winking smile" /></p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_4_5.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_4_5" src="/pics/artcl_4_5_thumb.gif" alt="artcl_4_5" width="421" height="265" border="0" /></a></p>
<p>Eğer kombinasyon sayısını arttırırsanız kısa sürede milyonlarca satırdan oluşabilecek devasa test verileri üretebilirsiniz. Örneği geliştirmek sizin elinizde <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_77.png" alt="Winking smile" /></p>
<p>Böylece geldik bir yazımızın daha sonuna. Bu yazımızda sadece <strong>5</strong> çeşit <strong>T-SQL</strong> ifadesine değindik ancak inanıyorum ki ilerleyen zamanlarda bunlara yenilerini ekliyor olacağım. Çünkü bu iş çok eğlenceli olmaya başladı. Merak işte <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_77.png" alt="Winking smile" /> Tekrardan görüşünceye dek hepinize mutlu günler dilerim.</p>
<p><a href="https://www.buraksenyurt.com/pics/2011%2f11%2fEglencelikSQL_2.sql">EglencelikSQL_2.sql (11,65 kb)</a></p>2012-01-01T14:06:00+00:00t-sqltransact sqlsql serverschemafunctiontemp tableoutputinsertedsp_msforeachdbsp_executesqlsql functionsbsenyurtHatırlayacağınız üzere geçtiğimiz günlerde kafayı T-SQL ile bozmuş ve can sıkıntısından eğlenceli ifadeler yazmaya çalışmıştım. Sanırım söz konusu bu eğlence sonraki günlere de sirayet etti ve yine bir kaç eğlenceli T-SQL sorgusu ile karşınızdayım...https://www.buraksenyurt.com/pingback.axdhttps://www.buraksenyurt.com/post.aspx?id=206f099d-cb8d-4609-be46-6a079beb14480https://www.buraksenyurt.com/trackback.axd?id=206f099d-cb8d-4609-be46-6a079beb1448https://www.buraksenyurt.com/post/T-SQL-ile-Eglenmeye-Devam(Ikinci-Devre)#commenthttps://www.buraksenyurt.com/syndication.axd?post=206f099d-cb8d-4609-be46-6a079beb1448https://www.buraksenyurt.com/post/T-SQL-ile-Dinlenmece-EglenmeceT-SQL ile Dinlenmece Eğlenmece2011-12-14T16:25:00+00:00bsenyurt<p><a href="https://www.buraksenyurt.com/pics/fun.jpg"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; padding-top: 0px; border: 0px;" title="fun" src="/pics/fun_thumb.jpg" alt="fun" width="320" height="240" align="right" border="0" /></a>Merhaba Arkadaşlar,</p>
<p>Hiç canınızın sıkıldığı ve böyle bir buhran anına girdiğinizde <strong>SQL Server Management Studio</strong>' yu açıp <strong>T-SQL</strong> ile eğlenceli bir şeyler yapmaya çalıştığınız oldu mu? <img class="wlEmoticon wlEmoticon-smile" style="border-style: none;" src="/pics/wlEmoticon-smile_25.png" alt="Smile" /> </p>
<p>Açıkçası geçtiğimiz günlerde böyle sıkkın ve bıkkın bir ruh halindeyken ve konuşmak istediğim tüm arkadaşlarım yoğunken, ekranımda duran <strong>Management Studio</strong>' daki bembeyaz ve bomboş <strong>Query </strong>penceresi ile muhabbet etmeye karar verdim. Aslında amacım basitti. Daha önceki tecrübelerime dayanarak ihtiyaçlar dahilinde kullandığım <strong>T-SQL</strong> ifadelerini şöyle bir tekrar etmeye çalışacak ve siz değerli okurlarıma bir blog girdisi olarak sunacaktım. Aklıma geldikçe ihtiyaçlarımın <strong>T-SQL</strong> karşılıklarını yazmaya başladım. Düşündüğüm ilk gereksinim, sistemimde yüklü olan kaç veritabanı olduğunu ve bunlara ait bazı temel bilgileri edinmekti...İşte serüvenimiz bu ilk sorgumuz ile başlıyor.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">select
database_id [Id]
,name [Database Name]
,create_date [Create Date]
,Case [compatibility_level]
when '60' then 'SQL Server 6.0'
when '65' then 'SQL Server 6.5'
when '70' then 'SQL Server 7.0'
when '80' then 'SQL Server 2000'
when '90' then 'SQL Server 2005'
when '100' then 'SQL Server 2008'
else 'unknown'
end as [Compatibility Level]
,collation_name [Collation]
,Case is_fulltext_enabled
when 1 then 'Enabled'
else 'Disabled'
end as [FullText]
,user_access_desc [User Access]
,state_desc [State]
,snapshot_isolation_state_desc [Snapshot Isolation]
,Case is_read_only
when 1 then 'Yes'
else 'No'
end as [Read Only]
,Case is_broker_enabled
when 1 then 'Yes'
else 'No'
end as [Service Broker]
from sys.databases
order by [Database Name]</pre>
<p>Yukarıdaki SQL sorgusunu kullanarak sistemde var olan veritabanlarına ait bazı temel bilgileri elde edebiliriz. Söz konusu ifadenin çalışma zamanındaki çıktısı aşağıdakine benzer olabilir<em>.</em> Bu sonuçlada pek tabi benim sistemimde yer alan veritabanları ve onlara ait bilgileri bulunmaktadır.<em> (Ekran çıktısının orjinal halini görmek içim fotoğrafa tıklayın)</em></p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_3_1.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_3_1" src="/pics/artcl_3_1_thumb.gif" alt="artcl_3_1" width="640" height="154" border="0" /></a></p>
<p>Görüldüğü üzere sistemimde var olan veritabanlarının adlarını, oluşturulma zamanlarını, SQL Server uyumluluk sürümlerini, hangi Collation’ ı kullandıklarını ve bunlara benzer bilgilerini elde etmiş bulunuyoruz<em>(Doğruyu söylemek gerekirse SQL tarafında sys ön eki ile başlayan View’ lar içerisinde inanılmaz sürprizler bulumaktadır. İncelemediyseniz bile araştırmanızı şiddetle öneririm<img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_76.png" alt="Winking smile" /> )</em></p>
<p>Gelelim sıradaki sorgumuza. Bu sefer sistemimde kullanıcı tanımlı ne kadar <strong>table, stored procedure, function, view </strong>ve <strong>trigger </strong>varsa, <strong>şema adları(Schema Name)</strong> ile birlikte elde etmek istedim. Bunun içinde yine sys ön ekli view' lardan yararlanabiliriz. İşte sorgumuz;</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">select
S.name+'.'+O.name [Object]
,object_id [Id]
,type
,type_desc
,create_date [Create Date]
,modify_date [Modify Date]
from sys.all_objects O
join sys.schemas S on O.schema_id=S.schema_id
where type in ('U','V','TR','FN','P')
order by [Object]</pre>
<p>Dikkat edileceği üzere <strong>sys.schema </strong>ve <strong>sys.all_objects </strong>isimli sistem görünümlerinden yararlanmaktayız. <strong>sys.all_objects </strong>tüm veritabanı nesnelerini tutan bir görünüm sunmaktadır. Aslında sadece belirli bir veritabanı bağlantısı ile ilişkili olan nesnelere gitmek istersek <strong>sys.objects </strong>görünümünden de yararlanabiliriz. Söz konusu <strong>T-SQL </strong>ifademizin benim sistemimde ürettiği sonuçlar ise aşağıdaki gibidir.</p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_3_2.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_3_2" src="/pics/artcl_3_2_thumb.gif" alt="artcl_3_2" width="640" height="323" border="0" /></a></p>
<p>Eğlenceli değil mi? <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_76.png" alt="Winking smile" /> O zaman hıs kesmeden devam edelim. Çalıştığım sırada aklıma gelen ve merak ettiğim sorgulardan birisi de şuydu : Acaba sistemimde yer alan tablolarda kullanılan <strong>alanların(Columns)</strong> tablo bazlı toplam sayıları neydi? Çok doğal olarak hangi tabloda kaç alan kullanıldığını bilmek istiyordum. Bunun için basit bir SQL ifadesi yeterli olacaktı. Aynen aşağıdaki gibi.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">select
T.Name
,Count(C.column_id) [Total Column Count]
from sys.tables T
join sys.columns C on T.object_id=C.object_id
where T.type='U'
group by T.Name
order by Count(C.Column_id) desc</pre>
<p>Bu kez <strong>sys.tables </strong>ve <strong>sys.columns </strong>view nesnelerini ele alıp ve kullanıcı tanımlı tabloları adlarına göre gruplandırarak sonuca ulaşmaya çalıştım. Tabi kendi sistemimde bu tabloyu yürüttüğümde aşağıdaki ekran çıktısında yer alan sonuçları elde ettim.</p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_3_3.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_3_3" src="/pics/artcl_3_3_thumb.gif" alt="artcl_3_3" width="298" height="408" border="0" /></a></p>
<p>Tabi kendi sistemimde gayet makul seviyelerde rakamlara ulaştığımı ifade edebilirim. Nasıl ki kod yazarken bazı metrikleri uyguluyor ve örneğin satır sayısı 25i geçen metodları tespit edip kod standartları açısından denetlemeler yapıyoruz, benzer şekilde SQL tarafında da bu tip metrikleri uygulayabiliriz. Bu sorgu söz konusu metriklerden birisi olarak düşünülebilir. Tabi çalışmakta olduğum bankada aynı sorguyu denediğim de piuvvvvv <img class="wlEmoticon wlEmoticon-openmouthedsmile" style="border-style: none;" src="/pics/wlEmoticon-openmouthedsmile_20.png" alt="Open-mouthed smile" /> Ehem…Ehem…Tekrar sistemime döneyim.</p>
<p>Bu kez aklımda şöyle bir soru vardı : Acaba sistemimde yer alan <strong>AdventureWorks </strong>veritabanında, hangi <strong>Stored Procedure</strong>’ ler içerisinde <strong>Update </strong>anahtar kelimesi(Keyword) kullanılmaktaydı <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_76.png" alt="Winking smile" /> Bir başka deyişle hangi SP' ler içerisinde güncelleme ile ilişkili işlemler yapıldığını görmek istiyordum. Bu tip bir ihtiyaç pek çok durumda gerekebilir. Özellikle SQL tarafına yıkılmış iş süreçlerinde değişiklikler yapmanız gerektiği durumlarda kullanabileceğiniz bir tespit yöntemidir. Söz gelimi bir tablonun adının değişmesi sonucu ilgili SP' lerde de geçtiği yerlerde de pansumanlar yapmak gerekecektir<em>(SQL Tarafında Visual Studio' da olduğu gibi Refactor-Rename özelliği olsaydı fena olmazı aslında)</em>Bunu öğrenmek için aşağıdaki sorguyu kullandım.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">Use AdventureWorks
Go
select
SPECIFIC_CATALOG
,SPECIFIC_SCHEMA+'.'+SPECIFIC_NAME [SP NAME]
,ROUTINE_DEFINITION
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE' and ROUTINE_DEFINITION like '%UPDATE%'</pre>
<p>Görüldüğü üzere farklı bir <strong>View </strong>içerisinde <strong>Stored Procedure</strong>’ lerin <strong>T-SQL </strong>içerikleri de tutulmaktadır. Sorgunun sonucu olarak aşağıdaki ekran görüntüsünde yer alan çıktıları elde ettim.<em> (Size bir antrenman önerebilirim. Eğer çok sayıda veritabanı ve çok sayıda SP ile karmaşık iş süreçlerini barındıran bir sistemde görev alıyorsanız, örneğin içerisinde<strong> @@IDENTITY, BEGIN TRANSACTION </strong>gibi kritik terimleri içeren SP’ leri araştırmayı deneyebilirisiniz <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_76.png" alt="Winking smile" /> )</em></p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_3_4.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_3_4" src="/pics/artcl_3_4_thumb.gif" alt="artcl_3_4" width="640" height="99" border="0" /></a></p>
<p>Aklıma sorgu geldikçe geliyordu. Karşımdaki <strong>SQL Query</strong> pencersi iyi bir arkadaştım. Ne sorsam cevap veriyordu<em>(Yani sayılır)</em>. Gerçi bazen ırım kırım ediyor naz yapıyordu ama olsun <img class="wlEmoticon wlEmoticon-smile" style="border-style: none;" src="/pics/wlEmoticon-smile_25.png" alt="Smile" /> Şimdi merak ettiğim sorgu ise şuydu : Acaba bir veritabanında veya daha da iyisi tüm sistemde yer alan tabloların kapladıkları alanların boyut bilgileri nelerdi? Burada çözüme giderken biraz sıkıntı çektiğimi ifade etmek isterim. <strong>Query </strong>pencersi ile bir türlü mutabakat sağlayamadık. Önce tek ve herhangibir tablo için bunu öğrenmeye çalıştım. Bunun için tasarlanmış özel bir sistem SP’ si mevcuttu nitekim<em>(Sanırım Tüme varım yöntemi ile hareket edeceğim)</em></p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">Use AdventureWorks
Go
sp_spaceused 'Production.Product'</pre>
<p>Sonuç ise şöyleydi.</p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_3_5.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_3_5" src="/pics/artcl_3_5_thumb.gif" alt="artcl_3_5" width="349" height="68" border="0" /></a></p>
<p>Şimdi işi bir adım daha ileri götürmeliydim. Çünkü asıl amacım sistemde ne kadar tablo varsa her birinin boyutsal özelliklerini öğrenmekti<em>(Yani ne kadar alanı reserve ettikleri, bu alanın ne kadarını kullandıkları vb)</em> Bunun içinde aslında bir <strong>for each </strong>ifadesini çalıştırmam gerekiyordu. Yani her bir tabloyu gezmeli ve her biri için <strong>sp_spaceused </strong>SP’ sini çalıştırmalıydım. Bu <strong>foreach </strong>içinde aslında sistem de yer alan güzel bir SP bulunmaktadır. <img class="wlEmoticon wlEmoticon-winkingsmile" style="border-style: none;" src="/pics/wlEmoticon-winkingsmile_76.png" alt="Winking smile" /></p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"</pre>
<p>ve işte sonuç ;</p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_3_6.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_3_6" src="/pics/artcl_3_6_thumb.gif" alt="artcl_3_6" width="469" height="410" border="0" /></a></p>
<p>Aslına bakarsanız istediğim bilgileri elde etmiştim. Ancak görüntü pek hoş değildi. Keşke tablo bazlı bir <strong>ızgara çıktısı(Grid View)</strong> elde edebilseydim <img class="wlEmoticon wlEmoticon-confusedsmile" style="border-style: none;" src="/pics/wlEmoticon-confusedsmile_14.png" alt="Confused smile" /> Ama çaresiz değildim. Biraz <strong>Cursor, </strong>biraz <strong>Temp </strong>tablo işimi görebilirdi pekala. Kolları sıvadım ve uzun bir uğraştan sonra aşağıdaki SQL ifadesini yazmayı başardım.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">Use AdventureWorks
Go
declare @TableName nvarchar(100)
create table #TempTable
(
[Table Name] nvarchar(100),
[Row Count] varchar(100),
[Reserved Size] varchar(50),
[Data Size] varchar(50),
[Index Size] varchar(50),
[Unused Size] varchar(50)
)
declare tableCursor cursor forward_only
for
select S.name+'.'+T.[name]
from sys.tables T
join sys.schemas S on T.Schema_id=S.Schema_id
where T.type='U'
for read only
open tableCursor
while (1=1)
begin
fetch next from tableCursor into @TableName
if(@@FETCH_STATUS<>0)
break;
insert #TempTable exec sp_spaceused @TableName
end
close tableCursor
deallocate tableCursor
select * from #TempTable Order by [Table Name]
drop table #TempTable</pre>
<p>Aslında teori basitti. Tablo ve Şema adlarını elde ettikten sonra her birisi için <strong>sp_spaceused</strong> SP’ ini çalıştıracak ama sonuçlarını bir <strong>Temp </strong>tabloya ekleyecektim. Şimdi sonuçlar ve elde edilen görüntü çok daha güzeldi.</p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_3_7.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_3_7" src="/pics/artcl_3_7_thumb.gif" alt="artcl_3_7" width="517" height="405" border="0" /></a></p>
<p>Tam bu sorguyu bitirmiştim ki aklıma başka bir ihtiyaç geldi. Acaba sistemde yer alan tablo adlarının tamamını, aralarına virgül koyarak tek bir hücreye indirgiyebilir miydim? Hımm…Eğer kod tarafında olsaydık bu benim çocuk oyuncağı sayılırdı. Ama SQL özürlü birisi olarak biraz araştırma yapmam gerekecekti. Sonuçta <strong>COALESCE </strong>fonksiyonundan yararlanarak bu isteği karşılayabileceğimi gördüm. Nasıl mı?</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(COALESCE(@Names + ',', '') + Name, @Names)
FROM sys.tables
where type='U'
select @Names</pre>
<p>ve sonuç</p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_3_8.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_3_8" src="/pics/artcl_3_8_thumb.gif" alt="artcl_3_8" width="637" height="66" border="0" /></a></p>
<p><strong>Query Explorer</strong> ile olan sohbetim harika ilerliyordu. Bu kez ondan bana sistem de yer alan veritabanlarının ne zaman yedeklendiğini<em>(ve hatta yedeklenmediğini) </em>söylemesini istiyordum. Aslına bakarsanız bu önemli bir sorguydu. Çünkü ilk çalıştırdığımda <strong>AdventureWorks </strong>için hiç bir zaman <strong>Backup </strong>almadığımı fark etmiştim <img class="wlEmoticon wlEmoticon-confusedsmile" style="border-style: none;" src="/pics/wlEmoticon-confusedsmile_14.png" alt="Confused smile" /></p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">SELECT
D.name [Database Name]
,case when MAX(b.backup_finish_date) is NULL
then 'Bakcup Yok'
else Convert(varchar(100), MAX(b.backup_finish_date))
end AS [Last Backup Time]
FROM sys.databases D
LEFT JOIN msdb.dbo.backupset B ON D.name = B.database_name AND B.type = 'D'
WHERE D.database_id NOT IN (2)
GROUP BY D.name
ORDER BY [Database Name] DESC</pre>
<p>Tabi burada anahtar nokta <strong>backupset </strong>içeriğinden yararlanılmasıydı. İlk sonuçlarda hiç backup almamış olduğumu görünce, hemen bir tane ürettirdim ve yeni sonuçlara baktığımda aşağıdaki ekran görüntüsünde yer alan çıktıyı elde ettim.</p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_3_9.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_3_9" src="/pics/artcl_3_9_thumb.gif" alt="artcl_3_9" width="323" height="240" border="0" /></a></p>
<p>Derken aklıma biraz daha eğlenceli bir sorgu geldi. Söz gelimi çalışanlarımızdan<em>(Hani o anda koca bir fabrikanın sahibi olduğunu düşündüm de)</em> rastgele 5 farklı kişiyi getirip onlara hediye dağıtmak istediğimi düşündüm. <em>(Bu o gün indirimli olarak satılacak rastgele 10 ürün de olabilirdi)</em>. Eğlenceli bir sorguydu. <strong>NewId() </strong>fonksiyonu burada işi eğlenceli hale getiren kişiydi. Örnek olarak <strong>Employee </strong>tablosu için şanslı 5 kişiyi bulmaya çalıştım.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">Select
Top 5 NewId() Id
,EmployeeID
,Title
,BirthDate
,ManagerID
,VacationHours
from HumanResources.Employee
order by 1</pre>
<p>ve aşağıdaki ekran görüntüsünde yer alan sonuçları elde ettim. Tabi ki her çalıştırılmada farklı sonuçlar elde edilmesi garantiydi.</p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_3_10.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_3_10" src="/pics/artcl_3_10_thumb.gif" alt="artcl_3_10" width="626" height="448" border="0" /></a></p>
<p>Gerçi şimdi fark ettim ki <strong>114 </strong>numaralı çalışan oldukça şanslıymış. Çünkü ilk iki sorguda tesadüfen çıkmış <img class="wlEmoticon wlEmoticon-smile" style="border-style: none;" src="/pics/wlEmoticon-smile_25.png" alt="Smile" /> Buna tabi bir tedbir almak gerektiği kanısındayım. Aslında bu tedbiri size bırakıyorum. En azından hediye çıkmış işçileri bir flag ile işaretlemeye veya farklı bir tabloda belirli süreliğine saklayarak tekrardan sorgu sonuçlarında çıkmalarını engellemeyi düşünebilirsiniz.</p>
<p><strong>SQL </strong>sorgularını denediğim sırada arka planda çalışmakta olan diğer SQL penceresine gözüm ilişmişti. Aslında arada sırada oraya bakmak zorundaydım. Nitekim <strong>Test </strong>ortamında yer alan bir veritabanı üzerinde bazı işlemler yapılması gerekiyordu. Ne varki ilgili sistemde yer alan tablo <strong>32 milyon satırlık </strong>veri içerdiğinden ve test makinesi nuhnebiden kalma bir <strong>Pentium III </strong>olduğundan miniminnacık sıkıntılar vardı. O anda aklıma acaba <strong>index </strong>kullanılmayan<em>(örneğin Clustered Index) </em>tablolar var mıdır acaba sorusu geldi? Hemen local sistemimde bunu araştırmak için aşağıdaki sorgu ifadesini hazırladım.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">select
S.name+'.'+T.name AS [TableName]
from sys.tables T
inner join sys.schemas S
on S.schema_id = T.schema_id
where OBJECTPROPERTY(OBJECT_ID,'TableHasClustIndex') =0 and T.Type='U'
order by[TableName] ASC</pre>
<p>Bir de ne göreyim <img class="wlEmoticon wlEmoticon-smile" style="border-style: none;" src="/pics/wlEmoticon-smile_25.png" alt="Smile" /></p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_3_11.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_3_11" src="/pics/artcl_3_11_thumb.gif" alt="artcl_3_11" width="218" height="86" border="0" /></a></p>
<p><strong>AdventureWorks </strong>veritabanındaki <strong>ProductProductPhoto </strong>tablosunda <strong>ClusteredIndex </strong>yok…Bak bak baaakk <img class="wlEmoticon wlEmoticon-smile" style="border-style: none;" src="/pics/wlEmoticon-smile_25.png" alt="Smile" /> Tabi bu işin şaka tarafı ama performans araştırmaları yaparken belki de işe yarayacak bir sorgu olarak düşünülebilir.</p>
<p>İşler gayet eğlenceli gidiyordu ama enerjim de bitmek üzereydi. Son olarak basit bir sorgu yardımıyla<strong> Query </strong>penceresi ile olan muhabbetime son vereyim istemiştim. Bu sefer merak ettiğim, çevrede var olan SQL sunucularının hangileri olduğuydu. Aşağıdaki sorgu bunu karşılıyordu.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">Select
server_id Id
,name [Server Name]
,product [Product Type]
,provider [Provider Name]
,data_source [Data Source]
,catalog
,case is_data_access_enabled
when 1 then 'Enabled'
else 'Disabled'
end as [Data Access]
from sys.servers</pre>
<p>İşte sonuçlar,</p>
<p><a href="https://www.buraksenyurt.com/pics/artcl_3_12.gif"><img style="background-image: none; margin: 4px 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;" title="artcl_3_12" src="/pics/artcl_3_12_thumb.gif" alt="artcl_3_12" width="539" height="76" border="0" /></a></p>
<p>Elbette ben yerel makinemden sadece tek bir veri sunucusuna bağlandığımdan cılız bir sonuç çıkmıştı. Ancak aynı sorguyu arka planda çalışmakta olduğum test makinesinde yürüttüğümde piuvvvvv!!! <img class="wlEmoticon wlEmoticon-smile" style="border-style: none;" src="/pics/wlEmoticon-smile_25.png" alt="Smile" /> </p>
<p>Buraya kadar yazılmış olan sorguları eğlence amaçlı olarak veya ciddi manada göz önüne alarak çalışmakta olduğunuz gerçek hayat SQL sunucuları üzerinde de deneyebilirsiniz. Çok ilginç sonuçlar elde edeceğinizi ama oldukça faydalı bilgiler alabileceğinizi belirtmek isterim.</p>
<p>Peki ya bundan sonrasında ne olacak? Aslında bakarsanız burada yazılmış olan pek çok <strong>SQL </strong>ifadesi birer <strong>View </strong>haline dönüştürülüp sunucu üzerindeki farklı bir veritabanında saklanabilirler. Hatta bu veritabanının karşılığı olan bir <strong>Entity Framework </strong>kütüphanesi üretilip ilgili raporların örneğin bir <strong>WCF Data Service </strong>yardımıyla dış ortama sunulması da sağlanabilir. Tabi hassas veriler söz konusu olduğundan bu pek de iyi bir fikir değildir. Ama dilerseniz basit bir <strong>WCF(Windows Communication Foundation) Servisini </strong>güvenli hale getirerek ilgili içerikleri dış dünyaya servis bazlı olarak sunabilirsiniz. Sanırım bir sonraki makalemde hangi konuyu/senaryoyu ele alacağımı anlamışsınızdır <img class="wlEmoticon wlEmoticon-openmouthedsmile" style="border-style: none;" src="/pics/wlEmoticon-openmouthedsmile_20.png" alt="Open-mouthed smile" /> Tekrardan görüşünceye dek hepinize mutlu günler dilerim.</p>
<p><a href="https://www.buraksenyurt.com/pics/2011%2f11%2fEglencelikSQL.sql">EglencelikSQL.sql (5,39 kb)</a></p>2011-12-14T16:25:00+00:00sqlsys.objectssys.tablessys.columnsstored proceduresnewidsys.serverstransact sqlcursordatabaseadventure workssql server 2008 r2temp tablebsenyurtAçıkçası geçtiğimiz günlerde böyle sıkkın ve bıkkın bir ruh halindeyken ve konuşmak istediğim tüm arkadaşlarım yoğunken, ekranımda duran Management Studio' daki bembeyaz ve bomboş Query penceresi ile muhabbet etmeye karar verdim. Aslında amacım basitti. Daha önceki tecrübelerime dayanarak ihtiyaçlar dahilinde kullandığım T-SQL ifadelerini şöyle bir tekrar etmeye çalışacak ve siz değerli okurlarıma bir blog girdisi olarak sunacaktım. Aklıma geldikçe ihtiyaçlarımın T-SQL karşılıklarını yazmaya başladım. Düşündüğüm ilk gereksinim, sistemimde yüklü olan kaç veritabanı olduğunu ve bunlara ait bazı temel bilgileri edinmekti...İşte serüvenimiz bu ilk sorgumuz ile başlıyor.https://www.buraksenyurt.com/pingback.axdhttps://www.buraksenyurt.com/post.aspx?id=fa5e7b6a-451b-4d63-8913-e879c495a2d64https://www.buraksenyurt.com/trackback.axd?id=fa5e7b6a-451b-4d63-8913-e879c495a2d6https://www.buraksenyurt.com/post/T-SQL-ile-Dinlenmece-Eglenmece#commenthttps://www.buraksenyurt.com/syndication.axd?post=fa5e7b6a-451b-4d63-8913-e879c495a2d6https://www.buraksenyurt.com/post/Sql-2005-XML-Veri-Tipini-XSD-ile-Dogrulamak-bsenyurt-com-danSql 2005 XML Veri Tipini XSD ile Doğrulamak2006-04-21T12:00:00+00:00bsenyurt<p>Değerli Okurlarım Merhabalar,</p>
<p>Sql Server 2005 ile birlikte gelen en büyük yeniliklerden birisi, yeni XML veri tipidir. XML veri tipini tablolarda alanlar, stored procedure' lerde ve fonksiyonlarda parametreler veya değişkenler için kullanabilmekteyiz. Ancak asıl iyi olan nokta, XML veri tipinden herhangibir içeriğin, XSD şemaları yardımıyla doğruluğunun kontrol altına alınabilmesidir. Bir XML şeması ile ilişkilendirilmiş ve doğruluğu bu şema bilgisinde verilen kriterlere göre sağlanacak olan XML verisine, türlendirilmiş XML (Typed XML) adı verilmektedir. <em>(Tam karşıtı olan Untyped XML verisi ise sadece well-formed olarak tanımlanmış XML içeriğini işaret etmektedir.)</em></p>
<blockquote>
<p>Typed XML verileri, çalışma zamanında Untyped XML verilerine göre daha yüksek performans sağlar. Çünkü Untyped XML içeriğinde, elementlere ve niteliklere ait veriler string formatında tutulmakta olup çalışma zamanında gereksiz yere tür dönüşümlerinin olmasına neden olmaktadır. Oysaki Typed XML verisinin içeriğinde yer alan element ve niteliklerin veri türleri zaten şemada belirtilen türlerden olmak zorunadır. Bu da çalışma zamanında gereksiz tür dönüşümlerini engelleyerek yüksek performans sağlamaktadır.</p>
</blockquote>
<p>Peki Sql Server 2005 üzerinde, özellikle bir tablo alanının veri tipini XML olarak belirttiğimizde, bu alanın içeriğini bir XML şeması ile (XML Schema) nasıl ilişkilendirebiliriz. Herşeyden önce, XML veri tipi ile ilişkili olan şema bilgilerinin nasıl ve ne şekilde tutulduğunu bilmekte fayda var. Sql Server 2005 üzerinde bir XML şeması çoğunlukla bir şema koleksiyonuna (Schema Collection) eklenerek kullanılmaktadır. Diğer taraftan sistemde yer alan bir kaç tablo üzerinde de, bu şema içerisinde yer alan isim alanlarına (namespaces), elementlere (elements), niteliklere (attributes) vb... ait bilgiler saklanmaktadır. Dolayısıyla çalışma zamanında bir XML veri alanının içeriğini kontrol etmek için, Sql Server 2005 sistemine kayıt edilmiş (register) şema koleksiyonlarından faydalalanılmaktadır. Öncelikle bir şema bilgisini Sql Server 2005 sistemine nasıl kayıt edebileceğimize bakalım. Elimizde aşağıdaki gibi bir şema olduğunu düşünelim. <em>(Var olan bir XML dökümanının şema bilgisini Visual Studio.Net ortamında Create Schema seçeneği yardımıylada oluşturabilirsiniz. Ben aşağıdaki örnek şemayı bu teknik ile oluşturdum ve Sql Server 2005 sistemine kayıt edilebilecek hale getirdim.)</em></p>
<p><img src="/makale/images/mk158_4.gif" alt="" width="556" height="143" border="0" /></p>
<pre class="brush:xml;auto-links:false;toolbar:false" contenteditable="false"><?xml version="1.0" encoding="utf-16"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.bsenyurt.com/Kitaplar" xmlns="http://www.bsenyurt.com/Kitaplar">
<xs:element name="Kitaplar">
<xs:complexType>
<xs:sequence>
<xs:element name="Kitap">
<xs:complexType>
<xs:sequence>
<xs:element name="Ad" type="xs:string" />
<xs:element name="Fiyat" type="xs:int" />
<xs:element name="Basim" type="xs:date" />
<xs:element name="Yazarlar">
<xs:complexType>
<xs:sequence>
<xs:element name="Yazar" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="ID" type="xs:unsignedShort" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema></pre>
<p>Bu şema bilgisinde Kitaplar root elementi içerisinde yer alan Kitap elementi tipinden boğumlar (nodes) yer almaktadır. Her bir Kitap boğumu içerisinde ID isimli ve unsignedShort tipinden nitelikler (attributes) olmak zorundadır. Kitap boğumları (nodes) içerisinde string tipinden Ad, integer tipinden Fiyat ve date veri tipinden Basim elementleri yer almaktadır. Ayrıca her Kitap boğumu (node) içerisinde string tipinden Yazar elementlerini taşıyan, Yazarlar isimli alt boğumlarda (Childe Nodes) yer almaktadır. Kısacası aşağıdaki örnek XML içeriğine ait bir şema yapısı söz konusudur.</p>
<pre class="brush:xml;auto-links:false;toolbar:false" contenteditable="false"><Kitaplar>
<Kitap ID="1000">
<Ad>Her Yönüyle C#</Ad>
<Fiyat>50</Fiyat>
<Basim>2001-01-01Z</Basim>
<Yazarlar>
<Yazar>Sefer Algan</Yazar>
</Yazarlar>
</Kitap>
</Kitaplar></pre>
<p>Yukarıdaki gibi bir şema(Schema) bilgisini sisteme kayıt edebilmek için Sql Server 2005 üzerinde aşağıdaki sorgu cümlesini çalıştırmamız gerekmektedir. Bu cümle ile yukarıdaki şema bilgisini sisteme KitapSchema XML şema koleksiyonu olacak şekilde eklemekteyiz.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">IF EXISTS (SELECT schema_id FROM sys.XML_schema_collections WHERE name='KitapSchema')
BEGIN
RAISERROR('Şema zaten var...',16,1)
END;
CREATE XML SCHEMA COLLECTION KitapSchema
AS
N'<?xml version="1.0" encoding="utf-16"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.bsenyurt.com/Kitaplar" xmlns="http://www.bsenyurt.com/Kitaplar">
<xs:element name="Kitaplar">
<xs:complexType>
<xs:sequence>
<xs:element name="Kitap">
<xs:complexType>
<xs:sequence>
<xs:element name="Ad" type="xs:string" />
<xs:element name="Fiyat" type="xs:int" />
<xs:element name="Basim" type="xs:date" />
<xs:element name="Yazarlar">
<xs:complexType>
<xs:sequence>
<xs:element name="Yazar" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="ID" type="xs:unsignedShort" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'</pre>
<p>Bu sql cümlesini çalıştırdığımız takdirde, sistemde yer alan belirli tablolara şemamız ile ilgili bilgiler eklenecektir. Bunların bir kısmını görmek için aşağıdaki ekran görüntüsünde yer alan sorguları çalıştırabilirsiniz. Bu bilgiler gördüğünüz gibi sistem tablolarında tutulmaktadır. Özetle şemamızın içeriği ayrıştırılarak tablolara dağıtılmıştır.</p>
<p><img src="/makale/images/mk158_1.gif" alt="" width="633" height="602" border="0" /></p>
<p>Dikkat ederseniz en tepede şema koleksiyonumuzun (KitapSchema) yer aldığı sistem tablosuna bakıyoruz. Burada şema koleksiyonumuz için oluşturulan xml_collection_id alanının, namespaces, elements ve attributes sistem tablolarında nasıl yer aldığına dikkat ediniz. Gördüğünüz gibi, şemamız içerisindeki her bir ayrıntı sistem tablolarına yazılmaktadır. Özellikle namespaces sistem tablosundaki isim alanı bizim için önemlidir. Buradaki isim alanını, şema bilgisini uygulamak istediğimiz XML veri tiplerinde kullanacağız. Varsayılan olarak, Visual Studio.Net gibi bir ortamda şema dosyanızı hazırladıysanız eğer (ki ben böyle yaptım) encoding formatının utf-8 olduğunu ve buradaki gibi http://www.bsenyurt.com/Kitaplar adında bir isim alanının eklenmediği görürsünüz. Burada Utf-8 formatını mutlaka Utf-16 olarak çevirmeliyiz. Nitekim Sql Server 2005 özellikle şema bilgilerinde sadece Utf-16 formatını desteklemektedir. Ayrıca, Sql Server 2005 içerisindeki XML verilerinin bu şemayı kullanabilmesi içinde, namespaces sistem tablosuna bir adın eklenmiş olması gerekmektedir. Bu amaçlada ayrıca bir xmlns' i eklememiz gerekti. Aksi takdirde, namespaces sistem tablosunda name alanı boş olan bir satır elde ederiz.</p>
<pre class="brush:xml;auto-links:false;toolbar:false" contenteditable="false"><?xml version="1.0" encoding="utf-16"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.bsenyurt.com/Kitaplar" xmlns="http://www.bsenyurt.com/Kitaplar"></pre>
<p>Artık sistemde, KitapSchema isminde bir XML şema koleksiyonumuz mevcuttur. Sıra geldi bunu nasıl kullanacağımıza. Sql Server 2005 Management Studio' da görsel olarak tabloları oluştururken alana ait veri tipini XML olarak seçtikten sonra bu alana ait özelliklerden Schema Collection' ı kullanarak şema bilgisini ekleyebiliriz. Örneğin aşağıdaki resimde görüldüğü gibi Kitap isimli alanın veri tipi XML olarak belirlenmiştir. Daha sonra ise Schema Collection özelliğinde bizim az önce eklediğimiz KitapSchema şema koleksiyonu seçilmiştir.</p>
<p><img src="/makale/images/mk158_2.gif" alt="" width="549" height="367" border="0" /></p>
<p>Benzer kurallar, tablomuzu sorgu cümlesi ile oluştururken de geçerlidir. Örneğin aşağıdaki sql cümlesinde, yukarıdaki tabloya ait script yer almaktadır. Gördüğünüz gibi XML veri tipini belirlerken içeriğin dbo.KitapSchema nesnesi tarafında denetleneceği belirtilmektedir.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">CREATE TABLE dbo.BookBase
(
ID int IDENTITY(1,1) NOT NULL,
Kitap xml(CONTENT dbo.KitapSchema) NOT NULL,
CONSTRAINT PK_BookBase PRIMARY KEY CLUSTERED
(
ID ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
)
ON PRIMARY</pre>
<p>Şimdi gelin, Kitap alanımıza örnek bir XML verisini eklemeye çalışalım. Elbette, eklenecek olan verinin buraya konulabilmesi için KitapSchema şemasının söylediği kurallara uyması beklenmektedir. Bunu sağlamak için, eklemek istediğimiz XML içeriğinde mutlaka ve mutlaka XML isim alanımız uygulanmalıdır. Aksi takdirde aşağıdaki gibi bir hata mesajı alırız.</p>
<p><img src="/makale/images/mk158_5.gif" alt="" width="434" height="294" border="0" /></p>
<p>Aşağıdaki insert sorgusunda geçerli bir veri girişi yapılmaktadır.</p>
<pre class="brush:sql;auto-links:false;toolbar:false" contenteditable="false">INSERT INTO dbo.BookBase VALUES
(N'<Kitaplar xmlns="http://www.bsenyurt.com/Kitaplar">
<Kitap ID="1000">
<Ad>Her Yönüyle C#</Ad>
<Fiyat>50</Fiyat>
<Basim>2001-01-01Z</Basim>
<Yazarlar>
<Yazar>Sefer Algan</Yazar>
</Yazarlar>
</Kitap>
</Kitaplar>')</pre>
<p>Şimdi şema bilgimizin çalışıp çalışmadığını kontrol edebileceğimiz örnek bir insert sorgusu çalıştıralım. Örneğin Basim elementine geçerli olmayan bir tarih bilgisi girelim. Bu durumda, çalışma zamanında bir istisna alırız ve satırın tabloya eklenmediğini görürüz.</p>
<p><img src="/makale/images/mk158_3.gif" alt="" width="541" height="311" border="0" /></p>
<p>Son olarak sistemde yer alan bir şema koleksiyonunu kaldırmak istediğimizde her zamanki gibi drop anahtar sözcüğünü aşağıdaki gibi kullanmamız gerekecektir.</p>
<pre class="brush:csharp;auto-links:false;toolbar:false" contenteditable="false">IF EXISTS (SELECT schema_id FROM sys.XML_schema_collections WHERE name='KitapSchema')
BEGIN
DROP XML SCHEMA COLLECTION KitapSchema
END;</pre>
<p>Görüldüğü gibi, Sql Server 2005 üzerinde yer alan XML tipindeki alanların bir şema yardımıyla kontrolü son derece kolay ve etkilidir. Çalışma zamanında sağlanan performansın yanı sıra, XML içeriğinin bizim belirleyeceğimiz şema kurallarına uygun bir biçimde doğrulanması oldukça önemlidir. Böylece geldik bir makalemizin daha sonuna bir sonraki makalemizde görüşünceye dek hepinize mutlu günler dilerim.</p>2006-04-21T12:00:00+00:00sql 2005xmlxsdxml validationvalidationbsenyurtSql Server 2005 ile birlikte gelen en büyük yeniliklerden birisi, yeni XML veri tipidir. XML veri tipini tablolarda alanlar, stored procedure' lerde ve fonksiyonlarda parametreler veya değişkenler için kullanabilmekteyiz. Ancak asıl iyi olan nokta, XML veri tipinden herhangibir içeriğin, XSD şemaları yardımıyla doğruluğunun kontrol altına alınabilmesidir. Bir XML şeması ile ilişkilendirilmiş ve doğruluğu bu şema bilgisinde verilen kriterlere göre sağlanacak olan XML verisine, türlendirilmiş XML (Typed XML) adı verilmektedir. (Tam karşıtı olan Untyped XML verisi ise sadece well-formed olarak tanımlanmış XML içeriğini işaret etmektedir.)https://www.buraksenyurt.com/pingback.axdhttps://www.buraksenyurt.com/post.aspx?id=086ccecc-32cf-420e-a008-8e9afb9f7de30https://www.buraksenyurt.com/trackback.axd?id=086ccecc-32cf-420e-a008-8e9afb9f7de3https://www.buraksenyurt.com/post/Sql-2005-XML-Veri-Tipini-XSD-ile-Dogrulamak-bsenyurt-com-dan#commenthttps://www.buraksenyurt.com/syndication.axd?post=086ccecc-32cf-420e-a008-8e9afb9f7de3