Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NULL Equals NULL?


NULL Equals NULL?

Author
Message
Cimode
Cimode
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 11
Well, that's your opinion, Boudjakdji.

<>
If you don't see what wrong in the absurd results returned by the queries, I am afraid I can not do much for you.


<>
If you have NO NULLS in the first place, you won't have to deal with all these problems...
Cimode
Cimode
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 11
//Thanks for sharing.it is very common one ,but peple even confuses.

it might clear the confusion of those//

People do not confuse, people are either ignorant or misled by ignorants. That the case of the person who wrote this stupid article.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671

Well everyone has the right to his own opinion.

Can you take 5 minutes of your time and write an article that will "correct" the situation and share it with the world... you will also be paid for sending the article in.


Tim Chapman-218780
Tim Chapman-218780
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 115
One thing that frustrates me about how SQL Server handles NULL within unqiue constraints is that it does only allow one NULL value (as the article does a nice job of illustrating). It makes sense that it would allow >1 NULL value because they are in fact different values. Other RDBMS do allow this, so I kinda wish that SQL Server did also.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671

There's a workaround on this one... but it comes with a cost :

CREATE VIEW dbo.vwTblNameForceUnique

WITH SCHEMABINDING

AS

SELECT PrimaryKey, UniqueCol

FROM dbo.TblName

WHERE UniqueCol IS NOT NULL

GO

--Create clustered index for the view

GO

CREATE UNIQUE INDEX IX_vwTblNameForceUnique_UniqueCol ON dbo.vwTblNameForceUnique (UniqueCol)

GO


Sharon Matyk
Sharon Matyk
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 47

Gosh, some people can be very strident. "Just don't use nulls!" ? Really, show me the db that can be guaranteed to have a value in every row, every column. Some people don't have a 2nd address line ... some people don't have a middle name ... And numerically "no value" means 0.00, not null, anyway. Simple really.

Or are you advocating that for text "no value" should always be a blank, not a null? I'd like a detailed answer on the overall impact that has, especially on a large database.

It just sounds to me like some people have got tripped up by nulls in the past and now harbor serious grudges against them.


hawg
hawg
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 760

Actually, "no value" does not mean 0.00 and NULL definitely does not mean 0.00. NULL means unknown or no value.

I have worked with, and continue to work with, several databases with tables that have numeric fields where a NULL is necessary. They are necessary because in these fields a 0 (zero) value means zero (production, amount, etc.). This means we get a reading, but the reading is zero (a known value). The NULL indicates that the value is unknown. For whatever reason, we did not get a value to populate with. This could be due to equipment malfunction, communication failures, or any number of reasons.

In these cases we don't want to put a zero because that may mean we had zero production when, in fact, there is production, but for whatever reason we do not have that value at a particular point in time.

I agree that working with NULLs can cause problems but to simply say "never use them" is irresponsible. Just like everything else, use them properly and know your system and data and you can work with the system you have.



----------------------------------------------------------

01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
Tim D-404826
Tim D-404826
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 43

For quite some time we have been using a particular syntax which has worked quite well for us in determining NULL matches.

In our where clause we would use...

Where ISNULL(column,'') <> '' -- this will match all non-NULL columns

In the article, you mention that ISNULL() is not preferred to use. Can you explain why?


Cimode
Cimode
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 11
//Can you take 5 minutes of your time and write an article that will "correct" the situation and share it with the world... you will also be paid for sending the article in.//
The fundamental problem with NULLS and 3VL is not a problem that can be taken care of in 5 minutes...For more info about NULL implications, lots of work made by knowledgeable audiences have been made among which Chris DATE (Codd's assistant)

Check thethirdmanifesto.com or
Practical Issues in Database Management (Fabian PASCAL) on Amazon...
Cimode
Cimode
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 11
//One thing that frustrates me about how SQL Server handles NULL within unqiue constraints is that it does only allow one NULL value (as the article does a nice job of illustrating). It makes sense that it would allow >1 NULL value because they are in fact different values. Other RDBMS do allow this, so I kinda wish that SQL Server did also.//
NULLS are SQL's way of handling missing data and it is the worst one to do it. There are other ways to do it but current DBMS do not support it...Be it on ORACLE, DB2 or SQL Server, they are an aberration...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search