SQL Server’de Neden “Sayılar” Tablosuna İhtiyaç Duyarız

SQL Server belli sayı aralığında işlem yapmak için genellikle while döngüsü kullanılır. Oysa elimizin altında ardışık sayıların bulunduğu bir tablo bulunursa her defasında while ile sözkonusu aralıktaki sayıları bulmaktansa basit Join işlemleriyle başı ve sonu verilmiş tarihsel, sayısal ve metinsel bir aralık oluşturulabilir. Bu yazıda böyle bir tablonun nasıl oluşturulacağı ve ne tür durumlarda kullanabileceğimizi örneklendireceğiz. Bu çalışmanın T-SQL ile ilgili iyi bir egzersiz olacağını düşünüyorum.
T-SQL aracılığıyla X ve Y aralığında sayıların bulunduğu bir tablo oluşturmanın bir çok yöntemi vardır. Bu yöntemlerin başında hiç şüphesiz while döngüsünü kullanmaktır. Öncelikle Sayilar isminde bir tablo oluşturalım.

CREATE TABLE Sayilar
	(Sayi INT NOT NULL PRIMARY KEY CLUSTERED);

Bu tabloda Sayi isminde tek bir kolon var. Bu kolon otomatik artan sayı özelliğine sahiptir yani tabloya eklenecek her satır için Sayi kolonuna yeni ardışık bir rakam eklenecektir. Şimdi While ile bu tabloya 1000’e kadar kayıt ekleyelim.

DECLARE @x INT;
SET @x = 1;
WHILE @x 
Bu işlemden sonra Sayilar tablosuna 1'den 1000'e kadar sayılar eklenir. 
<img src="http://www.ahmetkaymaz.com/wp-content/uploads/Sayilar_Tablosu_1.jpg"/>
Böyle bir tablo oluşturmanın 2. yöntemi çok sayıda kayıt içeren bir tablodan 1000 kayıt okunur IDENTITY fonksiyonu kullanılarak her kayıt için sayısal bir değer üretilir ve bu liste Sayilar ismindeki tabloya insert edilir.
IDENTITY (data_type [ , seed , increment ] ) AS column_name

IDENTITY fonksiyonu ancak INTO ile birlikte kullanılabildiği için Sayilar tablosunun daha önce oluşturulmamış olması gerekir.

-- Sayilar tablosunu silelim
DROP TABLE Sayilar;
GO
--İçerisinde çok kayıt bulunan Musteri tablosundan 1000 kayıt okuyoruz
--Sıralı sayısal liste 1'den başlayıp 1'er 1'er ilerlesin
SELECT TOP 1000 IDENTITY(int,1,1) AS Sayi
INTO Sayilar
FROM Musteri
GO
-- Tablo için bir primary key oluşturalım
ALTER TABLE Sayilar
ADD CONSTRAINT PK_Sayilar PRIMARY KEY CLUSTERED (Sayi);

Bu sorgulara yakın olarak aşağıdaki gibi de Sayilar tablosu oluşturulabilir.

CREATE TABLE Sayilar
(
	Sayi smallint IDENTITY(1, 1) PRIMARY KEY
)
GO

WHILE 1 = 1
BEGIN
	INSERT INTO Sayilar DEFAULT VALUES

	IF @@IDENTITY = 1000
	BEGIN
		BREAK
	END
END

SQL Server 2005 ile birlikte gelmiş olan ROW_NUMBER fonksiyonunu da kullanarak ardışık rakamlar elde edebiliriz. Bir sıralama fonksiyonu (ranking function) olan ROW_NUMBER, parametre olarak aldığı kriterlere uygun kayıtları sıralı olarak numaralandırır. Çok kaydı olan bir tablodan 1000 kayıt olup bunları ardışıl olarak numaralandırabiliriz. Bunun için elimizde büyük bir tablo yoksa SQL Server’in standart katalog tablosu olan sysobjects tablosunu kendisiyle çapraz join ederek çok kayıtlı bir liste elde edebiliriz.

SELECT TOP 1000 ROW_NUMBER() OVER(order by T1.name) as Sayi
 from sysobjects T1 cross join sysobjects T2

Başka bir yöntem olarak SQL Server 2005’te Common Table Expressions (CTE) yapısını kullanmaktır. Bilindiği gibi CTE sayesinde recursion (öz-yinelemeli) işlemler basitçe yapılabilmektedir. Buradaki mantık sayısal bir değişken tanımlayıp bu değişkeni 1 artıran sorguyu rekürsif olarak çağırmaktır.

WITH SayilarCTE(x)
AS(
	SELECT x = 1
	UNION ALL
	SELECT x = x + 1 FROM SayilarCTE WHERE x 
Mevcut yöntemler içeriside CTE yönteminin en hızlı seçenek olduğunu söyleyebiliriz. Peki bu tablo hangi sorgularda yardımcı araç olarak kullanabiliriz. 
<b><u>Virgüllü değerleri çözmek (split işlemi)</u></b>
İlk örnek olarak virgül veya başka karakterle birbirinden ayrılmış metinsel ifadeleri (CSV -comma seperated values) çözmek için bu tablodan yararlanabiliriz. Eğer ifadenin içerisinde sadece sayısal değerler varsa yapacağımız şey Sayilar tablosundaki sayilari CHARINDEX aracılığıyla sözkonusu metin içerisinde aramaktır. CHARINDEX fonksiyonu iki parametre alıp ilk parametredeki değerin ikinci parametre içerisindeki konumunu döndürür. Eğer ilk değer, ikinci değer içerisinde geçmiyorsa geriye 0 döndürür. Aşağıdaki örnekte 14,8,1,5,3,2 ifadesinin çözülmesi sağlanmıştır.
DECLARE @metin VARCHAR(255)
SET @metin = '14,8,1,5,3,2' 

SELECT Sayi
    FROM Sayilar
    WHERE CHARINDEX
    (
        ','+CONVERT(VARCHAR(12),Sayi)+',',
        ','+@metin+','
    ) > 0
    ORDER BY Sayi

1
2
3
5
8
14

Eğer çözülecek ifade içerisinde sadece sayısal değil metinsel (string) ifadeler de varsa aşağıdaki gibi düzenleme yapılır. Virgüllü ifadeye geçmeden önce bir kelimedeki harfleri listeleyen bir sorgu yazalım.
DECLARE @Kelime varchar(50)
SET @Kelime=’İstanbul’

SELECT Harf
FROM
(
SELECT TOP 100 PERCENT SUBSTRING(@Kelime, Sayi, 1) AS Harf
FROM Sayilar
–Sayilar tablosunda ifadenin uzunluğu kadar satır okuyacağız
WHERE Sayi
İ
s
t
a
n
b
u
l

Bu ifadedeki tekil harfleri listlemek için içteki sorguya aşağıdaki DISTINCT eklemek yeterli olacaktır.
DECLARE @Kelime varchar(50)
SET @Kelime=’Ankara’

SELECT Harf
FROM
(
SELECT DISTINCT TOP 100 PERCENT SUBSTRING(@Kelime, Sayi, 1) AS Harf
FROM Sayilar
–Sayilar tablosunda ifadenin uzunluğu kadar satır okuyacağız
WHERE Sayi
A
k
n
r

Herhangi bir metin içerisindeki sayıları bulalım.
DECLARE @Kelime varchar(50), @Sayi varchar(50)
SET @Kelime=’İstanbul 29 Mayıs 1453”te fethedildi.’
SET @Sayi=”

SELECT @Sayi = @Sayi + Harf
FROM
(
SELECT SUBSTRING(@Kelime, Sayi, 1) AS Harf
FROM Sayilar
–Sayilar tablosunda ifadenin uzunluğu kadar satır okuyacağız
WHERE Sayi
Sonuç : 291453
Şimdi virgüllü bir ifadeyi çözümleyelim.
DECLARE @metin VARCHAR(255)
SET @metin = ‘İstanbul,Ankara,İzmir,Gaziantep’

SELECT SUBSTRING
(
‘,’ + @metin + ‘,’,
Sayi + 1,
CHARINDEX
(
‘,’,
‘,’ + @metin + ‘,’,
Sayi + 1
) – Sayi – 1
)
FROM Sayilar
WHERE Sayi >= 1
AND Sayi
İstanbul
Ankara
İzmir
Gaziantep

Silinmiş identity değerleri bulmak.
Sayilar tablosunu kullanabileceğimiz ikinci örnek ise IDENTITY türündeki bir kolonda silinmiş değerleri bulmaktır. Bilindiği gibi bu tür kolonlarda bir kayıt silindiği zaman silinen kayda ait ID bilgisi bir daha başka bir satıra verilmez. Böylece silme işlemlerinden sonra identity zincirinde kopukluklar olacaktır. Aşağıdaki örnekte tabloda 8 kayıt oluşturulmuş ve ardından listeden 2 kayıt silinmişti. Alt kısımdaki sorgu silinen bu 2 kaydın identity bilgisini listeleyecektir.
CREATE TABLE OrnekTablo
(
SatirId INT IDENTITY(1,1)
)
–Örnek tabloya kayıtlar ekleyelim.
INSERT OrnekTablo DEFAULT VALUES
INSERT OrnekTablo DEFAULT VALUES
INSERT OrnekTablo DEFAULT VALUES
INSERT OrnekTablo DEFAULT VALUES
INSERT OrnekTablo DEFAULT VALUES
INSERT OrnekTablo DEFAULT VALUES
INSERT OrnekTablo DEFAULT VALUES
DELETE OrnekTablo WHERE SatirId = 3
INSERT OrnekTablo DEFAULT VALUES
DELETE OrnekTablo WHERE SatirId = 1
GO
SELECT Sayi
FROM Sayilar
WHERE Sayi NOT IN (SELECT SatirId FROM OrnekTablo)
AND Sayi
1
3

Belli aralıktaki tarihleri bulmak.
Üçüncü örnek olarak belli aralıktaki tarihleri listeleyeceğiz. Bunun için normalde while ile döngü kurmak gerekirdi. Burada while döngüsü kullanmak yerine Sayilar tablosunu referans alarak verilmiş olan ilk tarihten son tarihe varıncaya kadar tarihe gün sayısı eklenecektir.
DECLARE @IlkTarih SMALLDATETIME, @SonTarih SMALLDATETIME
SET @IlkTarih = ‘20070101’
SET @SonTarih = ‘20070110’

SELECT @IlkTarih + Sayi
FROM Sayilar
WHERE @IlkTarih + Sayi
2007-01-02 00:00:00
2007-01-03 00:00:00
2007-01-04 00:00:00
2007-01-05 00:00:00
2007-01-06 00:00:00
2007-01-07 00:00:00
2007-01-08 00:00:00
2007-01-09 00:00:00
2007-01-10 00:00:00

Kaynak : http://www.aspfaq.com

Bir cevap yazın

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

Time limit is exhausted. Please reload CAPTCHA.