SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How long is a NULL?


How long is a NULL?

Author
Message
hakan.winther
hakan.winther
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3311 Visits: 612
I got the answer correct, but only because I guessed there was a catch somewhere. I really don't like implicit conversions, especially when I get unexpected results Angry

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4336 Visits: 4408
Hugo Kornelis (12/12/2009)
I don't have a SQL Server 2000 instance running, so I can only guess there.
...
My guess is that on SQL Server 2000, the engine assumes a zero length string for NULL.

After reading this, I became interested in 'zero-length strings'. I found one mention of varchar(0) in BOL (http://msdn.microsoft.com/en-us/library/ms143359(SQL.90).aspx):
Behavior Changes to Database Engine Features in SQL Server 2005
SQL Server 2000 behavior
A zero-length string or binary value that is used as the definition of a computed table column creates a column of type varchar(0), nvarchar(0), or varbinary(0).

After that I found a SQL Server 2000 instance on one of our development servers. Some interesting results were obtained.
SELECT 'abcde' AS a, 'abcde' + NULL AS b INTO TestTable; 
go
EXEC sp_help TestTable;
go
DROP TABLE TestTable;


Column 'b' has type varchar(5).

Creating a table with a zero-length computed column:
CREATE TABLE TestTable (a VARCHAR(10), b AS SUBSTRING(a, 1, 0)) 
go
EXEC sp_help TestTable;
go
DROP TABLE TestTable;


Column 'b' has type varchar(0)!

Trying to create a copy of the above table:
CREATE TABLE TestTable (a VARCHAR(10), b AS SUBSTRING(a, 1, 0))
go
SELECT * INTO TestTable_Copy FROM TestTable


This code causes the error: "Msg 2731, Level 16, State 1, Line 1. Column 'b' has invalid width: 0."

So SQL Server 2000 sometimes uses varchar(0) data type. I was surprised :-)
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12770 Visits: 6903
Excellent question. I totally missed it, and this has finally driven me to join the COALESCE camp.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Enigma475
Enigma475
SSC Eights!
SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)

Group: General Forum Members
Points: 967 Visits: 200
Odly enough, when i ran the code I got '1234' as a result...
gopal.sharma 27953
gopal.sharma 27953
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 4
logic is here...

SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('1234'+NULL,'1234567890')

=>> 12345

SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('1234'+NULL,'1234567890')

=>> 12345

SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('1234'+NULL+Null,'1234567890')

=>> 123456


SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('1234'+NULL+Null+Null,'1234567890')

=>> 1234567

---
Logic >> '1'+'2'+'3'+'4'+'\0'+'\0'+'\0'
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25801 Visits: 12494
Ouch! I didn't know this had changed in SQLS 2005.

I wonder how much code (if any) the change has broken?

Tom

Peter Trast
Peter Trast
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 655
I cannot tell a lie... I ran the code before answering this one. :-P

I wanted to pick '1234567890' but I wanted to prove I was "right" first. What a surprise! I, too, will be using COALESCE more often...

Who knew that a NULL would have a character length of 1??? (in this case) Can't wait to stump my friends with this one!

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
sukhendass
sukhendass
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 94
SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('abcd'+NULL+1,'1234567890')
if i add any numeric value then it gives->1234567890 why?
Arto Ahlstedt
Arto Ahlstedt
Old Hand
Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)

Group: General Forum Members
Points: 384 Visits: 819
Adding a numeric value turns the expression into an int because of type precedence rules. The fact that 'abcd' does not readily convert into an int does not stop this nor generate a run-time error.

So we have a NULL of type int as the first argument and a string which is readily convertible into an int as the second argument and we get an int out of ISNULL.

Try adding one to the result. Or '1'.

Using COALESCE instead of ISNULL also returns an int. The difference is usually in the length of the varchar which is returned and this is sometimes subtle enough to sneak into production code waiting to bite you.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18657 Visits: 12426
sukhendass (7/8/2010)
SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('abcd'+NULL+1,'1234567890')
if i add any numeric value then it gives->1234567890 why?

The explanation posted by Arto is almost correct, but not entirely.

The inner expression ('abcd'+NULL+1) is evaluated left to right. The first part is 'abcd'+NULL. The 'abcd' is character (varchar(4) to be precise); the NULL is untyped, but based on context SQL Server concludes that you probably meant character as well, and uses the minimum length (varchar(1)). The result is varchar(5); because of the CONCAT_NULL_YIELDS_NULL, the value is NULL.

The second part is ( ('abcd'+NULL) + 1). The 'abcd' + NULL is, as we have seen, varchar(5); the 1 is considered to be integer. Rules of data type precedence say that in this case, varchar(5) gets converted to int - so NULL typed as varchar(5) gets converted to NULL typed as int, 1 is added, and the result is still NULL (and still typed as int)

Now, for a mental detour - what happens if you change SET CONCAT_NULL_YIELDS_NULL to OFF? In that case, the evaluation remains the same, but the result of 'abcd' + NULL is now 'abcd' (again, tpyed as varchar(5)). The + 1 again introduces conversion to integer - but for 'abcd', this conversion fails, so now you get a runtime error.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search