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
FargoUT
FargoUT
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 312
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* :-D
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10892 Visits: 11980
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
Jon Cooney
Jon Cooney
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 320
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
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3698 Visits: 4408
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
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10892 Visits: 11980
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
VijayakumarKP
VijayakumarKP
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
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

:-P
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2519 Visits: 6232
Are you still running SQL 2000? The question specified SQL 2005.
VijayakumarKP
VijayakumarKP
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 91
No.. SQL Server 2005.

:-P
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10892 Visits: 11980
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
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2519 Visits: 6232
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?
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