For me NULL means unknown and not "no value". If a name (for example) is unknow you put a NULL value and deal with it in the code. If there is no value (for the third name for example) than you can put an empty string or something else like "N/A".
But these are two different things and in my opinion you should not try to use empty string where it really is unknown. And if you find yourself with too many NULL it is time to rethink you db design.
Now this is only my point of view.
p.s I don't think like a DBA, I am a DBA.
There are certain coding jobs that are the IT equivalent of cleaning the loo.
Dealing with NULLs, dates and default values etc.
If you can take this away from your developers and put it into the database layer then they are going to love you forever.
Anything that brings the DBA and his developers closer together is a good thing.
My two pennys worth
I personally think the stored procedure should return the data in the required format for the code. We have lots of code of the type:
if isnull(rs("Field1")) then strValue = ""else strValue = rs("Field1")end if
Which can be replaced in the stored procedure bycoalesce(Field1, '') as Field1
One has lots of lines of code, the other doesn't, so for our way of working, it is a bit of a no brainer.
As for replacing nulls in update procedures, I can think of as many arguments for as I can against. So no pointers there then.
Interesting article and the concept of working with developers. Is that just a theoretical idea?
Seriously, I would like to spend more time with our developers, but I am swamped with all the administration and establishing of database servers/databases.
Here is a question: Can one DBA effectively manage 23 SQL servers containing almost 100 user-defined databases on serveral networks (manage:security, optimization, tunning, baselining, backups, etc.)?
Layering an application is an essential part of good software development. Decoupling system components makes maintainability easier. One such layer that I frequently employ is the Data layer. This layer is responsible for executing stored procedures and also retrieving field values.
e.g. myDataLayer.GetString(datareader, fieldname, String.Empty);
myDataLayer.GetInt(datareader, fieldname, Int32.MinValue);
Note : C# example
In this way it is the data layer that deals with nulls, or I could move null handling into the stored procedure level. Either way it soes not effect the rest of my code.
If we were all the same, life would be a tad boring!
Depends on their caffeine intake
Seriously though, there are so many factors that determine how much administrative work needs to be done that it is difficult to give a meaningful figure to this.
If find that as I go on I automate more and more tasks so that the admin gets less and less. I also document these tasks so the key information is not just locked up in my head.
If you want to protect your knowledge then fine, but you will end up shackled to your past achievements.
The big jump forward for me was finally getting our sys admin to let me switch SQL to using a domain account rather than LOCALSYSTEM. This enabled me to set up SQL Mail so now any important server messages get e-mailed to a generic internal support e-mail address.
It also means that the SQL QA Debugger now works correctly and I can set up various automated fail-over facilities to a standby server.
As ever it is a case of working smarter not just harder.
Personally, I would prefer the NULL conversion to be done in a data access layer of some kind - after all, who is to say there will never come a time when you WANT the code to see database NULLs?
So there would be instead of the two layers here (code that wants an empty string // sp that reads from the database), with a slightly arbitrary decision to make about which layer converts nulls, we would have three layers (code that wants an empty string // code that passes through the data, converting nulls to empty strings // sp that reads from the database and returns exactly what it reads). The key point here is that there can be more than one middle layer - one that converts nulls to empty string, one that converts nulls to 'Missing / n/a' (for a report?), one that converts nulls to ' ' (for some weird legacy consumer), etc etc.
You still have to decide whether that middle layer is a stored proc, or part of the same project as the consumer, or an entirely different entity on its own, of course - that's all part of the fun!
As a developer who codes both asp and sql I've found that the answer usually lies with which ever makes the client happier. If clean asp code is the clients main goal then the "null fix" is coded in the proc. Speed here, like Andy says is pretty much a mute point.
Personally, I found it made my DBA happier when I used "the power of SQL" and the client really could care less how the data was handled, provided they received the results they wanted. I'll go further to say that a Good DBA is a Happy DBA and anything I can do to help keep my DBA in a good mood is in my best interest, and thereby in my clients best interest.
I like my code clean, both in ASP and SQL. For me the SQL answer provides the the cleanest solution, less code, easier to maintain and more reusable.
That's my $.02!
As a developer and a DBA (I think a lot of developers are a bit of both nowadays) I make use of both techniques. As others here have said there is a time and a place for either/both.
Having null's in a table is not necessarily a sign of bad design, if you intended the nulls to mean something, and if you did, then on most occassions you would want the middle tier to see these values. I do however make use of isnull when writing stored procs and views that are to be digested by a reporting layer, as I find working with nulls in reporting tools to be something of a pain most of the time.
As far as putting code into SQL, why the hell not? It's a damn site faster at doing some things than C#, VB etc. Besides which it's a nice way of spreading the processing load across multiple boxes. Compare how many spare cycles you have on your web server, app server and data server and see what works for you.
It's essentially the same argument people were having about VB.NET or C#, which should you use? Well whichever one is right for the job, just like everything else.
Usual disclaimer, just my opinion.