Why is correct normalization so important in database systems?

Hi,

In this article, i will talk about, choosing right data types and normalization on database tables.

The “Database” is a basic word.   It contains “Data” and “Base” words. So it means “The Base of Data”.

So, in databases, the correct definition of the data is more important than we guess.

We define the variable as “string” while we are coding.

But in database systems, there are a lot of data types to define a string data.

Unfortunately, we generally use nchar for string values and use bigint for integer values.

In this article, I will explain, how this situation causes a big problem.

Now, let’s imagine!

We have a task. Our task is to design a citizenship database table for a country.

For example my country Turkey. The population is now about 85 million. Let’s assume that with the people who died and the family tree, we have about 200 million people in our database.

Let’s design the table.

First of all, we have an ID column. The column has an auto-increment value and it is also the primary key of the table.

And the other columns are here.

ID                                        :Auto increment primary key value

CITIZENNUMBER            :11 digits unique citizen number

CITYID                                :The id number of the city where the citizen was born

TOWNID                            :The id number of the town where the citizen currently lives

DISTRICTID                       :The id number of the district where the citizen currently lives

NAMESURNAME             :The citizen’s name surname info

I think these are enough for an easy understanding of the subject.

Actually, this is enough to understand the results of wrong datatypes.

Let’s start from the ID column. We think firstly, this column will address about 200 million rows. So this is a very big value. So we think that we must use the “bigint” data type.

The next column is CITIZENNUMBER. This has 11 digits number and we choose the bigint again.

And the other columns are also integer values. Then let’s choose the bigint for all of them too.

Now let’s calculate it. A bigint value uses 8 Byte in the storage. Even we use just 1 or 0 for the value.
If we use the bigint value for all these columns, we need about 7.6 GB of space for the data.
We have just one table and just 5 numeric columns. I think it is too much.
Well, how can we reduce this huge space? Of course, by using the correct data types.
The bigint data types addresses values between  -2^63 and  +2^63.
Ok, do we need this huge gap for 200 million rows?
Of course not.
We have also an integer data type. The integer data type addresses values between -2^32 ile +2^32. It means between about -2 Billion and +2 Billion.
So, for the ID column, the integer data type is enough and better than the bigint. Because the integer data type uses just 4 Bytes. It is two times better than the bigint. And we know that the population will never be more than 2 Billion.

CITIZENNUMBER: This  column has 11 digits number. So an 11 digits value needs bigint.

CITYID: This column identifies the city Id from the Cities table. In Turkey country, we have just 81 cities and we know that it will always be less than 255. So we can use the TinyInt data types for the CityID column. The Tinyint datatype addresses the values between 1 and 255 and it uses just 1 Byte for a value.

TOWNIND: In Turkey, there are about 1.000 towns. So we can’t use the tinyint because it is not enough. But, we don’t want to use the integer. Because 2 Billion is so much for a value between 1 and 1000. There is another data type Smallint. The Smallint  data type addresses the values between -32.000  and +32.000. So the Smallint is suitable for the TOWNID column.

DISTRICTID:
Turkey has about 55.000 districts. So I can’t use the Tinyint and Smallint. But the Bigint is so much for this type of value. Then the Integer is good for this column.

Now, let’s calculate again. We can see the total space. It is about 3.54 GB. It is about two times better than the first design.

Let’s look at a string column. NameSurname.
In the country, there are citizens from all over the World. So if we define a max character limit, we have to calculate the max character limit of the names from all over the World.
For example, I know that the Spanish people have the longest names. Because they give names to their children as father’s, grand father’s, grand grand father’s names. 😊
For example
“pablo diego josé francisco de paula juan nepomuceno maría de los remedios cipriano de la santísima trinidad ruiz y Picasso”
We just say “Pablo Picasso” 😊
If we want to keep this name-surname information on the database, we have to use 250 digits for namesurname.
This data type is a string. But in database systems, there are so many data types used for string values.
Char, nchar, varchar,nvarchar etc.
Let’s explain the differences between these types with a basic example.
Let’s create a table with the columns below and save it as TEST.
ID:Int
NAME1:char(50)
NAME2:nchar(50)
NAME3:varchar(50)
NAME4:nvarchar(50)

