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


Upload Image Close it
Select File

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

Madhivanan's TSQL Blog

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: 


Madhivanan
3 · 40% · 12936
23
 
 
 
10
 
0
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 · 36% · 11645
  • Nice post. Thanks for sharing

    commented on May 21 2012 3:11AM
    Hardik Doshi
    20 · 9% · 2864
  • 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
    151 · 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
    3071 · 0% · 2
  • Thanks for telling about null .very good article i ever saw

    commented on May 21 2012 12:41PM
    Anilvanjre
    257 · 1% · 171
  • Thanx a lot for the gr8 post.

    commented on May 21 2012 11:21PM
    pinakee
    387 · 0% · 105
  • nice post...

    commented on May 22 2012 12:03AM
    Nirav Gandhi
    38 · 5% · 1567
  • The Complete Reference on NULL in SQL Server. Nice post. Thanks for sharing.

    commented on May 22 2012 4:29AM
    Suvendu Shekhar Giri
    171 · 1% · 289
  • Thank you so much all of you for your valuable feedbacks

    commented on May 22 2012 5:19AM
    Madhivanan
    3 · 40% · 12936
  • HI Madhivanan,

    I think nothing is missing regarding NULL.

    Thanks for sharing your experience.

    commented on Jan 18 2013 3:17AM
    Bala Krishna
    83 · 2% · 676
  • Thank you Bala Krishna for your feedback

    commented on Jan 18 2013 3:26AM
    Madhivanan
    3 · 40% · 12936
  • 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% · 898
  • 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
    83 · 2% · 676
  • 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% · 898
  • HI ErikEckhardt,

    Thanks for your explanation

    commented on Jan 21 2013 11:06PM
    Bala Krishna
    83 · 2% · 676
  • 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
    363 · 0% · 114
  • 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
    145 · 1% · 342
  • 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 · 40% · 12936
  • 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
    83 · 2% · 676
  • 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
    83 · 2% · 676
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
NULL, NULL, NULL and nothing but NULL , 5.0 out of 5 based on 23 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]