Madhivanan's TSQL Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

sql_server	217
t-sql	211
tsql	116
sqlserver	96
BRH	78
#SQLServer	66
#TSQL	56
SQL Server	34
function	11
SSMS	9

Archive · View All

August 2007	17
August 2010	8
June 2012	7
June 2011	7
November 2007	7
August 2012	6
May 2012	6
November 2011	6
August 2011	6
October 2011	6

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (387) · Preferences

NULL, NULL, NULL and nothing but NULL

May 21 2012 12:00AM by Madhivanan

NULL. This is a magical word in Database programming. Here are some interesting facts about NULL in SQL Server.

1 NULL can be defined as absense of value, undefined, or the value which is unknown at this point of time.
2 All datatypes can be defined with NULL constraint
3 Direct usage of artimetic or logical operations on NULL will not work as expected
4 The system functions ISNULL, COALESE and NULLIF are used only to deal with NULL
5 NOT NULL constraint cannot be defined for a computed column until it is also PERSISTED
6 The only datatypes that will interpret NULL differently are rowversion and timestamp

Run this code and see the output

declare @rv rowversion , @ts timestamp 
select @rv=null, @ts=null
select @rv as rowversion,@ts as timestamp

Output is

rowversion         timestamp
------------------ ------------------
0x                 0x

7 NULL=NULL will never be true unless SET ANSI_NULLS is OFF

While the first query returns nothing, the second will return 6

--Query 1
set ansi_nulls on
select 6
where null=null

--Query 2
set ansi_nulls off
select 6
where null=null

The condition WHERE col IS NULL will not be affected by the above setting

8 The default datatype of NULL is INT. Refer Default datatype of NULL for more information
9 Column with UNIQUE constraint will allow only one NULL value in SQL Server (But not true in other RDBMSs)
10 NULL will make SQL Server to use short circuit logic in some cases

Consider this example

select 6/0/null

select null/6/0

While the first query throws an error the second query returns NULL

11 The value NULL is not equal to string value 'NULL'
12 By default NULL values come first when a column is ordered in ascending order and come last when ordered in descending order
13 If you dont use GROUP BY clause, the aggregate functions will always return single value (NULL) when the condition is false

select sum(col) as col from
(
	select 45 as col
) as t
where 1=0

The above returns NULL

14 NULL values are by default omitted in all aggregate functions

Consider the following example

select sum(col) as col_cum,count(col) as col_count,avg(col*1.0) as col_avg from
(
	select 1 as col union all
	select null as col union all
	select 2 as col union all
	select 3 as col 
) as t

The output is

col_cum     col_count   col_avg
----------- ----------- -----------
6           3           2.000000

15 Agrregate functions cannot be directly applied over NULL value

This code

select sum(null) as null_sum

returns the following error

Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.

You can wonder whether NULL is a datatype

Tags:

Previous: Use system functions cleverly
Next: CHAR vs VARCHAR - Trailing spaces

Madhivanan

3 · 39% · 12441

23 Readers Liked this

	Olga Medvedeva Liked this on 5/21/2012 12:19:00 AM Profile · Blog · Twitter
	Chintak Chhapia Liked this on 5/21/2012 12:42:00 AM Profile · Blog · Facebook
	Manas Ranjan Dash Liked this on 5/21/2012 1:00:00 AM Profile · Blog · Facebook · Twitter
	Dattatrey Sindol (Datta) Liked this on 5/21/2012 1:06:00 AM Profile · Blog
	Jacob Sebastian Liked this on 5/21/2012 1:24:00 AM Profile · Blog · Facebook · Twitter
	Madhivanan Liked this on 5/21/2012 1:32:00 AM Profile · Blog · Facebook · Twitter
	Guru Samy Liked this on 5/22/2012 11:44:00 PM Profile · Blog
	Hari Kumar Liked this on 5/25/2012 4:30:00 AM Profile
	Bala Krishna Liked this on 1/18/2013 3:14:00 AM Profile
	Bogdan Sahlean Liked this on 1/20/2013 11:20:00 AM Profile
	arvindkumar Liked this on 1/25/2013 6:44:00 AM Profile
	Anilvanjre Liked this on 5/21/2012 12:39:00 PM Profile
	ErikEckhardt Liked this on 5/21/2012 4:42:00 PM Profile
	Nirav Gandhi Liked this on 5/22/2012 12:02:00 AM Profile · Blog · Facebook · Twitter
	Suvendu Shekhar Giri Liked this on 5/22/2012 4:25:00 AM Profile · Facebook · Twitter
	Krishnrajsinh Rana Liked this on 5/22/2012 1:13:00 PM Profile
	kingkong0924 Liked this on 5/22/2012 8:17:00 PM Profile
	Nakul Vachhrajani Liked this on 5/21/2012 2:18:00 AM Profile · Blog · Facebook · Twitter
	Hardik Doshi Liked this on 5/21/2012 3:11:00 AM Profile · Facebook
	Vishal Gajjar Liked this on 5/21/2012 4:06:00 AM Profile · Blog
	Ramesh Velayudhan Liked this on 5/21/2012 5:46:00 AM Profile
	Nirav Liked this on 5/21/2012 9:38:00 AM Profile · Blog · Facebook · Twitter
	Dave Vroman Liked this on 5/21/2012 11:28:00 AM Profile

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

21 Comments

Very informative, very comprehensive and it MUST be compulsory for everyone working on SQL Server to read this post.

Thank-you, Madhivanan!

commented on May 21 2012 2:19AM

Nakul Vachhrajani

4 · 33% · 10587

Nice post. Thanks for sharing

commented on May 21 2012 3:11AM

Hardik Doshi

20 · 9% · 2839

One of the best post I ever read Madhi!

I indeed envy your understanding of NULL, well after reading the post, I think we are even now!

Wow!

commented on May 21 2012 8:49AM

Pinal Dave

146 · 1% · 326

Very nice and very informative article. I am passing this link to all my juniors for the better understanding of NULL. Thank you Madhivanan!

commented on May 21 2012 9:10AM

koushik.niranjan

2895 · 0% · 2

Thanks for telling about null .very good article i ever saw

commented on May 21 2012 12:41PM

Anilvanjre

257 · 1% · 170

Thanx a lot for the gr8 post.

commented on May 21 2012 11:21PM

pinakee

381 · 0% · 105

nice post...

commented on May 22 2012 12:03AM

Nirav Gandhi

38 · 5% · 1503

The Complete Reference on NULL in SQL Server. Nice post. Thanks for sharing.

commented on May 22 2012 4:29AM

Suvendu Shekhar Giri

168 · 1% · 289

Thank you so much all of you for your valuable feedbacks

commented on May 22 2012 5:19AM

Madhivanan

3 · 39% · 12441

HI Madhivanan,

I think nothing is missing regarding NULL.

Thanks for sharing your experience.

commented on Jan 18 2013 3:17AM

Bala Krishna

85 · 2% · 642

Thank you Bala Krishna for your feedback

commented on Jan 18 2013 3:26AM

Madhivanan

3 · 39% · 12441

The result for timestamp and rowversion is interesting. You can achieve this value another way, too:

Substring(Convert(varbinary(10), 0), 0, 0) -- yields 0x

This is like an empty string that has length 0--it is essentially a varbinary(0) value (though that itself is disallowed). If you create a column from this expression it is interpreted as varbinary(1).

In Oracle, the empty string is equivalent to NULL: '' IS NULL will be true. And '' = '' will be false. The behavior of rowversion is like some strange mirrored and translated echo of this that makes NULL become an empty string (empty varbinary).

commented on Jan 20 2013 6:33PM

ErikEckhardt

65 · 3% · 887

Hi ErikEckhardt,

See the following result set once..

select Substring(Convert(varbinary(10), 0), 0, 0) -- 0x

select Substring(Convert(varbinary(10), 1), 1, 1) -- 0x00

select Substring(Convert(varbinary(10), 2), 2, 2) -- 0x0000