Let’s insert my name, “ÖMER” to the table.

Let’s query the table.

I copy the value from the NAME1 column and paste it into an empty place.

Did you see where the cursor is blinking?

It is blinking far from the end of the string value. The name value “ÖMER” has 4 digits length but the cursor is blinking at 50 digits length. It means, the char(50) uses 50 digits for any value less or equal to 50 digits. It is a very big disadvantage. Think! We have a lot of short names, for example, Jhon, Marry, May, etc. Their average length is just  4 characters and it is a really huge waste.

Let’s investigate the NAME2 column. Copy the NAME2 column and paste it into the text area.

The situation is the same. The cursor is blinking at the end of the 50th character. So, we can say that char and nchar may be the same.

Now let’s look at NAME3 and NAME4 columns. NAME3 is varchar and NAME4 is Nvarchar. Copy the NAME3 and paste into the free text space.

Can you see where the cursor is blinking? The cursor is blinking at the end of the string. The length of the string is 4 digits and the cursor is blinking here. So we can understand that the varchar data type uses space as the string value’s length not max length.

According to this scenario, the char data type uses 50 digits and varchar uses just 4 digits. I think it’s really better than the char or nchar.

We can see that the Nvarchar is the same as the varchar. We don’t know what the “N” means on Nchar or Nvarchar does yet.

The “N” character symbolizes Unicode support. Unicode means the international characters.

Let me show you with an example to understand what Unicode support is.

I use google translate and translate a word to into Chinese. The first word that comes to my mind is book. In Chinese, “书” word means “book”. So I copy this word and paste it into the table.

And I select the data from the table below.

You can see that in the NAME1 and NAME3 columns we can’t see the Chinese character. These columns are both char and varchar and do not support the Unicode characters. It is very easy to understand. Char and Varchar data types use 1 Byte for each letter or character. 1 Byte means 0-255 different numbers. For example, in alphabet, there are 26 characters. So we have 26 lowercase and 26 uppercase letters, 10 numbers, and a lot of punctuations. 255 is enough to address all these letters. But think about the Japanese alphabet. The Japanese alphabet has about 2.000 letters. So 1 byte is not enough for all these characters. We need more. Maybe 2 Bytes can be suitable for us. 2 bytes addresses about 32.000 different letters. Nchar and Nvarchar uses 2 bytes for a letter and can show the Unicode characters.

Let’s turn the scenario again. We have 200 million rows that include namesurname column with max 250  digits and with Unicode support.

Then we have to use nchar(250) or nvarchar(250).

Using nchar(250) is a wrong stuation. Because 250 is just a limit but we arrange 250×2=500 Bytes for all namesurnames.

Using nvarchar (250) is the best choice. Because it supports Unicode characters and also uses space as the namesurname’s length.

Let’s calculate again

As you see, in the wrong design, we use about 100 GB. But in the correct design, we use just 11 GB. It is 10 times smaller than the first one.

We are talking about just one table and just 6 columns. And even in this situation, we can see a huge difference.

In database systems, we have a lot of tables with a lot of columns.

In this article, we talked about the normalization and importance of choosing the right data type.

We talked about the size of the data. You can think that it is only about the size on the disk and you can ignore all these calculations in your mind. You can say “It is all about the money. I can use bigger storage.”  But it is a wrong approach. Because it is not about just the storage. Because SQL Server works on memory and uses the CPU.

If the data type is bigint, You use 8 Byte space on your ram for the for example, 15 number.

If the data type is tinyint, You use 1 Byte space on your ram for the 15 number.

And also if you use bigint, you can use your CPU 8 times more.

As a result,

The Normalization and the knowing to use the right data types are more important than we guess.

See you again in the next article.

SQL Databaselerinin aylık ne kadar büyüdüğünü gösteren sorgu

Merhaba,

