SQL Server daki tablolalarım disk üzerinde ne kadar yer kaplıyor

Bugün, database'imizde bulunan tüm tabloların datalarının ve indexlerinin ne kadar yer tuttuğunu gösteren bir stored procedure yazacağız. Aşağıdaki kod ile sistemdeki tüm tabloların bilgilerini görebiliriz.

SELECT * FROM sys.tables

sp_MStablespace @TableName sistem prosedürü SQL Server 2000'de de vardı. SQL Server 2005'de de çalışıyor. Çalıştırıldığında tek satırlık 3 kolonluk bir resultset dönüyor.

• Rows: Tabloda kaç satır var


• DataSpaceUsed : Tablodaki veriler ne kadar yer kaplıyor


• IndexSpaceUsed :Tablonun index leri ne kadar yer kaplıyor

Bize örnek oluşturması için bir test tablosu yaratalım içine biraz data ekleyelim.

CREATE TABLE Test(ID int identity(1,1),TextVal varchar(200))
GO
DECLARE @i int

SET @i=1

WHILE @i<=100 BEGIN
-- Rastgele birşeyle oluşturalım
INSERT INTO Test(TextVal)
SELECT Replicate('ab',@i)
SET @i = @i+ 1
END


SELECT * FROM Test

ID	TextVal
1	ab
2	abab
3	ababab
4	abababab
5	ababababab
6	abababababab
7	ababababababab
8	abababababababab
9	ababababababababab
...	....

100. satırda, 100 kere 'ab' içeren bir datamız olacak.

Yarattığımız tabla ne kadar yer kaplıyormuş bakalım:

exec sp_MStablespace 'Test'

Rows	DataSpaceUsed	IndexSpaceUsed
100	16	8

Bütün tabloların boyutlarını öğrenmek istersek sys.tables view sistem view ini kullanarak tüm tabolar için bunu çalıştırabiliriz

CREATE TABLE #TableSize(TableName nvarchar(255),Rows int,DataSpace int, IndexSpace int)

DECLARE @Table nvarchar(255)


DECLARE oCur CURSOR READ_ONLY FAST_FORWARD FOR

SELECT name

FROM sys.tables

ORDER BY name


OPEN oCur

WHILE (1=1) BEGIN

FETCH NEXT FROM oCur INTO @Table

IF @@FETCH_STATUS <> 0 BREAK


INSERT INTO #TableSize(Rows,DataSpace,IndexSpace)

EXEC SP_MSTABLESPACE @table


-- Hangi tablo olduğu bilgisi proc. den dönmüyor. Null olarak yazdık. Şimdi güncelleyelim

UPDATE #TableSize SET TableName = @Table WHERE TableName is null

END

CLOSE oCur

DEALLOCATE oCur

SELECT * FROM #TableSize

Şimdi bunu başka bir yöntem ile yapalım.
Tablonun boyutunu öğrenmek için kullanılabilecek bir sistem prosedürü daha var : sp_spaceused @TableName
Bu procedure biraz daha farklı bir sonuç döndürüyor ancak dönen resultset içerisinde tablo nun ismi de var.

exec sp_spaceused 'Test' sonucu:

name	rows	reserved	data	index_size	unused
Test	100	24 KB	16 KB	8 KB	0 KB

Buradan dönen değerleri yine bir tabloya yazalım ama bu sefer cursor yerine farklı bir yöntem kullanalım.




Daha çok tüm tabloların indexlerini yeniden yaratmak için kullandığım bir sistem prosedürü var.

EXEC sp_MSforeachtable @command1='Command String'


@command1 e verilen sql ifadesini çalıştırır. Tablo parametresi geçmek için command içerisinde tablo adı yerine ? geçilir

CREATE TABLE #TableSize
(
[name] nvarchar(255),
[rows] int,
[reserved] varchar(20),
[data] varchar(20),
[index_size] varchar(20),
[unused] varchar(20)
)
-- Mevcut database deki tüm tablolar için sp_spaceused proc. ü çağırılıp, dönen recordset #TableSize tablosu eklenir
EXEC sp_MSforeachtable @command1=
"INSERT INTO #TableSize
EXEC sp_spaceused '?'"

