Bu yazıda T-SQL’de her an ihtiyaç duyabileceğimiz SQL Server’in kendi içinde bulunan veya bulunmayan metod ve ipuçlarını vereceğiz. Yeni bir hint ile karşılaştıkça yazının devamına ekleyeceğiz. Ayrıca sizin de kullandığınız kısa bir yöntem varsa paylaşmanızı diliyoruz.
1) FormatNumber fonksiyonu
T-SQL’de MS Access, VBScript’ten bildiğimiz FormatNumber benzeri bir fonksiyon bulunmamaktadır. Internet’te bu işlemi yapan birçok fonksiyon bulunabilir ama en kısa yöntem olarak CONVERT metodu kullanılabilir. CONVERT metodu, money formatında değer alarak 0,1,2 seçenekli style’ları uygulayabilir. Eğer değer money türünde değil decimal türündeyse o değeri money türüne çevirmek gerekir. Aşağıdaki ifadeleri kullanarak, para, ondalıklı sayıları biçimlendirebiliriz.
DECLARE @mSayi MONEY SELECT @mSayi = 123456789.456 --Formatsız Round eder SELECT CONVERT(VARCHAR,@mSayi,0) --123456789.46 --Sayıyı, virgülle formatlar ve Round eder SELECT CONVERT(VARCHAR,@mSayi,1) --123,456,789.46 --Herhangi bir format uygulamaz SELECT CONVERT(VARCHAR,@mSayi,2) --123456789.4560 --Decimal türünde değer olursa money'e dönüştürmeliyiz DECLARE @dSayi DECIMAL(15,4) SELECT @dSayi = 123456789.456 SELECT CONVERT(VARCHAR,CONVERT(MONEY,@dSayi),1) --123,456,789.46
2) Ortanca kaydı bulma
Üzerinde Identity tanımlı bir tablodaki ortanca kaydı bulmak için tablonun yarısı okunur ve sonuçlar içindeki son kayıt alınır.
SELECT TOP 1 MusteriId
FROM (SELECT TOP 50 PERCENT MusteriId
FROM MUSTERI
ORDER BY MusteriId ASC) AS M
ORDER BY MusteriId DESC
3) Kaç X karakteri içeriyor
Bazı sorgulamalarda bir string içerisinde bir karakterin kaç defa geçtiğine ihtiyacımız olabilir. Bunun için string’in uzunluğundan string içerisinde o karakter olmadığı zamanki uzunluğunu çıkarmamız yeterlidir.
DECLARE @Str varchar(50) SET @Str='Ahmet,Mehmet,,Ayşe' --Bu ifadede kaç tane virgül var. PRINT LEN(@Str)-LEN(REPLACE(@Str,',',''))--3
4) Transaction Log Shrink
En çok sorulan sorular biri de herhangi bir veritabanının transaction log’unun nasıl küçültüleceğidir. Bulunduğunuz database’in log dosyasını incelemek için DBCC LOGINFO(‘MUSTERI’) ve tüm database’lerin log dosyası büyüklüklerini öğrenmek için DBCC SQLPERF(LOGSPACE) ifadesi kullanılabilir. Genel olarak tüm database’i shrink etmek için DBCC SHRINKDATABASE herhangi bir data veya log dosyasını sıkıştırmak için DBCC SHRINKFILE deyimi kullanılır.
Aşağıdaki kod, MUSTERI database’ine ait log dosyasını 2 MB’a indirir.
DBCC SHRINKFILE(Musteri_log, 2) --Logtaki boşlukları silmek üzere truncate edelim. BACKUP log Pubs WITH TRUNCATE_ONLY --Log dosyasını küçültelim DBCC SHRINKFILE(Musteri_log, 2)
Veya sp_detach_db ile öncelikle database, detach edilir ardından log dosyası diskten silinir. Ve diskteki MDF dosyası sisteme sp_attach_db ile attach edilir. Log dosyasının shrink yapıldığı zaman artık hiçbir şekilde log dosyasından restore işleminin yapılamayacağını ve bundan sonra otomatik olarak shrink işleminin yapılabilmesi içn database’in Recovery Model seçeneğinin Simple olarak seçilmesi ve Auto Shrink seçeneğinin işaretlenmesi gerektiği unutmamalıyız.
5) Tüm Index’leri Rebuild Etmek
Üzerinde UPDATE, DELETE, INSERT işlemlerin çok yapıldığı veritabanlarında belli dönemlerde tüm tabloların indekslerini yeniden yapılandırmak gerekir. Bunun için farklı yöntemler kullanılsa da en kısa yöntem DBCC REINDEX deyiminin kullanılmasıdır. Bu bakımı her tablo için yapmak için bu deyime tablo adını referans olarak geçmemiz gerekir. Herhangi bir ifadeyi tüm nesneler için çalıştırmak için tek tek yazmak yerine ya cursor ya da sp_msForEachTable prosedürünü daha pratik bir çözüm olmaktadır. Aşağıdaki cursor yapısı tüm tablolardaki indexleri yeniden şekillendirir.
DECLARE Crs CURSOR FOR --Kullanıcı tablolarını listeleyim SELECT NAME FROM sys.sysobjects WHERE xtype = 'u' OPEN Crs DECLARE @TabloAdi varchar(25) --Cursor'da satır satır ilerleyelim FETCH NEXT FROM Crs into @TabloAdi WHILE @@fetch_status = 0 BEGIN DBCC DBREINDEX(@TabloAdi) FETCH NEXT FROM Crs into @TabloAdi END CLOSE Crs DEALLOCATE Crs
sp_msForEachTable prosedürü, belgelenmemiş(undocumented) bir prosedür olup parametre olarak aldığı komutu tüm tablolar için çalıştırır.
EXEC sp_MSforeachtable @command1="DBCC DBREINDEX ('?')"
Bütün database’ler için bir komut çalıştırmak için sp_MSforeachdb prosedürü kullanılır.
6) Unicode String
Client uygulamalardan veya herhangi bir şekilde SQL Server’da sorgulama yaparken işlem yapılan parametreler, NCHAR, NVARCHAR veya NTEXT gibi UNICODE karakter setini destekleyen türler olduğu zaman bu parametrelerin başına “N”(Büyük ne harfi) önekini eklemek gerekir. “N”, National Language ifadesinin kısaltılmışı olup kullanıcının gönderdiği değerin unicode olarak yorumlanmasını sağlar. Eğer string değerlerin başına “N” eki kullanılmazsa o SQL Server, gönderilen değeri non-Unicode olarak yorumlayacak bu da bazı sorgulamaların yanlış sonuç getirmesine neden olabilmektedir.
Encoding hikayesini anladığımızda bu işlerin neden böyle yapılması gerektiğini daha iyi anlamış oluruz. Bilindiği gibi biz insanların kullandığı harflerin, karakterlerin bilgisayarlara tanıtılması ASCII denen süreçle başladı. 60’lı yıllarda İngilizce karakterleri sayısal cihazlara tanıtmak amacıyla ASCII(American Standard Code for Information Interchange) denen bir çizelge oluşturuldu. Bilgisayar sistemleri sadece sayılarla işlem yapabildikleri için İngiliz karakterlerin her birine bir değer verilerek ASCII çizelgesi oluşturuldu. Bu çizelge, 8.bit parity bit(hata kontrolü) olarak kullanılmak üzere 7 bitlik(2^7 = 128 dizi) bir karakter seti olarak geliştirildi. Bu tablo, tüm sayısal cihazların belleğine standart olarak konuldu böylece sayısal cihazlar ile insanlar arasındaki iletişimin sorunsuz sağlanmış oldu. Klavyede yazdığımız her karakter(sayısal, alfabetik) bütün bilgisayarların bildiği ASCII tablosundaki değerine göre işlenir. Örneğin “A” harfinin ASCII tablosundaki kodu, 41’dir.
Standart ASCII setine göre 128 karakter tanımlanabilmekteydi. Fakat daha sonra IBM tarafından 8.bit te kullanılarak 128-255 arasındaki rakamlarla ifade edilebilecek yeni karakter listesi eklendi. Extended ASCII tablosu veya IBM karakter seti olarak tanımlanan bu çizelge 8 bitlik (2^8=256) kelime dizisi uzunluğuna sahip olmuş oldu. Daha sonraki çalışmalarda bu tablo, ANSI(American National Standards Institute) tarafından standart hale getirildi; 0-127 arasındaki değerler, tüm sistemlerde aynı olacak, 128-255 arasındaki değerler de ülkelerin karakter yapısına göre kullanılacaktır. Bunun için de her ülkeye bir code page verildi ve ülkeler bu code page bilgisine göre, 128’den sonraki değerleri kendi karakterleri için kullanmaya başladılar.
Fakat zamanla yetersiz gelen ASCII yerine dünyadaki tüm karakterleri içerecek UNICODE isimli evrensel karakter kodlama sistemi oluşturuldu. 16’bitlik olan bu sistemde, her karakter 2 byte ile ifade edilmektedir. Sonuçta “A” harfi, 10’luk sistemde 65, ASCII sisteminde 0×41 ve UNICODE sisteminde 0×0041 ile ifade edilerek “1000001¨ Binary Code ile işlem görür. Bu sistemde her karakter çift byte yer kapladığı için veritabanı sistemlerinde bilinçli kullanılması gerekir. En basit, bir notepad dosyasını açıp içerisine “Ahmet” yazdığımızda ve dosyayı ANSI formatında kaydettiğimizde dosyanın boyutu 5 byte olur. Aynı dosyayı UNICODE olarak kaydettiğimiz 12 byte yer kaplar. Buradaki fazlalık olan 2 byte bu dosyanın UNICODE formatında kayıt edildiğinin bilgisini tutar.
Şimdi bu bilgilerin SQL tarafındaki yansımasına geri dönelim;
ASCII sadece metinleri tanımlamak için kullanıldığı için metin dışındaki veriler ASCII ile ifade edilemez.
SQL Server’da char, varchar, text gibi ASCII tabanlı veri türleri, veritabanını diline ait code page’i kullanılır. Unicode ise code page’i değerlendirmez. Unicode değerler, Collation bilgilerine sahiptir. Bu bilgi, onların sıralamalarda veya büyük-küçük harf duyarlılığı konusunda nasıl davranacaklarını bildirir.
“Heráclito” ifadesi, karakter tipinde bir değerdir(character constant) “????????” ise global, evrensel karakter tipinde bir değerdir(unicode constant). Char veya NChar tipinde iki kolonlu bir tabloya bu değerleri aktaralım.
INSERT TB(ChrKolon,NChrKolon)
VALUES('Heraclito','Heraclito')
Bu sorguyu çalıştırdığımızda TB tablosuna “Heráclito” değeri eklenmiş olacaktır. Unicode değeri ekleyelim.
INSERT TB(ChrKolon,NChrKolon)
VALUES('????????','????????')
Bu işlemden sonra tabloya baktığımızda her iki kolonda “??????” değerinin olduğunu görürüz. Çünkü sözkonusu rusça ifadeyi SQL Server’e gönderdiğimizde bunun UNICODE formatında olduğunu ifade etmedik. İfadenin başına “N” ifadesini ekleyerek değerin SQL Server tarafında UNICODE olarak yorumlanmasını sağlayalım.
INSERT TB(ChrKolon,NChrKolon)
VALUES('????????',N'????????')
Char tipindeki kolon için “N” ekini ekleyip eklememiz birşeyi değiştirmeyecek çünkü Char tip olarak ancak ASCII değerlerini destekler.
7) Her Güne Ait Backup Dosyası
Blindiği gibi SQL Server üzerindeki Maintenance Plans bölümü kullanılarak sistem üzerindeki tüm database’ler için özel isimde belli peryotlarda otomatik olarak backup alınmasını sağlayabiliriz. Buradaki özel isimden kastımız, örneğin her gün aynı backup isminde backup aldığımızda önceki güne ait backup dosyasının üzerine yazılır. Oysa her güne ait fullbackup’a ihtiyacımız olabilir. Bu durumda her güne ait backup dosyasını o günün tarihiyle kaydetmek daha mantıklı olacaktır. Aşağıdaki kod, Maintenance Plans’taki gibi çalıştığı zaman o günün tarihini referans alarak backup dosyasını isimlendirir.
DECLARE @Sql varchar(1000) DECLARE @tarih char(16) DECLARE @tarih_format char(12) DECLARE @dosya_ad varchar(50) DECLARE @backup_path varchar(100) --Backup dosyasının konumunu ve adını tanımlayalım --Bugünki Tarih : 2007-05-01 12:00 SET @tarih=CONVERT(char(16), GETDATE(), 120) --Tarihteki "-" ve ":" karakterlerini yok edelim SET @tarih_format=REPLACE(REPLACE(REPLACE(@tarih, '-', ''), ' ', ''), ':', '') --@tarihformat 200705011200 SET @dosya_ad='Musteri_'+ @tarih_format+'.BAK' --Dosyanın sonuna backup alınan tarih ve saati bastıralım. SET @backup_path= 'C:\Musteri_' + @dosya_ad --C:\Yedek\Musteri_200705011200.BAK SET @sql = 'BACKUP DATABASE CRM' + CHAR(13)+CHAR(10) SET @sql = @sql + 'TO DISK = ' + QUOTENAME(@backup_path,'''') +CHAR(13)+CHAR(10) SET @sql = @sql + 'WITH' + CHAR(13)+CHAR(10) SET @sql = @sql + ' NOINIT,' + CHAR(13)+CHAR(10) SET @sql = @sql + ' NAME = ' + QUOTENAME(@dosya_ad,'''') /* print @sql--Aşağıdaki script oluşturulmuş olur. BACKUP DATABASE CRM TO DISK = 'C:\Yedek\CRM_200705011200.BAK' WITH NOINIT, NAME = 'CRM_200705011200.BAK' */ --PRINT @sql EXEC(@sql)
8 ) Bir Kolonu Sıralı Olarak Güncellemek
Aşağıdaki örnekte tablodaki bir kolon ardışık sayılar olacak şekilde güncellenmiştir.
--Tabloyu oluşturalım CREATE TABLE Ornek(SatirId INT, AdSoyad CHAR(10)) GO --Ornek tablosuna birkaç kayıt girelim INSERT Ornek SELECT NULL, 'kayit1' UNION SELECT NULL, 'kayit2' UNION SELECT NULL, 'kayit3' UNION SELECT NULL, 'kayit4' UNION SELECT NULL, 'kayit5' GO --Tabloyu yeni ID'lerle güncelleyelim DECLARE @ID INT SET @ID = 0 UPDATE Sequence SET @ID = SatirId = @ID + 1