İleriye yönelik sistem kaynak planlaması yaparken verilerinizin ne kadar büyüdüğünü görmek önemlidir. Aşağıdaki script sql backupları üzerinden databaselerinizin aylık ne kadar büyüdüğünü göstermektedir. Güzel ve kullanışlı bir script olduğu için paylaşmak istedim. Kaynak:https://www.mssqltips.com/

SELECT DATABASE_NAME,YEAR(backup_start_date) YEAR_ ,MONTH(backup_start_date) MONTH_ ,
MIN(BackupSize) FIRSTSIZE_MB,max(BackupSize) LASTSIZE_MB,max(BackupSize)-MIN(BackupSize) AS GROW_MB,
ROUND((max(BackupSize)-MIN(BackupSize))/CONVERT(FLOAT,MIN(BackupSize))*100,2)  AS PERCENT_
 FROM
(
SELECT
	s.database_name
,	s.backup_start_date
,	COUNT(*) OVER ( PARTITION BY s.database_name ) AS SampleSize
,	CAST( ( s.backup_size / 1024 / 1024 ) AS INT ) AS BackupSize
,	CAST( ( LAG(s.backup_size ) 
		OVER ( PARTITION BY s.database_name ORDER BY s.backup_start_date ) / 1024 / 1024 ) AS INT ) AS Previous_Backup_Size
FROM
	msdb..backupset s
WHERE
	s.type = 'D' --full backup
	and s.database_name='<dbname>'
) T GROUP BY DATABASE_NAME,MONTH(backup_start_date),YEAR(backup_start_date) 

Kaggle.com daki ilk veri setim. Global Terorizm Verisi (Türkçe)

Kaggle’da yayınladığım ilk veri setim. Yine Kaggle üzerinde indirdiğim ve Türkçeleştirdiğim Global Terörizm verisi.
1970-2016 yılları arasında tüm dünyada gerçekleşen 180.000’den fazla terör olayını içeriyor.
Veriler oldukça detaylı kategorize edilmiş durumda. Saldırı tarihi,türü,kim ya da hangi örgüt tarafından yapıldığı, silahlı saldırı mı bombalı saldırı mı, konu ile alakalı özet bilgi ve coğrafi konum bilgisini de içeren oldukça detaylı bir içerik. Tamamen Türkçe.
Veri görselleştirme ve veri bilimi ile uğraşanların dikkatini çekeceğini düşünüyorum.

https://www.kaggle.com/omercolakoglu/global-terrorism-database-turkish

JSON Formatındaki Veriyi SQL Server ile Sorgulama

Merhaba,

Bu yazımızda son yılların popüler veri formatı olan JSON formatını OPENJSON komutunu kullanarak nasıl sorgulayacağımızdan  basitçe bahsediyor olacağım.

Elimizde şu şekilde bir JSON verisi var.

{
    "firstName": "Rack",
    "lastName": "Jackon",
    "gender": "man",
    "age": 24,
    "address": {
        "streetAddress": "126",
        "city": "San Jone",
        "state": "CA",
        "postalCode": "394221"
    },
    "phoneNumbers": 
        { "type": "home", "number": "7383627627" }
    
}'

Bu Json’ı SQL Server ile aşağıdaki gibi sorgulayabiliriz.

DECLARE @JSON AS NVARCHAR(MAX)='{
    "firstName": "Rack",
    "lastName": "Jackon",
    "gender": "man",
    "age": 24,
    "address": {
        "streetAddress": "126",
        "city": "San Jone",
        "state": "CA",
        "postalCode": "394221"
    },
    "phoneNumbers": 
        { "type": "home", "number": "7383627627" }
    
}'


SELECT * FROM  
 OPENJSON ( @json )  
WITH (   

		 firstname varchar(100) '$.firstName' ,
		 lastName varchar(100) '$.lastName' ,
		 age int '$.age' ,
		 gender varchar(100) '$.gender' ,
		 streetAddress varchar(100) '$.address.streetAddress' ,
		 city varchar(100) '$.address.city' ,
		 postalCode varchar(100) '$.address.postalCode' ,
		 state varchar(100) '$.address.state' ,
		 address varchar(100) '$.address.streetAddress' ,
		 phoneNumbers varchar(100) '$.phoneNumbers.type'
)

