Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

NULL Equals NULL? Expand / Collapse
Author
Message
Posted Monday, February 26, 2007 5:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 11, 2011 3:56 PM
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...
Post #347389
Posted Monday, February 26, 2007 5:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 11, 2011 3:56 PM
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.
Post #347390
Posted Monday, February 26, 2007 6:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 21,385, Visits: 9,603

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.

Post #347391
Posted Monday, February 26, 2007 6:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 12, 2009 7:37 AM
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.
Post #347396
Posted Monday, February 26, 2007 6:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 21,385, Visits: 9,603

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

Post #347400
Posted Monday, February 26, 2007 6:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 26, 2010 10:00 AM
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.

Post #347402
Posted Monday, February 26, 2007 7:12 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 436, Visits: 591

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
Post #347406
Posted Monday, February 26, 2007 7:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 20, 2014 12:44 PM
Points: 68, Visits: 28

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?

Post #347416
Posted Monday, February 26, 2007 7:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 11, 2011 3:56 PM
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...
Post #347421
Posted Monday, February 26, 2007 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 11, 2011 3:56 PM
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...
Post #347423
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse