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.

• SQL Server ODBC sürücüsü ve SQL Server için SQL Server OLE DB Sağlayıcısı, bağlantı sırasında üç seçeneği de otomatik olarak AÇIK olarak ayarlar.

• Veritabanı Kütüphanesi uygulamalarından gelen bağlantılar için üç seçeneğin varsayılan değeri KAPALI’dır. Bazı ayarlar yalnızca nesneyi oluşturma sırasında etkiler:

• ANSI_PADDING ayarı yalnızca yeni sütunların tanımını etkiler. Sütun oluşturulduktan sonra, SQL Server değerleri sütun oluşturulurken kullanılan ayarlara göre depolar. Mevcut sütunlar, bu ayarda daha sonra yapılan bir değişiklikten etkilenmez. Veritabanları üzerinde hangi sütün değerinde bu özelliğin kapalı olduğunu görebilir. Bu değerin On yapılması gerekmektedir.

• Stored Procedure, SET ANSI_NULLS ve SET QUOTED_IDENTIFIER hariç, yürütme sırasında belirtilen SET ayarlarıyla yürütülür. SET ANSI_NULLS veya SET QUOTED_IDENTIFIER belirten procedured’ler, procedure oluşturma sırasında belirtilen ayarı kullanır. Bir procedure içinde kullanılırsa, herhangi bir SET ayarı yok sayılır. Tutarlılığı garantilemek için, SQL Server bazı görevleri yürütebilmek için bazı oturma alanlarının belirli bir değere ayarlanmasını gerektirir; örneğin, hesaplanmış bir sütunda veya bir görünümde dizin oluştururken.

• Hesaplanmış sütunu tanımlayan CREATE TABLE veya ALTER TABLE ifadesi yürütüldüğünde ANSI_NULLS bağlantı düzeyi seçeneği ON olarak ayarlanmalıdır.

• Hesaplanmış bir sütunda dizin oluşturmak için, dizinin oluşturulduğu bağlantı ve dizindeki değerleri değiştirecek INSERT, UPDATE veya DELETE ifadelerini deneyen tüm bağlantılarda altı SET seçeneği (ANSI_PADDING, ANSI_NULLS ve CONCAT_NULL_YIELDS_NULL dahil) ON ve bir seçenek OFF olarak ayarlanmalıdır. Optimizer, aynı seçenek ayarlarına sahip olmayan bir bağlantı tarafından yürütülen herhangi bir SELECT ifadesi için hesaplanmış bir sütundaki dizini yok sayar.

İ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'

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'

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.
İstersen bunun otomatik scriptini de oluşturabilirim.
• 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;

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)