SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Think like a DBA

By Andy Warren,

I've been managing a small development team for a few months now and one of the more interesting comments has been that I think like a DBA, not a developer. This usually comes about whenever I offer a solution that is more sql based than 'code' based. This article will take about one particular instance. Will be interesting to see what comments are posted!

Recently I was doing one of my semi daily walk arounds, seeing if anyone needed anything, were on track, etc. Usually I stop in for a couple minutes, move on, then back to my office to do my own work. I noticed one of the developers writing code that I think you will all recognize, so I'll just present a vanilla representation - translate to your language of choice:

if rs.fields("lastname") is null
    somevar = ''
end if

Basically you've retrieved a value from a table into a recordset, datareader, whatever, and you're assigning the results to a variable or assigning an empy string to the variable if the value is null. In essense, you want a string value, you don't want a null.

This isn't uncommon by any means. DBA's love nulls (well, those of us who believe in allowing them), developers don't find them as useful in program code. Typically char/varchar/text values that are null become empty strings, null integers become 0, null dates become 1/1/1900 or something like that. Now keep in mind I'm not saying that developers don't understand nulls or ever find a use for them, but I think most common app code tends to convert them to something 'friendlier'.

Looking at the code lead to a whole different discussion, but essentially circled back to me asking if you're going to get rid of the nulls, why not do it 'upstream', in the stored procedure?

Basically if I were trying to accomplish the same task, I would change the stored procedure to look something like this:

select isnull(lastname, '') as LastName from sometable where ....

This eliminates the possibility that I can get a null back at all. Conversely, if I were writing the value back to the db, I'd typically do something like this:

update sometable set lastname = nullif(lastname, '') where ....

The last thing I want in the db is empty strings unless they truly mean something. In both cases I'm doing the work on the server, as close to the data as possible. To me, this means that from that point on I don't have to worry about the values, I can just work with what I get.

Is the SQL technique 'better'? Performance on either approach is close enough to being the same to worry about and too small to bother with testing it anyway! Quality? I'd say once tested both work equally well. I think the SQL version results in less code written fewer times.

As we continued the discussion, we finally got down to bare metal - should you put any code besides insert/update/delete/select code in a stored procedure, violating the rules of n-tier design? I don't know that I'm qualified to speak on what the rules are or should be, but I think when it comes to a database you have two choices. One is treat it like a dumb data store, keep your procs as simple as possible or even use dynamic sql exclusively, leaving yourself the ability to connect to whatever type of data store your customer may elect to use. The other is to leverage the strength of the platform, even if that means breaking some of those 'rules' about what code should reside where. I like code with as many layers as possible, stored procedures are a powerful layer that I like to fully exploit. Still, if you're sticking with the rules, that does null out the isnull/nullif scenario? Or does it?

Ultimately it's about knowing that there are different ways to solve a problem and understanding when best to apply each. A lot of it is style, but the style has to be based on merit, not just 'I like SQL' or 'I like code'! Talk to your developers about this, how do they handle nulls? Do they know isnull and nullif? Throw them a curve, get them to look at the coalesce function. Get them to think about the power of the db platform and how it might change how they solve problems.

Total article views: 10892 | Views in the last 30 days: 0
Related Articles

Personal Development

Steve Jones thinks that a personal development plan is important and talks today a little about how ...


Stored Procedure Development Cycle

This month Robert covers the process he uses for developing stored procedures. In many ways it refle...


Have a Think

It's easy for most of us to get caught up in work and not stop to think about the larger picture, or...


Thinking Time

Taking time away from life and just thinking is important.


Great Developers

A great developer is worth more than an average one, but how much more? Steve Jones has a few though...