How long is a NULL?

Click here to monitor SSC

<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>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<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

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Arto Ahlstedt » How long is a NULL?

Add to briefcase

30 posts, Page 2 of 3

How long is a NULL?

Expand / Collapse

Author

Message

Posted Friday, December 11, 2009 11:19 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, October 11, 2011 1:51 PM
Points: 341, Visits: 311

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* BigGrin

BigGrin

Post #833109

Posted Friday, December 11, 2009 11:26 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 5,232, Visits: 7,022

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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #833114

Posted Friday, December 11, 2009 1:23 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 2:37 AM
Points: 191, Visits: 307

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.....

Post #833173

Posted Saturday, December 12, 2009 7:09 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 1:32 AM
Points: 3,187, Visits: 4,140

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"?

Post #833324

Posted Saturday, December 12, 2009 12:09 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 5,232, Visits: 7,022

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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #833388

Posted Monday, December 14, 2009 2:38 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 6:07 AM
Points: 341, Visits: 91

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

I got answer '1234'

Can you please explain???

Thanks and Regards
vijayakumar.P

Tongue

Tongue

Post #833672

Posted Monday, December 14, 2009 2:39 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:56 AM
Points: 1,256, Visits: 4,253

Are you still running SQL 2000? The question specified SQL 2005.

Post #833674

Posted Monday, December 14, 2009 2:43 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 6:07 AM
Points: 341, Visits: 91

No.. SQL Server 2005.

Tongue

Tongue

Post #833675

Posted Monday, December 14, 2009 2:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 5,232, Visits: 7,022

What compatibility level are you using?
SELECT compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

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

Post #833678

Posted Monday, December 14, 2009 2:51 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:56 AM
Points: 1,256, Visits: 4,253

Just tested it with a database in SQL 2000 compatibility mode (80) and it still returned 12345, at least on my system. Could it have changed in a SQL service pack?

Post #833680

« Prev Topic | Next Topic »

Add to briefcase

30 posts, Page 2 of 3

Permissions

Expand / Collapse