select Substring(Convert(varbinary(10), 3), 3, 3) -- 0x0003

select Substring(Convert(varbinary(10), 4), 4, 4) -- 0x04

select Substring(Convert(varbinary(10), 5), 5, 5) -- 0x

select Substring(Convert(varbinary(10), 6), 6, 6) -- 0x

select Substring(Convert(varbinary(10), 7), 7, 7) -- 0x

select Substring(Convert(varbinary(10), 8), 8, 8) -- 0x

select Substring(Convert(varbinary(10), 9), 9, 9) -- 0x

select Substring(Convert(varbinary(10), 10), 10, 10) -- 0x

select Substring(Convert(varbinary(10), 11), 11, 11) -- 0x ... ... ... ...

select Substring(Convert(varbinary(10), 45), 45, 45) -- 0x ... ... ... ...

select Substring(Convert(varbinary(10), 100), 100, 100) -- 0x

For the length 1,2,3,4 Result sets was different ,But rest of the cases Result sets are SAME.

Thanks for original post.

commented on Jan 20 2013 9:33PM

Bala Krishna

85 · 2% · 642

Bala Krishna,

None of those results are surprising. If you examine the interim result of the Convert expression in each case, you will see that due to the number-literal being interpreted as an int, the resulting expression is never longer than 4 bytes, e.g. 0x01020304. All Substring indexes on these varbinary values past 4 will then yield 0x exactly like with varchar where for example Substring('abcd', 5, 1) will yield an empty string (''). No mystery here.

commented on Jan 21 2013 5:03PM

ErikEckhardt

65 · 3% · 887

HI ErikEckhardt,

Thanks for your explanation

commented on Jan 21 2013 11:06PM

Bala Krishna

85 · 2% · 642

Nice work.

I would add informations regarding SQL Server's settings: ANSINULLS, CONCATNULLYIELDSNULL, ANSIDEFAULTS, ANSINULLDFLTON, ANSINULLDFLTOFF, ANSIPADDING, ANSI_WARNINGS, ARITHABORT, ARITHIGNORE. Also, it would be interesting too see the behaviour of some SQL Server's statements,functions regarding NULL.

Ex. 1

DECLARE @m NVARCHAR(200)
RAISERROR(@m,16,1) -- NULL error message

Ex. 2

CASE ... without else END is translated into CASE ... ELSE NULL END

commented on Jan 22 2013 5:05AM

Bogdan Sahlean

388 · 0% · 104

Madhivanan, Regarding the point behavior of NULL values, when order by clause is present, I wanted to add that -
In sql server NULL values (Unknown) is always treated as less than known values. So in case of asc NULL values comes first and in case of desc null values comes lasts.

Regards, Jeetendra

commented on Jan 23 2013 12:43AM

Jeetendra

153 · 1% · 315

Bogdan Sahlean , thanks for the code.

Jeetendra, Thanks for the information. This is specific to SQL Server. It is exactly opposite in ORACLE

commented on Jan 23 2013 1:17AM

Madhivanan

3 · 39% · 12441

HI Jeetendra,

Thanks for adding your thoughts.But same thing Madhivanan has included in Point No:12. Please correct me if am wrong

Any how thanks for sharing your thoughts.

commented on Jan 23 2013 1:17AM

Bala Krishna

85 · 2% · 642

HI Bogdan Sahlean,

Please find the following code as well.

DECLARE @STR1 VARCHAR(20) DECLARE @STR2 VARCHAR(20) SET @STR1='Bala krishna' SET @STR2=NULL -- results is null(general behaviour of null) SELECT @STR1+@STR2 -- after changing some settings by running following code set concatnullyields_null off -- Getting the result SELECT @STR1+@STR2

Thanks for information.

commented on Jan 23 2013 1:28AM

Bala Krishna

85 · 2% · 642

Previous 1 | 2 Next

Your Comment

Sign Up or Login to post a comment.

"NULL, NULL, NULL and nothing but NULL" rated 5 out of 5 by 23 readers +Madhivanan