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»»»

The Semantics of NULL in SQL Server 2008 Expand / Collapse
Author
Message
Posted Monday, August 23, 2010 10:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:36 PM
Points: 2, Visits: 99
Comments posted to this topic are about the item The Semantics of NULL in SQL Server 2008
Post #973839
Posted Tuesday, August 24, 2010 12:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 12,181, Visits: 9,132
Nice article. It summarizes a lot of pitfalls, but I would've liked a paragraph on the behaviour of NULL in COUNT(*) and COUNT(DISTINCT *).



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #973863
Posted Tuesday, August 24, 2010 12:06 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 2:43 PM
Points: 20,455, Visits: 14,069
Nice article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #973867
Posted Tuesday, August 24, 2010 2:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:30 AM
Points: 2,389, Visits: 2,281
One way to avoid worrying about NULLs is never to use them, always declaring columns as not allowing NULLs and designating default values for "empty" or "unknown". This will save you keystrokes, especially when you want to check whether a column does not have a certain value. However, you will anyway probably have to write logic for when columns have the designated default values.

TERRIBLE!
VERY TERRIBLE!
Post #973901
Posted Tuesday, August 24, 2010 2:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:30 AM
Points: 2,389, Visits: 2,281
ajs-1102829 (8/23/2010)
Comments posted to this topic are about the item <A HREF="/articles/T-SQL/70808/">The Semantics of NULL in SQL Server 2008</A>

SQL2008???
What's the difference from the other versions of sqlserver or ANSI SQL?
Post #973902
Posted Tuesday, August 24, 2010 2:25 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:53 AM
Points: 1,528, Visits: 5,171
Isn't ANSI_NULLS set to OFF by default? So the behaviour in this article will only apply if you've explicitly set it to ON. I'd have thought that deserved more than a single-line comment right at the end!
Post #973909
Posted Tuesday, August 24, 2010 2:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 11:41 AM
Points: 13, Visits: 20
I would be interested to know what the impact of using NULL is on the storage space required for a Column. i.e. does a nullable nvarchar column save space over a non-nullable nvarchar column with an empty string in it?
Post #973910
Posted Tuesday, August 24, 2010 2:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 5,074, Visits: 8,910
paul.knibbs (8/24/2010)
Isn't ANSI_NULLS set to OFF by default? So the behaviour in this article will only apply if you've explicitly set it to ON. I'd have thought that deserved more than a single-line comment right at the end!


The article says this: "The handling of NULLs as we have summarized follows the ANSI standard. However, Transact-SQL offers an extension for null processing: If the option ANSI_NULLS is set to OFF, comparisons between nulls, such as NULL = NULL, evaluate to TRUE." Quite correctly, this implies that ANSI_NULLS is ON by default, and only turned off if that's what you specify.

John
Post #973912
Posted Tuesday, August 24, 2010 2:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 12,181, Visits: 9,132
Carlo Romagnano (8/24/2010)
One way to avoid worrying about NULLs is never to use them, always declaring columns as not allowing NULLs and designating default values for "empty" or "unknown". This will save you keystrokes, especially when you want to check whether a column does not have a certain value. However, you will anyway probably have to write logic for when columns have the designated default values.

TERRIBLE!
VERY TERRIBLE!


Constructive feedback, lalalalala.
Please elaborate why this is so terrible, that way we can all learn something.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #973915
Posted Tuesday, August 24, 2010 2:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:53 AM
Points: 1,528, Visits: 5,171
Quite correctly, this implies that ANSI_NULLS is ON by default, and only turned off if that's what you specify.

John


I understood the situation to be the opposite--e.g. ANSI_NULLS is OFF by default. The description of the SET ANSI_NULLS command in Books Online appears to agree with that, too.
Post #973923
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse