SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 NULL opinion?
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 4

u4cast
Starting Member

United Kingdom
16 Posts

Posted - 03/19/2004 :  04:21:31  Show Profile  Visit u4cast's Homepage
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
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 03/19/2004 :  04:28:52  Show Profile  Visit ditch's Homepage
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
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 03/19/2004 :  04:33:27  Show Profile
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

Australia
1591 Posts

Posted - 03/19/2004 :  04:46:52  Show Profile
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

Switzerland
413 Posts

Posted - 03/19/2004 :  05:53:14  Show Profile  Visit Frank Kalis's Homepage
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

United Kingdom
16 Posts

Posted - 03/19/2004 :  06:09:24  Show Profile  Visit u4cast's Homepage
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

Switzerland
413 Posts

Posted - 03/19/2004 :  06:17:32  Show Profile  Visit Frank Kalis's Homepage
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

United Kingdom
16 Posts

Posted - 03/19/2004 :  06:43:18  Show Profile  Visit u4cast's Homepage
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

Switzerland
413 Posts

Posted - 03/19/2004 :  06:53:35  Show Profile  Visit Frank Kalis's Homepage
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

United Kingdom
12543 Posts

Posted - 03/19/2004 :  09:36:42  Show Profile  Visit nr's Homepage
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 - 03/19/2004 :  11:46:26  Show Profile
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-)

Edited by - X002548 on 03/19/2004 12:06:27
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 03/21/2004 :  08:35:52  Show Profile
>> 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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/22/2004 :  01:16:40  Show Profile
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

Edited by - eyechart on 03/22/2004 01:24:31
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 03/22/2004 :  01:36:22  Show Profile  Visit nr's Homepage
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

Switzerland
413 Posts

Posted - 03/22/2004 :  03:31:59  Show Profile  Visit Frank Kalis's Homepage
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

Edited by - Frank Kalis on 03/22/2004 03:39:40
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/22/2004 :  14:20:55  Show Profile
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

United Kingdom
103 Posts

Posted - 03/22/2004 :  16:17:23  Show Profile  Visit mark1504's Homepage  Send mark1504 a Yahoo! Message
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???




Edited by - mark1504 on 03/22/2004 16:20:43
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 03/22/2004 :  16:38:23  Show Profile  Visit Frank Kalis's Homepage
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 - 03/22/2004 :  16:47:06  Show Profile
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 - 03/22/2004 :  16:59:34  Show Profile
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

Switzerland
413 Posts

Posted - 03/22/2004 :  17:42:41  Show Profile  Visit Frank Kalis's Homepage
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
Page: of 4 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000