SELECT * FROM #TableSize

sp_rename ile nesne ismi değiştirmek

sp_rename

Genel yazım şekli:

exec sp_rename @object_name, @new_object_name, @object_type

Programlanabilir object lerde kullanımı :

Dikkat!!! : sp_rename syscomments deki text alanındaki nesne ismini değiştirmez. Bu nedenle "stored procedure", "function", "trigger" ve "view" gibi, programlanabilir nesneler için kullanılmamalıdır. Bu tür nesneler de drop edip, yeni ismi ile create ederek yeni obje ismi değiştirilebilir.

Şimdi bir örnekle bunu açıklayalım..

Basit bir stored procedure oluşturalım. Sonuç olarak "This is a test" mesajı döndürsün..

CREATE PROCEDURE sp_myTest
as
SELECT 'This is a test' as result

GO

Aşağıdaki SQL cümlesini kullanarak syscomments de nesnenin script ine bakabiliriz :

SELECT text
FROM syscomments WHERE id = object_id(N'sp_myTest')

Bu sql cümlesini çalıştırdığınızda :

create procedure sp_myTest ..... result isimli kolonda ile başlayan bir ifade göreceksiniz..

Şimdi de sp_rename i kullanarak nesnenin adını değiştirelim..

exec sp_rename 'sp_myTest','sp_myTest_newName'

Bakalım gerçekten değişmiş mi? Bir önceki kodu tekrar çalıştıralım.

SELECT text
FROM syscomments WHERE id = object_id(N'sp_myTest')

Biraz önce bir sonuç dönen sql cümlesi artık bir sonuç döndürmüyor. Çünkü sp_myTest isimli bir nesnemiz artık yok. Yeni isim ile çalıştıralım bakalım ne göreceğiz..

SELECT text
FROM syscomments WHERE id = object_id(N'sp_myTest_newName')

create procedure sp_myTest ..... result isimli kolonda ile başlayan bir ifade göreceksiniz..

Biz objenin adını değiştirip sp_myTest_newName olarak değiştirdik ve syscomments den eski isimle çağırdığımızda bir sonuç gelmediğini de gördük. Ancak text alanı hala sp_myTest olarak kalmış durumda..

Aslında sp_rename i çalıştırdığımız da aşağıdaki hatayı almıştık. Bu hafta yukarıdaki durumu açıklıyor..
Caution: Changing any part of an object name could break scripts and stored procedures.

Books Online daki tavsiye de programlanabilir objectlerde sp_renam kullanmayın. Objeyi drop edin, yeni isimle create edin..


Tablo adının değiştirilmesi :

myTable isimli bir tablo yaratalım.. Yaratılan her bir nesne nin bir id si olur ve sql servarda nesneler id leri ile saklanırlar. object_id system function ını kullanarak objenin id sini öğrenebilir ve yaratılıp yaratılmadığından da böylecek emin olabiliriz. object_id (objectname) null döndürüyorsa o isimde nesne ilgili database de yok demektir.

Bir başka yöntem de SELECT * FROM TabloAdı nı kullanmak olabilir.

CREATE TABLE myTable (ID int,Name varchar(10))

SELECT object_id(N'myTable')

exec sp_rename 'myTable','myTable_new'

SELECT object_id(N'myTable_new')

SELECT * FROM myTable
Invalid Object Name 'myTable' hatası döndürecektir.

SELECT * FROM myTable_new

cümlesini çalıştırarsınız, ID ve Name isimli 2 kolon dönecektir. İçerisinde hiç bir data olmadığı için bir data gösterilmeyecektir..

Kolon adının değiştirilmesi :

Biraz önce yarattığımız ve adını myTable_new olarak değiştirdiğimiz tablonun name kolonunu name_new olarak değiştirelim..

sp_rename prosedürünün bizim şimdiye kadar kullanmadığımız, default u 'object' olan bir parametresi daha var. Kolonlar, sysobjects de yer almazlar ve birer database objesi değillerdir ve tablo ismi olmadan da birşey ifade etmezler. x tablonun y kolonu diye saklanırlar ve çağırılırlar. Bu yüzden ismini değiştirdiğimiz nesnenin kolon tipinde olduğunu ve hangi tabloya ait olduğunu da söylememiz gerekir.

exec sp_rename 'myTable_new.name','name_new','COLUMN'

myTable_new tablomuza ait olan name isimli kolonu name_new olarak değiştirdik..

SELECT * FROM myTable_new

kodunu çağırarak kolon adının değiştiğini görebiliriz.

SQL Server 2005 de Sıralama (Rank) ve Sıra numarası verme işlemleri

Diyelim ki sitenizde bazı makaleler yayımlıyor ve en çok okunan makaleleri görmek istiyorsunuz..
Bunu basit bir sql cümlesi ile nasıl yaparız?
Birkaç yöntemi olmakla birlikte SQL SERVER 2005 ile gelen Rank() ve Dense_Rank() fonksiyonlarından bahsetmek istiyorum..


Sıralama (Ranking) Fonksiyonları (Rank(),Dense_Rank(),Row_Number() ):
[ranking_func_name] OVER ([partition_by_clause] order_by_clause)


1. ROW_NUMBER() fonksiyonu:
Her satır için 1 den başlayarak ardışık numara üretir. Her satır için farklı sayı ürettiğinden aynı gruplama içerisinde bir sayı sadece bir kez geçer.

Örnek Yazım şekli:

SELECT
ROW_NUMBER() Over( ORDER BY MakaleAdi ) as Row_No_Tumu,
ROW_NUMBER() Over( Partition BY Tur ORDER BY MakaleAdi ) as Row_No_TureGore
FROM @Okunma

2. RANK() fonksiyonu:
Verilen sıralamaya göre aynı değere sahip satırlar, aynı sıra numarasını alırlar. En sonuncu sıra numarası, kayıt sayısına eşittir.
Örneğin "Bir yarışmaya 100 kişi katılır, 100 kişiden 99 u aynı puanı alırsa, 99 kişi 1. olurken, diğer kişi 100. (sonuncu) olur." şeklindeki bir sıralamayı hesaplamak için kullanılır.

Örnek yazım şekli:
SELECT
Rank() Over(PARTITION BY NULL
ORDER BY OkunmaSayisi DESC) as Siralama_Tumu,
Rank() Over(PARTITION BY Tur
ORDER BY OkunmaSayisi DESC) as Siralama_TureGore
FROM @Okunma


3. DENSE_RANK() fonksiyonu:
Rank fonksiyonu gibi çalışır ancak üretilen rakamlar arasında boşluk olmaz.
Örneğin "Bir yarışmaya 100 kişi katılır, 100 kişiden 99 u aynı puanı alırsa, 99 kişi 1. olurken, diğer kişi 2. olur." şeklindeki bir sıralamayı hesaplamak için kullanılır.

Örnek yazım şekli:

SELECT
DENSE_RANK() Over(PARTITION BY NULL
ORDER BY OkunmaSayisi DESC) as Siralama_Tumu,
DENSE_RANK() Over(PARTITION BY Tur
ORDER BY OkunmaSayisi DESC) as Siralama_TureGore
FROM @Okunma


partition_by_clause
Fonksiyonun uygulandığı listeyi gruplara bölmek için kullanılır. Kullanılan sıralama fonksiyonu partition by da yazılan her gruba ayrı ayrı uygulanır ve grup değişiminde fonksiyon hesaplamaya en baştan başlar, sadece bu grup için yapar. Partition by ifadesi yazılmazsa veya "partition by null" yazılırsa tüm kayıt seti için fonksiyon çalışır.

order_by_clause
Sıralama fonksiyonlarının hangi sıraya göre hesaplanacağını burada ifade ederiz.

Aşağıdaki örnek de,makale adına göre bir sıra numarası vermek istersek

SELECT Tur,MakaleAdi,OkunmaSayisi,
ROW_NUMBER() Over( Partition BY NULL ORDER BY MakaleAdi ) as Row_No_Tumu,
ROW_NUMBER() Over( Partition BY Tur ORDER BY MakaleAdi ) as Row_No_TureGore
FROM @Okunma



