SQL Server’da ANSI_NULLS, ANSI_PADDING ve CONCAT_NULL_YIELDS_NULL Ayarlarının Doğru Kullanımı: Bu Üç Ayar Neden Daima ON Olmalıdır?

Bu makalede ANSI_NULLS, ANSI_PADDING ve CONCAT_NULL_YIELDS_NULL değerlerinin neden açık olduğu ne işe yaradığı kurulu olan sql server üzerinde nasıl kontrol edilmesi gerektiğini gelecek sql server sürümlerinde bu özelliklerin ne gibi sıkıntılara sebep verdiğini ele alacağız.

Bazı uygulamaların bağlantı sırasında bu seçenekleri ayarladığını unutmayın:

SQL Server Management Studio, varsayılan olarak bağlantı sırasında üç seçeneği de AÇIK olarak ayarlar, ancak gerekirse değiştirebilirsiniz.

Modern Sürücüler (ODBC ve OLE DB): SQL Server Native Client veya güncel ODBC sürücüleri kullanıldığında; ANSI_NULLS, ANSI_PADDING ve QUOTED_IDENTIFIER gibi kritik ayarlar otomatik olarak ON (AÇIK) konumuna getirilir. Bu, modern SQL standartlarına uyumu sağlar. Çok eski uygulamalar tarafından kullanılan bu kütüphanede varsayılan değerler OFF (KAPALI) gelir. Bu durum, eski yazılımlar modern bir SQL Server’a bağlandığında beklenmedik sonuçlara (örneğin boşlukların kırpılması veya null karşılaştırma hataları) neden olabilir.

Bir tabloyu CREATE TABLE ile oluştururken ANSI_PADDING hangi değerdeyse, o sütun o kuralla mühürlenir. Oturum ayarını daha sonra SET ANSI_PADDING ON yapsanız bile, önceden “OFF” ile oluşturulmuş bir sütunun davranışını değiştiremezsiniz. Veritabanındaki hangi sütunların bu özelliğinin kapalı olduğunu sys.columns sistem tablosundaki is_ansi_padded kolonundan görebilirsiniz. Modern uygulamalarda tutarlılık için bu değerin ON olması gerekir.

SET ANSI_NULLS ve SET QUOTED_IDENTIFIER ayarları, SP oluşturulduğu (CREATE) veya güncellendiği (ALTER) andaki değerleri kullanır. SP kodunun içine SET ANSI_NULLS ON yazsanız bile SQL Server bunu görmezden gelir ve oluşturulma anındaki değeri baz alır.

Ayar	Zorunlu Değer	Açıklama
ANSI_NULLS	ON	NULL karşılaştırmaları standartlara uygun olmalı.
ANSI_PADDING	ON	Değişken uzunluklu verilerde standart boşluk yönetimi.
ANSI_WARNINGS	ON	Sıfıra bölme gibi durumlarda uyarı/hata vermeli.
ARITHABORT	ON	Aritmetik hatalarda işlem durmalı.
CONCAT_NULL_YIELDS_NULL	ON	NULL ile metin birleşince sonuç NULL olmalı.
QUOTED_IDENTIFIER	ON	Çift tırnak nesne isimleri için kullanılabilmeli.
NUMERIC_ROUNDABORT	OFF	Hassasiyet kaybında işlem kesilmemeli.

İlk olarak bu değerlerin ne işe yaradığını ele alalım.

SQL Server’ın ANSI standartlarına göre nasıl davranacağını belirleyen oturum (session) ayarlarıdır. Sorguların NULL değerleri, boşlukları ve karşılaştırmaları nasıl ele aldığını doğrudan etkiler.

ANSI_PADDING

Bu ayar, CHAR / VARCHAR / BINARY / VARBINARY alanlara veri eklerken sondaki boşlukların veya sıfırların nasıl saklanacağını belirler.

ANSI_PADDING ON (Önerilen)
• VARCHAR gibi değişken uzunluklu alanlarda son boşluklar korunur.
• CHAR gibi sabit uzunluklu alanlarda doldurma yapılır.

INSERT INTO T(Kolon) VALUES ('test   ') -- Boşluklar saklanır..

ANSI_PADDING OFF
• VARCHAR alanlarda sondaki boşluklar kırpılır.

INSERT INTO T(Kolon) VALUES ('test   ') --'test   ' ifadesi 'test' olur.

Bu değerin OFF olmasının Riskleri:
• Veri kaybı yaşanabilir (son boşluklar kaybolur)
• Eski davranış olduğu için modern uygulamalarda sorun çıkarabilir.
• Unique indekslerde eşleşme problemleri olabilir.

ANSI PADDING değeri OFF olan sütunlara sahip nesneleri tanımlamak için, aşağıdaki komut ile tüm veritabanları üzerinde bulunan tablolarda bu değerlerin hangi modda olduğu görülmektedir:

