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
 General SQL Server Forums
 New to SQL Server Programming
 not null

Author  Topic 

jeff06
Posting Yak Master

166 Posts

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

Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-09 : 17:38:42
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 - 2007-04-09 : 22:17:57
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
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-10 : 00:30:36
not this debate again...


www.elsasoft.org
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-04-10 : 12:05:44
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

30421 Posts

Posted - 2007-04-10 : 12:13:26
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 - 2007-04-10 : 14:32:49
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 - 2007-04-10 : 20:07:49
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
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-10 : 23:27:45
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)

7020 Posts

Posted - 2007-04-10 : 23:54:29
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 - 2007-04-11 : 05:20:01
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

30421 Posts

Posted - 2007-04-11 : 05:26:03
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 05:27:21
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-11 : 07:05:51
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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-11 : 07:19:55
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

1537 Posts

Posted - 2007-04-11 : 07:44:33
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
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-11 : 09:38:41
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 - 2007-04-11 : 21:48:54
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 - 2007-04-11 : 21:53:59
>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
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-11 : 22:42:20
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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-12 : 00:04:19
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
  Previous Page&nsp;  Next Page

- Advertisement -