December 14, 2009 at 2:38 am
SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('abcd'+NULL,'1234567890')
I got answer '1234'
Can you please explain???
Thanks and Regards
vijayakumar.P
😛
December 14, 2009 at 2:39 am
Are you still running SQL 2000? The question specified SQL 2005.
December 14, 2009 at 2:43 am
No.. SQL Server 2005.
😛
December 14, 2009 at 2:48 am
What compatibility level are you using?
SELECT compatibility_level
FROM sys.databases
WHERE name = DB_NAME();
December 14, 2009 at 2:51 am
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?
December 14, 2009 at 7:24 am
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
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
December 14, 2009 at 7:42 am
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 behaviorA 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 🙂
December 17, 2009 at 4:51 pm
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
December 18, 2009 at 3:13 pm
Odly enough, when i ran the code I got '1234' as a result...
December 23, 2009 at 6:36 am
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'
February 4, 2010 at 1:32 pm
Ouch! I didn't know this had changed in SQLS 2005.
I wonder how much code (if any) the change has broken?
Tom
March 20, 2010 at 2:28 pm
I cannot tell a lie... I ran the code before answering this one. 😛
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
July 8, 2010 at 5:54 am
SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('abcd'+NULL+1,'1234567890')
if i add any numeric value then it gives->1234567890 why?
July 8, 2010 at 6:56 am
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.
July 8, 2010 at 7:34 am
sukhendass (7/8/2010)
SET CONCAT_NULL_YIELDS_NULL ONSELECT 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.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply