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
 General SQL Server Forums
 New to SQL Server Programming
 not null
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

jeff06
Posting Yak Master

166 Posts

Posted - 04/09/2007 :  17:36:57  Show Profile  Reply with Quote
X !='NULL'
does not work, what is right expression for " is not NULL" thanks.

Jeff

tkizer
Almighty SQL Goddess

USA
36613 Posts

Posted - 04/09/2007 :  17:38:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well it is "IS NOT NULL"!

...
WHERE SomeColumn IS NOT NULL
...

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/09/2007 :  22:17:57  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
This is ONE good reason to avoid NULL! You can't use normal relational syntax!

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 04/10/2007 :  00:30:36  Show Profile  Visit jezemine's Homepage  Reply with Quote
not this debate again...


www.elsasoft.org
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/10/2007 :  12:05:44  Show Profile  Visit russell's Homepage  Reply with Quote
SPAM

quote:
Originally posted by SQLUSA

This is ONE good reason to avoid NULL! You can't use normal relational syntax!

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com


SPAM

the quoted post is not intended to be helpful, just to promote the website in the signature
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 04/10/2007 :  12:13:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yeah, I noticed he has begun the promoting in this site too...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

MutantNinjaLoungeSinger
Starting Member

14 Posts

Posted - 04/10/2007 :  14:32:49  Show Profile  Reply with Quote
quote:

This is ONE good reason to avoid NULL! You can't use normal relational syntax!


And avoiding it dis-allows you to represent an unedited or unspecified field.
(short of using "magic" numbers or values, I suppose)

Just my opinion, but it's hard to take training from a guy seriously who has an irrational fear of null values.
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/10/2007 :  20:07:49  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
NULLS are good for nothing!

Most columns just can be set to a default like 0 or empty space.

If you really need to know if a cell contain something, you can add the IsColumnX bit flag.

What do you think what is SQL Server doing internally? It has a bit flag!

If you have your own, you can use NORMAL RELATIONAL LOGIC!

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 04/10/2007 :  23:27:45  Show Profile  Visit jezemine's Homepage  Reply with Quote
NULL is not the same as 0 or empty space.

I have two boxes. One contains nothing with certainty. Another contains an unknown item: perhaps nothing, perhaps a diamond necklace worth $100k, perhaps a dog turd. The point is, it's unknown what, if anything, the box contains.

which box would you rather have? Can you see there's a difference?


www.elsasoft.org
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/10/2007 :  23:54:29  Show Profile  Reply with Quote
quote:
Originally posted by SQLUSA

NULLS are good for nothing!

Most columns just can be set to a default like 0 or empty space.

If you really need to know if a cell contain something, you can add the IsColumnX bit flag.

What do you think what is SQL Server doing internally? It has a bit flag!

If you have your own, you can use NORMAL RELATIONAL LOGIC!

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com


So if a column cannot be set to 0 or empty space, what do you suggest?

Why would I want to add a bit column that basically just tells me that a column is null, when I could just set the column null? What advantage would that have? It just seems like extra work to me.






CODO ERGO SUM
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/11/2007 :  05:20:01  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
That way, you don't get all the inconsistencies you get with NULL.

Example: you could write

and IsColumnX = 1

instead of

and ColumnX is null

Also you don't get NULL result if ColumnV is NULL:

SELECT ColumnA+' - '+ ColumnB +' : '+ ColumnV



Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 04/11/2007 :  05:26:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by SQLUSA

That way, you don't get all the inconsistencies you get with NULL.

Example: you could write

and IsColumnX = 1

instead of

and ColumnX ColumnV is null

Also you don't get NULL result if ColumnV is NULL:

SELECT ColumnA+' - '+ ColumnB +' : '+ ColumnV

With that approach, you can't concatenate the string with your IsColumnX = 1 anyway!


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 04/11/2007 05:30:05
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 04/11/2007 :  05:27:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If you know ColumnV accepts NULLs, write
SELECT ColumnA + ' - ' + ColumnB + COALESCE(' : ' + ColumnV, '')