EXEC sp_MSforeachdb '
use [?];
select DB_NAME() AS Veritabani,
''ANSI PADDING OFF'' as setting
     , o.name as object_name
     , o.type_desc as object_type
     , c.name as column_name
     , usrt.name column_data_type
from sys.columns c inner join sys.objects o
ON o.object_id = c.object_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = c.user_type_id
where o.is_ms_shipped <> 1
      and 
     usrt.name IN (''char'',''varchar'',''binary'',''varbinary'')
     and
     is_ansi_padded <> 1
order by o.name, c.name'

Veritabanı seviyesinde SET ANSI_PADDING ON komutunu çalıştırmak, mevcut tabloları değiştirmez; sadece o andan itibaren oluşturulacak yeni sütunları etkiler. Mevcut tabloları düzeltmek için şu adımları izlemelisiniz:

1. Tabloyu Yeniden Oluşturmak (Rebuild): Tablodaki ilgili sütunların ayarını kalıcı olarak değiştirmek için tabloyu ALTER TABLE … ALTER COLUMN komutuyla (ANSI_PADDING ON set edilmiş bir session’da) güncellemeniz veya tabloyu yeniden oluşturmanız gerekir.
2. Index’leri Yeniden Oluşturmak: Eğer bu sütunlar üzerinde index varsa, index’lerin de REBUILD edilmesi gerekebilir.

Bu değişikliği yapmadan önce şu riskleri göz önünde bulundurmalısınız:

• Veri Uzunluğu ve Karşılaştırma: ANSI_PADDING OFF durumunda SQL Server, varchar sütunların sonundaki boşlukları kırpar. ON yaptığınızda bu boşluklar saklanır. Eğer uygulamanız sonundaki boşluklara göre bir mantık kurmuşsa (string karşılaştırmaları gibi), sonuçlar değişebilir.
• Disk Alanı: Çok küçük bir ihtimal olsa da, boşluklar artık kırpılmayacağı için veri boyutu çok az miktarda artabilir.
• Hesaplanmış Sütunlar ve Indexli Viewlar: Bazı özel SQL Server özellikleri (Filtered Indexes, Indexed Views vb.) ANSI_PADDING değerinin mutlaka ON olmasını şart koşar. Eğer bu özelliklerden birini kullanıyorsanız, OFF kalması hata almanıza neden olabilir.

Büyük bir tablomuzda bu ayarı değiştirmek için neler yapılması gerekir. Öncelikle, yapacağınız tüm ALTER işlemlerinin ON ayarıyla gerçekleşmesi için mevcut bağlantınızda bu ayarı aktif etmelisiniz.

SET ANSI_PADDING ON;
GO

Tabloda birden fazla kolon bu durumdaysa, tabloyu REBUILD etmek hem ANSI_PADDING ayarını o anki session ayarlarına göre günceller hem de veriyi fiziksel olarak düzenler. Bu düzenleme için oluşturulacak index jobının veya rebuild edilecek session başlangıcına SET ANSI_PADDING ON ifadesinin eklenmesi gerekmektedir. İşlem sırasında Log dosyanız çok büyüyecektir. Diskinizde yeterli alan olduğundan emin olun.

ANSI_NULLS

ANSI_NULLS, SQL Server’da NULL karşılaştırmalarının nasıl yapılacağını belirler. Varsayılan: ON (güncel SQL Server sürümlerinde her zaman ON olması önerilir)

ANSI_NULLS ON (Önerilen) NULL ile normal karşılaştırma her zaman FALSE döner.

SELECT * FROM Tablo WHERE Kolon = NULL;   -- sonuç yok
SELECT * FROM Tablo WHERE Kolon <> NULL;  -- sonuç yok

--Doğru kullanım:

Kolon IS NULL
Kolon IS NOT NULL

ANSI_NULLS OFF: NULL karşılaştırmaları eski SQL Server davranışına göre çalışır.

SELECT * FROM Tablo WHERE Kolon = NULL; -- SONUÇ GETİREBİLİR

Bu değerin OFF olmasının Riskleri:
• Modern SQL Server sürümlerinde bazı özelliklerle uyumsuzdur (Indexed View, Filtered Index, Persisted computed column vs).
• Tutarsız sonuçlar üretir.
• Microsoft gelecekte tamamen kaldıracak.

ANSI_NULLS değeri OFF olan sütunlara sahip nesneleri tanımlamak için, aşağıdaki komut kullanılmaktadır:

EXEC sp_MSforeachdb '
use [?];
select DB_NAME() AS Veritabanı,
 ''ANSI NULLS OFF'' as setting
     , o.name as object_name
     , o.type_desc as object_type 
     , NULL as column_name
     , NULL as column_data_type
from sys.objects o
where is_ms_shipped <> 1
and OBJECTPROPERTY (object_id, ''isAnsiNullsOn'') = 0
order by o.name'