Aşağıdaki tüm sql cümlesini kopyalayıp çalıştırırsanız, hafızade bir tablo yaratıp içerisine örnek veriler ekleyecek ve istediğimiz sorguları çalıştıracaktır

---

-- SQL Script begins

DECLARE @Okunma TABLE
(MakaleID int, MakaleAdi varchar(100), Tur varchar(10),OkunmaSayisi int)

-- Örnek verileri ekleyelim
INSERT INTO @Okunma(MakaleID,OkunmaSayisi,Tur)
SELECT 1,10,'SQL'
UNION SELECT 2,10,'ASP'
UNION SELECT 3,15,'SQL'
UNION SELECT 4,10,'ASP'
UNION SELECT 5,30,'ASP'
UNION SELECT 6,18,'ASP'
UNION SELECT 7,10,'SQL'
UNION SELECT 8,30,'SQL'
UNION SELECT 9,20,'SQL'
UNION SELECT 10,20,'ASP'
UNION SELECT 11,9,'ASP'

-- Makale isimlerini oluşturalım...
UPDATE @Okunma Set MakaleAdi = 'Makale_' + CAST(MakaleID as varchar)


-- Tüm makelelerin listesi
SELECT * FROM @Okunma ORDER BY Tur,MakaleID


SELECT Tur,MakaleAdi,OkunmaSayisi,
Rank() Over(PARTITION BY NULL
ORDER BY OkunmaSayisi DESC) as Siralama_Tumu,
Rank() Over(PARTITION BY Tur
ORDER BY OkunmaSayisi DESC) as Siralama_TureGore,
Dense_Rank() Over(PARTITION BY NULL
ORDER BY OkunmaSayisi DESC) as Siralama_Tumu_2,
Dense_Rank() Over(PARTITION BY Tur
ORDER BY OkunmaSayisi DESC) as Siralama_TureGore_2
FROM @Okunma
ORDER BY Tur, Siralama_TureGore


SELECT Tur,MakaleAdi,OkunmaSayisi,
ROW_NUMBER() Over( Partition BY NULL ORDER BY OkunmaSayisi DESC,Tur ) as Row_Okunma

FROM @Okunma

-- SQL Script end

---

Bu sql cümlelerini çalıştırdığınızda 3 farklı resultset göreceksiniz.

1.@Okunma tablosuna eklediğimiz tüm kayıtlar :

MakaleID	MakaleAdi	Tur	OkunmaSayisi
2	Makale_2	ASP	10
4	Makale_4	ASP	10
5	Makale_5	ASP	30
6	Makale_6	ASP	18
10	Makale_10	ASP	20
11	Makale_11	ASP	9
1	Makale_1	SQL	10
3	Makale_3	SQL	15
7	Makale_7	SQL	10
8	Makale_8	SQL	30
9	Makale_9	SQL	20

2. Okunma sayısına göre makalelerin sıralanması :

Tur	MakaleAdi	OkunmaSayisi	Siralama Tumu	Siralama TureGore	Siralama Tumu_2	Siralama TureGore_2
ASP	Makale_5	30	1	1	1	1
ASP	Makale_10	20	3	2	2	2
ASP	Makale_6	18	5	3	3	3
ASP	Makale_4	10	7	4	5	4
ASP	Makale_2	10	7	4	5	4
ASP	Makale_11	9	11	6	6	5
SQL	Makale_8	30	1	1	1	1
SQL	Makale_9	20	3	2	2	2
SQL	Makale_3	15	6	3	4	3
SQL	Makale_7	10	7	4	5	4
SQL	Makale_1	10	7	4	5	4

