Think like a DBA
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/thinklikea
-
Hi,
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.
-
Hi,
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 by
coalesce(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.
Cheers!
-
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.)?
-
Hi,
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!
Regards,
Matt -
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.
Tony
-
I think either way, the developer has to put the check for null in their code. If the SP changes and someone takes out the IsNull(), then you will have run-time errors in your code.
-
Agreed, prevention is always better than cure.
Of course the several error handlers around your code and the friendly error reporting that you had time to write when you weren't putting isnull checks in everywhere should lead you to a fix quick enough
Usual disclaimer, and please don't sue my employer, yadda yadda.
Tony
-
Thanks again for another thought-provoking article, Andy.
I wouldn't market myself a DBA, because I don't know quite enough about the guts of SQL Server. Maybe "Deputy DBA" would be appropriate.
In any event, I have almost always found myself in situations where we didn't have a designated SQL Server DBA, so I have had the opportunity to become well-versed in T-SQL , stored procedures, etc. to complement VB, ASP, and so forth.
I've generally found that everyone is happier when we replace many hundreds of lines of, say VBScript code in an ASP page with a few lines that call a stored procedure.
I find this usually makes good sense for a lot of reasons, including:
+ it's much easier to read the ASP code and thus immediately understand what's going on.
+ SQL bugs (i.e., something wrong with the way you wrote your SQL statement(s)) are *much* easier to troubleshoot in Query Analyser than doing things like spinkling 'Response.Write(sSql & "<BR>")' in your VBScript and running the web page
+ you can give your application EXECUTE permission on the stored procedure without giving any permissions at all on the underlying tables.
The only negative response I've ever gotten to doing things this way is that some (non-DBA) managers say, "I don't want to clutter up my database with a lot of stored procedures."
Now that never seemed like a reasonable position to take, but then again, I'm only a Deputy DBA....
-
In that situation the coder should have done a more thorough job with his error trapping and things should have caught the users attention with enough detail that when they call the company the company can chastise them but know where it would occurr without too much effort. I get so sick of companies who put out programs without enough detail to know where an error originated.
However that said I then also will say this is why most companies have rules against altering their code or even looking at it. They are saying in essence you screw it up it is your own darn fault.
But all in all I personally agree with Andy. I would rather simplify the code as much as possilbe and put more control on the server side when feasible, especially with compiled apps. It is far easier to correct issues with compiled apps if you do not have to recompile and distribute and customer appreciate far more. There are critics on both sides and yes you should never consider a product will not need to be rebuilt. However with null issue like this there is a standard on all database systems I have dealt with that will help work these out the same and the coding issue some would say be prepared for support of alternate systems for is mute in this case. Many follks just don't seem to bother to check these things before they make that particular statement.
-
Next time you're accused of cluttering up, try a quick comparison.
Physical storage required to hold one SP and an execution plan vs storage required to hold a few thousand lines of duplicated SQL statements and isnull statements in VB. And if that doesn't work, mention the costs of having to buy a faster SQL and App server to counteract the re-compiling of execution plans and extra lines of VB that need executing.
Besides, you can't even see the clutter unless you look in the Stored Procedures branch in Enterprise Manager. I think MS swept that under the rug quite nicely
Tony
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply