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 12345»»»

Table Defaults Expand / Collapse
Author
Message
Posted Wednesday, December 27, 2006 11:34 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 4:10 PM
Points: 6,911, Visits: 2,173
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/awarren/2779.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #333020
Posted Thursday, January 4, 2007 12:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, February 19, 2015 1:11 PM
Points: 1,588, Visits: 388
Andy,

I think this may come down to personal preference and agreed standards. Perhaps a little like the naming convention discussions I'm sure some people will have much stronger views than others, however my 2 penneth worth:

- I like defaults for things like dates
- I can see how setting a default for a lookup value may initially look useful but projects that I have worked on previously have regularly seen the business rules develop to an extent where a single default value cannot be specified and it must be calculated based on other criteria. In this instance defaults must be moved back to the business layer/application
- Defaults, a little bit like triggers, are not overly "visible". By that I mean they are not obvious by looking at a table and it's columns. No biggy but it requires the person using the table to dig a little further. Similarly specifying the defaults in the stored procedures/tsql enforces this visibility but loses the benefits (and potentially breaks) the defaults specified.

Overall I'm probably caught between options 2 and 3 in your list and I think the realistic answer is that it will vary by organisation and project, however the most important thing, as always, is consistency. Get a consistent approach, certainly within a project, and preferably within an organisation and as long as all people working on that project adhere to it things won't go too far wrong.




Dan
www.firstcs.co.uk
Post #334237
Posted Thursday, January 4, 2007 5:35 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:02 AM
Points: 15,538, Visits: 29,753
I have to agree. I'm stuck between option 2 & 3 (with occasional forays into 4). Since defaults aren't readily visible to the developers working on stored procs and since you can easily override defaults, enforcement is a bear. We've got well over 100 developers, the majority creating stored procs on various projects. There are seven of us checking on them. Getting them to use good joins, reference the covering index or clustered index, avoiding LIKE & NOT IN & UNION ALL & DISTINCT... This is where we get the biggest wins over all. It's a shame there wasn't a way (short of masking tables with views or something) to optionally disallow inserts to columns that have a default value specified.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #334271
Posted Thursday, January 4, 2007 5:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, February 12, 2015 12:49 AM
Points: 2,675, Visits: 701

I generally dislike nullable columns, lots of nulls usually mean a poor design, however, sometimes you don't always have all the data - and lets not forget about apps that create new rows by inserting a blank row with the PK ( usually a guid )  and then update the newly created row.

So if you don't have all the data you need a value , maybe null or maybe a default, such as 'not set' or 'unknown' etc.etc.

I'm slightly confused about the overwriting of a default, a default is usually used to give a value where one is not provided at the time, for instance a blood group ( well I figure not all people know their blood group ) in a doctor's system.  The defaults such as dates, userid or such - why would you overwrite them - your documentation and data dictionary should cover this and your test and release process catch any issues. ?



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #334275
Posted Thursday, January 4, 2007 5:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, February 12, 2015 12:49 AM
Points: 2,675, Visits: 701
Sorry Andy - liked the article.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #334276
Posted Thursday, January 4, 2007 6:37 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 4:10 PM
Points: 6,911, Visits: 2,173

Masking with views is a interesting approach to combine with defaults though unwieldy I guess, you need an 'insert' view and an everything else view. Colin, I'm kinda with you, except in practice not everyone has a data dictionary (or reads it). It is perhaps an interesting thing to scan for before applying a change, looking to see if they are overriding a default.

I appreciate the comments, wasn't sure how this topic would be received!



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #334292
Posted Thursday, January 4, 2007 7:22 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, July 16, 2015 4:09 PM
Points: 139, Visits: 153
The answer, as always, is "it depends". What are you trying to optimize for?
Post #334308
Posted Thursday, January 4, 2007 7:40 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 4:10 PM
Points: 6,911, Visits: 2,173

For those interested, here is the link Joe mentioned: http://www.dbazine.com/ofinterest/oi-articles/celko35

Joe, it's an interesting technique and one I haven't applied at the db level - yet! I'll be looking for a place to try that out. But I'd be curious to hear more from you about pro/con of using defaults.



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #334311
Posted Thursday, January 4, 2007 7:43 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 30, 2015 5:29 AM
Points: 296, Visits: 299
Andy, excellent article. I prefer to use simple text encoding schemes that are human readable for things like state. An example of alternative to your numeric encoding is:

U = Unverified
V = Verified
F = Unverified (Follow up sent)
X = Expired (Can be deleted)

The advantage to this is that users who are creating reports on the information will come to understand the encoding scheme easier and won't need to join in another table to get the description. Even when there are just a couple encodings on a table, adding the extra join can be a hassle.

Joe's article on transition constraints is also very good. Here is the link for everyone: http://www.dbazine.com/ofinterest/oi-articles/celko35/view.


Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Post #334313
Posted Thursday, January 4, 2007 10:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 31, 2015 1:39 PM
Points: 1,620, Visits: 1,571

The problem with using human readable codes such as a single letter is if one of the codes changes and it used in 10,000 million records, you have to choose between having the human readable code no longer match what it means or you have to update 10,000 records.

For example, using your example above, if management wanted to change Expired to say Deprecated, then either you have to update all of the data with a new code (like D = Deprecated) or you have to have a human readable code that does not immediately indicate its meaning (X = Deprecated).

I think the integer code is the absolute best way to go and if you want to have a human readable code, make it a computed column or create a view that includes that column. So your lookup table would be something like:

StatusID, StatusCode, StatusName

0, U, Unverified
1, V, Verified
2, F, Unverified (Follow up sent)
3, X, Expired (Can be deleted)

And your view would be something like:

Select D.*, S.StatusCode
From DataTable D with(nolock)
Inner Join Statuses S with(nolock) On D.StatusID = S.StatusID

 





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #334411
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse