To Default or not to Default

Question

Post reply

To Default or not to Default

David Burrows

SSC Guru

Points: 65143
More actions
April 14, 2004 at 2:51 am

#84888

I have built a database that will be used by a 3rd party company developing an application on our behalf. I have not defined any DEFAULTS on my columns as I prefer to control these values via stored procedures. However the external company prefers to use DEFAULTS. I know the final decision is mine as I control the database but everything comes down to spending money.
My question is, do you use DEFAULTS on all your columns or do you put the values in stored procedures? Is there good/bad best practice?
Any comments / views welcome.
Far away is close at hand in the images of elsewhere.
Anon.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hey Dave,

what's wrong with you? ASKING a question, instead of answering?

This might also be a rather religious question, but I like DEFAULTS and use them whenever I can. That way I don't need to rely on some programming logic to enforce some kind of integrity that can be done at a lower level.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65143 More actions · Answer 2

what's wrong with you? ASKING a question, instead of answering?

Thanks Frank can always rely on you for comment

I realised that could be a religious question and everyone will have their views. It's one of those things I never did and wondering if I should break the habit. I can see times when it would prove useful but it seems to me to invite lazy coding by developers and having insert procedures that do not define all columns.

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Looking at the emoticon I'm glad not to sit next to you

Absolutely right, everything has its pros and cons. Personally I'm used to *my* approach, which might not be the best. Others are just as valid as mine.

But why does it come down to spending money? I don't think using DEFAULTS will save much development time at all.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 4

my Ying says its basicaly the same, but with usage-constraints

my Yang says "use the defaults because some naughty smarta** may not use your state of the art sp's to perform the inserts." (keep in mind how many times you [ .. euh .. I ] have to perform manual updates on behalf of the user/developer because of some user-apps bug

IMO the big advantage of having defaulted columns is that you have a standard set for a column,

the advantage of handeling defaults in a sp is that you can have default-sets that are data-driven. If that functionality is needed you will have to restrict inserts only via your sp and avoid column-defaults for those columns which default is data-driven.

You can have both. (who is writing/managing the sp-s)

btw : Most of my developers prefer null because _they_ don't know because of the lack of DA. There's my crusade

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

David Burrows SSC Guru Points: 65143 More actions · Answer 5

alzdba,

I am writing some of the procs, but the 3rd party will be writing some. I have two choices, either put defaults on the columns where needed (they are NOT NULL columns) or give the 3rd party a document stating the defaults for relevant columns.

I do like your reasoning though and now leaning toward putting defaults on the columns

Many thanks.

Far away is close at hand in the images of elsewhere.
Anon.

beechpilot Right there with Babe Points: 779 More actions · Answer 6

To DEFAULT or not to DEFAULT - that is the question -- answered here in probably the best way possible, "It's up to your preferences".

I straddle the middle of the road personally, because I look at the application being developed, or whether I have to deal with a legacy application that is now using SQL Server.

Example: We have an application that runs under PHP on Apache, which used PostgreSQL - all on Un*x. PostgreSQL is quite forgiving in terms of NULL columns and datatypes in certain circumstances where SQL Server is not. When it was found (after coming on board, and having to do a great deal of justifying my decisions), we migrated this application to use SQL Server. To compensate for this application's shortcomings (and PostgreSQL's "forgiveness"), I instituted a series of DEFAULTs - it was the lesser (and easier) of two evils. (Note: It still uses PHP under Apache, but with FreeDTS on the Un*x box to connect to SQL Server).

We are now writing a replacement application. The design of underlying database uses a few DEFAULTs, but for most of the I/O, stored procedures will be used to standardize access.

So - and again, this is entirely personal (religious?) thing - if needed for a legacy app, the easiest way to deal with this issue is to use DEFAULT - but for new applications, use DEFAULT judiciously and use stored procedures and views to standardize your I/O makes the application easier to manage - especially when it comes to structural changes.

Just by US$0.02 worth. (Not much, and much less in the UK )

-- Joe