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


Think like a DBA


Think like a DBA

Author
Message
Andy Warren
Andy Warren
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24639 Visits: 2746
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
deden
deden
SSC-Addicted
SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)

Group: General Forum Members
Points: 490 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.





Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16356 Visits: 3403

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
www.simple-talk.com
Keiths123
Keiths123
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 114

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!





currym
currym
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 61

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





Mark Whitton
Mark Whitton
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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!


Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16356 Visits: 3403

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
www.simple-talk.com
AKM
AKM
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 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!


Matt Simmons
Matt Simmons
SSC Eights!
SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)

Group: General Forum Members
Points: 913 Visits: 378

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
Tony Hinkley
Tony Hinkley
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search