Can I configure an SQL Server 2008 DB so that it will save all empty strings as NULLs?

In my current project it would be convenient to exclude empty strings from the domain, so that is an empty string is met wherever, it is treated as N开发者_开发百科ULL, for example if I UPDATE [table] SET [Something]='', SQL Server should treat it as SET [Something]=NULL, so that in next SELECT a NULL is returned as [Something] value.

Should I use triggers or there is a better way?

There is no automatic means to force an empty string to be converted into a null. However, you could add check constraints on all char, varchar, nchar, and nvarchar colums which requires that the value be greater than zero or null. That would cause the client app to throw an exception if it did not convert the empty strings to null.

Beyond the use of check constraints, the ideal place to ensure that empty strings are converted to NULL is your data access layer. You should encapsulate the code that opens a connection, executes something against the database and then closes the connection. Presumably, that code will take an array of arguments used as the parameters to your parametrized queries. In that code, it can check if the data type is a string and if so, convert it to null.

It's worth noting that NULL is not always the same thing as an empty string (even though today it is, tomorrow it may confuse someone to have their empty string deleted). For example, are these two situations identical:

1. User has not entered data to the field;
2. User has entered and then removed the data?

You should consider whether there is value to you in knowing that the field has had a value before you force all empty strings to NULL. Also - is a single space an empty string? Multiple spaces? CHR(254)?

I wish. All your insert and update expressions need to handle this properly.

I have functions in several languages that I use for constructing string expressions properly. In PHP it's

function QuoteOrNull($str)
{
    return empty($str) ? "NULL" : "'$str'" ;
}

There's another one for integer values:

function IntOrNull($num)
{
    return is_null($num) ? "NULL" : intval($num);
}

etc.

All the usual admonitions about making sure your business rules are making proper distinctions between zero and null apply to the second. I've never seen any useful reason to intentionally insert zero-length strings however. It seems when you do, someone wants to sort on them and then you're in trouble.