| Author |
Topic |
|
u4cast
Starting Member
16 Posts |
 Posted - 2004-03-19 : 04:21:31
|
| Has anyone got an opinion on this:If you've got a ZIP/Post code column (for example) thats varchar(10) and it's not mandatory - do you make it nullable or use a default value? Let's say there are 1 million rows and of those 100,000 will have a value in this field.I'm just curious about what people do in this situation. I use null myself but I'm willing to change if anyone has a good reason to use a default value. |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-03-19 : 04:28:52
|
| It depends on how you want things handled.Sometimes NULL returns results that you don't really expect.Try this for example: SELECT (NULL + 'ABCD')Returns NULL.I would try not to use NULL.Duane. |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-03-19 : 04:33:27
|
| Searching here for "null" will turn up some 'enthusuastic discussions' on this topic.my 2c....allow NULL. It does have a down side in that your application/db code will have to deal with NULL....but you can treat that as a learning exercise in the use of some of the more obscure SQL COMMANDS! |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-03-19 : 04:46:52
|
| So 90% of the data for that column is "missing" or "unknown"...You could create another table, add a one-to-one constraint and only populate when there is a value..DavidM"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. " |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-19 : 05:53:14
|
I'll second Andrew. But let me ask, is over there in the UK a need to have a VARCHAR(10) for ZIP Code? Here we have at most 5 characters.Just curious. --Frankhttp://www.insidesql.de |
|
|
|
u4cast
Starting Member
16 Posts |
Posted - 2004-03-19 : 06:09:24
|
| Frank - the length wasn't meant to be accurate. We use 7 characters here though, if you're interested. byrmol - Creating another table would mean using an additional join everytime I want to get details and I'd rather avoid the overhead. Although I do sort of do this anyway with a whole chunk of columns for the customer. For a single column it'd be overkill.andrew/ditch - LOL! There are the two sides of the argument. I guess it's down to personal preference. I'll stick with NULL and the coding overhead unless anyone else can add to the argument against...thanks guys |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-19 : 06:17:32
|
| The discussion how to deal with missing information has been raging since Codd invented the wheel and ANSI created this NULL thingy. It won't be solved here, but it is always funny. Thanks, btw, for explanation. Have you considered the fixed-length CHAR?--Frankhttp://www.insidesql.de |
|
|
|
u4cast
Starting Member
16 Posts |
Posted - 2004-03-19 : 06:43:18
|
| Yeah, who'd have thought that "nothing" could create such a big deal?I'll stick with varchar and bigger than 7 too because I need to deal with ZIP/Postal codes from around the world.Thanks.Paul |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-19 : 09:36:42
|
| I would use null for unknown as any other value could be misleading.Space usage shouldn't be a major consideration in database design unless you have huge volumes or need a very efficient system.It's a good idea for developers to be forced to learn how the server handles null's (notice I didn't say sql as different implementations and versions have different rules).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-03-21 : 08:35:52
|
| >> is over there in the UK a need to have a VARCHAR(10) for ZIP CodeUK zipcodes only have a handful of adresses in them and I was quite amazed when I entered my zipcode into a website once. It suggested my street-address and only came up with like 7 different suggestions, all in the same streetname just with different housenumbers! I thought that was pretty cool...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-03-22 : 01:16:40
|
| NULLs are evil, plain and simple. Avoid them like the plague. I will defer to Date and Pascal, but this subject has indeed been argued to death.See these links:http://www.dbdebunk.com/page/page/772081.htmhttp://www.dbdebunk.com/page/page/622320.htmhttp://www.dbdebunk.com/page/page/622640.htmhttp://www.dbdebunk.com/page/page/622689.htmRead this book (Practical Issues in Database Mgmt): http://www.amazon.com/exec/obidos/tg/detail/-/0201485559/qid=1079936371/sr=1-1/ref=sr_1_1/103-0607092-2988661?v=glance&s=booksIf you are going to use NULLS, definitely usa a VARCHAR for this column. If you use CHAR(10), 10 bytes will be used for every row inserted even if it is NULL. This adds up especially on tables with millions of rows.-ec |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-22 : 01:36:22
|
| Only read the first of these but>> NULL value” is a contradiction in terms; the very problem with NULLs is that they are not values and, consequently, violate Codd’s most fundamental Information Principle: In a relational database all data should be represented explicitly and in only one way, as values in tables. Consequently, tables with NULLs are not R-tables Logic mistake - data should be represented in tables doesn't mean that everything in tables represents data >> It is hard to always tell what NULLs stand for in SQL databasesThat's why you gain experience and document design principles for your database.>> UNIQUE constraints should fail on NULLs in SQL, but do notDon't see how you can say that. SQL server allows one null value - other database allow many - maybe some allow none. I think allowing many is most sensible but can live with one (or none come to that). DOesn't matter as long as you know what the behaviour is.Didn't read any further but it doesn't matter. If you think it's going to cause problems the don't allow nulls. If you understand the behaviour and are willing to handle nulls and are willing to force other people to learn then null values can be useful.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-22 : 03:31:59
|
quote:
Originally posted by eyechart
NULLs are evil, plain and simple. Avoid them like the plague. ...
I disagree with CJ Date and others who think NULL is a waste in SQL. NULL does not apply only to SQL but to almost all languages and in fact came from the others as far as concept. NULL is the abstract expression of the unknown. Without the abstract value of NULL how then do you define a completely unknown value? Some suggest defaults but give no clear idea as to how these defaults solve the NULL problem as they will still represent the unknown. quote:
If you are going to use NULLS, definitely usa a VARCHAR for this column. If you use CHAR(10), 10 bytes will be used for every row inserted even if it is NULL. This adds up especially on tables with millions of rows.
You have a point here!--Frankhttp://www.insidesql.de |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-22 : 14:20:55
|
| Where's that thread where a guy was trying to create a linked server to DB2, and had a problem with a date column, because either the dbas wouldn't make a date column nullable or the developers were to lazy...and used 01-01-0001 sa the default "null" date....painful....He kept getting errors everytime he ran across the row...couldn't even use ISDATE() to prevent it....Brett8-) |
|
|
|
mark1504
Posting Yak Master
103 Posts |
Posted - 2004-03-22 : 16:17:23
|
quote:
UK zipcodes only have a handful of adresses in them and I was quite amazed when I entered my zipcode into a website once. It suggested my street-address and only came up with like 7 different suggestions, all in the same streetname just with different housenumbers! I thought that was pretty cool...
Having worked in postal sorting offices buried under boxes of mis-sorts, I don't share your enthusiasm for British sortcodes :-( That's assuming the sender bothers to write the post code. Often he prefers the NULL option too!I should also correct a previous post. FWIW, there are between 6 and 8 characters in a post code including the middle space.) And they're as impossible for foreigners to comprehend as our love for warm beer.Considering that the UK has 1/5 of the US population why do we suffer from longer and more complicated postcodes, addresses and phone numbers??? |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-22 : 16:38:23
|
| Hey, you not only have warm beer, but also black pudding!--Frankhttp://www.insidesql.de |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-22 : 16:59:34
|
Kinda makes you thirsty thoughquote:
Refrain: In München steht ein Hofbräuhaus:Eins, zwei, g`suffa...Da läuft so manches Fässchen aus:Eins, zwei, g`suffa...Da hat so manche brave Mann:Eins, zwei, g`suffa...Gezeigt was er so vertragen kann Schon früh am Morgen fing er anUnd spät am Abend kam er heraus So schön ist`s im Hofbräuhaus. 1. Da, wo die grüne Isar fließt,wo man mit "Grüß Gott" dich grüßt,liegt meine schöne Münch`ner Stadt,die ihresgleichen nicht hat. Wasser ist billig, rein und gut,Nur verdünnt es unser Blut,Schöner sind Tropfen gold`nen Wein`s,Aber am schönsten ist eins: In München steht ein... (Refrain) 2. Da trinkt man Bier nicht aus dem glas,Da gibt`s nur "die große Maß!"Und wenn der erste Maßkrug leer,Bringt dir die Reserl bald mehr.Oft kriegt zu Haus die Frau `nen Schreck,Bleibt der Mann mal länger weg. Aber die braven Nachbarsleut`,Die wissen besser Bescheid! In München steht ein... (Refrain) 3. Wenn auch so manche schöne Stadt Sehenswürdigkeiten hat,Eins gibt es nirgendwo wie hier: Das ist das Münchener Bier.Wer dieses kleine Lied erdacht Hat so manche lange Nachtüber dem Münchener Bier studiertUnd hat es gründlich probiert. In München steht ein... (Refrain)
Brett8-) |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-22 : 17:42:41
|
Nah, every nation has its cretins. Impossible to argue against this.  To speak with one of the greatest German:"Against stupidity the gods themselves struggle in vain." Happy googling!  --Frankhttp://www.insidesql.de |
|
|
|
Next Page
|
NULL opinion?