SQL Server Programlama ve Yönetim İpuçları – I

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

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

Time limit is exhausted. Please reload CAPTCHA.