Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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 = ''
else
    somevar=rs.fields("lastname")
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: 10881 | Views in the last 30 days: 0
 
Related Articles
ARTICLE

Personal Development

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

ARTICLE

Stored Procedure Development Cycle

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

ARTICLE

Great Developers

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

FORUM

Thinking about going DBA..

Hi all, Been surveying the site for a while and am considering a shift in careers.. Been a Server...

BLOG

Developer Stereotypes

It’s interesting to listen when DBA’s get together, there is always a story or two about the evil th...

Tags
miscellaneous    
programming    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones