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 Monday, December 14, 2009 7:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 3, 2014 2:45 AM
Points: 2,531, Visits: 536
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

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
Post #833808
Posted Monday, December 14, 2009 7:42 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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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 behavior
A 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
Post #833831
Posted Thursday, December 17, 2009 4:51 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
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? -- Stephen Stills
Post #836055
Posted Friday, December 18, 2009 3:13 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 1:39 PM
Points: 922, Visits: 197
Odly enough, when i ran the code I got '1234' as a result...
Post #836680
Posted Wednesday, December 23, 2009 6:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 23, 2009 6:27 AM
Points: 27, Visits: 4
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'

Post #838482
Posted Thursday, February 4, 2010 1:32 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 8,741, Visits: 9,289
Ouch! I didn't know this had changed in SQLS 2005.

I wonder how much code (if any) the change has broken?


Tom
Post #859938
Posted Saturday, March 20, 2010 2:28 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
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
Post #886963
Posted Thursday, July 8, 2010 5:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:10 AM
Points: 242, Visits: 94
SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('abcd'+NULL+1,'1234567890')
if i add any numeric value then it gives->1234567890 why?
Post #949214
Posted Thursday, July 8, 2010 6:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:34 PM
Points: 319, Visits: 693
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.
Post #949256
Posted Thursday, July 8, 2010 7:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
sukhendass (7/8/2010)
SET CONCAT_NULL_YIELDS_NULL ON
SELECT 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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #949279
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse