So, I had a discussion with a co-worker yesterday regarding the application of defaults on columns. Let me give you some background.

We are converting a Foxpro database to SQL Server. The front end is being re-written in .NET. The conversion of the data is such that when a character column is imported that has no data, it comes in as an empty string (''), not a NULL value.

However, when NEW records are added via the .NET front end, it obviously stores them as NULL (if no value is supplied for that column).

This is breaking reports, because it did not don on us to check for NULL values in our queries.

So, the solution my co-worker is recommending is to add a default to all character columns that inserts an empty string. That way we don't have to go back and retro fit a bunch of views and stored procs to add the IS NULL check.

However, my question to the group is the following: Can anyone think of a reason NOT to go this route, such as possible performance degredation?

The correct answer would have been to do NULL checking from the start and we realize that, but we are too far into this to go back and "fix" some things unless we really have to.

Thanks...


Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

__________________
========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================

You're going to have to do a data cleanup at one point or another... and, it's easier to change procs/views in the database than it is to recompile front end code. AND, NULLs and blanks mean two different things... personally, I'd change the blanks to NULLs in the data and fix the abhorrent code in the database.

--Jeff Moden

Using a default would have some impact on performance, but unless your current perf is right on the edge of some acceptable limit and the volume of transactions is at the absolute maximum capacity of the current system, then its unlikely you would ever see the tiny difference the default would add.

However as Jeff points out NULL does not equal '' (empty string) In some cases this doesnt matter to the app. If thats the case for you then the easiest change is to just add the default.

But be warned this could come back and bite you in the butt at some later point when you suddenly realise you really do need to have a NULL value :(



Steve Wort
Co Author "Professional SQL Server 2005 Administration"