Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Think like a DBA Expand / Collapse
Author
Message
Posted Wednesday, June 16, 2004 11:18 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:51 PM
Points: 6,790, Visits: 1,902
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/thinklikea

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #121411
Posted Thursday, July 29, 2004 1:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, February 14, 2006 9:14 AM
Points: 476, Visits: 1

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.




Post #128956
Posted Thursday, July 29, 2004 2:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 2,908, Visits: 1,833

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.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #128969
Posted Thursday, July 29, 2004 2:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:14 AM
Points: 495, Visits: 100

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!




Post #128970
Posted Thursday, July 29, 2004 2:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 8:45 AM
Points: 49, Visits: 43

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.)?




Post #128974
Posted Thursday, July 29, 2004 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 9, 2004 2:35 AM
Points: 1, Visits: 1

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!

Post #128978
Posted Thursday, July 29, 2004 3:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 2,908, Visits: 1,833

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.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #128979
Posted Thursday, July 29, 2004 3:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 22, 2005 4:01 AM
Points: 118, Visits: 1

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!

Post #128985
Posted Thursday, July 29, 2004 6:17 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 5, 2012 6:32 PM
Points: 341, Visits: 131

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!




Matt,
Peace Out!
Post #129012
Posted Thursday, July 29, 2004 6:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 3, 2011 2:30 AM
Points: 31, Visits: 19

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

Post #129014
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse