Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

How long is a NULL? Expand / Collapse
Author
Message
Posted Friday, December 11, 2009 11:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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*
Post #833109
Posted Friday, December 11, 2009 11:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,794, Visits: 8,009
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 6:01 AM
Points: 191, Visits: 317
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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:08 AM
Points: 3,448, Visits: 4,406
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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,794, Visits: 8,009
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

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


Post #833672
Posted Monday, December 14, 2009 2:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 6:05 AM
Points: 1,528, Visits: 5,172
Are you still running SQL 2000? The question specified SQL 2005.
Post #833674
Posted Monday, December 14, 2009 2:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 6:07 AM
Points: 341, Visits: 91
No.. SQL Server 2005.

Post #833675
Posted Monday, December 14, 2009 2:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,794, Visits: 8,009
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 6:05 AM
Points: 1,528, Visits: 5,172
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 ««1234»»»

Permissions Expand / Collapse