Ve bu da elde ettiğimiz sonuç.

Şimdi veri sayısını biraz çoğaltalım.

DECLARE @JSON AS NVARCHAR(MAX)='
[
{
    "firstName": "Rack",
    "lastName": "Jackon",
    "gender": "man",
    "age": 24,
    "address": {
        "streetAddress": "126",
        "city": "San Jone",
        "state": "CA",
        "postalCode": "394221"
    },
    "phoneNumbers": 
        { "type": "home", "number": "7383627627" }
    
	},
	
	{

    "firstName": "Marrie",
    "lastName": "Coldman",
    "gender": "woman",
    "age": 39,
    "address": {
        "streetAddress": "156",
        "city": "Newyork",
        "state": "NY",
        "postalCode": "10019"
    },
    "phoneNumbers": 
        { "type": "home", "number": "555689998" }   
}	
]'


SELECT * FROM  
 OPENJSON ( @json )  
WITH (   

		 firstname varchar(100) '$.firstName' ,
		 lastName varchar(100) '$.lastName' ,
		 age int '$.age' ,
		 gender varchar(100) '$.gender' ,
		 streetAddress varchar(100) '$.address.streetAddress' ,
		 city varchar(100) '$.address.city' ,
		 postalCode varchar(100) '$.address.postalCode' ,
		 state varchar(100) '$.address.state' ,
		 address varchar(100) '$.address.streetAddress' ,
		 phoneNumbers varchar(100) '$.phoneNumbers.type'
)

İlerleyen zamanlarda konu ile alakalı daha detaylı yazılar gelecek inşallah.

2011 Yılında yaşadığım bir deneyim. SQL Server ve Jumbo Paket Sorunu.

Bu yazı 2011 yılında yazılmıştır. İlginç bir sorun ve çözüm içerdiği için tekrar paylaşmak istedim.


Geçenlerde bir sistem upgrade’i yaptık. Server ımız değişti, Sistemde database olarak SQL 2005’ten SQL 2008’e, İşletim sistemi olarak Windows Server 2003’ten Windows Server 2008’e geçtik ve cluster yapısı kurduk. ERP Sistemimizde de versiyon geçişi yaptık ve yeni versiyonun çalışması için client bilgisayarlarda mdac versiyon upgrade’ine ihtiyaç duyduk.
Sonuç olarak çok ilginç bir sorunla karşılaştık. Kullanıcı tarafındaki çok basit bir işlem kimi bilgisayarda 1 sn sürerken kimi bilgisayarda 20 sn sürüyordu. Sonuç olarak sorun server kaynaklı, İşletim sistemi kaynaklı, SQL 2008 kaynaklı, Erp programı kaynaklı, ya da mdac kaynaklı olabilirdi. Çünkü bunların hepsi de değişmişti. Epey bir inceleme yaptık sorun üzerinde.
Öncelikle şunu söyleyim bu ayarla alakalı sql server üzerinde bir article bulamadım. Ancak başka uygulamalarda benzer sıkıntılar yaşanmış onun üzerine bu konu üzerine gittik. Burada yeni ethernetler Jumbo frame denilen yapıyı destekliyor ve normalde 1500 byte lık olan network paketleri 9000 byte olarak tek seferde gönderiyor. Paketleri parçalama işini client ın etherneti yapıyor. Özellikle benzer işlem tekrarlarında bu durumu sistem otomatik olarak yapıyor yani kendince optimize etmeye çalışıyor. Client’ta özellikle döngüye takıp aynı sonucu döndüren tek satırlık ya da çoğunlukla sıfır satırlık select cümlelerinin kullanıldığı yerlerde bu özellik devreye giriyor. Eğer karşıdaki ethernet jumbo paketi desteklemiyor ise paket tekrardan servera gönderiliyor bu kez server bu paketi tekrardan parçalayıp client a gönderiyor. Bu da her paket için yapıldığında yaklaşık 10 kat bazen daha fazla gecikmeye sebep oluyor.
Çözüm iki türlü ya serverdan Large Recive Offload Data özelliğini disable etmek ya da client da jumbo paket size değerini arttırmak. Ancak clientta işlem yapmanın iki dezavantajı var bunlardan biri ethernet ya da switchler desteklemiyor olabilir ikincisi de bu özellik enable yapıldığında networkte büyük paketler dolaşmaya başladığından networku tıkayabilir. Bu konuda bir kaç kişiye sorduk pek önermediler büyük paketleri. En doğrusu server üzerinde bu ayarı disable etmek gibi görünüyor.
Biz bu ayarı serverda disable ederek sorunu çözdük. Zaten eski serverda ethernet desteklemiyormuş ondan sorun olmamış.
Bu bahsettiğim sorundan kaynaklı sıkıntı olduğunu düşündüğün makinada sorun olup olmadığını anlamak için performance monitorden send packet/sec değerlerine bakılabilir. Hızlı makina ile yavaş makina arasındaki fark en az 10 kat oluyor. Aşağıda bu ayarın nasıl yapıldığının resmi mevcut.