With your approach (with extra bit column) the query would look something like

SELECT ColumnA + ' - ' + ColumnB + CASE WHEN ColumnX = 1 THEN '' ELSE ' : ' + ColumnV END

What is the benefit of that?


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 04/11/2007 05:29:48
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/11/2007 :  07:05:51  Show Profile  Reply with Quote
quote:
Originally posted by SQLUSA

That way, you don't get all the inconsistencies you get with NULL.

Example: you could write

and IsColumnX = 1

instead of

and ColumnX is null

Also you don't get NULL result if ColumnV is NULL:

SELECT ColumnA+' - '+ ColumnB +' : '+ ColumnV



Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com



I see you managed to not answer my first question:
"So if a column cannot be set to 0 or empty space, what do you suggest?"

So once again, what value would you put in a column if it cannot be set to 0 or empty space?

I see no advantage in coding this:
and IsColumnX = 1
instead of
and ColumnX is null

I see nothing wrong with getting a null result when you add together two columns and one is null.








CODO ERGO SUM
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 04/11/2007 :  07:19:55  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
The only benefit of not having NULL columns is that SQL Server will not create NULL bitmap for each row. But that advantage is easily nullified by your suggestion of adding bit column plus other complexities introduced in the code. The code becomes unreadable and difficult to maintain, at the best.

I don't see any reason to fear about NULLs if you know how to handle them and if you don't know how to handle them, then......I guess you are in the wrong field.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1535 Posts

Posted - 04/11/2007 :  07:44:33  Show Profile  Visit JimL's Homepage  Reply with Quote
quote:
I don't see any reason to fear about NULLs if you know how to handle them and if you don't know how to handle them, then......I guess you are in the wrong field.



Can you say Smack!

Jim
Users <> Logic
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 04/11/2007 :  09:38:41  Show Profile  Visit jezemine's Homepage  Reply with Quote
But that advantage is easily nullified...

how can it be nullified if he doesn't allow NULL?


www.elsasoft.org
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/11/2007 :  21:48:54  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
What if the column cannot be defaulted to 0 or empty space?

If you set the IsColumnX flag 0, that means it is not populated, so you can set it to anything.

Oh yeah, "ABC" + NULL + "XYZ" = NULL !!! Very logical! The triumph of science and technology! Even a 2nd grader would know that it does not make sense! IT IS ILLOGICAL!!!




Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/11/2007 :  21:53:59  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
>I see no advantage in coding this:
>and IsColumnX = 1
>instead of
>and ColumnX is null

The first is normal algorithmic coding, the second is GARBAGE! What does "is" mean? A fruit is blue, a fruit is red, a fruit is NULL?




Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 04/11/2007 :  22:42:20  Show Profile  Visit jezemine's Homepage  Reply with Quote
quote:
Originally posted by SQLUSA

Oh yeah, "ABC" + NULL + "XYZ" = NULL !!! Very logical! The triumph of science and technology! Even a 2nd grader would know that it does not make sense! IT IS ILLOGICAL!!!



such pronouncements on your part demonstrate that your understanding of NULL is severely lacking. NULL means UNKNOWN. And you are a "trainer" in SQL? yikes.

so tell me, what does 1 + UNKNOWN equate to? you think it's 1? That would only be true if the unknown value happened to be 0. But it's not. It's unknown. Because it's unknown, the sum must also be unknown, and therefore NULL, because that's what NULL means.

NULL means UNKNOWN. got it?


www.elsasoft.org
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 04/12/2007 :  00:04:19  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
quote:
Originally posted by SQLUSA

>I see no advantage in coding this:
>and IsColumnX = 1
>instead of
>and ColumnX is null

The first is normal algorithmic coding, the second is GARBAGE! What does "is" mean? A fruit is blue, a fruit is red, a fruit is NULL?




Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com



Thank GOD!!

You were not part of ANSI-92 committee...what would have been life without NULLS?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000