SQL Server’da bir tablo veya sütun ANSI_NULLS OFF ile oluşturulmuşsa, bu durum genellikle eski (legacy) sistemlerden gelen bir kalıntıdır. Bunu düzeltmenin yolu nesneyi (tabloyu) ANSI_NULLS ON ayarı aktifken fiziksel olarak yeniden oluşturmaktır.

ANSI_NULLS ayarı ON olan bir oturumda tabloyu yeniden oluşturmaktır (Rebuild). ANSI_PADDING için konuştuğumuz kurallar burada da geçerlidir.

Sadece index yeniden Rebuild edildiğinde bu bir job olabilir veya bir session olabilir. İlgili oturumun başında SET ANSI_NULLS ON ayarının eklenmesi gerekmektedir.

-- 1. ADIM: Oturum ayarlarını standartlara çekin
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
GO

-- 2. ADIM: Tabloyu fiziksel olarak yeniden inşa edin
-- Bu işlem tabloyu okuyup yeni ayarlarla tekrar yazar.
ALTER TABLE [Tablo_Adı] REBUILD WITH (ONLINE = ON); 
GO

Not: Yukarıdaki komutla verilen Rebuild işlemlerinde tablonun fragmentation değerine bakmaz. Belirtilen tabloda Rebuild işlemi yapmaktadır. Ama olahallengren script’lerinde oluşturulan index bakım job’ında belirtilen fragmentation değerine göre index rebuild işlemi yapılmaktadır.

CONCAT_NULL_YIELDS_NULL

Bu ayar string birleştirme işlemlerinde (+) NULL değerlerin nasıl davranacağını belirler. CONCAT_NULL_YIELDS_NULL ON (Önerilen) NULL ile yapılan birleştirme NULL döner.

SELECT 'Merhaba' + NULL   -- Sonuç: NULL

CONCAT_NULL_YIELDS_NULL OFF: NULL, boş string (””) gibi davranır.

SELECT 'Merhaba' + NULL   -- Sonuç: Merhaba

Bu değerin OFF olmasının Riskleri:
• NULL değerlerin yanlışlıkla görünmez olması
• Beklenmeyen rapor sonuçları
• Veri temizliğinde tutarsız davranışlar

ANSI PADDING, ANSI NULLS veya CONCAT_NULL_YIELDS_NULL’un OFF olduğu oturumların olup olmadığını belirlemek için aşağıdaki betiği çalıştırın:

select COUNT(*)
from sys.dm_exec_sessions
where is_user_process = 1
and (ansi_padding <> 1 OR ansi_nulls <> 1 OR concat_null_yields_null <> 1) 

ANSI PADDING ve ANSI NULLS yapılarının da OFF olan ifadenin on olarak değiştirilmesi isteniyorsa ilk olarak geçici yeni bir kolon eklenir. ANSI PADDING ve ANSI NULLS ifadeleri ON iken, daha sonra geçici bir kolon oluşturulmaktadır. Eski kolondaki veriler yeni olan kolona kopyalanmaktadır. Eski kolon silinir. Geçici olan kolon orjinal olan kolon olarak yeniden adlandırılır.

-- 1. ANSI_PADDING açık
SET ANSI_PADDING ON;

-- 2. Geçici kolon oluştur
ALTER TABLE Urunler
ADD Aciklama_tmp VARCHAR(100);

-- 3. Veriyi yeni kolona kopyala
UPDATE Urunler
SET Aciklama_tmp = Aciklama;

-- 4. Eski kolonu sil
ALTER TABLE Urunler
DROP COLUMN Aciklama;

-- 5. Geçici kolonu asıl kolona çevir
EXEC sp_rename 'Urunler.Aciklama_tmp', 'Aciklama', 'COLUMN';

Eğer kolon index, foreign key, default constraint, check constraint bağlıysa önce bunlar kaldırılmalı, sonra geri eklenmelidir.
• Veri tipi aynı olmalıdır.
• Tablo üzerinde çalışırken kesinti olabilir.

Oturum (Session) Bazında Ayarlamak

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET CONCAT_NULL_YIELDS_NULL ON;

Server seviyesinde bu ayarın kontrol edilmesi gerekiyorsa:

SELECT 
    SESSIONPROPERTY('ANSI_NULLS') AS ANSI_NULLS,
    SESSIONPROPERTY('ANSI_PADDING') AS ANSI_PADDING,
    SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') AS CONCAT_NULL_YIELDS_NULL;

SSMS’de query option kısmında bu ayarları set edebiliriz.

Oluşturulacak tablo, stored procedure ve diğer işlemlerin hangi ayarın set edilmesi için ilgili değerlerin set edilmesi gerekmektedir.

Bu üç ayar SQL Server’da en doğru ve modern kullanım için her zaman ON yapılmalıdır. Microsoft’un tüm yeni özellikleri (indexed view, filtered index, persisted computed column vb.) bu ayarların ON olmasını zorunlu kılar.

Başka makalede görüşmek üzere..

Huzur Allah’tandır. (Necm-43)