SQL Server’da Canlı Veriyi Tablo Bazlı Sıkıştırma (Compress)

Merhaba,

Bu yazımızda SQL Server’daki bir tabloyu sıkıştırma yani Compress özelliğinden bahsediyor olacağım. Bir çoğumuz veritabanlarında text veriler kullanıyoruz. Bu verilerde ise gerek veritabanı mimarisi sebebiyle ya da gerekse içerisindeki veriler sebebiyle boşluklar bulunmakta. Bu boşluklar ise gereksiz yer teşkil etmekte.

Özellikle varchar, varbinary gibi alanlar yerine char,binary gibi veri tipleri kullanımı veritabanımızın gereksiz büyümesine sebep oluyor. Hazır paket programlarda bu veritabanı mimarisinde değişiklik yapamıyoruz ancak SQL 2008’den bu tarafa olan compress özelliğini kullanabiliriz.

Şimdi elimizde bir tane tablosu olan bir database i miz var. Özellikle tek tablo kullandım ki yaptığımız kazancı rahatlıkla görebilelim.

Tablomuz yaklaşık olarak bu şekilde.

Bu da tablomuzun normalizasyon yapısı. Görüldüğü gibi çok sayıda char tipinde alanlar kullanılmış.

Şimdi bir de tablomuzun diskte kapladığı alana bakalım.

Satır sayısı: 529.324

Kaplanan alan:1,4 GB

Görüldüğü gibi yaklaşık 1.4 GB büyüklüğünde tek tablolu bir database imiz var.

Şimdi bu tabloyu sıkıştırmayı deneyelim.

Tablo üzerinde sağ tık Storage>Manage Compression diyoruz.

Karşımıza bir wizard çıkıyor.

Tablo ile alakalı 3 tür sıkıştırma yapısı var.

None:Sıkıştırma yok

Row:Satır bazlı sıkıştırma

Page:Page bazlı sıkıştırma.

Şimdi Row seçelim ve Calculate tuşuna basalım.

1.378 MB’lık tablonun 163 MB’a düşeceğini öngörüyor. Yani 1378/163=8.5 kat sıkıştırma.

Şimdi de Page seçelim ve Calculate tuşuna basalım.

O da 116 MB çıkardı. Yani Yani 1378/116=11,7 kat sıkıştırma. Burada page ya da row based sıkıştırma daha iyi diye bir yorum yapmak zor. O yüzden calculate yapıp hesaplamak daha mantıklı.

Şimdi akla gelen bir diğer soru ise performans. Yani sıkıştırılmış bir tabloda sorgu performansı ne olur.

Gelin onu da hep birlikte deneyelim.

Tablomuzdan 2019 Ağustos ayında Adana şehrinde yapılan satışları indexli,indexsiz olarak çekeceğiz. Bakalım compression aktif ve pasif olduğunda nasıl sonuç döndürecek.