Siralama Tumu kolonu : "Rank() Over(PARTITION BY NULL ORDER BY OkunmaSayisi DESC) as Siralama_Tumu" kodu ile oluşturuldu.
Makale_5 ve Makale_8 isimli makaleler 30 hit alarak en çok okunan 1. makale olmayı başarmışlar.
Rank ifadesini biraz daha açalım..
Partition by null ile, sayıları tekrar başlatmak istediğimiz herhangi bir kolon olmadığını, ilgilendiğimiz tüm kayıtlar için sıralama yapılmasını istediğimizi ifade ediyoruz.
Sıralamanın neye göre yapılacağı bilgisi ise "ORDER BY" ifadesinden sonra yazdığımız alan isimleri ile belirtiliyor. Biz "OkunmaSayisi" alanındaki verileri dikkate alacağız ancak en çok okunan makalenin daha üste gelmesini istediğimiz için sıralamanın büyükten küçüğe yapılmasını istediğimizi DESC ifadesini de ekleyerek söylüyoruz.. Buradaki "ORDER BY" ifadesinin yazımı ve anlamı select cümlesinde kullandığımız order by ile aynı... Birden fazla alana göre sıralama yapmak istersek, kolon isimlerini "," ile ayırarak yan yana yazabiliriz..

3. Satır sayısı:

Tur	MakaleAdi	OkunmaSayisi	Row_Okunma
ASP	Makale_5	30	1
SQL	Makale_8	30	2
ASP	Makale_10	20	3
SQL	Makale_9	20	4
ASP	Makale_6	18	5
SQL	Makale_3	15	6
ASP	Makale_4	10	7
ASP	Makale_2	10	8
SQL	Makale_1	10	9
SQL	Makale_7	10	10
ASP	Makale_11	9	11

ROW_NUMBER() fonksiyonu:

Aşağıdaki örnek de, ilk 8 makale için, makale adına göre bir sıra numarası vermek istiyoruz.. İlk kolonda (Row_No_Tumu) de 1 den 8 kadar makale adına göre sıralı numara üretmesi için "partition by null" yazıyoruz. Row_No_TureGore kolonunda ise, her 2 tür içerisinde yine makale adına göre sıralı bir numara üretmesini istiyoruz. ancak her tür için numarayı 1 den başlatmasını da istiyoruz..

SELECT Tur,MakaleAdi,OkunmaSayisi,
ROW_NUMBER() Over( Partition BY NULL ORDER BY MakaleAdi ) as Row_No_Tumu,
ROW_NUMBER() Over( Partition BY Tur ORDER BY MakaleAdi ) as Row_No_TureGore
FROM @Okunma


Örnek sonuç :

Tur	MakaleAdi	Okunma Sayisi	Row_No Tumu	Row_No TureGore
SQL	Makale_1	10	1	1
ASP	Makale_2	10	2	1
SQL	Makale_3	15	3	2
ASP	Makale_4	10	4	2
ASP	Makale_5	30	5	3
ASP	Makale_6	18	6	4
SQL	Makale_7	10	7	3
SQL	Makale_8	30	8	4

Makale_2 isimli makale tüm sıra numarasında 2 numarayı alırken, türe göre de 1 numarasını alıyor. Makale_2 den önce gelen Makale_1 başka bir türde yazılmış bir makale..

Satırlardaki Bilgileri Kolonlarda Birleştirme (Transpose)

Bir değişken tablo tanımlayalım ve bu tabloya 3 satırlık data ekleyelim.. Amacımız bu satırları tek bir satırda, araları "," ile ayrılmış olarak göstermek

DECLARE @Multiple TABLE(TestColumn varchar(20))

INSERT INTO @Multiple

SELECT 'Test 1'

UNION SELECT 'Test 2'

UNION SELECT 'Test 3'

SELECT * FROM @Multiple

Tüm tabloyı çekersek aşağıdaki kayıt setini görebiliriz.

TestColumn

Test 1

Test 2

Test 3

Aslında özel birşey yapmıyoruz. FOR XML PATH'in xml tag leri yaratmasını ve aynı xml tag'ine alacağı satırları birleştirmesi özelliğini kullanıyoruz.

SELECT ', ' + TestColumn FROM @Multiple FOR XML PATH('')

Yukarıdaki sql cümlesi bize şöyle bir sonuç üretecek:

, Test 1, Test 2, Test 3

Şimdi de en baştaki ", " u stuff fonksiyonunu kullanarak kaldıralım.

SELECT isNull(Stuff((SELECT ', ' + TestColumn FROM @Multiple FOR XML PATH('')),1,2,''),'')

İşte sonuç :
Test 1, Test 2, Test 3

Tetikleyicileri devre dışı bırakmak / devreye almak (DISABLE/ENABLE Trigger)

