<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Adolfo J. Socorro,...
»
The Semantics of NULL in SQL Server 2008
69 posts, Page 1 of 7
1
2
3
4
5
»
»»
The Semantics of NULL in SQL Server 2008
Rate Topic
Display Mode
Topic Options
Author
Message
Adolfo J. Socorro, Ph.D.
Adolfo J. Socorro, Ph.D.
Posted Monday, August 23, 2010 10:04 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:16 AM
Points: 2,
Visits: 73
Comments posted to this topic are about the item
The Semantics of NULL in SQL Server 2008
Post #973839
Koen Verbeeck
Koen Verbeeck
Posted Tuesday, August 24, 2010 12:00 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 9,372,
Visits: 6,469
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
SQLRNNR
SQLRNNR
Posted Tuesday, August 24, 2010 12:06 AM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733,
Visits: 12,332
Nice article.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
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
Carlo Romagnano
Carlo Romagnano
Posted Tuesday, August 24, 2010 2:08 AM
SSCommitted
Group: General Forum Members
Last Login: Today @ 1:51 AM
Points: 1,971,
Visits: 1,821
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
Carlo Romagnano
Carlo Romagnano
Posted Tuesday, August 24, 2010 2:11 AM
SSCommitted
Group: General Forum Members
Last Login: Today @ 1:51 AM
Points: 1,971,
Visits: 1,821
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
paul.knibbs
paul.knibbs
Posted Tuesday, August 24, 2010 2:25 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 3:43 AM
Points: 1,257,
Visits: 4,255
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
paulm-771594
paulm-771594
Posted Tuesday, August 24, 2010 2:26 AM
Grasshopper
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
John Mitchell-245523
John Mitchell-245523
Posted Tuesday, August 24, 2010 2:30 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 4,427,
Visits: 7,193
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
Koen Verbeeck
Koen Verbeeck
Posted Tuesday, August 24, 2010 2:35 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 9,372,
Visits: 6,469
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
paul.knibbs
paul.knibbs
Posted Tuesday, August 24, 2010 2:50 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 3:43 AM
Points: 1,257,
Visits: 4,255
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 »
69 posts, Page 1 of 7
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
