Dlaczego należy jawnie specyfikować czy kolumna ma akceptować wartości puste czy nie?

Załóżmy, że w procedurze składowanej mamy tabelkę tymczasową:

CREATE TABLE #Temp (Column1 Int, Column2 Varchar(15));

Wszystko działa bez zarzutu, może nawet w środowisku produkcyjnym, aż w pewnym momencie ktoś mówi: Wiesz co dzisiaj Twoja procedura wywaliła się i krzyczy, że kolumna Column1 nie pozwala na wartości NULL. Sprawdzasz błąd na swoim środowisku, ale wszystko działa. Sprawdzasz na innym serwerze, na innej bazie danych i też działa. Co u licha?! Magia czy co?

Jak to zwykle bywa w takich sytuacjach nie magia, ale PEBKAC. Jeśli tworzymy tabelkę tymczasową lub inną i nie podamy jawnie czy kolumna ma akceptować wartości NULL czy nie to domyślnie będzie ona... No właśnie tu tkwi problem, a odpowiedź brzmi to zależy.

Przy standardowych ustawieniach będzie akceptować wartości NULL, ale można to zmienić na poziomie bazy danych, a co gorsza na poziomie sesji użytkownika! Wystarczy, więc mała zmiana i nasz kod przestaje działać. Dlatego dobra praktyka mówi więc aby zawsze jawnie specyfikować czy kolumny mają akceptować puste wartości czy nie.

Co do opcji, które sterują tym zachowanie. Na poziomie bazy danych MSSQL służy do tego komenda:

ALTER DATABASE dbname SET ANSI_NULL_DEFAULT [OFF|ON]

Natomiast ustawienia sesji użytkownika określamy w SQL Server Management Studio:

Tools->Options->Query Execution->SQL Server->ANSI

Lub przy pomocy jednej z dwóch komend, które aby było łatwiej mają dokładnie przeciwne znaczenie i się wykluczają:

SET ANSI_NULL_DFLT_ON ON [OFF|ON]
SET ANSI_NULL_DFLT_OFF ON [OFF|ON]