Bazen öyle durumlar oluyor ki, geçici olarak SQL Server da yazdığımız tetikleyici(trigger)ları devre dışı bırakmak, geçici olarak çalışmamasını sağlamak gerekebiliyor.

Eskiden, trigger'ın koduna girer, en başına return yazarak triggerın çalışır çalışmaz hiç bir iş yapmamasını sağlamış olurdum. Bu triggerın ismini de bir yere not eder, tekrar devreye almak istediğimde kodu değiştirirdim.. Eğer bu notu kaybedersem (ki jira dan önce başıma gelen birşeydi bu) tüm trigger scripleri içerisinde return aratır ve gereksiz bir sürü trigger koduna bakmak zorunda kalırdım..
Daha önceki versiyonlarında var mıydı bilmiyorum ama SQL Server 2005 ile keşfettiğim bir SQL kodu var. ENABLE/DISABLE TRIGGER [myTriggerName] ON [myTableNameName]

Önce örnek bir tablo ve o tabloya ait bir trigger yazalım :

CREATE TABLE TestTablo1(ID int identity(1,1),Name varchar(255))
GO
CREATE TRIGGER trTestTrigger1 ON TestTablo1 AFTER UPDATE,INSERT,DELETE
AS BEGIN
select 'triggerdan gelen inserted' as note, * from inserted
select 'triggerdan gelen deleted' as note, * from deleted
END

GO

Bu trigger, sadece test amaçlı yazıldığı için özel bir iş yapmıyor. Sadece trigger lar içerisinden ulaşılabilen ve yeni kayıtları (update ise verilerin yeni halini) gösteren inserted tablosundaki tüm satırları ve silinen (update işleme ise verilen eski halini) gösteren deleted tablosundaki tüm kayıtları getiriyor.

Aşağıdaki insert kodunu çalıştırdığımızda trigger da çalışacağı için 2 recordset dönecek. İlk inserted daki kayıt satırını getirecek, 2. sinde herhangi bir silme kaydı olmadığı için boş kayıt gelecek..

INSERT INTO TestTablo1(Name)
VALUES('Test1')

Trigger'ın çalıştığını ve 2 recordset döndürdüğünü gördük.. Şimdi de devre dışı bırakalım.. Aşağıdaki kodu yazmak ve çalıştırmak yeterli..

DISABLE TRIGGER trTestTrigger1 ON TestTablo1
GO

Aynı insert kodunu bir kere daha çalıştırırsak herhangi bir recordset dönmediğini, yani trigger ın çalışmadığını anlayabiliriz..

Tekrar devreye almak yine çok kolay...

ENABLE TRIGGER trTestTrigger1 ON TestTablo1
GO

Kodunu çalıştırmak yeterli..

Trigger ımızı tekrar devre dışı bırakalım. Sonra da disabled trigger larımızı nasıl görebileceğimize bakalım..

Aktif veritabanındaki tüm triggerların isimlerini, bağlı oldukları tablonun id sini ve birkaç özelliğini gösteren bir system view i tanımlı. Bu view de trigger disabled olup olmadığı bilgisi de var.
Disabled olan tüm triggerları aşağıda sql cümleciğini kullanarak çekebiliriz. Triggerın bağlı olduğu tablonun objectid si de geliyor, object_name sistem fonksiyonunu kullanarak bu tablonun da adını alabiliriz..

SELECT
object_name(parent_id) as parentTable_name,
*
FROM sys.triggers
WHERE is_disabled = 1

Artık hangi triggerı devre dışı bırakmıştım, devreye almayı unuttuğum trigger olabilir mi gibi soruların cevabı basit bir sql cümlesinde..

Hatta bir tablodaki tüm triggerları da enable/disable etmek mümkün.. Trigger ismi yerine "ALL" anahtar kelimesini yazmak yeterli..

DISABLE TRIGGER ALL ON TestTablo1
GO

ENABLE TRIGGER ALL ON TestTablo1
GO

Triggerları kolayce devreden çıkarabildiğimize göre, test için yarattığımız objelerimi artık drop edebiliriz.

DROP TRIGGER trTestTrigger1
GO


DROP TABLE TestTablo1
GO