| Author |
Topic |
|
X002548
Not Just a Number
15586 Posts |
 Posted - 2010-11-22 : 16:27:03
|
| Writing code and doing some training to see if we can write a migration sproc between an old version of a database to a new empty database versionWe3 decide the new version of SQL server should electrocute a data modeler who makes their date columns NOT NULL and doesn't provide a defaultWhat type of columns might these be that are defined as col datetime NOT NULLbirthdate babies not born yet DOD I'm not dead yetEffective date would have to force them to guessTerm date would have to force them to guessMaybe Tax Deadline Date..but that can be changed or unknownADD_TS ...well I give that a defaultUPD_TS...row has been modified (although I cheat here and give it a default anyway)Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 19:37:26
|
Wow, I hope you don't think I just feel like arguing with you today Brett, but I can easily think of an appropriate place for DATETIME NOT NULL with no default.Say you have a calendaring application, or a hotel reservation system. Say an event table.Create Table Events ( EventId int IDENTITY (1, 1) NOT NULL, EventDate SMALLDATETIME NOT NULL, UserId int NOT NULL, Constraint FK_EVENT_USER Foreign Key (UserId) REFERENCES ([user].[id])); Here we have a datetime defined as not null with no default, and it's appropriate.Of course all of the examples you listed, should have been defined as nullable. That's when you get to send it back to the developer and make 'em fix it  |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-11-22 : 19:56:55
|
| I'm ALWAYS up for a good discussionFor example..what value doe that table haveSome User had an EventThat's like my ADD_TS example of The ONLY Reason to have a datetime NOT NULL...I noticed you didn't make a defaultMeaning, for the "Event" they most know what the event, why it happened and WHEN it happened, or is going to happenUNTIL you really know...it meaningless"OH, hi AVIS IO need to Rent a car""Surely sir, when do you want it?""I don't know, but can you still take a reservation?"Try ThatBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 21:02:03
|
| lol. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 21:08:45
|
| I agree with you. I'm just saying that there are events that are meaningless until the date of the event is known. like your rental car example.so in that case we can have a reservation date not null without having to provide a default...right?am i missing a critical point here? (wouldnt be the first time lol) |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 22:32:39
|
| That's my point exactly. So the reservation table can have the date as not null, and doesn't need to provide a default. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-23 : 01:06:59
|
I'm not looking forward to next year. When I test our checkout it is so easy ... spaces are all optional  Name = 1Credit card number (OK ... I have to type a "4" here) - 4111 1111 1111 1111Expiry date 11 11 11Security number 111But after the 11th November next year ...  I hate date defaults. Harks back to putting 99/99/99 in dates as a marker-date. Just leave them NULL if the value is unknown, otherwise operators will put bogus values in to get past them. I known one would.  |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Migration Fun