Sql-server – Should I convert empty varchar values to NULL

best practicesempty stringsql serverstored-procedures

I have a stored procedure which accepts various varchar parameters. The middle tier code which calls the procedure has not been consistent in terms of the values it submits. For example, sometimes a parameter like "Transport Description" will be submitted as NULL and sometimes it will be an empty string.

In terms of the client, an empty string simply means a value was not entered.

Should I convert all the empty varchar parameters to NULL before inserting the data?

I can't decide if this falls into the realm of a best-practice to preserve the integrity of the data or a bad idea because I'm not representing what was actually submitted to the database.

I should note that all inserts and updates are tracked in an audit log.

Best Answer

As a DBA, your sacred duty is to take whatever data was entrusted to you, and leave it as is.

A NULL in a field usually means "DATA was not entered" or "DATA is not applicable" (but can have other meanings - it is context specific), and may actually MEAN something to the business layer. An empty string may actually be what the user entered and WANTS. Of course, I am generalizing - possibly the reason you get either NULLs or '' (empty strings) is due to some sloppy coding in the business layer (let us hope not, though).

Either way, have a chat with whoever provides the Middle Tier and see what they are using NULL vs Empty String for - and then go from there. Maybe they are thinking that they always pass in an Empty String (or always NULLS) and are not aware of the randomness (in which case advise them on what you think should be the standard), or maybe NULL really is information to them

Related Solutions

When to use NULL and when to use an empty string

Let's say that the record comes from a form to gather name and address information. Line 2 of the address will typically be blank if the user doesn't live in apartment. An empty string in this case is perfectly valid. I tend to prefer to use NULL to mean that the value is unknown or not given.

I don't believe the physical storage difference is worth worrying about in practice. As database administrators, we have much bigger fish to fry!

Sql-server – Empty Strings: Why or when is ” equal to ‘ ‘

Trailing blanks explained:

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

Here's a well known example of all the cases mentioned above:

DECLARE @a VARCHAR(10)
DECLARE @b varchar(10)

SET @a = '1'
SET @b = '1 ' --with trailing blank

SELECT 1
WHERE 
    @a = @b 
AND @a NOT LIKE @b
AND @b LIKE @a

Here's some more detail about trailing blanks and the LIKE clause.

Best Answer

Related Solutions

When to use NULL and when to use an empty string

Sql-server – Empty Strings: Why or when is ” equal to ‘ ‘

Related Question