Şimdi SQL Server’ın bize önerdiği indexi ekleyelim.

Şimdi row compression yapıp deneyelim.

İşlem tamamlandı. Şimdi tablo boyutuna bakalım. Gördüğümüz gibi 160 MB civarına indi. Yani yaklaşık 8.5 kat sıkıştı.

Şimdi index’i silip sorgumuzu çalıştıralım.

Normalde sıkıştırma yaptığı zaman daha uzun  sürede gelmesini bekleriz. Oysa daha az okuma yaptığı için sistem 8 kat daha performanslı çalıştı.

Şimdi index ekleyip tekrar çalıştıralım.

Gördüğümüz gibi indexin şu an için sıkıştırmada bir payı olmadığından sıkıştırma aktifken ya da pasifken bir fark olmadı.

Şimdi de page bazlı compression’a bakalım.

Data boyutu tahmin edilenden daha aza indi. Yaklaşık 80 MB oldu.

Şimdi performansa bakalım.

Önce index i silelim.

Index yokken bile çok iyi sonuç getirdi. Hatırlayalım sıkıştırmadan önce 1,44 GB lık okuma yapıyordu burada ise sadece 87 MB.

Şimdi de indexi tekrar ekleyerek bakalım.

Özetle

  • Bu yazıda SQL Server’da compression özelliğini ve nasıl kullanılacağını anlattık.
  • 2 GB lık bir database’i 85 MB’a kadar küçülttük.
  • Ayrıca row based ve page based sıkıştırmanın farkını gördük.
  • Son olarak compress aktif bir tabloda pasif olana göre beklediğimizin tam aksi daha az okuma ve daha fazla performans gösterdiğini gördük.

Sonraki yazıda görüşmek dileğiyle.

Sağlıcakla kalın.

SQL SERVER’da SUSPECT MODA DÜŞEN BİR DATABASE’ İ KURTARMA

Bir database’in suspect moda düşmesi demek database dosyalarından (mdf,ldf,ndf) en az birini okurken bir sorunla karşılaşmış olması anlamına gelir.

Genel olarak bu sorunlar,

  1. Database dosyaları bozulmuş olabilir.
  2. Sistemde yeterli disk alanı kalmamış olabilir.
  3. Yeterli memory kalmamış olabilir.
  4. Database dosyaları silinmiş ya da işletim sistemi dosyaların kullanılmasına izin vermiyor olabilir.
  5. Server düzgün kapatılmadığı için ya da bir takım donanımsal sorunlar yüzünden dosyalar okunamıyor olabilir.

Bu moda düşen bir database’i normale çevirmek için aşağıdaki komutları kullanırız.

–Database’in statüsünü resetleme komutu. Böylece manuel müdaheleye izin verir.
EXEC sp_resetstatus ‘dbName’;
–Database’i emergency moda çekiyoruz.
ALTER DATABASE dbName SET EMERGENCY ;

–Log dosyası silinir ve yeni bir yeni boş bir log dosyası oluşturulur. Bu sırada log dosyasındaki –kaydedilmeyen veriler silinir.
DBCC CheckDB (‘dbName’, REPAIR_ALLOW_DATA_LOSS)
–Database’i kullanıma açıyoruz.
ALTER DATABASE dbName SET MULTI_USER
Database’imizi artık gönül rahatlığıyla selectleyebiliriz:)

SQL Server Database Mail ve Gmail ile Kullanımı

Zaman zaman SQL Server üzerinden otomatik mail gönderme ihtiyacımız olur. Örneğin bir yedek alma işlemi sorunsuz tamamlandığında ya da yedek alırken sorun yaşandığında sistem bize otomatik mail atsın isteriz.
Ya da bir sql sorgusunun sonucu bize mail olarak gelsin isteriz.
İşte bu işlemler için SQL Mail konfigürasyonunun yapılmış olması gerekir.
SQL Mail konfigürasyonunun örnek bir gmail hesabı ile nasıl yapılacağını anlattığım yeni yazım.