How long is a NULL?

Question

Post reply

How long is a NULL?

Arto Ahlstedt

Mr or Mrs. 500

Points: 572
More actions
December 10, 2009 at 9:13 pm

#214531

Comments posted to this topic are about the item How long is a NULL?

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Bhavesh_Patel SSCrazy Points: 2259 More actions · Answer 1

I did not really knew that.

[font="System"]Bhavesh Patel[/font]

http://bhaveshgpatel.wordpress.com/

BrainDonor SSCoach Points: 19254 More actions · Answer 2

Does anyone know why it behaves like this? It appears to be at odds with any documentation I can find.

BrainDonor.

Steve Hall
Linkedin
Blog Site

Arto Ahlstedt Mr or Mrs. 500 Points: 572 More actions · Answer 3

The documentation is slightly different; 2005 BOL says that when "replacement_value" is returned, it will be implicitly converted to the type of "check_expression" while 2000 BOL just demands both expressions to be of same type.

This was the last straw for me, I haven't typed "ISNULL" into a code window since but switched to always using COALESCE.

Which I was expecting someone would mention. Perhaps there are circumstances where usage of ISNULL is warranted, but such use would require good commenting as most think of ISNULL and COALESCE as being equal.

COALESCE returns the full 10-character "replacement_value" in both versions.

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 4

Wow, that is just bizarre. I wouldn't have called that in a million years, and I don't feel the least bit bad about getting it wrong. Great question!

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

Yes, I found this bizarre behaviour the first time I encountered it. Just did a quick experiment on a SQL 2008 installation and I see it still does the same there, so guess we're probably stuck with this now. Pity, because it's utterly non-intuitive that NULL should have a size at all!

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 6

Also a void string take 1 CHAR:

SET CONCAT_NULL_YIELDS_NULL ON

SELECT ISNULL('abcd'+''+null,'1234567890')

Result: 123456

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 7

[font="Comic Sans MS"]

as well ..

SET CONCAT_NULL_YIELDS_NULL ON

SELECT ISNULL('abcd'+NULL+NULL,'1234567890')

Results : 123456

[/font]

[font="Comic Sans MS"]--
Sabya[/font]

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 8

The question is:

it is a BUG?

... or it is by design?

... or both: it is a BUG by design!

Try this:

DECLARE @v-2 char(10)

SET CONCAT_NULL_YIELDS_NULL ON

SELECT ISNULL('abcd'+@v,'1234567890')

RESULT: 1234567890

This behavior is all right!

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 9

ahhh ... just another case of getting 'caught' by an 'implicit' converstion !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

FargoUT SSC Eights! Points: 853 More actions · Answer 10

Interesting behavior ... I had to look up the YIELDS_NULL ON before I answered the question, then still got it wrong. Had I thought about it longer, I would have realized that there is only one answer that actually fits. But I jumped the gun and chose the wrong one. Oh well, yet another one I got wrong. *beginning to question my choice in career now* 😀

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 11

Arto ahlstedt (12/10/2009)
the NULL that is appended somehow contributes to the total length of the expression with an effective length of 1.

Got to love the "somehow"...

Since NULL can be of any datatype, SQL Server always has a hard time determining what datatype was meant when it encounteres the constant NULL. In this case, since it's appended to a string, it's obviously a string as well. So that makes it a varchar (char could have been used as well, but SQL Server chooses varchar in these cases). Both of these have a default length of 1.

The result of concatenating varchar(5) ('abcde') with varchar(1) is of course varchar(6), so that is the datatype of the first argument. And since ISNULL returns the same datatype as the first argument, the result is also varchar(6).

Here is some code to repro this:

SELECT 'abcde' AS a, 'abcde' + NULL AS b INTO TestTable;

go

EXEC sp_help TestTable;

go

DROP TABLE TestTable;

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jon Cooney Old Hand Points: 329 More actions · Answer 12

SET CONCAT_NULL_YIELDS_NULL ON

DECLARE @STR AS VARCHAR(8)

SET @STR = 'abcdefghij'

SELECT @STR

SELECT LEN(@str)

SELECT LEN(@str+NULL)

SELECT ISNULL(@str+NULL,'1234567890')

--------

abcdefgh

-----------

8

-----------

NULL

---------

123456789

Okay.....

vk-kirov SSCertifiable Points: 7686 More actions · Answer 13

Hugo Kornelis

Thank you for explanation. Could you also explain why "under SQL Server 2000, the result is '1234' regardless of the number of NULLs appended"?

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 14

vk-kirov (12/12/2009)
Hugo Kornelis
Thank you for explanation. Could you also explain why "under SQL Server 2000, the result is '1234' regardless of the number of NULLs appended"?

Thanks!

First, please be aware that my explanation for the SQL Server 2005 behaviour is based on guesswork and then verified by experiments. I don't have a SQL Server 2000 instance running, so I can only guess there.

Also be aware that, as far as I know, there is no documentation about how SQL Server guesses the datatype of a NULL. As with any undocumented feature, it can change between sessions, or even between service packs or hotfixes.

My guess is that on SQL Server 2000, the engine assumes a zero length string for NULL. That, or some completely different conversion takes place - if I recall correctly, a lot of the implicit type conversion stuff was changed between 2000 and 2005.

Bottom line - if you need NULL to be the datatype you expect it to be, always use CAST(NULL AS datatype)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/