Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 NULL opinion?

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.
Go to Top of Page

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!
Go to Top of Page

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.. "
Go to Top of Page

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.




--Frank
http://www.insidesql.de
Go to Top of Page

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
Go to Top of Page

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?

--Frank
http://www.insidesql.de
Go to Top of Page

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
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-19 : 06:53:35
MUCH ado about nothing. Here is the ultimate programming language
http://www.baetzler.de/humor/null.html



--Frank
http://www.insidesql.de
Go to Top of Page

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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-19 : 11:46:26
NULLS!

Not be existentialist or anything, but nothing has been around a lot longer than anything....oh wait, that statement would probably evaluate to FALSE....



One of my favorite articles, and authors

http://www.db2mag.com/db_area/archives/2000/q1/programmer.shtml



Brett

8-)
Go to Top of Page

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 Code

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

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

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.htm
http://www.dbdebunk.com/page/page/622320.htm
http://www.dbdebunk.com/page/page/622640.htm
http://www.dbdebunk.com/page/page/622689.htm

Read 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=books


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.


-ec
Go to Top of Page

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 databases

That's why you gain experience and document design principles for your database.

>> UNIQUE constraints should fail on NULLs in SQL, but do not

Don'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.
Go to Top of Page

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!

--Frank
http://www.insidesql.de
Go to Top of Page

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





Brett

8-)
Go to Top of Page

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



Go to Top of Page

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!


--Frank
http://www.insidesql.de
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-22 : 16:47:06
Hey then there's always blutwurst...

http://www.germandeli.com/geierblutind.html



Brett

8-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-22 : 16:59:34
Kinda makes you thirsty though

quote:

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 an
Und 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 studiert
Und hat es gründlich probiert.
In München steht ein... (Refrain)





Brett

8-)
Go to Top of Page

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!




--Frank
http://www.insidesql.de
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -